Total Query

[Jason Lepack]

Last returns the last record entered into the database.

Instead of using last, use Max.

Cheers,
Jason Lepack

 

[Greg]

I would like to thank those who have given me assistance in getting this far
but I have run into something else..

I am trying to select records with the greatest date field. I do have
partial success(sometimes). The

Example:

Table Invoice

Telephone Number (Account Number)
Invoice Date(mm/dd/yy)
Billing Code
Current Charges
Local Service Charges

 

[John Vinson]

Performs a Last on the date field

That's your problem.

Last is a pretty much USELESS aggregate function. It returns the last
record - *in disk storage order*, an order over which you have no
control and generally no interest!

Use Max instead to retrieve the maximum date, or - more likely - use a
Subquery instead to filter the records so that you return only a
single, most recent record rather than an aggregate (which might have
some fields from one record and some fields from another). In SQL view
it would be something like

SELECT [Telephone Number], [Invoice Date(mm/dd/yy)], [Billing Code],
[Current Charges], [Local Service Charges]
FROM Invoices
WHERE [Invoice Date(mm/dd/yy)] = (SELECT Max([Invoice Date(mm/dd/yy)])
FROM Invoices AS Z WHERE Z.[Telephone Number] = Invoices.[Telephone
Number]);


One suggestion: Special characters such as parentheses, slashes, and
even blanks really should *not* be used in fieldnames. SInce users
should generally not be seeing table datasheets at all, they can be
provided with readable labels on Forms; the table fieldnames could be
"camel case" single words such as TelephoneNumber or InvoiceDate.

John W. Vinson[MVP]

 

[Greg]

Jason and John,

Thank you- Max works great! John can you explain when I would possibly get
"(which might have
some fields from one record and some fields from another)" ?

Thank You,
Greg

Performs a Last on the date field

That's your problem.

Last is a pretty much USELESS aggregate function. It returns the last
record - *in disk storage order*, an order over which you have no
control and generally no interest!

Use Max instead to retrieve the maximum date, or - more likely - use a
Subquery instead to filter the records so that you return only a
single, most recent record rather than an aggregate (which might have
some fields from one record and some fields from another). In SQL view
it would be something like

SELECT [Telephone Number], [Invoice Date(mm/dd/yy)], [Billing Code],
[Current Charges], [Local Service Charges]
FROM Invoices
WHERE [Invoice Date(mm/dd/yy)] = (SELECT Max([Invoice Date(mm/dd/yy)])
FROM Invoices AS Z WHERE Z.[Telephone Number] = Invoices.[Telephone
Number]);


One suggestion: Special characters such as parentheses, slashes, and
even blanks really should *not* be used in fieldnames. SInce users
should generally not be seeing table datasheets at all, they can be
provided with readable labels on Forms; the table fieldnames could be
"camel case" single words such as TelephoneNumber or InvoiceDate.

John W. Vinson[MVP]

 

[John Vinson]

Jason and John,

Thank you- Max works great! John can you explain when I would possibly get
"(which might have
some fields from one record and some fields from another)" ?

If you JUST use a Totals query, the [Current Charges] or [Billing
Code] field will need to either be in the Group By - which will give
you one record per different value of the charges and code rather than
the most recent - or some other aggregate function such as Max. That
would give you (e.g.) the latest date (from the Max of date) and -
*independently* - the largest charge anywhere in the table (from the
Max on the charge).

The subquery is unambiguous - it will find the latest record and
display all the fields from that record.

John W. Vinson[MVP]

 

[Greg]

John,


Thank You,

Greg

Jason and John,

Thank you- Max works great! John can you explain when I would possibly get
"(which might have
some fields from one record and some fields from another)" ?

If you JUST use a Totals query, the [Current Charges] or [Billing
Code] field will need to either be in the Group By - which will give
you one record per different value of the charges and code rather than
the most recent - or some other aggregate function such as Max. That
would give you (e.g.) the latest date (from the Max of date) and -
*independently* - the largest charge anywhere in the table (from the
Max on the charge).

The subquery is unambiguous - it will find the latest record and
display all the fields from that record.

John W. Vinson[MVP]