Results 1 to 3 of 3
  1. #1
    kuk is offline Novice
    Windows XP Access 2002
    Join Date
    Sep 2018
    Posts
    2

    ms access query (ms access freezes)

    hello,

    I have this report and need to add totals for each person (the red circle)

    Click image for larger version. 

Name:	REP1.JPG 
Views:	10 
Size:	125.9 KB 
ID:	35474



    I cannot change the existing report so I export data from MS SQL to MS Access and create a new report there. I got it working for one employee but have trouble with a query which would for multiple employees.

    Click image for larger version. 

Name:	REP2.JPG 
Views:	10 
Size:	59.7 KB 
ID:	35475

    This query extract data use as input:

    SELECT[TIME].[RCD_NUM],[TIME].[EMP_ID],[TIME].[PPERIOD],[TIME].[PRUN],[TIME].[TDATE],[TIME].[PC],[TIME].[RATE],[TIME].[HOURS],[TIME].[AMOUNT],[TIME].[JOB_ID],[TIME].[UPDATED],[TIME].[UPDATED_BY],[TIME].[LOG_DATE],[TIME].[ORIGINAL_REC_NUM]
    FROM[TIME]
    WHERE((([TIME].[EMP_ID])=376)And(([TIME].[TDATE])<=#12/31/2006#And([TIME].[TDATE])>=#1/1/2006#)And(([TIME].[PC])<599));




    this query populates the report:

    SELECT
    *
    FROM TIME1
    WHERE RCD_NUM =(SELECT Max(RCD_NUM)FROM[TIME1] UQ WHERE UQ.PPERIOD =[TIME1].PPERIOD AND UQ.PC =[TIME1].PC);

    the problem is if I remove EMP_ID from the first query like this

    SELECT[TIME].[RCD_NUM],[TIME].[EMP_ID],[TIME].[PPERIOD],[TIME].[PRUN],[TIME].[TDATE],[TIME].[PC],[TIME].[RATE],[TIME].[HOURS],[TIME].[AMOUNT],[TIME].[JOB_ID],[TIME].[UPDATED],[TIME].[UPDATED_BY],[TIME].[LOG_DATE],[TIME].[ORIGINAL_REC_NUM]
    FROM[TIME]
    WHERE((([TIME].[TDATE])<=#12/31/2006#And([TIME].[TDATE])>=#1/1/2006#)And(([TIME].[PC])<599));

    then the second query doesn't work and ms access freezes when running this query.
    any help/idea please?

    Last edited by pbaldy; 09-13-2018 at 03:00 PM. Reason: removed unrelated link

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    If second query is to select just the latest record for each employee, review http://allenbrowne.com/subquery-01.html#TopN

    If not, exactly why are you doing nested query?

    Nested queries can perform slowly with large dataset.
    Last edited by June7; 09-13-2018 at 09:43 PM.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    kuk is offline Novice
    Windows XP Access 2002
    Join Date
    Sep 2018
    Posts
    2
    The reason the second query selects the latest record is because if a change is made to data it doesn't update the specific record but add new record to database (see attached picture of recordset); this is how this program works and I cannot change it.
    So the second query selects a valid/actual data for each PPERIOD skipping any updates made before.

    Click image for larger version. 

Name:	DATA1.JPG 
Views:	8 
Size:	180.1 KB 
ID:	35478

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

Similar Threads

  1. Access 2003 Freezes
    By bell in forum Access
    Replies: 3
    Last Post: 12-16-2015, 08:24 AM
  2. Access freezes up and crashes
    By CompTechRx in forum Access
    Replies: 5
    Last Post: 11-01-2011, 02:53 PM
  3. Design view 'freezes' in Access 2010
    By dknowle in forum Access
    Replies: 1
    Last Post: 08-31-2011, 11:05 AM
  4. Access Freezes at startup
    By pokerim in forum Access
    Replies: 1
    Last Post: 07-20-2010, 06:28 AM
  5. Access freezes when opening forms
    By thorpef1 in forum Access
    Replies: 0
    Last Post: 01-04-2010, 04:48 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