repeated records

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Query in my database is showing double records while the sorce table is
alright. In my query i compare two tables for processed entries. My query has
the following fields:
ID / Name / ProcessDate_T1 / code_T1 / amnt_T1 / ProcessDate_T2 / code_T2 /
amnt_T2 / Difference ([amnt_T1]-[amnt_T2]) / ID (IsNull)(unmarked)

PLease advice on the right track...
Thanks in advance
 
Query in my database is showing double records while the sorce table is
alright. In my query i compare two tables for processed entries. My query has
the following fields:
ID / Name / ProcessDate_T1 / code_T1 / amnt_T1 / ProcessDate_T2 / code_T2 /
amnt_T2 / Difference ([amnt_T1]-[amnt_T2]) / ID (IsNull)(unmarked)

PLease advice on the right track...
Thanks in advance
Click to expand...

My guess is that the table you're joining in the Query has two records
for those ID's which are showing double records. Could you perhaps
post a more readable description of the query, by opening it in SQL
view and posting the SQL text?

John W. Vinson[MVP]
 
Here you go... this is in more details...

SELECT DISTINCT qry_SAP_HOURS_weekly.BADGE_SAP AS BADGE,
qry_SAP_HOURS_weekly.NAME_SAP AS NAME, qry_SAP_HOURS_weekly.WTYPE_SAP AS
WAGE, qry_K2_HOURS_weekly.DAYS_K2, qry_K2_HOURS_weekly.HOURS_K2,
qry_K2_HOURS_weekly.JOBCODE_K2, qry_K2_HOURS_weekly.[LOC & UPLIFT_K2],
qry_K2_HOURS_weekly.ATTEND_K2, qry_SAP_HOURS_weekly.DAYS_SAP,
qry_SAP_HOURS_weekly.HOURS_SAP, qry_SAP_HOURS_weekly.JOBCODE_SAP,
qry_SAP_HOURS_weekly.[LOC/UPLIFT_SAP], qry_SAP_HOURS_weekly.ATTEND_SAP,
[HOURS_K2]-[HOURS_SAP] AS DIFF
FROM qry_SAP_HOURS_weekly LEFT JOIN qry_K2_HOURS_weekly ON
(qry_SAP_HOURS_weekly.DAYS_SAP = qry_K2_HOURS_weekly.DAYS_K2) AND
(qry_SAP_HOURS_weekly.JOBCODE_SAP = qry_K2_HOURS_weekly.JOBCODE_K2) AND
(qry_SAP_HOURS_weekly.ATTEND_SAP = qry_K2_HOURS_weekly.ATTEND_K2) AND
(qry_SAP_HOURS_weekly.HOURS_SAP = qry_K2_HOURS_weekly.HOURS_K2) AND
(qry_SAP_HOURS_weekly.BADGE_SAP = qry_K2_HOURS_weekly.BADGE_K2)
WHERE (((qry_K2_HOURS_weekly.BADGE_K2) Is Null))
ORDER BY qry_SAP_HOURS_weekly.BADGE_SAP, qry_K2_HOURS_weekly.DAYS_K2,
qry_SAP_HOURS_weekly.DAYS_SAP;



John Vinson said:
Query in my database is showing double records while the sorce table is
alright. In my query i compare two tables for processed entries. My query has
the following fields:
ID / Name / ProcessDate_T1 / code_T1 / amnt_T1 / ProcessDate_T2 / code_T2 /
amnt_T2 / Difference ([amnt_T1]-[amnt_T2]) / ID (IsNull)(unmarked)

PLease advice on the right track...
Thanks in advance
Click to expand...

My guess is that the table you're joining in the Query has two records
for those ID's which are showing double records. Could you perhaps
post a more readable description of the query, by opening it in SQL
view and posting the SQL text?

John W. Vinson[MVP]
Click to expand...
 
Here you go... this is in more details...

SELECT DISTINCT qry_SAP_HOURS_weekly.BADGE_SAP AS BADGE,
qry_SAP_HOURS_weekly.NAME_SAP AS NAME, qry_SAP_HOURS_weekly.WTYPE_SAP AS
WAGE, qry_K2_HOURS_weekly.DAYS_K2, qry_K2_HOURS_weekly.HOURS_K2,
qry_K2_HOURS_weekly.JOBCODE_K2, qry_K2_HOURS_weekly.[LOC & UPLIFT_K2],
qry_K2_HOURS_weekly.ATTEND_K2, qry_SAP_HOURS_weekly.DAYS_SAP,
qry_SAP_HOURS_weekly.HOURS_SAP, qry_SAP_HOURS_weekly.JOBCODE_SAP,
qry_SAP_HOURS_weekly.[LOC/UPLIFT_SAP], qry_SAP_HOURS_weekly.ATTEND_SAP,
[HOURS_K2]-[HOURS_SAP] AS DIFF
FROM qry_SAP_HOURS_weekly LEFT JOIN qry_K2_HOURS_weekly ON
(qry_SAP_HOURS_weekly.DAYS_SAP = qry_K2_HOURS_weekly.DAYS_K2) AND
(qry_SAP_HOURS_weekly.JOBCODE_SAP = qry_K2_HOURS_weekly.JOBCODE_K2) AND
(qry_SAP_HOURS_weekly.ATTEND_SAP = qry_K2_HOURS_weekly.ATTEND_K2) AND
(qry_SAP_HOURS_weekly.HOURS_SAP = qry_K2_HOURS_weekly.HOURS_K2) AND
(qry_SAP_HOURS_weekly.BADGE_SAP = qry_K2_HOURS_weekly.BADGE_K2)
WHERE (((qry_K2_HOURS_weekly.BADGE_K2) Is Null))
ORDER BY qry_SAP_HOURS_weekly.BADGE_SAP, qry_K2_HOURS_weekly.DAYS_K2,
qry_SAP_HOURS_weekly.DAYS_SAP;
Click to expand...

Well, as I said, it would appear that for some records in
qry_SAP_HOURS_WEEKLY there are two records in qry_K2_HOURS_WEEKLY for
the join fields that you specify. Try opening this query and find a
record that's showing duplicates; open qry_K2_HOURS_weekly and filter
it by the DAYS, JOBCODE, ATTEND, HOURS and BADGE fields. Betcha there
are two records.

John W. Vinson[MVP]
 
Back
Top