Query help - Need ASAP

[jfgambit]

I have 3 queries each one is built from seperate tables, but all can be linked by a unique ID number. I need a query that by matching the ID number will give me each line of data from each query in consecutive order.

Example:
Query 1 has ID number, Name, Address
Query 2 has ID number, Area, Mass
Query 3 has ID Number, City, State

Final Query has (linked by ID):
1234, John, 555 Home
1234, NE, 5.45
1234, Burbank, CA

1235, Mike, 222 Great St
1235, GW, 6.78
1235, San Fran, CA

Etc.

Can anyone help??

 

[dynamictiger]

You may be able to make one query. Place all the tables in the design grid and link the UniqueID number.

Drag the ID number from one of the tables and all the rest of the fields into the query how you want them.

HTH

 

[jfgambit]

Unfortunately, that is going to give me 1 line of data for each unique id...I need three lines of data for each Unique ID

ID Number Data Field 1 DataField 2
1234 John 222 Home
1234 NE 5.4
1234 Burbank CA
1235 Mike 555 Home
1235 GW 6.4
1235 San Fran CA

I can't use:

ID Number
1234 John 222 Home NE 5.4 Burbank CA
1235 Mike 555 Home GW 6.4 San Fran CA

Anyone??? Anyone??

 

[Jon K]

You can use a Union Query to combine the three queries as follows (type/paste in the SQL View of a new Query):-

SELECT 1 as LineNumber, [ID Number], Name as Column1, Address as Column2 from [Query 1]
UNION
SELECT 2, [ID Number], Area, Mass from [Query 2]
UNION
SELECT 3, [ID Number], City, State from [Query 3]
ORDER BY [ID Number], LineNumber;

Save it as qryUnion and run it.


The LineNumber field is solely for sorting purpose. If you want to remove it, just create another new query as follows:-

SELECT [ID Number], Column1, Column2
FROM qryUnion;


Hope it helps.