Multiple table SELECT queries in one statement?

[bugnote]

Hi there,

I'm fairly new to access and am finding some of the table joins a bit tricky.
I have a design view statement defined that queries multiple tables for information based on the contents of another table.
I now need to query the tables from within an excel database.

If I build the SQL statements individually I can query access successfully using VBA and ADODB.Recordset functions.

But when I try and build one statement with multiple table joins, which queries each table and either returns a value of nil I can't figure it out.

Example, I wish to query each table within the below statement for a user ID e.g. 1000101 and it will return a hit for each table that it finds a match in.

I'm unsure how to pass in the ID to each table?

any help/pointers very much appreciated.

Cheers

Bug

[FONT=Arial][FONT=Arial]sSQL = "SELECT HRTABLE.[First names], HRTABLE.[Last name], HRTABLE.[E-mail address], BUSINESSLeavers.[Date of Leaving], " & _[/FONT]
[FONT=Arial]"HRTABLE.[Org Unit ID], HRTABLE_1.[First names], HRTABLE_1.[Last name], HRTABLE_1.[E-mail address], " & _[/FONT]
[FONT=Arial]"BUSINESSLevels.[BUSINESS LEVEL (Equivalent Standard Level)], HRTABLE.[Org Unit 1 ID], HRTABLE_2.[First names], " & _[/FONT]
[FONT=Arial]"HRTABLE_2.[Last name], HRTABLE_2.[E-mail address], BUSINESSLevels_1.[BUSINESS LEVEL (Equivalent Standard Level)], " & _[/FONT]
[FONT=Arial]"SMART.PID, SMART.Forename, SMART.Surname, SMART.Email, SMART.[Reports to ID], SMART_1.Forename, SMART_1.Surname, " & _[/FONT]
[FONT=Arial]"SMART_1.Email, BUSINESSLevels_2.[BUSINESS LEVEL (Equivalent Standard Level)], OTHERCOMPANYHR.[First Name], OTHERCOMPANYHR.Surname, " & _[/FONT]
[FONT=Arial]"OTHERCOMPANYHR.[Supervisor Empno], OTHERCOMPANYHR_1.[First Name], OTHERCOMPANYHR_1.Surname, OTHERCOMPANYLevels.grade_band, OTHERCOMPANYLevels.grade_level " & _[/FONT]
[FONT=Arial]"FROM BUSINESSLeavers, (((HRTABLE LEFT JOIN HRTABLE AS HRTABLE_1 ON HRTABLE.[Org Unit ID] = HRTABLE_1.Persno) " & _[/FONT]
[FONT=Arial]"LEFT JOIN HRTABLE AS HRTABLE_2 ON HRTABLE.[Org Unit 1 ID] = HRTABLE_2.Persno) " & _[/FONT]
[FONT=Arial]"LEFT JOIN BUSINESSLevels ON HRTABLE.[Org Unit ID] = BUSINESSLevels.Persno) " & _[/FONT]
[FONT=Arial]"LEFT JOIN BUSINESSLevels AS BUSINESSLevels_1 ON HRTABLE.[Org Unit 1 ID] = BUSINESSLevels_1.Persno, " & _[/FONT]
[FONT=Arial]"SMART LEFT JOIN SMART AS SMART_1 ON SMART.[Reports to ID] = SMART_1.PID, " & _[/FONT]
[FONT=Arial]"LEFT JOIN BUSINESSLevels AS BUSINESSLevels_2 ON SMART.[Reports to ID] = BUSINESSLevels_2.Persno, " & _[/FONT]
[FONT=Arial]"OTHERCOMPANYHR LEFT JOIN OTHERCOMPANYHR AS OTHERCOMPANYHR_1 ON OTHERCOMPANYHR.[Supervisor Empno] = OTHERCOMPANYHR_1.[File Number]), " & _[/FONT]
[FONT=Arial]"LEFT JOIN OTHERCOMPANYLevels ON OTHERCOMPANYHR.[Supervisor Empno] = OTHERCOMPANYLevels.file_number"[/FONT]
[/FONT]

 

[MSAccessRookie]

There are two standard structures for Table Joins, each requiring a specification of the Columns to Link:

1. From Table1 {Some Type Of Join} Table2 On Table1.JoinColumn = Table2.JoinColumn
2. From Table1, Table2 Where Table1.JoinColumn = Table2.JoinColumn

The first thing that I noticed about your Query was that you were using both methods for joining Tables, and that at least some of your Table Joins were not properly structured.

The Table Joins that used the On structure appear to be OK, while the Table Joins that did not, also did not have any Where Clause to define the joins. Without the proper Where Clause, these Joins will become Cartesion Joins, and will not provide the correct results. Adding an appropriate Where clause that corrects the structures of these Joins could provide the results that you are looking for. Another option would be to convert these Joins to use an On Keyword.

I should note at this time that although I am sure that it is possible to Mix and Match Join types, I do not like to do so because the difference in structures makes it harder for me to sort out bugs, so I would choose to convert the Joins to use an On Keyword. This is a personal preference only.

I hope this information is useful.

 

[bugnote]

