Using 'LastOf' function in queries

[Candice]

Hi, I have inherited some Access databases that are using 'LastOf' in queries
to pull records to show on forms and reports. I have never used this function
but apparently it returns the 'last' record of the recordset. This doesn't
really mean anything to me because there is no Order By clause. I assume
Access is ordering the records in whatever way it chooses (the order they
were created?) and giving me the last one. So my question: Shouldn't LastOf
always be used with OrderBy? The users are telling me they have intermittent
problems with a boolean field showing up true one day, and false the next on
reports and forms. I think this is due to using LastOf with no OrderBy. Do
you think I'm right?

To make matters worse, I can see that when users update various columns on
the table through a form, the form adds a new row to the table instead of
updating the current row. So now data on all previous rows is hidden by the
most current row. I believe the programmer must have been trying to use
'LastOf' to overcome this problem. But it is unsuccesful. This stinks!
Suggestions?

 

[Stefan Hoffmann]

hi Candice,

Hi, I have inherited some Access databases that are using 'LastOf' in queries
to pull records to show on forms and reports. I have never used this function
but apparently it returns the 'last' record of the recordset. This doesn't
really mean anything to me because there is no Order By clause. I assume
Access is ordering the records in whatever way it chooses (the order they
were created?) and giving me the last one.

There is an aggregate function called LAST().

http://mvps.org/access/queries/qry0020.htm

As it says: the Jet engine determines what last means.

Use an explict date/time field with a TOP 1 and a ORDER BY clause.

mfG
--> stefan <--

 

[Candice]

Sorry for the typo - I mean to ask about the 'Last' function. The programmer
of this database named all the queries 'qryLastOf...' and so I typed the
wrong thing.

 

[Candice]

OK well I did find another post similar to mine so my assumption is correct -
the 'Last' function is pretty useless unless you use an Order By clause.
Meanwhile, I have inherited a database that is using 'Last' with no Order By,
ALL OVER the place, and the users are complaining of data appearing and
disappearing randomly. I'm going to file for technical harrassment.

 

[Candice]

Thank you for that link; it's helpful. I think my problem lies even deeper
than this. Say user1 updates the row with information in column1. Then user2
updates information in column2. Instead of ending up with one row with info
in both column1 and in column2, I end up with 2 rows - one with info in
column1 and one with information in column2. Then the report gets run using
the 'Last' function. Well, neither row shows the correct information. Even if
I select the row with the max timestamp (and of course there is no timestamp
column), I will still get the wrong information. So the update part of this
is wrong as well. I need a drink.

 

[Stefan Hoffmann]

hi Candice,

OK well I did find another post similar to mine so my assumption is correct -
the 'Last' function is pretty useless unless you use an Order By clause.
Meanwhile, I have inherited a database that is using 'Last' with no Order By,
ALL OVER the place, and the users are complaining of data appearing and
disappearing randomly. I'm going to file for technical harrassment.

I don't think your users will understand it Basically even with ORDER
BY the LAST() is not useful, thus your users have these problems.


mfG
--> stefan <--

 

[Stefan Hoffmann]

hi Candice,

Thank you for that link; it's helpful. I think my problem lies even deeper
than this. Say user1 updates the row with information in column1. Then user2
updates information in column2. Instead of ending up with one row with info
in both column1 and in column2, I end up with 2 rows - one with info in
column1 and one with information in column2.

Sounds wierd. Can you explain this with more details?

Well, neither row shows the correct information. Even if
I select the row with the max timestamp (and of course there is no timestamp
column), I will still get the wrong information. So the update part of this
is wrong as well. I need a drink.

Go for a beer

You need this date/time timestamp column. All other efforts will be
pretty useless.

mfG
--> stefan <--

 

[Jeff Boyce]

Candice

When you file, will you be looking to harrass the developer, or to recover
pain/suffering damages?

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[Candice]

BOTH!!!
As well as the cost of all the Advill I'm having to take