Joining Fields to form Master List

[AggieLJ]

Hello All!

I have multiple tables containing lab information, invoice information, and customer information. I would like to have a "master list" containing all the data in a compact form.

The Table/Fields are like this:

Table:______|___Invoices_____|
Field:______|___InvoiceID____|
Data:.......|.......1........|
............|.......2........|

Table:______|___SLabs_______________
Field:______|___InvoiceID___SampleID__TestWanted___Price___
Data:.......|.......1..........123........S1........10.00...
............|.......1..........124........S2........15.00...

Table:______|___WLabs_______________
Field:______|___InvoiceID___SampleID__TestWanted___Price___
Data:.......|.......1..........987........W1.........5.00...
............|.......2..........988........W2........10.00...


I am trying to create a list that outputs like this:

InvoiceID__|__SampleID(from both SLabs & WLabs)__|__TestWanted__|__Price
....1......|.................123.................|......S1.....|..10.00
....1......|.................124.................|......S2.....|..15.00
....1......|.................987.................|......W1.....|...5.00
....2......|.................988.................|......S2.....|..10.00

NOT like this:

InvoiceID__|__SampleID(from SLabs)__|__SampleID(from WLabs)__|__TestWanted__|__Price
....1......|...........123..........|...........987..........|......S1.....|..10.00
....1......|...........124..........|...........987..........|......S2.....|..15.00
....1......|...........123..........|...........988..........|......S1.....|..10.00
....1......|...........124..........|...........988..........|......S2.....|..15.00


Any suggestions? I can upload a sample if y'all need something to tinker around with....

Thanks in Advance!

 

[jal]

Start with this:

SELECT * FROM Slabs
UNION ALL
SELECT * FROM WLabs

and then let us know how it goes.

 

[AggieLJ]

It works great! I ended up using it to join 5 queries together...

SELECT * FROM [TESTSoilLabs]
UNION ALL
SELECT * FROM [TESTWaterLabs];
UNION ALL
SELECT * FROM [TESTForageLabs];
UNION ALL
SELECT *FROM [TESTBiosolidLabs];
UNION ALL
SELECT * FROM [TESTResearchLabs];

You have definitely given me a new route to explore in combining information (but I have realized I still have a lot to learn about all the different ways)!