Results 1 to 8 of 8
  1. #1
    MsAxes is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Oct 2021
    Posts
    100

    How to verify data in columns match?

    Hello all

    I have a daily table that contains employee ids, punch in date, punch out date, punch in time, punch out time as well as other fields.



    employees get reimbursed for their expenses so the data I’m given will sometimes have an employee Id more than once. For some reason each expense they enter creates another row and sometimes they enter different punch in time.

    I need to verify that multiple employee ids have the same punch in time. If not create some sort of flag that notifies me to look at that entry. Any idea what function can help?

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Why would you not expect a row for each expense?

    Are you sure they 'enter' punch time and code is not automating this input? As long as punch date is same, why would the time have to be? If code is doing this input, then time will not be same for each record.

    If you want to provide db for analysis, follow instructions at bottom of my post.
    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
    MsAxes is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Oct 2021
    Posts
    100
    The system our employees use to enter expenses needs to be updated (expected at end of 3q). For now, the employee enters the time they clocked-in and other various required fields. The table you see in my sample database, I created with only the fields that concern me.

    As you can see employee ids 5161 and 5646 have different punch-in times. For the most part this happens because they typed in too fast without noticing or they have the wrong date. I would just like a column that has a flag when a 'punch-in times do not match'.
    Attached Files Attached Files

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Strongly advise not to use space nor punctuation/special characters (underscore only exception) in naming convention.

    Query won't open because of errors in the DLookup(). Should be:
    Expr1: DCount("[Punch-In Date]","Expenses","[Punch-In Time]<>#" & [Punch-In Time] & "#")
    However, the result doesn't really make sense. Consider

    DCount("*","Expenses","EmployeeID = " & [EmployeeID] & " AND [Punch-In Date]=#" & [Punch-In Date] & "# AND [Punch-In Time]<>#" & [Punch-In Time] & "#")

    Or

    IIf(DCount("*","Expenses","EmployeeID = " & [EmployeeID] & " AND [Punch-In Date]=#" & [Punch-In Date] & "# AND [Punch-In Time]<>#" & [Punch-In Time] & "#")=0,"Match","UnMatch")

    I would be more concerned about the duplicate Gas expense by employee 5164.

    If you want to do data validation, then build a form for user input of data and have code behind form.
    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.

  5. #5
    MsAxes is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Oct 2021
    Posts
    100
    First, thank you. Your expressions worked. I will also start using names of fields with underscores (it's not the first time you advise me about that).

    I have looked up a few examples on DLookup and I just don't understand how to write the criteria. All I find online is samples like
    Code:
    DCount("UnitPrice", "Order Details", "OrderID = 10248")
    Is there a video or website that has examples on how to write criteria similar to what you posted?

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    This site seems fairly good https://documentation.help/MS-Access...af05c9b339abf1

    The example you show uses static parameter. Anything between quote marks is literal text. Whenever you want dynamic variable input, must concatenate variable. In your case variable inputs are values from fields of each record.
    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.

  7. #7
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    See if the tool supplied here helps you.
    It should.
    https://www.access-programmers.co.uk...r-v1-0.322974/
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  8. #8
    orange's Avatar
    orange is online now Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Richard Rost has many youtube videos/tutorials on Access topics. You can find some using Google with
    "access learning zone 599cd domain functions". Once you find one that suits your needs, you may find related links to other topics.
    I reference some of his videos in the Database Planning and Design link in my signature.

    Try this one on DLookup.

    Here are some links:
    Intro to VBA: https://599cd.com/VBA
    DLookup: https://599cd.com/DLookup
    SQL: https://599cd.com/SQLwithAccess
    After Update: https://599cd.com/AfterUpdate
    Nz: https://599cd.com/Nz
    Double Quotes: https://599cd.com/DoubleDouble

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

Similar Threads

  1. Replies: 3
    Last Post: 01-26-2022, 08:29 AM
  2. Replies: 2
    Last Post: 03-29-2021, 11:26 AM
  3. Replies: 4
    Last Post: 02-26-2021, 05:58 PM
  4. Import Data from Excel when columns dont match
    By NickWren in forum Import/Export Data
    Replies: 3
    Last Post: 01-29-2016, 09:14 AM
  5. Locate match in two columns
    By jill27 in forum Queries
    Replies: 3
    Last Post: 03-01-2013, 01:44 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