none
Incorrect Function RRS feed

  • Question

  • Hello,

    Each day I receive a text file that I import into a Access 2010 table called tblFunctionTime. As you can see in the example below, a employee (EmpID) chooses the same function twice instead of alternating (Start, Finish, Start, Finish, Start, Finish, etc). Currently, I need to review the text file to make sure the same function wasn't chosen twice. Is there a way to identify through a query when a EmpID chooses the same Function twice?

    Hope this makes sense. Thanks for your help.

    Wednesday, January 6, 2016 4:55 PM

Answers

  • That worked great DB Guy. However, in your sample database, you have the EmpID field type as a number. In my table, the EmpID is a text field type. My apologies that I didn't mention that. What do I need to adjust in the query for a text field type for the EmpID?

    Thanks

    Try the following query:

    SELECT tblFunctionTime.EmpID, tblFunctionTime.Function, tblFunctionTime.DateTime, 
    IIf([Function]=DLookUp("Function","tblFunctionTime","EmpID='" & [EmpID]
    & "' AND DateTime=#" & Nz(DMax("DateTime","tblFunctionTime","EmpID='"
    & [EmpID] & "' AND DateTime<#" & [DateTime] & "#"),[DateTime]-1) & "#"),"Duplicate","") AS Dups FROM tblFunctionTime ORDER BY tblFunctionTime.DateTime;

    Hope that helps...

    • Marked as answer by KevinATF Wednesday, January 6, 2016 9:03 PM
    Wednesday, January 6, 2016 8:56 PM

