Duplicate Label_id History

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Nick - 05 Mar 2008 18:29 GMT

I have a query that find duplicate scans for a certain day, but it the
duplcate scan occurred today and was scanned on a previous day, I only see
the single scan for today. I would like to know when the first scan happened.
I have copied the SQL for the duplicate scans. Please help. I have to uses a
sepreate query to input the label_Id number to deterimine the history of the
dulicate.
SELECT DISTINCTROW ALID0001.scans, ScannAction.Scanname, ALID0001.scans,
ALID0001.label_id, ALID0001.date, ALID0001.time, ALID0001.Delete,
ALID0001.route_id, ALID0001.Employee_id, ALID0001.Scanner_id, [Last Name] & "
," & [first name] AS Expr1, AccountAction.AccountAction
FROM AccountAction INNER JOIN (ScannAction INNER JOIN (Employees RIGHT JOIN
ALID0001 ON Employees.ScannerId = ALID0001.Employee_id) ON
ScannAction.ScanActionId = ALID0001.scans) ON AccountAction.AccoutnNo =
ALID0001.Manual
WHERE (((ALID0001.scans) In (SELECT [scans] FROM [ALID0001] As Tmp GROUP BY
[scans],[label_id] HAVING Count(*)>1 And [label_id] = [ALID0001].[label_id])
And (ALID0001.scans)="01") AND ((ALID0001.date)=[enter date]) AND
(("01")<>"19"))
ORDER BY ALID0001.scans, ALID0001.date DESC;

Reply to this Message

John Spencer - 05 Mar 2008 20:18 GMT

First query: Return just the scans you are interested in

SELECT ALID0001.scans, ALID0001.LabelID
FROM ALID0001
WHERE scans In (SELECT scans FROM ALID0001 As Tmp
GROUP BY scans ,label_id
HAVING Count(*)>1
And[label_id = ALID0001.label_id)
And ALID0001.scans="01" AND ALID0001.date=Enter_Date

Query 2

SELECT DISTINCTROW ALID0001.scans, ScannAction.Scanname, ALID0001.scans,
ALID0001.label_id, ALID0001.date, ALID0001.time, ALID0001.Delete,
ALID0001.route_id, ALID0001.Employee_id, ALID0001.Scanner_id, [Last Name] &
"
," & [first name] AS Expr1, AccountAction.AccountAction
FROM AccountAction
INNER JOIN (ScannAction
INNER JOIN (Employees
RIGHT JOIN ALID0001
ON Employees.ScannerId = ALID0001.Employee_id)
ON ScannAction.ScanActionId = ALID0001.scans)
ON AccountAction.AccoutnNo = ALID0001.Manual

Query 3
SELECT Query2.*
FROM Query2 INNER JOIN Query1
ON Query2.Scans = Query1.Scans
and Query2.Label_Id = Query1.Label_ID

Signature

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
.

>I have a query that find duplicate scans for a certain day, but it the
> duplcate scan occurred today and was scanned on a previous day, I only see
[quoted text clipped - 22 lines]
> (("01")<>"19"))
> ORDER BY ALID0001.scans, ALID0001.date DESC;

Reply to this Message

Nick - 06 Mar 2008 07:30 GMT

Thanks John but I seem to be getting a cascade effect. Where can I correct
this?

> First query: Return just the scans you are interested in
>
[quoted text clipped - 53 lines]
> > (("01")<>"19"))
> > ORDER BY ALID0001.scans, ALID0001.date DESC;

Reply to this Message

John Spencer - 06 Mar 2008 12:32 GMT

Since I don't know what you mean by a cascade effect, I have no idea what to
correct.

Let's try something slightly different

SELECT A.scans, A.Label_ID
FROM ALID0001 As A
WHERE scans In
(SELECT scans FROM ALID0001 As Tmp
GROUP BY scans , label_id
HAVING Count(*)>1
And label_id = A.label_id)
AND A.scans="01" AND A.date=Enter_Date

IF so, try this as the next query
SELECT A1.*
FROM ALID0001 as A1 INNER JOIN QueryOne as Q
ON A1.Scans = Q.Scans AND
A1.Label_id = Q.Label_Id

Finally, use query two in
SELECT DISTINCTROW Q2.scans, ScannAction.Scanname, Q2.scans,
Q2.label_id, Q2.date, Q2.time, Q2.Delete,
Q2.route_id, Q2.Employee_id, Q2.Scanner_id, [Last Name] & "
," & [first name] AS Expr1, AccountAction.AccountAction
FROM AccountAction
INNER JOIN (ScannAction
INNER JOIN (Employees
RIGHT JOIN QueryTwo as Q2
ON Employees.ScannerId = Q2.Employee_id)
ON ScannAction.ScanActionId = Q2.scans)
ON AccountAction.AccoutnNo = Q2.Manual
ORDER BY Q2.scans, Q2.date DESC;

Signature

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
.

> Thanks John but I seem to be getting a cascade effect. Where can I correct
> this?

Reply to this Message

Nick - 06 Mar 2008 16:43 GMT

Thanks,
I well get several enteries for a single scan. A Label_id may have been
scanned on 2/21 at 12:30 and at 4:00 but the data will be duplicated. In
other words the same inforamation four times. It may have to do with the
employee_ID It is a no duplecate but it is not a primary key. If that is the
case how do I correct.

> Since I don't know what you mean by a cascade effect, I have no idea what to
> correct.
[quoted text clipped - 31 lines]
> > Thanks John but I seem to be getting a cascade effect. Where can I correct
> > this?

Reply to this Message

John Spencer - 06 Mar 2008 17:14 GMT

It probably has to do with the tables you are using and the data in those
tables. IF the information you are returning is identical for all fields,
then you might try replacing DistinctRow with Distinct and see if that gives
you the desired results..

Otherwise try deleting the employees table (or the Account action table) to
see if that eliminates the duplicates. If eliminating the table reduces or
eliminates the duplicated records, then you need to determine what records
in that table could be causing the problem. Look for duplicated values on
the join fields.

Signature

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
.

> Thanks,
> I well get several enteries for a single scan. A Label_id may have been
[quoted text clipped - 41 lines]
>> > correct
>> > this?

Reply to this Message

Nick - 06 Mar 2008 17:40 GMT

Thank You!

> It probably has to do with the tables you are using and the data in those
> tables. IF the information you are returning is identical for all fields,
[quoted text clipped - 51 lines]
> >> > correct
> >> > this?

Reply to this Message