Results 1 to 8 of 8
  1. #1
    cap.zadi is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2011
    Location
    KSA
    Posts
    481

    Required Crosstab Query Type Results!

    Hi

    I have the below information as in first table and required the results as like in second table.




    Click image for larger version. 

Name:	Emp.PNG 
Views:	23 
Size:	9.1 KB 
ID:	35772

    Any help would be appreciated.

  2. #2
    Minty is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Your data doesn't allow you to absolutely identify if a record is check in or check out, so you can't create this output. If someone forgets to check out, your data will be useless if you rely solely on the records following on from each other
    You really need a field to record the type of time record being recorded.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  3. #3
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Yes, either a type field then query that pulls data from another record - review: http://allenbrowne.com/subquery-01.html#AnotherRecord

    Or have In and Out fields horizontal. My employer has time sheet designed with StartIn LunchOut LunchIn EndOut fields. Normally 1 record for each day. Although holidays worked and shifts crossing midnight cause multi-line days. I don't know the database platform nor the code, but I suspect is something much more sophisticated than Access/VBA, especially since it is web-based entry.
    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.

  4. #4
    cap.zadi is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2011
    Location
    KSA
    Posts
    481
    Hi

    I do agree with your point of view and its valid argument. There is another field of "checktimetype" like "I" for timein and "0" for timeout but normally workers dont do select this option and just punched at exit or entry and this can be reverse case when night shift punched in at 17:00 and next day leave at 07:00.

    So lets say we can change field heads like that" CheckTimeA" & CheckTimeB" at that specific day whereas in night shift one entry would be missing at day one.
    So at least this should be in available in the desired format as table two.

    *May we can have another solution to assign the shifts for workers which are change over month end basis which could guide for the time sheet.


    hope to hear from you

  5. #5
    cap.zadi is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2011
    Location
    KSA
    Posts
    481
    Hi

    Thanks for the input information.

    You are right and in general the format is the same in most of the org. In our case, we do have two entries only just IN & OUT and no another entry would be accepted around the 24 hours time. This is how the bio metric machines provided.


    So the only the concern is to get the specific above table information.

  6. #6
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    you still don't have enough data - if the rule is 'check in is the first check in of the day' you can determine first with a query

    SELECT EMPID, DATED, Min(CheckTime) AS CheckIn
    FROM tblBio
    WHERE
    GROUP BY EMPID, DATED

    However this won't work for the night shift, so you need another field to indicate which shift the employee is on

    So lets say we can change field heads like that" CheckTimeA" & CheckTimeB" at that specific day whereas in night shift one entry would be missing at day one.
    So when is 'day one' - how do you identify when a shift pattern started?

  7. #7
    cap.zadi is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2011
    Location
    KSA
    Posts
    481
    Hi, Thanks for the efforts.

    I do agree with you.

    So what do you think if to have an another real way out in which every month shifts are assigned to employees. a group of employees are given day shift and others night shift and by last day of month or last weeked its changed vice versa.

    Then is it possible to control the entries even if they punch without selecting the in/out in biometeric machine?

    Kindly advise?

  8. #8
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    probably would work, but sounds too restrictive to meet real life requirements - what if someone swaps shifts? does a double shift? Really sounds like you need to train your employees to use the machine properly or change the machine for one that actually does what you want

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

Similar Threads

  1. Clear crosstab query results?
    By MikeN in forum Queries
    Replies: 17
    Last Post: 12-17-2015, 03:44 PM
  2. Weird Crosstab results
    By SteveApa in forum Queries
    Replies: 2
    Last Post: 01-28-2015, 07:04 AM
  3. Query to sum results of two crosstab queries
    By Gryphoune in forum Queries
    Replies: 3
    Last Post: 08-05-2014, 12:15 PM
  4. Sorting Crosstab query results?
    By Dragongem in forum Queries
    Replies: 1
    Last Post: 06-13-2013, 09:21 PM
  5. Replies: 16
    Last Post: 09-23-2009, 08:47 AM

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