Query with Append of 4th table (1 Viewer)

Jaykappy

Jaykappy
Local time
Today, 01:20
Joined
Apr 19, 2005
Messages
13
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.
 

Ranman256

Well-known member
Local time
Today, 02:20
Joined
Apr 9, 2015
Messages
4,339
when you get > 1 table in the query, you run the possibilty of getting wrong counts due to the join.
But you can make a sum query on a single table (the 4th table)
Then join the qsSumTbl4 to table1 and get a 1-to-1 relation, an no extra records.
(the above is just an example to explain)
 

stopher

AWF VIP
Local time
Today, 06:20
Joined
Feb 1, 2006
Messages
2,396
Sounds like you what to use UNION so that you stack the results of your query with those of table 4.

Google union.

But it's not normal to union data of different granularity so might be useful to understand why you want to do this.
 

Users who are viewing this thread

Top Bottom