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.
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.
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.
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
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) )
= DCount("YourDateField", "YourTable", "Rider = " & chr(34) & [Rider] & chr(34) & " AND RiderSub = " & chr(34) & [RiderSub] & chr(34) )
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?
Any idea how to just get the number of dates?
ASKER
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) )
= DCount("*", "YourTable", "[YourDateField] IS NOT NULL AND Rider = " & chr(34) & [Rider] & chr(34) & " AND RiderSub = " & chr(34) & [RiderSub] & chr(34) )
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks mbizup!
Glad to help out :)
Open in new window
You can use dlookup for selecting records from this query