Need a little help in converting Oracle SQL (Access 2003)

Topic

New Reply

I have a short SQL statement that I would like to use in Access, but I am not quite sure of the Access SQL equivalent.

select * from EMPLOYEE_TABLE start with USER_ID in (‘Bubba’,’Babble’) connect by SUPV_ID = prior USER_ID

I am not sure how to code the START WITH and CONNECT BY portions of the SQL. I do have a not so elegant way of doing this by joining the same table in a downward string 11 times & takes 4 queries to give me the desired output. (to cover all bases, I need to expand this to 3 more table listings)

Basically what this does is gives me the Employee_Table listing all employees that would roll up to one of the supervisors listed in the IN statement. As there can be many levels of management between a top tier manager & the lowest worker bee (and dummy roll up ID’s for large orgs), the SQL sample I listed above is much easier to maintain & I think accurate then what I slapped together in Access.

So if anyone can provide me some guidance, that would be much appreciated!

PS: This is currently is being run against a Access table.

Reply | Quote

Replies

The START WITH … CONNECT BY construct in Oracle SQL provides recursion. There is no equivalent for this in Jet SQL, so you must either resort to joining the table to itself as many times as the “deepest” hierarchichal relation requires, or use VBA – you could write a recursive function that returns the top manager for an employee and use that in your query.

Reply | Quote

Thanks Hans.

I guess I should re-state my goal. To get a list of employees that eventually map up to the person(s) listed in the IN statement.

So in my example, there are 300 employees that eventually map up to Bubba & 400 up to Babble. So what I want to return is a list of 700 employee id’s. (then use that list to limit other queries)

May need work on creating a recursive VBA function as you suggest. (yeeach) At least there are only 20K +/- or so records in the table.

Reply | Quote

Let us know if you need help with the recursive function. It’ll be slow, though. Why not leave the whole thing in Oracle? The START WITH … CONNECT BY construct is much more convenient than any solution in Access.

Reply | Quote

Hi there

Without the table structure I have taken the liberty of doing a little demo and you may be able to get some ideas from it. On first reading this I have made the assumption that the Oracle table had a Self Join, something that Access is unable to do. You can use the SQL Editor to create temporary tables giving them pseudonyms to assist syntax. I have have a habit of using Oracle syntax as this was what I was first taught and I prefer it and it works very well in MS Access

The image below depicts an employee table with who reports to who. The SQL selects the unique staff records from tblEmployees calls it a and then creates a temp table with the same content called b then matches the staff against manager. This is a simplified example but has very powerful possibilities but it appears to be doing something similar to what you are asking about.

I have added the code to the image to assist, note that Fred reports to himself and therefore the Manager field is empty

Reply | Quote

Thanks Hans & Jerry.

Hans, we have a few Access DB’s where we are connected to the Oracle tables through a ODBC connection. Some of this data is then joined to local Access tables or at times we import the Oracle table if small enough to work with locally as the ODBC connection sometimes has much to be desired (IE: A query that may take over a minute or 2 to run bombs). Other tables may have 100’s of millions of records. I don’t have very many SQL tools currently available to me where I can create & merge the data with the local Access data. Currently I am using Oracle SQL Developer when I can leave Access out of the process.

In the Example I am running against a employee table where I am trying to get a list of all employees that roll up to a specific or list of manager ID’s. Here is a little more detailed of an example:

Emp……Mgr
Joe
Pam…..Joe
Mary…..Pam
Pat……..Mary
Fred…..Pat
Scot…..Fred
Larry…Scot
Jim……Larry
John…Larry

So in my query if I enter Mary, I would want a list of Pat, Fred, Scot, Larry, Jim & John. Only Pat reports directly to Mary, the rest report to someone that reports to someone that reports to Mary.

In one example I am tryting to work on I have a Access table with 1.5 million or so records that gets added to monthly from another job that does run against the Oracle tables, but may take 2 hours to run. I have a need to then produce some monthly / quarterly or yearly metrics against this Access data where the Employee heirarchy would come into play (IE: order count for a date range of all people that roll up to Fred)

Jerry, your example does work for me except for limiting where the top tier starts. Here is my version of your example:

SELECT DISTINCT a.HRID, a.EMAIL_HANDLE, a.EMP_LEVEL, a.SUPV_HRID, b.EMAIL_HANDLE AS Supv_Handle, b.EMP_LEVEL AS Supv_Level
FROM Headcount AS a, Headcount AS b
WHERE (((a.SUPV_HRID)=b.Hrid))
ORDER BY a.EMAIL_HANDLE;

So what I need to add is a filter for the SUPV_HRID to give me everyone that reports to that field, or to someone that reports to someone that reports to that id.
For each employee, it does list who their supervisors HRID is (SUPV_HRID). This is why I am using – WHERE (((a.SUPV_HRID)=b.Hrid))

Thanks to both of you for your suggestions & examples.

Reply | Quote

I understand what you want, but there is no simple way to get it. As I wrote, you either need a query with as many instances of the table as there are hierarchical levels, or you need a recursive function. Neither is as easy or convenient as START WITH … CONNECT BY.

Reply | Quote