Link to home
Start Free TrialLog in
Avatar of wlreimer
wlreimer

asked on

Using DCount on summarized data

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.
Example
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
SMITH A 2
SMITH B 1
DOUG L 1

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.
Avatar of als315
als315
Flag of Russian Federation image

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
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.
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:
https://www.experts-exchange.com/Microsoft/Development/MS_Access/A_12-Dlookup-and-the-Domain-Functions.html
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) )

Avatar of wlreimer
wlreimer

ASKER

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?
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.  
Something like this?


= DCount("*", "YourTable", "[YourDateField] IS NOT NULL AND Rider = " &  chr(34) & [Rider] & chr(34) & " AND RiderSub = " & chr(34) & [RiderSub] & chr(34) )
ASKER CERTIFIED SOLUTION
Avatar of mbizup
mbizup
Flag of Kazakhstan image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks mbizup!
Glad to help out :)