Respose from Steve

[Gene C]

Subject: Re: Table Design/Reports
From: "Gene C" <[email protected]> Sent:
9/25/2003 6:17:03 PM



Thanks for the information on designing the report. It
works well with 1 exception. It only works with multiple
reads but only with 1 location. We have multiple sites

The report we are trying to construct has multiple reads
associated with multiple locations (sites). When I
construct a report using your criteria I only get 1 site.

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

 

[Steve Schapel]

Gene,

Thanks for the information on designing the report. It
works well with 1 exception. It only works with multiple
reads but only with 1 location. We have multiple sites

Any reason for not mentioning this before?

The report we are trying to construct has multiple reads
associated with multiple locations (sites). When I
construct a report using your criteria I only get 1 site.

The report we are looking for would have headings of

Site Number
Date Read
Previous Reading
Current Reading
Difference

Modify the query I gave you before, to...

SELECT SiteNumber, 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 AS T2
WHERE T2.SiteNumber=TableName.SiteNumber
ORDER BY ReadDate DESC)
GROUP BY SiteNumber

- Steve Schapel, Microsoft Access MVP

 

[Gene C]

Steve,

It never occured to us to mentioned the multiple
locations. Stupidly we probably thought the method that
would work would work for multiple locations.

We apologize for the omission.

Gene

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