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