Help! - How can I make a calculation using a previous record?

[Gary]

I have an example table with fields and records as shown below:

RECORD_ID DATE PRODUCT QUANTITY
1 4/1/05 widget1 50
2 4/2/05 widget1 48
3 4/3/05 widget1 42
4 4/4/05 widget1 28
5 4/5/05 widget1 13
6 4/6/05 widget1 5
.. . . .
.. . . . (and so on)
30 4/30/05 . .

What I need is an output like this:

DATE PRODUCT SOLD (The quantity from the day before - today's)
4/2/05 widget1 2 (50 - 48)
4/3/05 widget1 6 (48 - 42)
4/4/05 widget1 14 (42 - 28)
4/5/05 widget1 15 (28 - 28)
4/6/05 . .
4/7/05 . .
4/8/05 . .
. . .
4/30/05 . . (and so on)


Calculating the quantity SOLD seems like it would be a common database
task and yet this Newbie can only come up with making 30 queries for
each day of the month. I'm wondering if a VBA module could be made to
loop through these records and perform the calculations...but I'm not a
programmer. Any help would be greatly appreciated!

[Mark]

You can use the example I gave in a response to your previous post "Newbie
out of Gas - Needs Help". 05/04/2005 23:38

"Gary" <ke*****@gmail.com> wrote in message
news:11*********************@g14g2000cwa.googlegro ups.com...
I have an example table with fields and records as shown below:

RECORD_ID DATE PRODUCT QUANTITY
1 4/1/05 widget1 50
2 4/2/05 widget1 48
3 4/3/05 widget1 42
4 4/4/05 widget1 28
5 4/5/05 widget1 13
6 4/6/05 widget1 5
.. . . .
.. . . . (and so on)
30 4/30/05 . .

What I need is an output like this:

DATE PRODUCT SOLD (The quantity from the day before - today's)
4/2/05 widget1 2 (50 - 48)
4/3/05 widget1 6 (48 - 42)
4/4/05 widget1 14 (42 - 28)
4/5/05 widget1 15 (28 - 28)
4/6/05 . .
4/7/05 . .
4/8/05 . .
. . .
4/30/05 . . (and so on)


Calculating the quantity SOLD seems like it would be a common database
task and yet this Newbie can only come up with making 30 queries for
each day of the month. I'm wondering if a VBA module could be made to
loop through these records and perform the calculations...but I'm not a
programmer. Any help would be greatly appreciated!

[jimfortune]

Gary wrote:

programmer. Any help would be greatly appreciated!

tblSales
Record_ID AutoNumber, PK
SalesDate Date
ProductName Text
QuantityOnHand Long

1 4/1/05 widget1 50
2 4/2/05 widget1 48
3 4/3/05 widget1 42
4 4/1/05 widget2 25
5 4/3/05 widget2 20

qrySalesByDateAndProduct:
SELECT tblSales.SalesDate, tblSales.ProductName,
(tblSales.QuantityOnHand - (SELECT First(A.QuantityOnHand) FROM
tblSales AS A WHERE A.Record_ID > tblSales.Record_ID AND A.ProductName
= tblSales.ProductName)) AS QuantitySold FROM tblSales;

results:
SalesDate ProductName QuantitySold
4/1/05 widget1 2
4/2/05 widget1 6
4/3/05 widget1 Blank
4/1/05 widget2 5
4/2/05 widget2 Blank

I enjoy these puzzles but my deja vu sense indicates that there's a
better way to organize this information :-). Even though you can
calculate QuantitySold from the QuantityOnHand information, two
separate tables, one for the product (one record per product with a
QuantityOnHand field) and one for daily sales (one record per product
sold per day) is a more common way to do this. BTW, I sense myself
getting better at solving Access puzzles like this thanks to this NG.
I have to resort to VBA code for only the seriously tough problems. Be
sure to test this query more thoroughly if you plan on using it.

James A. Fortune

[Gary]

Sorry Mark

The example you gave earlier will work perfectly with the above
example. Please disregard it. I'm not very good at explaining what I
need and I apologize to you and everyone else.

In the gasoline problem I actually have 95 tanks loaded with one of
five fuel types: Regular, Super, Diesel, Mid-Grade and Kerosene. All 95
tank volumes are recorded every day, so I need to take the volume of
each tank as recorded yesterday and subtract it from each tank volume
as recorded today. The example you gave is subtracting the same min
value from all 95 tanks, and the min volume of tank_1 may not be the
min volume of tank_2. Do you see what I mean? Each tank is distinct.

I'm sorry I wish I could be clearer - the example I gave above was over
simplified and dumb of me to post.

-Gary

[Gary]

Thank you Jim for your efforts!

The real problem I'm having is explain more under my previous post
"Newbie out of Gas - Needs Help". 05/04/2005 23:38... but I'm doing a
poor job of explaining my problem.

My apologies to you and Mark.

-Gary

[Alan Webb]

Gary,
The general case of consumption equals the difference between yesterday's
volume and today's volume is simple if you arrange your data well. Let's
assume a list of line items on invoices where products are sold. Some of
the products will sell on some line items of invoices each day. You can't
know which products will sell on a given day and therefore appear as a line
item on an invoice. What you can know is that yesterday's sales are
everything that sold the day before. So, you can query the line item table
for all lines with a sale date of date-1, or DateAdd("d",Date(),-1).
Today's sales are the same query but where sale date = today. If you join
on product then you are assured that you are comparing sales for the last
two days for the same product. Once this view is built, then it's a simple
matter of setting up an expression in the containing view that calculates
consumption = sales_yesterday - sales_today. One other wrinkle, if you want
a comprehensive list of products regardless of whether they sell on a given
day you would need an outer join between product and line item.
You've got to get off the idea that you need 31 or 30 or whatever queries
for each day of the month. Even in the gas tank example the worst case
scenario is a query for each tank/numeric measure. There were about 5
numeric measures, or five columns and 95 tanks. So, the result would have
155 columns if you needed a column per numeric measure & day of the month.
It would have 95 rows, one each for each tank. Jet crosstab queries top out
at 255 columns so this could easily be done as a Jet crosstab without any VB
coding.
--
Alan Webb
kn*******@SPAMhotmail.com
"It's not IT, it's IS
"Gary" <ke*****@gmail.com> wrote in message
news:11*********************@g14g2000cwa.googlegro ups.com...

I have an example table with fields and records as shown below:

RECORD_ID DATE PRODUCT QUANTITY
1 4/1/05 widget1 50
2 4/2/05 widget1 48
3 4/3/05 widget1 42
4 4/4/05 widget1 28
5 4/5/05 widget1 13
6 4/6/05 widget1 5
. . . .
. . . . (and so on)
30 4/30/05 . .

What I need is an output like this:

DATE PRODUCT SOLD (The quantity from the day before - today's)
4/2/05 widget1 2 (50 - 48)
4/3/05 widget1 6 (48 - 42)
4/4/05 widget1 14 (42 - 28)
4/5/05 widget1 15 (28 - 28)
4/6/05 . .
4/7/05 . .
4/8/05 . .
. . .
4/30/05 . . (and so on)


Calculating the quantity SOLD seems like it would be a common database
task and yet this Newbie can only come up with making 30 queries for
each day of the month. I'm wondering if a VBA module could be made to
loop through these records and perform the calculations...but I'm not a
programmer. Any help would be greatly appreciated!

[Alan Webb]

Gary,
I said in my previous posts about the gas tank problem that I would stop
further work on the example I made until I had sample data & money. Well .
.. . I like the problem enough that I kept going on the copy of the example
database I have at home. There are additional comments in the code I
started writing and more forms. Money isn't the huge issue if I am going to
keep working as much as sample data so I can test my thinking. I can
generate fictitious gas stations but it would be better if I had a
fictitious list you generated. Write to me at the e-mail address in my
signature (minus the --NO--SPAM-- additions) if you are interested.
--
Alan Webb
kn*******@SPAMhotmail.com
"It's not IT, it's IS"

"Gary" <ke*****@gmail.com> wrote in message
news:11*********************@g14g2000cwa.googlegro ups.com...

I have an example table with fields and records as shown below:

RECORD_ID DATE PRODUCT QUANTITY
1 4/1/05 widget1 50
2 4/2/05 widget1 48
3 4/3/05 widget1 42
4 4/4/05 widget1 28
5 4/5/05 widget1 13
6 4/6/05 widget1 5
. . . .
. . . . (and so on)
30 4/30/05 . .

What I need is an output like this:

DATE PRODUCT SOLD (The quantity from the day before - today's)
4/2/05 widget1 2 (50 - 48)
4/3/05 widget1 6 (48 - 42)
4/4/05 widget1 14 (42 - 28)
4/5/05 widget1 15 (28 - 28)
4/6/05 . .
4/7/05 . .
4/8/05 . .
. . .
4/30/05 . . (and so on)


Calculating the quantity SOLD seems like it would be a common database
task and yet this Newbie can only come up with making 30 queries for
each day of the month. I'm wondering if a VBA module could be made to
loop through these records and perform the calculations...but I'm not a
programmer. Any help would be greatly appreciated!