All replies

  • I suppose you could try the following query:

    SELECT EmpID, Function, DateTime, IIf(Function=DLookup("Function","tblFunctionTime","EmpID=" & [EmpID]
    & " AND DateTime=#" & DMax("DateTime","tblFunctionTime","EmpID=" & [EmpID]
    & " AND DateTime<#" & [DateTime] & "#") & "#"),"Duplicate","") AS Dups FROM tblFunctionTime ORDER BY DateTime

    (untested)

    Hope that helps...

    Wednesday, January 6, 2016 5:16 PM
  • Hey DB guy. Thanks for your reply. You have been very helpful to me in the past. I tried your suggestion. When I run the query, the "Dups" field is says "#Error". Any thoughts? Thanks.
    Wednesday, January 6, 2016 6:25 PM
  • Hi. I'll need some sample data for testing. You can email it to me to make it quicker. Otherwise, I am not sure when I'll be able to generate some data myself. Cheers!
    Wednesday, January 6, 2016 6:30 PM
  • In the meantime, you could try this version:

    
    SELECT EmpID, Function, DateTime,
    IIf(Function=DLookup("Function","tblFunctionTime","EmpID=" & [EmpID] 
    & " AND DateTime=#" & Nz(DMax("DateTime","tblFunctionTime","EmpID=" & [EmpID] 
    & " AND DateTime<#" & [DateTime] & "#"),[DateTime]-1) & "#"),"Duplicate","") AS Dups
    FROM tblFunctionTime
    ORDER BY DateTime
    
    
    Hope that helps...

    Wednesday, January 6, 2016 6:40 PM
  • I tried your last suggestion. I got the same results. I sent some test data to your g mail account. The subject line will say Incorrect Function.

    Thanks again!

    Wednesday, January 6, 2016 6:59 PM
  • Just a comment: Sounds like a process problem.

    When this should not happen, then the question is why do you get this data? And what want you to do with it?

    Cause there multiple cases possible:

    1) Start was chosen incorrectly.

    2) Two of the Start rows are wrongly entered and should not be there.

    3) There are two Finish rows missing.

    Imho there is nothing you can do. When it is always case 1, then the problem can be solved, Because the Function column is irrelevant as you can calculate start/finish based on the row number.

    Wednesday, January 6, 2016 7:29 PM
  • Stefan, there are cases when a problem exists that we cannot correct. So we have to deal with what we get. In this case, I know the problem. But the fix is so far upstream in my company that they are not willing to devote time, money and resources to correct it for this little project I'm working on.

    If you have a solution to this problem that DB Guy is helping me with then please share.

    Wednesday, January 6, 2016 7:35 PM
  • Thanks for all your help DB Guy! I'm posting your solution that you emailed to me.

    Cheers!

    SELECT tblFunctionTime.EmpID, tblFunctionTime.Function, tblFunctionTime.DateTime, IIf([Function]=DLookUp("Function","tblFunctionTime","EmpID=" & [EmpID] & " AND DateTime=#" & Nz(DMax("DateTime","tblFunctionTime","EmpID=" & [EmpID] & " AND DateTime<#" & [DateTime] & "#"),[DateTime]-1) & "#"),"Duplicate","") AS Dups
    FROM tblFunctionTime;
    

    • Marked as answer by KevinATF Wednesday, January 6, 2016 7:58 PM
    • Unmarked as answer by KevinATF Wednesday, January 6, 2016 8:48 PM
    Wednesday, January 6, 2016 7:58 PM
  • I tried your last suggestion. I got the same results. I sent some test data to your g mail account. The subject line will say Incorrect Function.

    Thanks again!

    Hi Kevin,

    Got it! I tried this query on it:

    SELECT tblFunctionTime.EmpID, tblFunctionTime.Function, tblFunctionTime.DateTime,
    IIf([Function]=DLookUp("Function","tblFunctionTime","EmpID=" & [EmpID]
    & " AND DateTime=#" & Nz(DMax("DateTime","tblFunctionTime","EmpID="
    & [EmpID] & " AND DateTime<#" & [DateTime] & "#"),[DateTime]-1) & "#"),"Duplicate","") AS Dups FROM tblFunctionTime
    ORDER BY tblFunctionTime.DateTime;

    Hope that helps...

    Wednesday, January 6, 2016 8:01 PM
  • Well, what ever works.. But keep in mind, garbage in garbage out.

    Based on the data, you cannot tell, whether it is a duplicate or not.

    btw. your question could be solved much simpler, when it's about detecting errors:

    SELECT T.EmpID, Sum(IIf(T.Function="Start",1,-1)) AS FunctionBalance FROM T GROUP BY T.EmpID HAVING Sum(IIf(T.Function="Start",1,-1)) <> 0;





    • Edited by Stefan Hoffmann Wednesday, January 6, 2016 11:08 PM typo in formula
    Wednesday, January 6, 2016 8:06 PM
  • That worked great DB Guy. However, in your sample database, you have the EmpID field type as a number. In my table, the EmpID is a text field type. My apologies that I didn't mention that. What do I need to adjust in the query for a text field type for the EmpID?

    Thanks

    Wednesday, January 6, 2016 8:54 PM
  • That worked great DB Guy. However, in your sample database, you have the EmpID field type as a number. In my table, the EmpID is a text field type. My apologies that I didn't mention that. What do I need to adjust in the query for a text field type for the EmpID?

    Thanks

    Try the following query:

    SELECT tblFunctionTime.EmpID, tblFunctionTime.Function, tblFunctionTime.DateTime, 
    IIf([Function]=DLookUp("Function","tblFunctionTime","EmpID='" & [EmpID]
    & "' AND DateTime=#" & Nz(DMax("DateTime","tblFunctionTime","EmpID='"
    & [EmpID] & "' AND DateTime<#" & [DateTime] & "#"),[DateTime]-1) & "#"),"Duplicate","") AS Dups FROM tblFunctionTime ORDER BY tblFunctionTime.DateTime;

    Hope that helps...

    • Marked as answer by KevinATF Wednesday, January 6, 2016 9:03 PM
    Wednesday, January 6, 2016 8:56 PM
  • That worked great! Thanks DB Guy and all the best!

    Cheers

    Wednesday, January 6, 2016 9:04 PM
  • You're welcome! Good luck with your project.

    Wednesday, January 6, 2016 9:10 PM