Break Out Distinct Groups 1

[mtownbound]

This is what I have:

Order Number Order Date Item State
99801 1/5/2015 Shoes FL
99801 1/5/2015 Pants FL
99802 3/7/2015 Shoes TN
99803 4/7/2015 Shoes UT
99803 4/7/2015 Shirt UT
99803 4/7/2015 Pants UT

I need to count the number of distinct orders per month while being grouped by state (i.e. How many distinct orders, not items, did UT have for April?)

I'm aware of adding the text field to the group header and with a source of =1 and the field in the footer to get the distinct count total for each group, but getting stumped on adding a "Abs(where month=x)" to break them out by months. Any ideas??


Thanks!

 

[PWise]

how about
Select [Order Number],State,Month([Order Date]),Count(*)
From Tablename
Group by [Order Number],State,Month([Order Date])

 

[dhookom]

I would create one or more totals queries that arrive at you required numbers and join the final totals query into your report's record source.

Can you tell us what results you would expect based on your sample data?

Duane
Hook'D on Access
MS Access MVP

 

[mtownbound]

Thanks for the response but the count(*) is returning count of entries versus count of orders.

 

[mtownbound]

dhookom, the sample table is a terrible example, but looking for a report similar to (with the distinct count of order numbers being suppressed):

States jan feb mar apr
UT 0 0 0 2
TN 0 0 1 0
FL 1 0 0 0

 

[dhookom]

Why does UT have 2 in Apr when there is only one distinct?

Please use the Pre tag so your columns line up correctly.

[pre]States jan feb mar apr
UT 0 0 0 2
TN 0 0 1 0
FL 1 0 0 0[/pre]

Duane
Hook'D on Access
MS Access MVP

 

[mtownbound]

That's a fatfinger, should be 1.

 

[dhookom]

Is that exactly what you want your report to display?
Do you want columns for all months?
How about different years but same months?
Do you have actual table and field names to share?

Duane
Hook'D on Access
MS Access MVP

 

[mtownbound]

Is that exactly what you want your report to display? Yes
Do you want columns for all months? Yes
How about different years but same months? Will be filtered for current year
Do you have actual table and field names to share?

tblOrders
[OrderNo]
[OrderDate]
[ItemType]
[CustomerState]

 

[dhookom]

Create a group by query to return the unique combinations of OrderNo, Month, and State. Note I had to use a different table name since I already had a tblOrders in my sandbox database.

[qgrpOMS]

SELECT OrderNo, Format([OrderDate],"mmm") AS Mth, CustomerState
FROM tbl_Orders
GROUP BY OrderNo, Format([OrderDate],"mmm"), CustomerState;

Then create a crosstab query with SQL view of:

TRANSFORM Count(qgrpOMS.OrderNo) AS CountOfOrderNo
SELECT qgrpOMS.CustomerState
FROM qgrpOMS
GROUP BY qgrpOMS.CustomerState
PIVOT qgrpOMS.Mth In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

Use this crosstab as the record source of a report or subreport. It isn't clear whether you want to display both detailed and summary information in your report.

Duane
Hook'D on Access
MS Access MVP

 

[mtownbound]

Okay, let me try that. Thanks again!

 

[mtownbound]

Sorry for the delay, but that did it. Thanks again!!