Table Design/Reports

[Gene C]

I have a table with the following data

ReadID (Auto number/Key)
DateRead
Reading


Readings are taken every week.

I want to generate a report that will subtract the last
reading from the current reading and give me the
difference. What formula would I need in a query or form
to give me the result?

i.e.

Reading 9/10/2003 1100
Reading 9/17/2003 1800
Difference 700
Thanks,
Gene C.

 

[Larry]

-----Original Message-----
I have a table with the following data

ReadID (Auto number/Key)
DateRead
Reading


Readings are taken every week.

I want to generate a report that will subtract the last
reading from the current reading and give me the
difference. What formula would I need in a query or form
to give me the result?

i.e.

Reading 9/10/2003 1100
Reading 9/17/2003 1800
Difference 700
Thanks,
Gene C.
.
use DateDiff

 

[Steve Schapel]

Gene,

There are probably several approaches to this. Here is one idea...
Make a query along these lines:

SELECT Min(Reading) AS PreviousReading, Max(Reading) AS
CurrentReading, Min(ReadDate) AS PreviousReadDate, Max(ReadDate) AS
CurrentReadDate FROM TableName
WHERE ReadDate In (SELECT Top 2 ReadDate FROM TableName
ORDER BY ReadDate DESC)

Then, base your report on this query, and for your Difference, put an
unbound textbox on the report with ControlSource set to...
=[CurrentReading]-[PreviousReading]

- Steve Schapel, Microsoft Access MVP

 

[Gene C]

Thanks for the information on designing the report. It
works well with 1 exception
The only problem I have is that the report we are trying
to construct has multiple reads associated with multiple
locations (sites) with multiple readings. When I
construct a report using your criteria I only get 1 site.
The table actually looks like

ReadID
SiteNumber
DateRead
Reading

The report we are looking for would have headings of

Site Number
Date Read
Previous Reading
Current Reading
Difference

We certainly appreciate any help you may supply.
Thanks,
Gene

-----Original Message-----
Gene,

There are probably several approaches to this. Here is one idea...
Make a query along these lines:

SELECT Min(Reading) AS PreviousReading, Max(Reading) AS
CurrentReading, Min(ReadDate) AS PreviousReadDate, Max (ReadDate) AS
CurrentReadDate FROM TableName
WHERE ReadDate In (SELECT Top 2 ReadDate FROM TableName
ORDER BY ReadDate DESC)

Then, base your report on this query, and for your Difference, put an
unbound textbox on the report with ControlSource set to...
=[CurrentReading]-[PreviousReading]

- Steve Schapel, Microsoft Access MVP