Group Record... 1

[tamer64]

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

 

[dhookom]

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

 

[tamer64]

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.

 

[dhookom]

Using TGML is like formatting in Word. You select the text and click the appropriate tag/icon.
Try:

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

 

[tamer64]

Thank You that worked perfectly!!