Respose from Steve

  • Thread starter Thread starter Gene C
  • Start date Start date
G

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
Click to expand...
 
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
Click to expand...

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
Click to expand...

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
 
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,
Click to expand...
 
Back
Top