Simple Query Help

[MX1]

Help? This is simple but I'm drawing a blank. Have a very small table, but
need specific data out of it. Here is the structure:

ClientID InvDate ItemAmt
001 3/7/03 $10
001 3/7/03 $20
001 5/8/04 $10
001 5/8/04 $3
002 5/9/04 $5
002 5/9/04 $2

I'm trying to make the query do two things. First is go through data set
and find items with "most recent or MAX date". Second, sum up items for a
that most recent date into a single line item total. Do I need to split
into 2 queries? Resultant set should look like this:

Client ID InvDate ItemAmt
001 5/8/04 $13
002 5/9/04 $7

Can anyone help with this? I'm drawing a blank. Thanks!

[Peter Russell]

Create two queries.
Q1:based on main table(T1)
Include ClientId, InvDate; convert to a Totals query;
Group By ClientId and set InvDate to max

Q2:
based on T1 and Q! joined on ClientId and Invdate = Max(Invdate)
Include Clientid, invdate and itemamt from T1.
Convert to a Totals query.
GroupBy ClientId and Invdate, set ItemAmt to Sum

Peter

MX1 previously wrote:

Help? This is simple but I'm drawing a blank. Have a very small
table, but
need specific data out of it. Here is the structure:

ClientID InvDate ItemAmt
001 3/7/03 $10
001 3/7/03 $20
001 5/8/04 $10
001 5/8/04 $3
002 5/9/04 $5
002 5/9/04 $2

I'm trying to make the query do two things. First is go through data
set
and find items with "most recent or MAX date". Second, sum up items
for a
that most recent date into a single line item total. Do I need to split
into 2 queries? Resultant set should look like this:

Client ID InvDate ItemAmt
001 5/8/04 $13
002 5/9/04 $7

Can anyone help with this? I'm drawing a blank. Thanks!

[Theo Peterbroers]

"MX1" <mx*@mx1.abc> wrote in message news:<TeLGc.3799$WX.2252@attbi_s51>...

Help? This is simple but I'm drawing a blank. Have a very small table, but
need specific data out of it. Here is the structure:

ClientID InvDate ItemAmt
001 3/7/03 $10
001 3/7/03 $20
001 5/8/04 $10
001 5/8/04 $3
002 5/9/04 $5
002 5/9/04 $2

I'm trying to make the query do two things. First is go through data set
and find items with "most recent or MAX date". Second, sum up items for a
that most recent date into a single line item total. Do I need to split
into 2 queries? Resultant set should look like this:

Client ID InvDate ItemAmt
001 5/8/04 $13
002 5/9/04 $7

Can anyone help with this? I'm drawing a blank. Thanks!

ItemAmt is a number, right?

SELECT a.ClientID, a.InvDate, Sum(a.ItemAmt)
FROM TableX AS a
GROUP BY a.ClientID, a.InvDate
HAVING a.InvDate=
(SELECT max(invdate)
FROM TableX b
WHERE b.clientid=a.clientid
)
;

[MX1]

Thanks, I'll give this a try.

:)

"Theo Peterbroers" <pe*********@floron.leidenuniv.nl> wrote in message
news:39**************************@posting.google.c om...

"MX1" <mx*@mx1.abc> wrote in message

news:<TeLGc.3799$WX.2252@attbi_s51>...

Help? This is simple but I'm drawing a blank. Have a very small table, but need specific data out of it. Here is the structure:

ClientID InvDate ItemAmt
001 3/7/03 $10
001 3/7/03 $20
001 5/8/04 $10
001 5/8/04 $3
002 5/9/04 $5
002 5/9/04 $2

I'm trying to make the query do two things. First is go through data set and find items with "most recent or MAX date". Second, sum up items for a that most recent date into a single line item total. Do I need to split
into 2 queries? Resultant set should look like this:

Client ID InvDate ItemAmt
001 5/8/04 $13
002 5/9/04 $7

Can anyone help with this? I'm drawing a blank. Thanks!

ItemAmt is a number, right?

SELECT a.ClientID, a.InvDate, Sum(a.ItemAmt)
FROM TableX AS a
GROUP BY a.ClientID, a.InvDate
HAVING a.InvDate=
(SELECT max(invdate)
FROM TableX b
WHERE b.clientid=a.clientid
)
;

[MX1]

Thanks. Worked like a champ.
:)

"Peter Russell" <ru***@127.0.0.1> wrote in message
news:me**********************@russellscott.btinter net.com...

Create two queries.
Q1:based on main table(T1)
Include ClientId, InvDate; convert to a Totals query;
Group By ClientId and set InvDate to max

Q2:
based on T1 and Q! joined on ClientId and Invdate = Max(Invdate)
Include Clientid, invdate and itemamt from T1.
Convert to a Totals query.
GroupBy ClientId and Invdate, set ItemAmt to Sum

Peter

MX1 previously wrote:

Help? This is simple but I'm drawing a blank. Have a very small
table, but
need specific data out of it. Here is the structure:

ClientID InvDate ItemAmt
001 3/7/03 $10
001 3/7/03 $20
001 5/8/04 $10
001 5/8/04 $3
002 5/9/04 $5
002 5/9/04 $2

I'm trying to make the query do two things. First is go through data
set
and find items with "most recent or MAX date". Second, sum up items
for a
that most recent date into a single line item total. Do I need to split
into 2 queries? Resultant set should look like this:

Client ID InvDate ItemAmt
001 5/8/04 $13
002 5/9/04 $7

Can anyone help with this? I'm drawing a blank. Thanks!