Query problem using "last" (1 Viewer)

[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.