query criteria

Public Schedule Face-to-Face & Online Instructor-Led Training - View dates & book

Forum home » Delegate support and help forum » Microsoft Access Training and help » Query criteria

Query criteria

resolvedResolved · Medium Priority · Version 2007

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

Mon 19 Mar 2012: Automatically marked as resolved.


 

Access tip:

Closing form after a certain time period

To make a form close automatically after a certain time period, you need to assign the close function to a macro.

Save the macro and ope up the form in design view. open the Properties sheet.

In the TimerInterval property enter the length of time you want the form open for. This should be in milliseconds, so for instance if you want the form open for 5 seconds enter 5000, for an minute enter 60000.

You now need to attach your macro (to execute the Close action) to the OnTimer event property of the form.



View all Access hints and tips


Server loaded in 0.08 secs.