what do you do when you need to do an ambiguous outer join intentionally...

[sao123]

I intentionally need to do an ambiguous outer join on 2 queries... I really do. how do i get around this stupid error??



I have 2 tables... (Simplified, only showinf relevant fields)
As before, i am making a database to track salt usage in relation to snow plowing roads.

LogEntry:
PriKey
Date
Site
Operator
Foreman

Material Usage
PriKey
LogID (related to Logentry above)
MaterialTypeMix
MaterialUsedInPounds
MilesofRoad
LbsPerMile
GallonsofLiquidMaterial

for each log entry, there may be 1 or more materials used.
Also, depending on the material used, it may be measured in Lbs or Gallons.

A single entry may be like:
Log entry: 12/1/08, Uniontown, John Driver, Joe Bossman
Material: 50%Salt-50%Stone, 1200 Lbs, 30Miles, 400LbsPerMile, X
Material2: LiquidPreTreatment, X, 10 Miles, X, 200Gallons

I have a query which will take all my dry mixes and convert them to Tons, and figure out the actual amount of salt used... (IE, 1200lbs of 50/50 is only 600lbs of salt and 600lbs of stone.) then it totals up all the salt used across all my sites.
i have a similar query, which totals up the salt used in liquid salt brine, uses a formula to figure out how much dry salt is added to water.
i now have 2 queries with dry salt totals, each with a group of sites.
the sites may not match, some will be in one, some in the other, and many in both.

I NEED to DO AN AMBIGUOUS OUTERJOIN QUERY to combine this data into one report...
and access wont let me!!! If i dont do an ambiguous outer join... only the sites which appear in both of the above querys appear in the final result... similarly if i do a left or right join, i am missing site data from the other...

someone please tell me how to do this!!!

 

[KLin]

You need a 3rd query that shows all loggentries and do left joins back to the other 2 queries. This will allow you to see all data.

EDIT: And please take a chill pill.

 

[sao123]

Originally posted by: KLin
You need a 3rd query that shows all loggentries and do left joins back to the other 2 queries. This will allow you to see all data.

EDIT: And please take a chill pill.

this is exactly what I tried...
then I get the error you are attempting to do an ambiguous outerjoin.

 

[KLin]

I forgot to mention taking those 3 queries and putting them in a 4th query. 😱

Query1. SELECT PriKey from LogEntry
Query2. Select LogID, CalculatedSaltBrine from MaterialUsage
Query3. Select LogID, CalculatedDrySalt from MaterialUsage
Query4. SELECT PriKey, CalculatedSaltBrine, CalculatedDrySalt FROM Query1 LEFT JOIN Query2 on Query1.PriKey = Query2.LogID LEFT JOIN Query3 on Query1.PriKey = Query3.LogID