Re: [MS_AccessPros] Re: Query speed

 

Ken-

If you can depend on one record per date or a sequence number, then that's the better way to go.  Note that you can also put that criteria on the ON clause of your JOIN.

FROM [Water Readings] As R1 INNER JOIN

[Water Readings] As R2 

ON R2.ReadingDate = (R1.ReadingDate - 1)

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 3:27 AM, statmanbp@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

From query I posted earlier

Old Code: (SELECT Max(R3.ReadingDate)
      FROM [Water Meter Readings] AS R3  
      WHERE (R3.frg_MeterID = R1.frg_MeterID)  
        AND (R3.ReadingDate < R1.ReadingDate)    
    )

New Code: [R1].[ReadingDate]-1

M is alias for table [Meter Information] R1 is alias for table [Water Meter Readings] R2 is alias for Water Meter Readings

M table [Meter ID] Joins frg_MeterID one to one and R1frg_MeterID Joins R2 frg_MeterID one to many

The above code was to retrieve the previous record regardless of Date. The current code requires all days to be entered. The database runs quickly with the new code.  Is there a better way to retrieve the last record instead of subtracting days. I look at selecting an ID field but am afraid of deleted records. I hope this makes sense.
Ken

__._,_.___

---

Posted by: John Viescas <johnv@msn.com>

---

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•

Messages in this topic (8)

Visit Your Group

• Privacy • Unsubscribe • Terms of Use

.

__,_._,___