Query with Append

[jaykappy]

Not sure how to approach this.



I have a query that is joining 3 tables by a unique field.
tbl_Location (locationID)
tbl_Collections (ID)
tbl_Gear (ID)

There is a one to many from tbl_Location to tbl_Collections
If tbl_Collections ID field exists in tbl_Location show me the locationID and all the tbl_Collections Records.

This works fine BUT
I have a 4th table that has Counts. This table is in the same format as tbl_Collections and also can be joined by ID field.

What i want to do is add these records as results to the query above.

SO if tbl_Collections had 345 records and the 4th table (once joined by ID) had 32 records I want the resulting table to have 377 records.

[aytee111]

Join the queries together with Union:
SELECT .......
UNION SELECT ....

Both queries must have exactly the same fields. Use this query for appending.

[jaykappy]

I did this...
If I run them by themselves I get 65K records for the first and 854 records for the second...

If I Run the below and I only get about 1200 records total


SELECT Locations.CollectionID, Locations.UserN, Locations.Stream, Locations.StreamName, Locations.StreamCode, Locations.Date, Gear.GearType, Gear.Equip, Gear.[Sample type], Gear.Species, Gear.Effort, [Water quality].Time, [Water quality].WatTemp, [Water quality].[Sechi Disc], [Water quality].AirTemp, [Water quality].pH, [Water quality].PHCOLOR, Fish_Enter.Number
FROM (Locations INNER JOIN (Gear INNER JOIN Fish_Enter ON (Gear.NetNum = Fish_Enter.NetNum) AND (Gear.CollectionID = Fish_Enter.CollectionID)) ON (Locations.CollectionID = Gear.CollectionID) AND (Locations.CollectionID = Fish_Enter.CollectionID)) INNER JOIN [Water quality] ON Locations.CollectionID = [Water quality].CollectionID


UNION


SELECT Locations.CollectionID, Locations.UserN, Locations.Stream, Locations.StreamName, Locations.StreamCode, Locations.Date, Gear.GearType, Gear.Equip, Gear.[Sample type], Gear.Species, Gear.Effort, [Water quality].Time, [Water quality].WatTemp, [Water quality].[Sechi Disc], [Water quality].AirTemp, [Water quality].pH, [Water quality].PHCOLOR, Fish_Sum.Number
FROM ((Locations INNER JOIN Gear ON Locations.CollectionID = Gear.CollectionID) INNER JOIN [Water quality] ON Locations.CollectionID = [Water quality].CollectionID) INNER JOIN Fish_Sum ON (Locations.CollectionID = Fish_Sum.CollectionID) AND (Gear.NetNum = Fish_Sum.NetNum) AND (Gear.CollectionID = Fish_Sum.CollectionID);

[aytee111]

This is a new one on me! Use UNION ALL

[jaykappy]

THink I got it....FORGOT the ALL

UNION SELECT

vs

UNION ALL SELECT