duplicate rows returned

  • Thread starter Thread starter Ray
  • Start date Start date
R

Ray

I am using Access 97 (don't ask)
I have linked to several tables in an Oracle 8 dB
the query produces rows of data associated with a Case # (this is a
Cusomer Support dB)
in some cases, not all, there are several rows displayed, all
duplicates relating to the same Case
The only difference in these duplicate rows is that there are more
than one Contacst relating to the Customer Site. But the query is for
one specific Contact.
does anyone have any thoughts on why this is happening and how to
prevent it?
Thanks
Ray
 
I am using Access 97 (don't ask)
Click to expand...

Best version of Access ever released; lots of us still have it.
I have linked to several tables in an Oracle 8 dB
the query produces rows of data associated with a Case # (this is a
Cusomer Support dB)
in some cases, not all, there are several rows displayed, all
duplicates relating to the same Case
The only difference in these duplicate rows is that there are more
than one Contacst relating to the Customer Site. But the query is for
one specific Contact.
does anyone have any thoughts on why this is happening and how to
prevent it?
Click to expand...

Evidently the query is incorrect. How is the query supposed to be
limiting the contacts? Could you please open the query in SQL view and
post it here?
 
Here it is John....thanks very much for your help
Ray

SELECT [SA_TABLE_Case]![ID_NUMBER] AS [Log ID],
SA_TABLE_APPT_EMPL_USER.EMPLOYEE_NO,
SA_TABLE_APPT_EMPL_USER.LAST_NAME, SA_TABLE_APPT_EMPL_USER.FIRST_NAME,
"DocuShare" AS PROD_NAME, [SA_TABLE_Case]![TITLE] AS Problem,
SA_TABLE_CONTACT.E_MAIL, SA_TABLE_CASE2CASE_CLS.CLOSE_DATE
FROM Contact INNER JOIN (SA_TABLE_APPT_EMPL_USER INNER JOIN ((([user]
INNER JOIN ((((SA_TABLE_CASE INNER JOIN SA_TABLE_CASE2CASE_CLS ON
SA_TABLE_CASE.ID_NUMBER = SA_TABLE_CASE2CASE_CLS.ID_NUMBER) INNER JOIN
SA_TABLE_SUPERCASEV ON SA_TABLE_CASE.ID_NUMBER =
SA_TABLE_SUPERCASEV.ID_NUMBER) INNER JOIN SA_TABLE_V_CLS_CASE ON
SA_TABLE_CASE.ID_NUMBER = SA_TABLE_V_CLS_CASE.ID_NUMBER) INNER JOIN
[Case] ON SA_TABLE_CASE.OBJID = Case.OBJID) ON user.OBJID =
Case.CASE_OWNER2USER) INNER JOIN SA_TABLE_EMPL_USER ON user.LOGIN_NAME
= SA_TABLE_EMPL_USER.LOGIN_NAME) INNER JOIN SA_TABLE_CONTACT ON
SA_TABLE_CASE.PHONE_NUM = SA_TABLE_CONTACT.PHONE) ON
SA_TABLE_APPT_EMPL_USER.EMPLOYEE = SA_TABLE_EMPL_USER.EMPLOYEE) ON
Contact.PHONE = SA_TABLE_CASE.PHONE_NUM
WHERE (((SA_TABLE_CASE2CASE_CLS.CLOSE_DATE)>[enter begining date] And
(SA_TABLE_CASE2CASE_CLS.CLOSE_DATE)<[enter ending date]) AND
((SA_TABLE_CASE.X_CALL_ORIGIN)="Hotline"))
ORDER BY [SA_TABLE_Case]![PHONE_NUM];
 
Here it is John....thanks very much for your help
Ray

SELECT [SA_TABLE_Case]![ID_NUMBER] AS [Log ID],
SA_TABLE_APPT_EMPL_USER.EMPLOYEE_NO,
SA_TABLE_APPT_EMPL_USER.LAST_NAME, SA_TABLE_APPT_EMPL_USER.FIRST_NAME,
"DocuShare" AS PROD_NAME, [SA_TABLE_Case]![TITLE] AS Problem,
SA_TABLE_CONTACT.E_MAIL, SA_TABLE_CASE2CASE_CLS.CLOSE_DATE
FROM Contact INNER JOIN (SA_TABLE_APPT_EMPL_USER INNER JOIN ((([user]
INNER JOIN ((((SA_TABLE_CASE INNER JOIN SA_TABLE_CASE2CASE_CLS ON
SA_TABLE_CASE.ID_NUMBER = SA_TABLE_CASE2CASE_CLS.ID_NUMBER) INNER JOIN
SA_TABLE_SUPERCASEV ON SA_TABLE_CASE.ID_NUMBER =
SA_TABLE_SUPERCASEV.ID_NUMBER) INNER JOIN SA_TABLE_V_CLS_CASE ON
SA_TABLE_CASE.ID_NUMBER = SA_TABLE_V_CLS_CASE.ID_NUMBER) INNER JOIN
[Case] ON SA_TABLE_CASE.OBJID = Case.OBJID) ON user.OBJID =
Case.CASE_OWNER2USER) INNER JOIN SA_TABLE_EMPL_USER ON user.LOGIN_NAME
= SA_TABLE_EMPL_USER.LOGIN_NAME) INNER JOIN SA_TABLE_CONTACT ON
SA_TABLE_CASE.PHONE_NUM = SA_TABLE_CONTACT.PHONE) ON
SA_TABLE_APPT_EMPL_USER.EMPLOYEE = SA_TABLE_EMPL_USER.EMPLOYEE) ON
Contact.PHONE = SA_TABLE_CASE.PHONE_NUM
WHERE (((SA_TABLE_CASE2CASE_CLS.CLOSE_DATE)>[enter begining date] And
(SA_TABLE_CASE2CASE_CLS.CLOSE_DATE)<[enter ending date]) AND
((SA_TABLE_CASE.X_CALL_ORIGIN)="Hotline"))
ORDER BY [SA_TABLE_Case]![PHONE_NUM];
Click to expand...

