By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,481 Members | 1,199 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,481 IT Pros & Developers. It's quick & easy.

Simple Query Help

P: n/a
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!
Nov 13 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
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!


Nov 13 '05 #2

P: n/a
"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
)
;
Nov 13 '05 #3

P: n/a
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
)
;

Nov 13 '05 #4

P: n/a
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!

Nov 13 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.