Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Group Record... 1

Status
Not open for further replies.

tamer64

IS-IT--Management
Aug 27, 2007
120
US
How would I do I group my records into one record for each month?

SELECT Montly_Rpt_Table.REPID, Montly_Rpt_Table.Rep, Montly_Rpt_Table.MonthAbb, Montly_Rpt_Table.LostAmt, Montly_Rpt_Table.WonAmt
FROM Montly_Rpt_Table
WHERE (((Montly_Rpt_Table.REPID)=2));


REPID REP MonthAbb LostAmt WonAmt
2 MIKE JAN $15,000
2 MIKE JAN $7,000
2 MIKE FEB $1,500
2 MIKE FEB &1,200
 
Hi tamer64,
Please use the TGML tags to format your posts so they can be more easily read. We can't tell if there are numbers in one or the other column. Then always Preview the message prior to posting.

It would also help if you posted what you want your results to look like so we can understand your requirements.

[pre]
REPID REP MonthAbb LostAmt WonAmt
2 MIKE JAN $15,000
2 MIKE JAN $7,000
2 MIKE FEB $1,500
2 MIKE FEB $1,200[/pre]

We don't know what you want to do the the Rep fields and if you are summing or returning the max, min, or average of the amount columns.

I expect you could group by the MonthAbb column and Sum() the amounts.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016
 
Thanks for the tip, but I tried using the TGML tags and I made sure it was checked and it doesn't seem I can get this to format correctly.

I don't need to sum any totals. The second record for "MIKE" the amount $7,000 is actually a WonAmt and that goes the same for the last record in the WonAmt of $1,200. The output should only be two records. One record for Jan with a LostAmt and WonAmt on the same row for Mike.

I hope this makes some sense.
[bigsmile]
 
Using TGML is like formatting in Word. You select the text and click the appropriate tag/icon.
Try:

SQL:
SELECT REPID, REP, MonthABB, Sum(LostAmt) as Lost, Sum(WonAmt) As Won
FROM Montly_Rpt_Table
WHERE REPID=2
GROUP BY REPID, REP, MonthABB;


Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016
 
Thank You that worked perfectly!![bigsmile]
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top