HOW TO GROUP BY FIELD

[mytaurus2000]

I HAVE A ORDERS TABLE WITH

SSN, DATE, CANCELED

My code is

SELECT DISTINCT tblOrders.SSN
FROM tblOrders
GROUP BY tblOrders.SSN, tblOrders.Date, tblOrders.Canceled
HAVING (((tblOrders.Date) Between [Forms]![frmReportDialog]![StartDate] And [Forms]![frmReportDialog]![EndDate]) AND ((tblOrders.Canceled)=No));

Which returns the correct value, but on my report I would like to break/group by the date. When I place DATE, in my SELECT statement, it is giving me SSN for each order date. Although the customer may have more than one order, we want to count them just once.

Any ideas??

 

[Crowley16]

don't use full caps for your titles, it's bad etiquite...

if this instance you should apply groupings on your report itself. Once you apply the groups on your report, you'll get group header and footer and detail sections...

--------------------
Procrastinate Now!

 

[mytaurus2000]

Can't group by date, because this field is not part of my query. When I add to SELECT statement, it's counting SSN per order, and this isn't what I want.

I need the report to count unique SSN despite how many orders between the period.

Is this possible?

 

[ProgramError]

In design view, have you tried right clicking the detail bar? This will give you the sorting and grouping table.

Ian M

Ian Mayor (UK)
Program Error
Always make your words sweet and nice. Because you never know when you may have to eat them.

 

[dhookom]

I'm not sure what you are looking for. But, here is a possible answer anyway:

SELECT tblOrders.SSN
FROM tblOrders
WHERE tblOrders.Date Between [Forms]![frmReportDialog]![StartDate] And [Forms]![frmReportDialog]![EndDate] AND tblOrders.Canceled=No
GROUP BY tblOrders.SSN;

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]