Date Issue

[jvhazelbaker]

I am trying to figure out a net bushel weight per silo per date taking into account usage and new receipts. So, this is what I'm doing.

First Query: Figures bushel weight per silo per day
net weight: =([gross wt]-[empty wt]/56)

Second Query: Figures actual bushel usage per silo

Third Query: Problem begins. Example below:

Date Silo1: iif([silo]=1,[net wt]-[actual bu usage],0)
4/1 1342.5
4/2 4534.6
4/3 3465.7

What I want is for the first date total and the second date total to add together to get x, then x to add to the third date total to get y and so on for infinity. However, I obviously can't add a field to itself without giving it some sort of identifier. So rather than builing down, I need to build across, but I can't figure out how to accomplish this since the dates and totals per silo will be infinite, so how do you name something that doesn't yet, but will at some point, exist?

 

[dhookom]

This isn't very clear. My first question is, wouldn't the Net Weight be:
net weight: ([gross wt]-[empty wt])/56

Where do [net wt], [Silo], and [actual bu usage] come from?

Do you have weights for each silo for each date?


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[jvhazelbaker]

Actually, the exact expression is:

Net Wt: Sum(([gross wt]-[empty wt])/56)

So, [net wt] comes from the first query (as shown in the expression above). [Silo] is from a table, [actual bu usage] is from a table, both populated earlier in the process and brought together through the query.

Does this help?

 

[PHV]

Have you searched for running total ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[jvhazelbaker]

I don't understand what you mean, exactly. Can you elaborate?

 

[Golom]

A running total would be something like
[tt]
Date Silo1 RunningTotal
4/1 1342.5 1342.5
4/2 4534.6 5877.1
4/3 3465.7 9342.8
[/tt]

 

[jvhazelbaker]

How would that be done programmatically in the RunningTotal column? I would need to differentiate between the 4/1 row and the 4/2 row and so on, but I don't know how to do so per row, only per column with an infinite number such as this. Any ideas?

 

[Bhavna13]

The DSUM function can be used to do running totals.
An example is available here :

http://support.microsoft.com/default.aspx?scid=kb;en-us;290136

Hope this is what u lookin for