Using DCount on summarized data

Posted on 2011-10-20
Medium Priority
Last Modified: 2012-05-12
I have a table that has the following columns: ID, Rider, RiderSub, Date, Class.

ID is a primary Key.  For each date, each Rider/RiderSub may have many classes.
1 SMITH A 5/1/11 51
2 SMITH A 5/1/11 70
3 SMITH A 5/1/11 82
4 SMITH B 5/1/11 81
5 DOUG  L 5/1/11 51
6 SMITH A 6/3/11 51
7 SMITH A 6/3/11 61
I need to know how many days each Rider/RiderSub combination participated.
For the example above, I need to see

I can't figure out what I'm doing wrong.  I keep getting the number of classes rather than the number of dates no matter how I go about it.

I'm using a DCount because it's going on a report, I could possibly use a query if necessary.

If I can just get pointed in the right direction, I'd appreciate it.
Question by:wlreimer
  • 5
  • 3
  • 2
LVL 40

Expert Comment

ID: 37004603
You should use query:
SELECT Table1.Rider, Table1.RiderSub, Count(Table1.Date) AS [Count-Date]
FROM Table1
GROUP BY Table1.Rider, Table1.RiderSub;

Open in new window

You can use dlookup for selecting records from this query
LVL 40

Expert Comment

ID: 37004609
Some comments:
Table1 - table name (change it to real table name).
Date - reserved word and will be better to change field name to something like Date1.
LVL 61

Expert Comment

ID: 37005340
Were you using a DCount as a control source for a textbox on your report?

This would work, assuming that you have Rider and RiderSub present on your report. Include the = sign, and substitute table and field names as needed:

= DCount("YourDateField", "YourTable", "Rider = '" & [Rider] & "' AND RiderSub = '" & [RiderSub] & "'")

Rider and RiderSub are the fieldnames as they appear in your table
[Rider] and [RiderSub] are the fieldnames as they appear on your report

I am assuming both are text fields.  The syntax would be a little different otherwise.

Jim Dettman has a very good article on DLookup and the Domain functions (DCount is a domain function) here:
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

LVL 61

Expert Comment

ID: 37005372
Just another note - If your rider names can contain apostrophes (such as O'Malley), use this alternate syntax instead:

= DCount("YourDateField", "YourTable", "Rider = " &  chr(34) & [Rider] & chr(34) & " AND RiderSub = " & chr(34) & [RiderSub] & chr(34) )


Author Comment

ID: 37023865
mbizup, I at least got rid of the 'error' with yours, but it's counting the number of times the rider/ridersub combination exist in the table rather than how many dates there are for that combination.  

Any idea how to just get the number of dates?

Author Comment

ID: 37023874
als315, if I create your query, I get basically the same thing as from the dcount, I get a row for each date for each rider/ridersub combination that tells me how many classes the combination was in for each date.  
LVL 61

Expert Comment

ID: 37023884
Something like this?

= DCount("*", "YourTable", "[YourDateField] IS NOT NULL AND Rider = " &  chr(34) & [Rider] & chr(34) & " AND RiderSub = " & chr(34) & [RiderSub] & chr(34) )
LVL 61

Accepted Solution

mbizup earned 1000 total points
ID: 37023931
<how many classes the combination was in for each date.   >

I think I'm following now...

SELECT Count(Class), [Rider] & " " & [RiderSub] AS RiderAndSubCombo,[YourDateField]
FROM YourTable
GROUP BY [YourDateField], [Rider] & " " & [RiderSub]

Author Closing Comment

ID: 37085110
Thanks mbizup!
LVL 61

Expert Comment

ID: 37087859
Glad to help out :)

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…

621 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question