Query problem using "last" (4 Viewers)

[ryetee]

The ONLY time that LAST works reliably as an aggregate (either in a DLAST - domain qualifier - or SELECT LAST(x) - SQL qualifier) is if you have a query that sorts by some field that uniquely determines the particular order in which something appears. If you have blank or null in some records for this ordering field, at least SOME of those records WILL be out of order or will just not appear at all. In your context, since you admit to having some blank fields, you ARE going to have issues.

The reason is simple: If your field's value for LAST, whatever is being ordered, is not guaranteed, then you run into the problem that LAST doesn't always mean the last date listed on a transaction. It instead could refer to the last time that record was updated. Particularly if more than one record has the same value, the order of LAST is questionable. The aggregate FIRST / DFIRST suffers from the same problem but from the other direction.

Thanks for the info

 

[Ken Sheridan]

I can but in talks with the user about this and he's questioning why he wants the due date anyway.

If the aim is to track payments for goods or services ordered then you might find the attached little demo file of interest. It enables line items from multiple orders by a customer to be consolidated into a single invoice as and when the goods/services are supplied. The demo also includes an invoices form in which one or more payments against can be recorded in a subform.

 

[ryetee]

Last() will return the Last record you added, While Max() will return the Biggest value in a Field.
suggest you add a TimeStamp field to your table that whenever you Add New, this field is Updated to
the current Date/Time.
then you will only need to Lookup the Max() of this field to retrieve the latest.

It doesn't return last record added. It seems to return the last in the order they have been stored

 

[ryetee]

It appears each next payment is 3 days after the last one so create a query that contains the Max Next Due Date then lookup that value and use it as the default value for the Next Due Date field + 3.

That is the default but user can change it so it can't be guaranteed

 

[Ken Sheridan]

Unfortunately when the order is paid in full it's dateless

The query I posted in post #10 provides a model for handling that. In your case customers are analogous to patients, orders to patient treatments, order lines to patient treatment programme rows, and products to treatments. The nested subqueries determine whether a row is the latest in date order if all rows have dates, or otherwise the date is Null.

 

[ryetee]

If the aim is to track payments for goods or services ordered then you might find the attached little demo file of interest. It enables line items from multiple orders by a customer to be consolidated into a single invoice as and when the goods/services are supplied. The demo also includes an invoices form in which one or more payments against can be recorded in a subform.

Thanks Ken. It's really a list of orders ultimately for the taxman to show what's been collected and what's outstanding. He won't be interested in the due date so user just wants it removing. Wish I'd know before I spent the entire night scratching my head!

 

[ryetee]

The query I posted in post #10 provides a model for handling that. In your case customers are analogous to patients, orders to patient treatments, order lines to patient treatment programme rows, and products to treatments. The nested subqueries determine whether a row is the latest in date order if all rows have dates, or otherwise the date is Null.

OK I'll take a look
Thanks

 

[Ken Sheridan]

He won't be interested in the due date so user just wants it removing

My demo doesn't include a due date. The clinical query on the other hand is from a file which does include a DateScheduled column, but it's not used by the query. That's based solely on the DateAdministered column, so a Null in that column means the patient is still awaiting treatment. This is semantically the opposite to a Null date in your case, where it means there are no outstanding payments. This does illustrate why Null can problematical, however, as there is nothing inherent in the data itself to tell you this is what's indicated.

 

[GPGeorge]

It doesn't return last record added. It seems to return the last in the order they have been stored

Please re-read the various comments in this thread regarding the ill-advised use of "Last" in queries. Because it is unreliable, the alternatives, based on Max() or Min() values are almost always the safer method than "last", however, you choose to define it.

 

[Isaac]

I'm a strong supporter of having a DateTimeStamp column in a table, but assigning the return value of the Now() function to the DefaultValue property is not in itself sufficient. While it's fine if a row is being inserted by means of an INSERT INTO statement or by code into a recordset, in a bound form the value assigned to the DefaultValue property is the DateTime value when the record pointer is moved to an empty new record, which could be considerably before the user begins to insert any data.

The solution is to insert the value in the appropriate event procedure. Which one you use depends on whether you want the value to be when the user begins to insert data, or when the row is saved to the table. If the former then the form's BeforeInsert event procedure can be used. If the latter then the BeforeUpdate event procedure can be used, though in the latter case the assignment should be conditional on the form's NewRecord property being True. In either case the value should be assigned to the column's Value not its DefaultValue property.

I suppose technically that's true, although I've never wanted for more information than I got from using the default value, but sure, you could make it more accurate from a systems standpoint as far as the point of insertion goes by doing it on your own in the event procedure. But that only applies to the "insertion date" concept. what about CreatedDate? As far as Access is concerned, that record has begun to be created at the time it applies that default value - whether we like it or not. But who knows when the business considers a record Created - may even need to ask them.

 

[Isaac]

Although I added the "last" bit in one of my amendments, the underlying design is not mine. If it was I'd have exactly that!

