Re: Duplicate Label_id History

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" <Nick@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:85633B80-2A82-46D3-AED0-8B3966FE2FF4@xxxxxxxxxxxxxxxx
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;



.

Relevant Pages

  • Re: Missing Information in Query - HELP
    ... An INNER JOIN tells Access to find all of the records when BOTH tables have ... and ANY matching records in the other? ... the query design window, not the SQL window. ... Input Table) basically tracks which employees attended what ...
    (microsoft.public.access.reports)
  • Re: correlated subquery in the crosstab
    ... FROM (tblStudent INNER JOIN (tblClass INNER JOIN ... values from the outer query and I have made an alias for it. ... I have students, courses, exam groups containing exams of courses, ... that the crosstab query in access has much more capablities than ...
    (microsoft.public.access.queries)
  • Re: correlated subquery in the crosstab
    ... I solved my problem using stored queries to act as subqueries. ... FROM (tblStudent INNER JOIN (tblClass INNER JOIN ... values from the outer query and I have made an alias for it. ... that the crosstab query in access has much more capablities than ...
    (microsoft.public.access.queries)
  • Re: Weekly crosstab query
    ... queries for the other information and then linking the queries together by ... ensure that the queries return data for the same set of employees. ... Query Two uses query one in place of the OvertimeTracking table ... INNER JOIN qPrior ON ...
    (microsoft.public.access.queries)
  • Re: correlated subquery in the crosstab
    ... The first query ... TRANSFORM FirstAS FirstOfscore ... FROM tblStudent INNER JOIN (((tblEduYear INNER JOIN tblExamGrp ON ... that the crosstab query in access has much more capablities than ...
    (microsoft.public.access.queries)
Loading