help - total max working how to get other data (1 Viewer)

[paulhaggo]

Hello

I am new here, i have been :bangheadn this one, i know a fair bit about access 2010 but i am truely stuck on this, can anyone help

I have the following table

------------------------------------------------------------------------------
- product no ----- Branch ID --- POS Description --last sold date --
------------------------------------------------------------------------------
- 123 ------- 1 ----- fly rod ---- 19/01/2014 ----
- 123 ------- 2 ----- fly rod ---- 19/03/2014 ----
- 123 ------- 3 ----- fly rod ---- 19/05/2014 ----
- 123 ------- 4 ----- fly rod ---- 19/06/2014 ----
- 123 ------- 5 ----- fly rod ---- 19/09/2014 ----
- 633 ------- 1 ----- fly reel ---- 11/01/2014 ----
- 633 ------- 2 ----- fly reel ---- 11/03/2014 ----
- 633 ------- 3 ----- fly reel ---- 11/05/2014 ----
- 633 ------- 4 ----- fly reel ---- 11/06/2014 ----
- 633 ------- 5 ----- fly reel ---- 11/09/2014 ----
------------------------------------------------------------------------------

I need it to give me back, one row per product (product no) - the newest date (Date Last Sold)

So i am trying to find out the last time each product sold, regardless of where

Which i can do with MAX date, group by product num

SELECT SMKPLUDetail.[Product No], Last(SMKPLUDetail.[Branch ID]) AS [LastOfBranch ID], Last(SMKPLUDetail.[POS Description]) AS [LastOfPOS Description], Max(SMKPLUDetail.[Date Last Sold]) AS [MaxOfDate Last Sold]
FROM SMKPLUDetail
GROUP BY SMKPLUDetail.[Product No];


------------------------------------------------------------------------------
- product no ----- Branch ID --- POS Description --last sold date --
------------------------------------------------------------------------------
- 123 ------- 5 ----- fly rod ---- 19/09/2014 ----
- 633 ------- 5 ----- fly reel ---- 11/09/2014 ----
------------------------------------------------------------------------------

This is great, but ofcourse (last of is fine for description) as is does not change

But last of, always returns branch 11.. and i infact want the branch which is correct, the branch which the date last sold is from, the current record really

but i do not seem to be able to get it to show me, any other fields from the querry current row

any ideas ??

thank you

 

[namliam]

Try joining your query back to your original table.

 

[paulhaggo]

i tried make table, with what i had, then a relationship whereby it used 2 colums (pro num and date sold) matches, to find the branch id

How ever i do not really understand how, or if a multi column query would work, as i have only done 1 column joins

do you think i should be using a inner join or left.right in the sql

or, do a make table and carry on from there

sorry i'm a bit lost at this bit

 

[CazB]

might not be the 'best' solution, but this should work....

Use 2 queries: in the first one, just pull out the Product Number and date last sold

Query1:

SELECT SMKPLUDetail.[Product No], Max(SMKPLUDetail.[Date Last Sold]) AS [Last Sold]
FROM SMKPLUDetail
GROUP BY SMKPLUDetail.[Product No];

Query2:

SELECT SMKPLUDetail.[Product No], SMKPLUDetail.[Branch ID], SMKPLUDetail.[POS Description], SMKPLUDetail.[Date Last Sold]
FROM Query1 INNER JOIN SMKPLUDetail ON (Query1.[Last Sold] = SMKPLUDetail.[Date Last Sold]) AND (Query1.[Product No] = SMKPLUDetail.[Product No]);