Query Joins Missing Results

[hotrodsue]

This forum has been a life saver for me many times. Attached is database that I’m having trouble with. I have a query named QryNetPounds to calculate Net Pounds and Net Tons. This query is based on 2 queries QryHarvestLoadSum and QryRecentTareAverage, which are joined by Date and VehicleID.

When I run QryNetPounds I’m prompted to enter Date of Harvest. There is test data entered for three harvest dates: 03/30/2009, 03/31/2009, and 04/01/2009. There are no results returned for 04/01/2009. It seems QryNetPounds returns results when BOTH dates match in the queries joined by date – QryHarvest Load Sum (Contains Harvest Date) and QryRecentTareAverage (Contains Tare Date).

That makes sense to me. However, what I need to do is get results on 04/01/2009 when there is NOT a matching Tare Date. I’d like it to use the most recent Tare Date entered which is 03/31/2009. I’m not sure how to accomplish this. Thanks in advance for any suggestions.

 

[Galaxiom]

The problem is you need a one sided join on the date fields.

Right click on the join line in the query and change the join type.
If you don't get the Join Properties selection move the mouse a little and keep right clicking until you do.
Once you get in there it is all clearly explained.

 

[hotrodsue]

Thanks so much for your quick response. I'm past my deadline on this project. In join properties, the first option was selected. I tried option 2 and option 3, both gave me an error message:

"The SQL statement could not be executed because it contains ambiguous outer joins. To force one of the joins to be performed first, create a separate query that performs the first join and then include that query in your SQL statement."

I don't think I've done this before, I'll play with it and see what I come up with. Any suggestions? I'm sweating this one as the clock is ticking. Again, many thanks for your help.

 

[Galaxiom]

The one you need is show all of the records from the Harvest table.

Would there be three tables in the query?
If so make all the joins point in the same direction.

It could also be due to underlying relationships conflicting with the join.