calculating values based on a date

[Guest]

Hi there. A bit stuck and need some help.

I have a table called Print Actuals. THis table tracks the print count for
a series of printers. It records the date and the number of print
impressions.

I have three months worth of data now. I need to create a report showing
june printing volume and july printing volume. The calculation for june
printing is:
[June's print count] - [may print count].

Problem: How do i do this in Access? I need separate fields to create the
calculation but I am not sure how to accomplish this.

 

[Rick B]

What is the structure of your table? You say you have three month's worth
of data. How is it structured?

I would expect you to have a date, and a number of documents printed for
that date.

Then, in your query, you'd simply pull all the records for the month in
question and total them. Is that not how your tables are structured?


You say June's print count would be...
[June's print count] - [may print count]

That does not make sense.

 

[Guest]

Hi there,
My table is structured this way:
Serial Number
CountMonth
PrintCount

Problem, i have for example:

SerialNumber CountMonth PrintCount
1234A May 30, 2005 5000
1234A June 30, 2005 5500
1234A July 30, 2005 6000

Issue: I want to know how many pages were printed for June. So, subtract
print count for june from print count for may ([5500]-[5000]).

Problem: How do I do this?

What is the structure of your table? You say you have three month's worth
of data. How is it structured?

I would expect you to have a date, and a number of documents printed for
that date.

Then, in your query, you'd simply pull all the records for the month in
question and total them. Is that not how your tables are structured?


You say June's print count would be...
[June's print count] - [may print count]

That does not make sense.



--
Rick B

Hi there. A bit stuck and need some help.

I have a table called Print Actuals. THis table tracks the print count for
a series of printers. It records the date and the number of print
impressions.

I have three months worth of data now. I need to create a report showing
june printing volume and july printing volume. The calculation for june
printing is:
[June's print count] - [may print count].

Problem: How do i do this in Access? I need separate fields to create the
calculation but I am not sure how to accomplish this.

 

[John Spencer (MVP)]

You could use a subquery. First assumption is that the counter doesn't roll
over. Other assumption, you don't care about the first month for any
serialnumber since you can't do the calculation there.

UNTESTED SQL statement follows

SELECT Main.SerialNumber,
Main.CountMonth,
Main.PrintCount -
(SELECT Max(T.PrintCount)
FROM YourTable as T
WHERE T.PrintCount < Main.PrintCount AND
T.SerialNumber = Main.SerialNumber) as NumPrinted
FROM YourTable as Main

IN the query grid you would need to enter the subquery as a calculated field

Field: NumPrinted: Main.PrintCount - (SELECT Max(T.PrintCount) FROM YourTable as
T WHERE T.PrintCount < Main.PrintCount AND T.SerialNumber = Main.SerialNumber)

You can also use the DMax function

Field: NumPrinted: [TableName].PrintCount -
DMax("PrintCount","[TableName]","PrintCount <" & PrintCount & " AND SerialNumber
= """ & SerialNumber & """")

Hi there,
My table is structured this way:
Serial Number
CountMonth
PrintCount

Problem, i have for example:

SerialNumber CountMonth PrintCount
1234A May 30, 2005 5000
1234A June 30, 2005 5500
1234A July 30, 2005 6000

Issue: I want to know how many pages were printed for June. So, subtract
print count for june from print count for may ([5500]-[5000]).

Problem: How do I do this?

What is the structure of your table? You say you have three month's worth
of data. How is it structured?

I would expect you to have a date, and a number of documents printed for
that date.

Then, in your query, you'd simply pull all the records for the month in
question and total them. Is that not how your tables are structured?


You say June's print count would be...
[June's print count] - [may print count]

That does not make sense.



--
Rick B

Hi there. A bit stuck and need some help.

I have a table called Print Actuals. THis table tracks the print count for
a series of printers. It records the date and the number of print
impressions.

I have three months worth of data now. I need to create a report showing
june printing volume and july printing volume. The calculation for june
printing is:
[June's print count] - [may print count].

Problem: How do i do this in Access? I need separate fields to create the
calculation but I am not sure how to accomplish this.