Cross-query neded? 1

[Andrzejek]

If I have a table (MyTable) with fields and data like this:

[pre]
ID PROJECT MY_NO EST_DATE AMOUNT
1 ABC [blue]0001 1/1/2014 1200[/blue]
1 ABC 0001 5/5/2011 1000
1 ABC 0001 2/2/2010 2500
1 ABC [blue]0002 7/7/2014 5000[/blue]
1 ABC 0002 10/10/2013 4500
2 XYZ 0001 6/6/2011 2500
[/pre]

I would like to get:
1. For every ID, PROJECT, MY_NO get the AMOUNT for the latest EST_DATE
2. Add those AMOUNTs for all MY_NOs (for the same ID and PROJECT)
3. End up with info like this:

[pre]
1 ABC [blue]7/7/2014 6200[/blue]
2 XYZ 6/6/2011 2500
[/pre]

It will require some funky cross-query, I think.

I know I can get the latest date per ID, PROJECT with

SELECT ID, PROJECT, MAX(EST_DATE) AS MY_MAX_DATE
FROM MyTable
GROUP BY ID, PROJECT

but I am lost of how to get the SUMs of the other stuff.

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[dhookom]

You can try cahnge your SELECT to get the maximum date by ID, PROJECT, and MY_NO. Then join this query to MyTable in a final query that GROUPS BY ID and PROJECT while summing AMOUNT.

Duane
Hook'D on Access
MS Access MVP

 

[PHV]

You wanted this ?

SELECT A.ID, A.PROJECT, Max(EST_DATE) AS LastDate, Sum(AMOUNT) AS Total
FROM MyTable A INNER JOIN (
SELECT ID, PROJECT, MY_NO, MAX(EST_DATE) AS MY_MAX_DATE FROM MyTable GROUP BY ID, PROJECT, MY_NO
) B ON A.ID = B.ID AND A.PROJECT = B.PROJECT AND A.EST_DATE = B.MY_MAX_DATE
GROUP BY A.ID, A.PROJECT

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Andrzejek]

dhookom, that's what I was thinking but could not quiet get the syntax right, but PHV got it on the very first shot (not a surprise )

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.