Sarah has attended:
Access Introduction course
Access Intermediate course
Access Advanced course
Query criteria
I have created a Make table query to show the records of all the requests from students for specific training sessions. I have then created a report from this table which shows the total number of requests for each training session and a grand total.
Because some students may have requested more than one training session the totals in my reports include this duplication of student records. I need to find a way of creating a query/table/report which shows how many individual students have requested the sessions as well as the total number of requests but I'm stuck on what criteria/expression I need to use.
The field names I'm playing with are 'Last_Name', 'First_Name' and 'Training_Name'
RE: Query criteria
Hi Sarah, thanks for your query. What you're after is called a Domain Aggregate Function, specifically DCOUNT. A useful walkthrough is here:
http://www.lqexcel.com/domainaggregate.php
Hope this helps,
Anthony
RE: Query criteria
Thank you but I cannot open the link!
RE: Query criteria
Hi Sarah, that sounds like your workplace has prevented php scripts from running. Either try it on a different machine or look for Domain Aggregate functions on Google.
Hope this helps,
Anthony
RE: Query criteria
Thanks. I have found the relevant information and am attempting to write my DCount expression but am stuck again! I am not sure how to finish the following expression so that I only get a total of records where the Last_Name value is unique - what is the operator for this?:
=DCount("[Last_Name]","[Training Requests]","[Last_Name]
Thanks
RE: Query criteria
Hi,
Please would someone be able to help on this as my business users are now pushing me for the report and I can't give them the stats they need
Thanks
RE: Query criteria
Sarah, lets abandon the domain aggregate function and split the problem into two. Create a sub-query with two fields, both training_name. On the second field hit the autosum button and set the group by option to Count. Switch to datasheet mode and you should see a list of the courses with the number of individual requests.
I would then create another query involving the first one you built and drawing in the Counts from my query. Then run the the report off that.
If your users are pressurising you I need to just say this forum is for training and learning purposes rather than solving business critical issues. We are primarily trainers and can't always guarantee you'll get responses as quickly as you need them. But I hope this solution is what you need!
All the best,
Anthony
RE: Query criteria
Hi Anthony,
Thank you I will try this solution. I will ask my users to be patient in future!
RE: Query criteria
No problem, Sarah. Good luck!
Anthony
