If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Duplicate Values in Query
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? |
#2
|
|||
|
|||
Duplicate Values in Query
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. "AccessIM" wrote: 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? |
#3
|
|||
|
|||
Duplicate Values in Query
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. "AccessIM" wrote: 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? |
#4
|
|||
|
|||
Duplicate Values in Query
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. "AccessIM" wrote: 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? |
#5
|
|||
|
|||
Duplicate Values in Query
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 "AccessIM" wrote: 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. "AccessIM" wrote: 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? |
#6
|
|||
|
|||
Duplicate Values in Query
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 "AccessIM" wrote: 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. "AccessIM" wrote: 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? |
#7
|
|||
|
|||
Duplicate Values in Query
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 "AccessIM" wrote: 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. "AccessIM" wrote: 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? |
Thread Tools | |
Display Modes | |
|
|