Incorrect Function

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.

Answer 1

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...

Answer 2

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.

Answer 3

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!

Answer 4

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...

Answer 5

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!

Answer 6

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.

Answer 7

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.

Answer 8

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;

Answer 9

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...

Answer 10

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;

Answer 11

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

Answer 12

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...

Answer 13

That worked great! Thanks DB Guy and all the best!

Cheers

Answer 14

You're welcome! Good luck with your project.