Well, if there are multiple records in ANY of the tables Contact,
User, SA_TABLE_CASE, SA_TABLE_SUPERCASEV, or SA_TABLE_V_CLS, you'll
see that many duplicate records - that's just how inner joins are
designed to work. Your only criteria are on the Close_Date and
X_Call_Origin fields - if there are (e.g.) multiple USER records that
match the records selected, you'll see all of them.

What would you LIKE to see? If there are indeed multiple user records,
do you want to see just one user? Which?
 
John
I just want to see the ONE User associated withat a Case
thanks
Here it is John....thanks very much for your help
Ray

SELECT [SA_TABLE_Case]![ID_NUMBER] AS [Log ID],
SA_TABLE_APPT_EMPL_USER.EMPLOYEE_NO,
SA_TABLE_APPT_EMPL_USER.LAST_NAME, SA_TABLE_APPT_EMPL_USER.FIRST_NAME,
"DocuShare" AS PROD_NAME, [SA_TABLE_Case]![TITLE] AS Problem,
SA_TABLE_CONTACT.E_MAIL, SA_TABLE_CASE2CASE_CLS.CLOSE_DATE
FROM Contact INNER JOIN (SA_TABLE_APPT_EMPL_USER INNER JOIN ((([user]
INNER JOIN ((((SA_TABLE_CASE INNER JOIN SA_TABLE_CASE2CASE_CLS ON
SA_TABLE_CASE.ID_NUMBER = SA_TABLE_CASE2CASE_CLS.ID_NUMBER) INNER JOIN
SA_TABLE_SUPERCASEV ON SA_TABLE_CASE.ID_NUMBER =
SA_TABLE_SUPERCASEV.ID_NUMBER) INNER JOIN SA_TABLE_V_CLS_CASE ON
SA_TABLE_CASE.ID_NUMBER = SA_TABLE_V_CLS_CASE.ID_NUMBER) INNER JOIN
[Case] ON SA_TABLE_CASE.OBJID = Case.OBJID) ON user.OBJID =
Case.CASE_OWNER2USER) INNER JOIN SA_TABLE_EMPL_USER ON user.LOGIN_NAME
= SA_TABLE_EMPL_USER.LOGIN_NAME) INNER JOIN SA_TABLE_CONTACT ON
SA_TABLE_CASE.PHONE_NUM = SA_TABLE_CONTACT.PHONE) ON
SA_TABLE_APPT_EMPL_USER.EMPLOYEE = SA_TABLE_EMPL_USER.EMPLOYEE) ON
Contact.PHONE = SA_TABLE_CASE.PHONE_NUM
WHERE (((SA_TABLE_CASE2CASE_CLS.CLOSE_DATE)>[enter begining date] And
(SA_TABLE_CASE2CASE_CLS.CLOSE_DATE)<[enter ending date]) AND
((SA_TABLE_CASE.X_CALL_ORIGIN)="Hotline"))
ORDER BY [SA_TABLE_Case]![PHONE_NUM];
Click to expand...

Well, if there are multiple records in ANY of the tables Contact,
User, SA_TABLE_CASE, SA_TABLE_SUPERCASEV, or SA_TABLE_V_CLS, you'll
see that many duplicate records - that's just how inner joins are
designed to work. Your only criteria are on the Close_Date and
X_Call_Origin fields - if there are (e.g.) multiple USER records that
match the records selected, you'll see all of them.

What would you LIKE to see? If there are indeed multiple user records,
do you want to see just one user? Which?
Click to expand...
 
John
I just want to see the ONE User associated withat a Case
thanks
Click to expand...

I was using USER as *an example* of *one of the possibilities*. Since
I cannot see your tables, I have no idea which table might have
multiple records. I'm essentially certain that you are seeing multiple
records because *SOME* table - I don't know which, you'll have to
investigate that for yourself - has more than one record for its
linking field. If you know for a fact that there is only one User
record for a given Case record (i.e. there is a One to Many
relationship defined between Users and Cases), then it's some other
table - but only you can determine which one it is!
 
Back
Top