"last" function in totals query

[WimDC]

Hello,

I'm trying to build (a rather simple) totals-query, but the "last" function doesn't give me the right values.
Suppose I have a database where the amount of products that were sold are stored per month. It's possible that some products are not sold in some months. (see the really dummy-database in attachment)

Now, I want the LAST REAL VALUE (= amount) for each product, no matter what month :

prod1 = 4
prod2 = 3
prod3 = 3

How can I do that, because the LAST-function gives me
prod1 = (empty)
prod2 = 3
prod3 = (empty)

Thanks for helping!

 

[pr2-eugin]

Hello WimDC.. I would suggest you look into your table Design again.. The way you currently have stored data is not a good Normalized structure.. Your table should have IMHO, only four fields..

tblProductSummary
autoID - (PK)
productID
sellDate
sellQty

Thus your data in Table would be like..

autoID    productID    sellDate    sellQty
1            1        05/01/2013    10
2            1        06/02/2013    5
3            2        06/02/2013    15
4            3        06/02/2013    10

Then your Query would be very simple..

 

[WimDC]

Hi pr2-eugin,

Thanks for your reply.
I know I have to normalize the database / design; that's why I called it a "really dummy database". My only intention was to show how I have to find the "last totals".

I want to try your suggestion (= after normalizing), but for the moment, I don't see your solution for a very simple query.
Suppose I change the data you gave to this (I changed the months for productID 2 and 3):

autoID    productID    sellDate    sellQty
1            1        05/01/2013    10
2            1        06/02/2013    5
3            2        06/01/2013    15
4            3        06/01/2013    10

... then I would like to have these "last values":

productID    last sellQty
1                 5                       (= totals of February)
2                 15                     (= totals of January)
3                 10                     (= totals of January)

How do I make such a very simple query?

 

[pr2-eugin]

In that case, the following will be the Query..

SELECT tblProductSummary.productID, Last(tblProductSummary.sellQty) AS LastOfQty
FROM tblProductSummary
GROUP BY tblProductSummary.productID;

 

[WimDC]

Yes, that did it.

THANKS !!

 

[Brianwarnock]

You should not use Last for this but Max on the date which will then require 2 queries 1 to get the nax date in a Group and the 1 to use this to get the rest of the information from that record.
From Help

You use the First or Last functions to return the first or last record in the group you are performing calculations on. These functions return the first or last record as it was entered in chronological order. Sorting the records has no effect on these functions.

My bold. Not sure why First and Last exist ditto the domain functions.

Brian

 

[Pat Hartman]

Actually Brian, it's worse than that. You can never depend on the First OR Last values unless the table has been compacted prior to the query and the table has not been updated. You start out with records essentially in primary key order and that is also the sequence after compacting. However, Access records are variable length and they are written to disk one after another. What happens is if you update a record and change its length to be longer than it was originally, the entire record can't be written back into place so Access puts it somewhere else. Usually at the end of the file. Other RDBMS handle this a little differently but records still get displaced.

So, if I have 1,2,3,4 and I update 1 and increase its length such that it has to be moved, I end up with 2,3,4,1 and so First is 2 and Last is 1. Probably not what you really want.

Bottom line is First and Last are pretty useless. Min() and Max() are more likely to return what you are looking for.