How to calculate Feed Consumed?

[Shawnus-Maximus]

Last monday's [Feed Inv]+ this weeks [Feed Del] - this weeks [Feed Inv]= Feed Consumed

Table:

Autonumber (is PK)
Date
Farm
Barn
Lbs Del (pounds delivered)
Lbs Inv (pounds inventoried, this is a physical inventory done at farm)

I can use Between[Start Date] And [End Date] in the criteria to show this weeks Lbs Del and Lbs Inv in a query, no problem.
How do I tell the query or report to subtract 7 from the Start Date to pull last weeks feed inventory?

The result will be displayed in a report, so calculating in a report would be ideal.

Thanks,
SM

 

[gemma-the-husky]

just take 7 away

eg Between[Start Date]-7 And [End Date]-7
or Between[Start Date]-7 And [Start Date]

or something similar

you might want to check this carefully as there may be get some cut off issues - especially if your dates include time.

 

[Shawnus-Maximus]

Sorry, I don't believe I was very clear.

I need both this weeks and last weeks Feed Inv to use in the calculation.
But, I can not use them summed together. Last weeks Feed Inv. will be added to this weeks Feed Del, and then this weeks Feed Inv. will be subtracted from that total.

(Last weeks[Feed Inv]+this weeks[Feed Del]) - this weeks[Feed Inv]

This calculation will give me how much feed was consumed at the farm.

Every Monday feed inventories are taken at the farms, all week long feed is delivered at the farms. My table has a place for the Date, Farm, Barn, Feed Del, and Feed Inv. So I need to pull last weeks feed inventory to use in a calculation with this weeks feed inv.

I am not sure if I should do this in a query or a report. Do I need to use two queries and combine the queries with another query?

Thanks,
SM

 

[Shawnus-Maximus]

The double query method worked. I then queried the two queries, and created a report on the new query . And calculated on the report.

Thanks,
SM