Add if you can, priceless for troubleshooting.

 

[The_Doc_Man]

It doesn't return last record added. It seems to return the last in the order they have been stored

Very close to reality. So far as I can tell from a few experiments, LAST (in the absence of an ORDER BY clause) returns the record that was last updated with one exception. After a COMPACT & REPAIR, records that have a primary key will be compacted in order of the PK. Records with no PK are stored such that the real-world chronologically last record to be updated becomes the record selected by LAST. When records are loaded in bulk, it APPEARS that they are loaded in the order they were presented, even if in theory, the presentation is supposed to be "monolithic" and simultaneous. Once the records are "live" there is no guarantee they will retain that order. In fact, if you did an UPDATE with an ORDER BY x DESC clause, the records could actually appear in the reverse order of their previous order when done.

 

[Ken Sheridan]

As far as Access is concerned, that record has begun to be created at the time it applies that default value - whether we like it or not. But who knows when the business considers a record Created - may even need to ask them.

Let's assume that, in a table in which the DateTimeStamp column is assigned its value by its DefaultValue property, user A navigates to an empty new record in a bound form, but doesn't begin to insert a row. User B then navigates to a new record and inserts a credit transaction. User C then inserts a debit Transaction. User A then returns to the form and inserts a debit transaction. Chronologically the account has remained in credit throughout this process. However, user A's transaction will be first in the sequence on the basis of the values in the DateTimeStamp column, and this shows spuriously that the account has been overdrawn following user A's transaction.

The table is thus not an accurate representation of the true sequence of events. Moreover, user A will be unaware of this as, even if the form includes code to warn the user that the account does not contain sufficient funds to meet the withdrawal, this code will not be triggered.

In the past it was normal banking practice to post all current account credits in each business day before all debits, so none of this would matter, and recording the time of day of each transaction would be pointless, but nowadays, as any current account statement will show, transactions are posted in the order in which they take place.

PS: One way to avoid this problem would be by, rather than date/time stamping rows, using Roger Carlson's method for generating sequential numbers. In the event of clashes in a multi-user environment Roger's solution increments the values where necessary, so the numeric sequence does reflect the order in which rows are saved to the table.

 

[arnelgp]

For the sake of argument, supposed your table does not have autonumber field or any numeric field.
what you have is FirstName and LastName (all short text) fields only.
How then will you know which is the Last record entered without using Last() in SQL?

Last() would have been stripped by MS if they don't know what the is the purpose of it, do they?
after all, MS created this function and must know the usefulness of it.

 

[The_Doc_Man]

I know that MSFT acquired Access via their usual method - they bought out the company that made it. (You can look that up on Wikipedia.) I have some doubts that they would remove anything at least initially. But there is also this to consider: FIRST and LAST actually ARE functional and even productive when there are ORDER BY elements in the query being used. I could see using the LAST aggregate as a way to know where you stopped if you were the last or only person to make data entry, and you wanted to know where you stopped. As long as the recordset hasn't been programmatically updated, it would be easy find out where you left off last night. Admittedly, an imprudent method - but it could have value.

 

[arnelgp]

As long as the recordset hasn't been programmatically updated, it would be easy find out where you left off last night. Admittedly, an imprudent method - but it could have value.

test it first. add a record one by one.
Create a query that uses Last("yourFieldName") against your table and save the query (qryLast).
run the query and note the result.
edit any record (not the record qryLast has shown).
then run/refresh the query and you will get same result.
do any edits on another future time, maybe tomorrow or after tomorrow, but not on the "last record" shown in qryLast.
you will notice, it is giving the same results over and over.
so editing a record, does not stamp the record it with it's "creation date".

 

[arnelgp]

I know that MSFT acquired Access via their usual method - they bought out the company that made it. (You can look that up on Wikipedia.

if you acquire something, say a house, or a car, i am sure you get the "house plan" or the car manual.
it include the technical manuals.
you don't acquire and update (as ms does with mso) it blindly.

i am sure you have those "field manuals", "parts manual" "tech manuals" while you are on the navy.

 

[The_Doc_Man]

Not what I meant, @arnelgp - this has to do with the rules of shared DBs in which one user is updating a record while another user is just reading it. When that happens, the new (updated) record is created at the end of the recordset (based on memory allocation algorithms) but the in-use record is still listed in the recordset until the automatic requery occurs (based on a File >> Options >> Current DB parameter). The user doing the update won't see the old record any more and the user reading the old record can't see the new one - but the two records exist until everyone leaves the DB or closes the recordset.

 

[The_Doc_Man]

you don't acquire and update (as ms does with mso) it blindly.

I don't. You don't. MSFT? No confidence in how they treat things. Why do you think that Access and Excel have different date references for day 0 of their internal calendar?

 

[arnelgp]

No confidence in how they treat things.

Why do you think that Access and Excel have different date references for day 0 of their internal calendar?

that is way off this topic.