Repeating the max date in a sum query

[Frank]

Hi again,. You all have been so very helpful on this forum. Thank you.
My endgame is a crosstab query where I will be putting dates for IDs and
amounts into buckets. Problemm is the same ID can appear in diff date
buckets so in the query that feeds the crosstab I'm trying to make it look at
the latest date and copy that date for evey ID. I can them sum on the ID and
place that into the date buckets and the amount buckets, in the rows.

Here's the query:

DATE RISKID AMOUNT AMOUNT BUCKET DATE BUCKET
31-May-06 9L ($1,260) (a) Less than 5K 30-60 days
11-May-06 7L ($3,880) (a) Less than 5K 60-90 days
02-May-06 7L $52,895 (c) Between 50-100K 60-90 days
01-Jun-06 7L $11,542 (b) Between 5-50K 30-60 days
02-May-06 7L ($52,894) (c) Between 50-100K 60-90 days
11-May-06 7L $14,379 (b) Between 5-50K 60-90 days

I need a subquery to add 01-Jun-06 to all the 7L (the bucket fileds in this
example would actually be created after this subquery is run, and the DATE
BUCKET for all 7Ls would be 30-60 days, then the amounts netted by risk ID,
then put into amount buckets)

Any help would be appreciated.
Thanks

 

[KARL DEWEY]

I am guessing at what you want. Try this --
TRANSFORM Sum(Frank_Bucket.AMOUNT) AS SumOfAMOUNT
SELECT Frank_Bucket.RISKID, Max(Frank_Bucket.DATE_) AS MaxOfDATE_
FROM Frank_Bucket
GROUP BY Frank_Bucket.RISKID
PIVOT Frank_Bucket.[AMOUNT BUCKET];