ACCESS FEED: Re: [MS_AccessPros] Re: Query speed

Minggu, 07 Juni 2015

Re: [MS_AccessPros] Re: Query speed

John,

The MIN is only an easy check to see if there is more than one record. It is not used in the final result.

TOP is new to me - I see that the trailing figure is a percentage, so how many records would your example return if there were less than 50 records?

The OP said that there is a one to many link between the first and second copy of the Water Meter Readings table. Would it be faster if instead there were two one-to-many links between his Meter Information table and the two copies of the Water Readings Table?

Dave W

----- Original Message -----

From: John Viescas JohnV@msn.com [MS_Access_Professionals]

To: MS_Access_Professionals@yahoogroups.com

Sent: Sunday, June 07, 2015 9:18 AM

Subject: Re: [MS_AccessPros] Re: Query speed

Dave-

I don't think that will work when there are more than 2 entries per meter in the table. Ken needs the immediately previous record, but MIN will return the oldest.

Something that might be faster than doing the = SELECT Max(ReadingDate) .. WHERE ReadingDate < ….) is to use TOP. A query like this would get the two most recent:

SELECT R1.frg_MeterID, R1.ReadingDate

FROM [Water Meter Readings] As R1

WHERE R1.ReadingDate IN

(SELECT TOP 2 ReadingDate

FROM [Water Meter Readings] As R2

WHERE R2.frg_MeterID = R1.frg_MeterID)

That might run fairly quickly if there are indexes on the meter ID and the date. Then you could use the Max / Min technique on that result set.

I tried that in the old Northwind sample on the Orders table (using Customer ID and Order Date), and it seemed to work correctly. Of course, there's not a lot of data in that sample.

John Viescas, Author

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications

SQL Queries for Mere Mortals

http://www.viescas.com/

(Paris, France)

On Jun 6, 2015, at 11:38 PM, 'Dave W' davewi11@yahoo.co.uk [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

Ken,

I've come up with a method of displaying the latest date and reading together with the previous date and reading on the same row for each meter. I assume every reading has a unique date, although they may be many days apart.

To get round the problem of a new meter not having any previous reading, my Query1 finds min and max dates for each meter, and another column 'NoHistory' is -1 or 0 respectively for min and max being same or different.

Query2 takes the max date from Query1, then shows the previous date or the same date depending on the value of 'NoHistory'.

If Query1 is not required because all readings have previous ones, then Query2 could be simplified.

Query3 shows the corresponding readings for the two dates, from two copies of the 'Water Meter Readings' table.

SQL is:
Query1:
SELECT R1.frg_MeterID, Max(R1.ReadingDate) AS Latest, Min(R2.ReadingDate) AS Earliest, [Latest]=[Earliest] AS NoHistory
FROM [Water Meter Readings] AS R1 INNER JOIN [Water Meter Readings] AS R2 ON R1.frg_MeterID = R2.frg_MeterID
GROUP BY R1.frg_MeterID;

Query2:
SELECT Query1.frg_MeterID, R1.ReadingDate, Max(R2.ReadingDate) AS Previous
FROM (Query1 INNER JOIN [Water Meter Readings] AS R1 ON (Query1.Latest = R1.ReadingDate) AND (Query1.frg_MeterID = R1.frg_MeterID)) INNER JOIN [Water Meter Readings] AS R2 ON Query1.frg_MeterID = R2.frg_MeterID
WHERE (((Query1.NoHistory)=0) AND ((R2.ReadingDate)<[R1]![ReadingDate])) OR (((Query1.NoHistory)=-1))
GROUP BY Query1.frg_MeterID, R1.ReadingDate;

Query3:
SELECT Query2.frg_MeterID, R1.ReadingDate, R1.Reading, R2.ReadingDate AS Previous, R2.Reading AS Was
FROM (Query2 INNER JOIN [Water Meter Readings] AS R1 ON (Query2.ReadingDate = R1.ReadingDate) AND (Query2.frg_MeterID = R1.frg_MeterID)) INNER JOIN [Water Meter Readings] AS R2 ON (Query2.frg_MeterID = R2.frg_MeterID) AND (Query2.Previous = R2.ReadingDate);

Dave W

----- Original Message -----

From: statmanbp@yahoo.com [MS_Access_Professionals]

To: MS_Access_Professionals@yahoogroups.com

Sent: Saturday, June 06, 2015 3:06 PM

Subject: Re: [MS_AccessPros] Re: Query speed

Thanks John,

I guess I could create both queries and have the report check for record dates and if a date is missing run the old query if they exist run the new query. Just feels like creating databases I spend more time on how the user can screw up than on what information I want. Oh well Thanks for the advise.

Ken