Duplicate Label_id History

[Nick]

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;

 

[John Spencer]

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

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

 

[Nick]

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

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

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

 

[John Spencer]

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;
--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Nick]

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.

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;
--
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?

:

 

[John Spencer]

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

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;
--
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?

:

 

[Nick]

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

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;
--
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?

: