Duplicate Values in Query

[AccessIM]

I have the following code in a query:

SELECT qryDisciplineLevelAll.EMPLOYEEID, qryDisciplineLevelAll.SSN,
qryDisciplineLevelAll.NAME, qryDisciplineLevelAll.DISCIPLINEDATE,
qryDisciplineLevelAll.LASTINCIDENTDATE, qryDisciplineLevelAll.TOTALPOINTS,
qryDisciplineLevelAll.DISCIPLINELEVEL, qryDisciplineLevelAll.FROZEN,
(SELECT Count(*) FROM [qryDisciplineLevelAll] As [qryDisciplineLevelAll_1]
WHERE [qryDisciplineLevelAll].[SSN]=[qryDisciplineLevelAll_1].[SSN] AND
(Format([qryDisciplineLevelAll].[DISCIPLINEDATE],
"mmddyyyy"))<=(Format([qryDisciplineLevelAll_1].[DISCIPLINEDATE],
"mmddyyy"))-1) AS SEQUENCE_NUMBER,
IIf([qryDisciplineLevelAll.DISCIPLINELEVEL]<=[qryDisciplineLevelAll_1.DISCIPLINELEVEL],
"NO DISCIPLINE NECESSARY", [qryDisciplineLevelAll.DISCIPLINE]) AS
DISCIPLINENOTICE
FROM qryDisciplineLevelAll, qryDisciplineLevelAll AS qryDisciplineLevelAll_1
ORDER BY qryDisciplineLevelAll.DISCIPLINEDATE;

When I run the query, which should return two records, it duplicates the
records and returns four.

qryDisciplineLevelAll is a union query and returns the two records. Of the
duplicate records in the query coded above, two shoe the correct information
and two do not. I tried adding the Total line and using the Group By but it
grabs the two incorrect records.

Can someone help me with this and let me know what I am doing wrong that is
creating duplicates?

 

[Daryl S]

AccessIM -

You do not need the ", qryDisciplineLevelAll AS qryDisciplineLevelAll_1" at
the end (right before the ORDER BY clause). That will cause the duplicate
records.
--
Daryl S

Sorry, I have a correction to my post below. I have not done the group by
but I have tried creating a relationship between qryDisciplineLevelAll and
qryDisciplineLevelAll_1 in Design view. This stopped the duplicate records
but returned the incorrect two records.

I have the following code in a query:

SELECT qryDisciplineLevelAll.EMPLOYEEID, qryDisciplineLevelAll.SSN,
qryDisciplineLevelAll.NAME, qryDisciplineLevelAll.DISCIPLINEDATE,
qryDisciplineLevelAll.LASTINCIDENTDATE, qryDisciplineLevelAll.TOTALPOINTS,
qryDisciplineLevelAll.DISCIPLINELEVEL, qryDisciplineLevelAll.FROZEN,
(SELECT Count(*) FROM [qryDisciplineLevelAll] As [qryDisciplineLevelAll_1]
WHERE [qryDisciplineLevelAll].[SSN]=[qryDisciplineLevelAll_1].[SSN] AND
(Format([qryDisciplineLevelAll].[DISCIPLINEDATE],
"mmddyyyy"))<=(Format([qryDisciplineLevelAll_1].[DISCIPLINEDATE],
"mmddyyy"))-1) AS SEQUENCE_NUMBER,
IIf([qryDisciplineLevelAll.DISCIPLINELEVEL]<=[qryDisciplineLevelAll_1.DISCIPLINELEVEL],
"NO DISCIPLINE NECESSARY", [qryDisciplineLevelAll.DISCIPLINE]) AS
DISCIPLINENOTICE
FROM qryDisciplineLevelAll, qryDisciplineLevelAll AS qryDisciplineLevelAll_1
ORDER BY qryDisciplineLevelAll.DISCIPLINEDATE;

When I run the query, which should return two records, it duplicates the
records and returns four.

qryDisciplineLevelAll is a union query and returns the two records. Of the
duplicate records in the query coded above, two shoe the correct information
and two do not. I tried adding the Total line and using the Group By but it
grabs the two incorrect records.

Can someone help me with this and let me know what I am doing wrong that is
creating duplicates?