Use of Last function in a query

  • Thread starter Thread starter Andy
  • Start date Start date
A

Andy

Hi,

I have several tables in my database..

Products, Customer, Items, Supplier.

The Items is a kind of transaction table that stores
every product sold.

What I want to achieve is a query that will only show the
last entry (based on the last date) of the item table,
but also including desciption fields from other tables. I
just wnt to be able to see what the last price a product
was sold to somebody (which is why i was thinking to use
Last on the date field) By the way the date field is
sorted.

The problem is that each time an item is recorded, the
cost and sell price are likely to be different, therefore
I want to be able to see every product sold for each
customer (grouped by the Product) so there may be many
boots sold to the same customer and from the same
supplier but different date and sell/cost prices. It is
just the last date for each different product that I want
to show

e.g

CustomerName ProdDesc Date Cost Sell Supplier
=========================================================
Joe Bloggs Boots 10/06/04 20.00 40.00 BootsRUs
Joe Bloggs Jacket 09/06/04 15.00 35.00 JktsRUs
John Smith Jacket 05/06/04 15.00 35.00 JktsRUs

etc etc

The query I have setup gets info from the user for the
customer name and the product description (although
either or both of these can be left blank to return all)

When I run the query, it is still showing all items and
not just the latest date for that product, even though I
have used Last([Date]). I know I must be doing something
wrong.....but WHAT :-)

I hope I am making myself clear ?? :-)

Any help would be appreciated

Thanks

Andy
 
Andy

The Access Last() aggregate function may not be doing what you expect.
Instead, try using the Max() aggregate function (under the assumption that
the maximum value in the date field is the most recent date/time).
 
Hi Jeff,

I have tried the Max() function however it still seems to
list every item, rather than the last.

I have tried putting a Where in the other fields, and it
does then only show the correct items BUT it then does
not show the fields with the Where Clause in. If I then
add these fields again twice (as access suggests) I then
get all my entries back again.

Any ideas?

TIA

Andy
 
Hi Jeff,

I have tried the Max() function however it still seems to
list every item, rather than the last.

I have tried putting a Where in the other fields, and it
does then only show the correct items BUT it then does
not show the fields with the Where Clause in. If I then
add these fields again twice (as access suggests) I then
get all my entries back again.
Click to expand...

You may do better to use a Subquery: Max() will return the maximum
date in the field, but if you're still grouping by the other fields,
you'll see every record where ANY field is different.

Instead, try using a (non totals) query with a criterion of

=(SELECT Max([Date]) FROM yourtable AS X WHERE X.CustomerName =
yourtable.CustomerName)

on the date field.

Note that Date is a reserved word (for the today's-date function) and
Access can get confused; you may want to use Saledate instead. And
also, if you are in fact storing the customer name in the sales table,
you'll run into problems both with different customers who happen to
have the same name (I know three guys named Fred Brown) and with the
same person showing up as two different names, due to misspellings or
"Bob"/"Robert" and the like. You may want to consider a Customers
table and a CustomerID in the sales table.
 
Thanks John,

I will try the subquery. Also, the Date part may have
been causing problems as that is what I used in my query.

I have a customers table, so customers names are only
stored in that table. The Items table is baed on the
CustomerID.

Thanks for your help

Andy
-----Original Message-----
Hi Jeff,

I have tried the Max() function however it still seems to
list every item, rather than the last.

I have tried putting a Where in the other fields, and it
does then only show the correct items BUT it then does
not show the fields with the Where Clause in. If I then
add these fields again twice (as access suggests) I then
get all my entries back again.
Click to expand...

You may do better to use a Subquery: Max() will return the maximum
date in the field, but if you're still grouping by the other fields,
you'll see every record where ANY field is different.

Instead, try using a (non totals) query with a criterion of

=(SELECT Max([Date]) FROM yourtable AS X WHERE X.CustomerName =
yourtable.CustomerName)

on the date field.

Note that Date is a reserved word (for the today's-date function) and
Access can get confused; you may want to use Saledate instead. And
also, if you are in fact storing the customer name in the sales table,
you'll run into problems both with different customers who happen to
have the same name (I know three guys named Fred Brown) and with the
same person showing up as two different names, due to misspellings or
"Bob"/"Robert" and the like. You may want to consider a Customers
table and a CustomerID in the sales table.


.
Click to expand...
 
Hi John,

Tried the sub query, and with a bit of additions to make
it compare with ProductDescription aswell as the customer
name, it now works exactly as wanted.

Thanks very much

Andy
-----Original Message-----
Hi Jeff,

I have tried the Max() function however it still seems to
list every item, rather than the last.

I have tried putting a Where in the other fields, and it
does then only show the correct items BUT it then does
not show the fields with the Where Clause in. If I then
add these fields again twice (as access suggests) I then
get all my entries back again.
Click to expand...

You may do better to use a Subquery: Max() will return the maximum
date in the field, but if you're still grouping by the other fields,
you'll see every record where ANY field is different.

Instead, try using a (non totals) query with a criterion of

=(SELECT Max([Date]) FROM yourtable AS X WHERE X.CustomerName =
yourtable.CustomerName)

on the date field.

Note that Date is a reserved word (for the today's-date function) and
Access can get confused; you may want to use Saledate instead. And
also, if you are in fact storing the customer name in the sales table,
you'll run into problems both with different customers who happen to
have the same name (I know three guys named Fred Brown) and with the
same person showing up as two different names, due to misspellings or
"Bob"/"Robert" and the like. You may want to consider a Customers
table and a CustomerID in the sales table.


.
Click to expand...
 
Back
Top