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;
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;
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);
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 -----Sent: Saturday, June 06, 2015 3:06 PMSubject: 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
__._,_.___
Posted by: "Dave W" <davewi11@yahoo.co.uk>
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (11) |
.
__,_._,___
Tidak ada komentar:
Posting Komentar