Group By - SQL Question?

[abstract_daze]

I have a table with the following:

Id Name amount Date

1 JOHN SMITH 10.00 8/1/2006
2 JOHN SMITH 20.00 8/5/2006
3 MARY JONES 50.00 5/5/2006
4 MARK LONG 25.00 4/4/2006
5 MARK LONG 5.00 5/8/2006


I WOULD LIKE THE SQL RESULTS TO LOOK LIKE THIS:

NAME AMOUNT(SUM) ID1 ID2

JOHN SMITH 30.00 1 2
MARY JONES 50.00 3
MARK LONG 30.00 4 5

How can I accomplish this - I am most concerned in
getting the NAME & SUM fields but if I could get the IDs
on the same row that would be great!
Any help is greatly appreciated!

 

[Rick Brandt]

I have a table with the following:

Id Name amount Date

1 JOHN SMITH 10.00 8/1/2006
2 JOHN SMITH 20.00 8/5/2006
3 MARY JONES 50.00 5/5/2006
4 MARK LONG 25.00 4/4/2006
5 MARK LONG 5.00 5/8/2006


I WOULD LIKE THE SQL RESULTS TO LOOK LIKE THIS:

NAME AMOUNT(SUM) ID1 ID2

JOHN SMITH 30.00 1 2
MARY JONES 50.00 3
MARK LONG 30.00 4 5

How can I accomplish this - I am most concerned in
getting the NAME & SUM fields but if I could get the IDs
on the same row that would be great!
Any help is greatly appreciated!

A crosstab query might do this if there aren't too many unique ID values.

 

[Duane Hookom]

To create a crosstab, you would need to derive the column heading with an
expression like:

ColHead: "ID" & DCount("ID","tblNoName", "[Name]=""" & [Name] & """ AND
ID<=" & [ID])

 

[abstract_daze]

Could you give me a Sql example for the following:

name amount id1 id2
JOHN SMITH 30.00 1 2
MARY JONES 50.00 3
MARK LONG 30.00 4 5



I can get the name and amount fields by the following:

select name, sum(amount)
from receipts
where date > '1/1/2006'
group by name


BUT I have no idea how to get the ids to show up.

Thanks

 

[Duane Hookom]

See what you get with this and then change it into a crosstab query.

SELECT [name], sum(amount) as SumAmt, "ID" & DCount("ID","Receipts",
"[Name]=""" & [Name] & """ AND ID<=" & [ID]) as ColHead, First(ID) as
TheValue
FROM Receipts
WHERE [date] > '1/1/2006'
GROUP BY [name], "ID" & DCount("ID","tblNoName", "[Name]=""" & [Name] & """
AND ID<=" & [ID])

Name and date are not good field names since they are a property and a
function.