many thanks Rookie, it now makes more sense. One thing I'm unsure about is where abouts to place the 'where' for the table join not ON.

Many thanks

Bug

 

[MSAccessRookie]

many thanks Rookie, it now makes more sense. One thing I'm unsure about is where abouts to place the 'where' for the table join not ON.

Many thanks

Bug

The Where Statement just goes where it normally goes. What to put there is up to you. Depending on your choice of Joins, the Basic SQL Structure is either:

SELECT Something
FROM SomeTable { LEFT/RIGHT } JOIN SomeOtherTable
ON SomeTable.JoinColumn = SomeOtherTable.JoinColumn

or:

SELECT Something
FROM SomeTable, SomeOtherTable
WHERE SomeTable.JoinColumn = SomeOtherTable.JoinColumn

 

[bugnote]

ahh, thanks. I thought I may have to nest the where statement within the first part of the query.

I'll try again in work tomorrow.

Regards

Bug

 

[bugnote]

SELECT TableA.Data , TableAA.Data, TableB.Data, TableBB.Data, TableC.More
FROM (((TableA LEFT JOIN TableA AS TableAA ON TableA.OtherData = injectedValue)
        TableB LEFT JOIN TableB AS TableBB ON TableB.OtherData = injectedValue")
           TableC ON TableC.OtherData = injectedValue)

Hi once again,

conceptually this is making more sense, where I am still struggling is in the forming of the ON statement where no join is required.

In my above example, I wish to select data from 3 tables, 2 of which require a join, the final does not. The tables do not have a common relationship and I am running the query for multiple values in an excel loop.

Any pointers as to how I should form the above query would be greatly appreciated.

Apologies if the previous explanations help more than I realise ;-)

Cheers

Bug

 

[MSAccessRookie]

SELECT TableA.Data , TableAA.Data, TableB.Data, TableBB.Data, TableC.More
FROM (((TableA LEFT JOIN TableA AS TableAA ON TableA.OtherData = injectedValue)
        TableB LEFT JOIN TableB AS TableBB ON TableB.OtherData = injectedValue")
           TableC ON TableC.OtherData = injectedValue)

Hi once again,

conceptually this is making more sense, where I am still struggling is in the forming of the ON statement where no join is required.

In my above example, I wish to select data from 3 tables, 2 of which require a join, the final does not. The tables do not have a common relationship and I am running the query for multiple values in an excel loop.

Any pointers as to how I should form the above query would be greatly appreciated.

Apologies if the previous explanations help more than I realise ;-)

Cheers

Bug

Be careful about "the forming of the ON statement where no join is required", because in a proper database, there is no such case. The purpose of a Join Column , no matter what the type is to limit the number of possible responses. If you do not specify any Join Column, then Access will return all possible combinations of one Column from TableA and one Column from TableB. This is also known as a Cartesian Join. In other Words:

If Table A has 5 records, and Table B has 6 Records, then a Query with a Join Column will return at most 6 records (depending on whether it is a Left, Right, or Inner Join). A Query without a Join Column, on the other hand, will return 30 Records (5x6).
​

This is a very simple example. Imagine what would happen if TableA had 50000 Rows, and TableB had 60000 Rows! In my opinion (again, personal preference only), the best thing for you to do would be to try to convert all of the Joins to use On Statements as opposed to Mixing and Matching them.

 

[bugnote]

[FONT=Arial] [/FONT]
[FONT=Arial]SQL = "SELECT HR.Persno, HR.[First names], HR.[Last name], HR.[E-mail address], HR.[ ID], HR_1.[First names], HR_1.[Last name], HR_1.[E-mail address], COLevels.[CO LEVEL], HR.[ 1 ID], HR_2.[First names], HR_2.[Last name], HR_2.[E-mail address], COLevels_1.[CO LEVEL ] " & _[/FONT]
[FONT=Arial]"FROM (((HR LEFT JOIN HR AS HR_2 ON HR.[OU1 ID] = HR_2.Persno) LEFT JOIN HR AS HR_1 ON HR.[OU ID] = HR_1.Persno) LEFT JOIN COLevels ON HR.[OU ID] = COLevels.Persno) LEFT JOIN COLevels AS COLevels_1 ON HR.[OU1 ID] = COLevels_1.Persno " & _[/FONT]
[FONT=Arial]"WHERE HR.Persno='" & LookupVal & "'"[/FONT]
[FONT=Arial] [/FONT]
[FONT=Arial]SQL = "SELECT COLeavers.[Date of Leaving] FROM COLeavers WHERE COLeavers.Persno = '" & LookupVal & "'"[/FONT]

Hi there, for my project I went with multiple SQL statements as I couldn't get the multiple join/on statements to work ;-(

My access database has multiple tables and some are linked. so if I get a hit in HR, I need the appropriate level to be returned. For the leaving information table I would like to check the LookupVal within the same statement if no match in HR is found.

The above code shows my 2 statements that achieve what I need, but I know I can reduce calls to the database with one statement, I just can't seem to construct it.

Can I hit multiple tables with the same data from one statement?

Any help much appreciated.

Regards

Bug