Results 1 to 6 of 6
  1. #1
    crimedog is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2013
    Posts
    141

    Query takes forever and constantly refreshes

    I have a Query that lists all of the current Employees.
    I have a separate query that lists all of the activities done in a month with Employee ID Number, ActivityDescription, and Type Description

    I added to the Query with All Employees several fields that show DCount results: example:
    Code:
    Demo: DCount("[ScheduleID]","Q2ActivityTeam","[PersonnelID]=" & [PersonnelId] & "AND [ActivityDescription]='Demo' And [TypeDescription]='Appt'")
    What I want is the employee Name plus a count of about 6 different types of activities. I am doing it with DCount because:
    1. If the employee has no activity I want it to display 0's
    2. There are multiple criteria for each activity (I am taking the information from a Sql DB)


    It is working like I want - but the DCounts are SLOW
    And if you click on the Query or resize it - it refreshes



    Is there a better way? I tried a form with the DCounts - just as slow and would not export to excel.

  2. #2
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    Instead of using DCOUNTS, create a separate aggregate query to get all your counts. You can then join that back in with your original list of all employees. If you do a left outer join, it won't drop the employees who do not have any counts. You can use the NZ function to return 0 for those people, i.e.
    Code:
    NZ([CountField],0)+0

  3. #3
    crimedog is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2013
    Posts
    141
    How would you set the parameters field [Type Description]="Appt" AND [ActivityDescription]="Demo"

    Twist - the refreshing doesn't happen on my computer front end - but does happen on other user's computer front ends

  4. #4
    NTC is offline VIP
    Windows 7 64bit Access 2013
    Join Date
    Nov 2009
    Posts
    2,392
    JoeM has the correct design change advice. Parameter queries are a different topic. They should still fire. I myself never use them as I believe they are not user friendly by forcing the user to re-enter parameters if they are doing multiple queries. Instead I set up fields in the screen form that holds those values - - and then call them as criteria into the query. Many such parameter criteria are best set up as comboboxes so the user can have the selector experience and not simply free form type info and sometimes having to guess what should entered. By having the info held in a form's textbox - the user can reuse the parameters more easily without having to retype them each time.

  5. #5
    crimedog is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2013
    Posts
    141
    1. The refreshing problem - It is apparently a compatibility issue to the SQL server - by setting the compatibility in Access properties it seems to fix it.
    2. Query is still slow to run. It is a Query that feeds a form - the parameters are supplied by another form called Choose Month. I am going to look for a tutorial that walks me through it. the main issue: I have on field with 8 options - the form is laid out as a table : Y axis is employees, X Axis is the 8 options (i.e. Calls, Demos, Sales...)

    Thanks for the help so far!

  6. #6
    crimedog is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2013
    Posts
    141
    2. Created the Aggregate Query - replaced the DCount to iif formula - works like a charm

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 5
    Last Post: 05-01-2014, 07:58 AM
  2. front end changes take forever
    By survivo01 in forum Programming
    Replies: 4
    Last Post: 04-19-2013, 01:26 PM
  3. Replies: 2
    Last Post: 10-12-2012, 02:33 PM
  4. Replies: 11
    Last Post: 09-04-2012, 11:03 AM
  5. Access Takes my Query and Alters it!
    By dso808 in forum Queries
    Replies: 2
    Last Post: 10-01-2010, 03:45 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums