Query Results by Date

[hotrodsue]

Attached is my database project. My query, QryDMTons is returning the correct results for the following field names:

DM Tons
Adjusted 30%

Expressions for field names are as follows:

DM Tons: [Avg Of DryMatter]*[Net Tons]/100
Adjusted 30%: [DM Tons]/0.3

Prior to adding the above expressions to the query, I had Net Tons by Harvest Date, FieldID, and Vehicle ID as follows:

HarvestDate By Day: March 30,2009
QryNetPounds_FieldID: Putnal Big Field
VehicleID: 166
Net Tons: 50.33

After adding the 2 expressions for DM Tons and Adjusted 30%, I now have each record showing on 2 dates, March 30, 2009 and June 27, 2009. (These are Dry Dates that are input on Dry table via Dry form.) I need to show the results for only one Dry date. Each Dry Date will always be the same as each Harvest Date in this calculation.

Any suggestions are greatly appreciated.

 

[jzwp22]

Sorry, I cannot open your database here at work, we do not have Access 2007, but how are you calculating or bringing in the value of the [Avg of DryMatter] field?

 

[hotrodsue]

From another query titled QryDryAverage, that calculates the average by date.

 

[jzwp22]

Did you join the QryDryAverage to QryDMTons by the date field as well? You might need an unequal join (Left or Right join rather than an inner join). Join all records from QryDMTons to only those records in QryDryAverage where the dates match.

 

[hotrodsue]

Thank you, that worked.

 

[jzwp22]

You're welcome