Results 1 to 8 of 8

Required Crosstab Query Type Results!

  1. #1
    cap.zadi is online now Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2011
    Location
    KSA
    Posts
    479

    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 offline Hacker
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    961
    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.
    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 online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    45,528
    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.
    To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression. Attachment Manager is below Advanced editor window, click Go Advanced below Quick Reply window.

  4. #4
    cap.zadi is online now Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2011
    Location
    KSA
    Posts
    479
    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 online now Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2011
    Location
    KSA
    Posts
    479
    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
    Ajax is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    6,351
    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 online now Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2011
    Location
    KSA
    Posts
    479
    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
    Ajax is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    6,351
    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, 02:44 PM
  2. Weird Crosstab results
    By SteveApa in forum Queries
    Replies: 2
    Last Post: 01-28-2015, 06:04 AM
  3. Query to sum results of two crosstab queries
    By Gryphoune in forum Queries
    Replies: 3
    Last Post: 08-05-2014, 11:15 AM
  4. Sorting Crosstab query results?
    By Dragongem in forum Queries
    Replies: 1
    Last Post: 06-13-2013, 08:21 PM
  5. Replies: 16
    Last Post: 09-23-2009, 07: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
  •  
Tech Forums: Microsoft Office Forums