INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Break Out Distinct Groups

Break Out Distinct Groups

(OP)
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!


RE: Break Out Distinct Groups

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

RE: Break Out Distinct Groups

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

RE: Break Out Distinct Groups

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

RE: Break Out Distinct Groups

(OP)

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

RE: Break Out Distinct Groups

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

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

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

Duane
Hook'D on Access
MS Access MVP

RE: Break Out Distinct Groups

(OP)
That's a fatfinger, should be 1.

RE: Break Out Distinct Groups

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

RE: Break Out Distinct Groups

(OP)
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]

RE: Break Out Distinct Groups

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]

CODE --> SQL

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:

CODE --> SQL

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

RE: Break Out Distinct Groups

(OP)
Okay, let me try that. Thanks again!

RE: Break Out Distinct Groups

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close