Duplicates in Query (2002)

Topic

New Reply

I have a query that pulls specific accounts from a table based on their expiration date. I have been using this query (changing the requested dates) for three years. Suddenly, it is creating duplicate accounts. Does it duplicate every account? No-o-o. It duplicates only a few, apparently from one department, and not all accounts from that department. I have checked the table. There are no duplicates there. Does anyone have a clue???

Reply | Quote

Replies

Could you post the SQL for the query?

Reply | Quote

Here is the SQL

SELECT[PSA Assignment].PSA_1 AS Expr1, master,[dept-div],master.[grant-nbr], master.[grantor], master.[reporting-grant-dept], master.[account-nbr], master.[account-expiration], master.[project-expiration], master.[last-name]AS PI, ” ” AS COMMENTS
FROM master LEFT JOIN [PSA Assignment] ON master.[dept-div] = PSA Assignment [DEPT LTR]
WHERE (((master.[account-expiration]) Between “100103” AND “113003”) AND ((master.activity)=”1″) AND((Right(account-expiration],2=”03″))
ORDER BY [PSA Assignment].PSA_1, master.[dept-div],master.[grant-nbr], master.[account-nbr];

Keep in mind that this has been working for three years. The master table is reloaded with current data at the begging of each month. In the query, only the dates requested have been changed each month. The expression ((Right(account —-,2 = *)) has been changed to reflect the current year.

The SQL as written (barring typos) is the one I ran where the problem was seen. But on changing to the current year -04- and running the query for accounts expiring in January, there was one account from the same department that was duplicated. .

Thanks for any help you can give me

Reply | Quote

You’re using a LEFT JOIN between [Master] and [PSA Assignment] (I assume the lack of brackets around the second table in the JOIN statement isa typo here and not in your actual SQL). What happens if you change it to an INNER JOIN? Which table did you look in for duplicates, Master or PSA Assignment? I have to tell you that this kind of query misbehavior is usually a data issue.

Reply | Quote

Thanks Charlotte!!
I looked in master. I never thought about a duplicate in the PSA table. It’s fixed.

You are all great. I am so glad to have found you.

Reply | Quote

Thanks Charlotte!!
I looked in master. I never thought about a duplicate in the PSA table. It’s fixed.

You are all great. I am so glad to have found you.

Reply | Quote

You’re using a LEFT JOIN between [Master] and [PSA Assignment] (I assume the lack of brackets around the second table in the JOIN statement isa typo here and not in your actual SQL). What happens if you change it to an INNER JOIN? Which table did you look in for duplicates, Master or PSA Assignment? I have to tell you that this kind of query misbehavior is usually a data issue.

Reply | Quote

Here is the SQL

SELECT[PSA Assignment].PSA_1 AS Expr1, master,[dept-div],master.[grant-nbr], master.[grantor], master.[reporting-grant-dept], master.[account-nbr], master.[account-expiration], master.[project-expiration], master.[last-name]AS PI, ” ” AS COMMENTS
FROM master LEFT JOIN [PSA Assignment] ON master.[dept-div] = PSA Assignment [DEPT LTR]
WHERE (((master.[account-expiration]) Between “100103” AND “113003”) AND ((master.activity)=”1″) AND((Right(account-expiration],2=”03″))
ORDER BY [PSA Assignment].PSA_1, master.[dept-div],master.[grant-nbr], master.[account-nbr];

Keep in mind that this has been working for three years. The master table is reloaded with current data at the begging of each month. In the query, only the dates requested have been changed each month. The expression ((Right(account —-,2 = *)) has been changed to reflect the current year.

The SQL as written (barring typos) is the one I ran where the problem was seen. But on changing to the current year -04- and running the query for accounts expiring in January, there was one account from the same department that was duplicated. .

Thanks for any help you can give me

Reply | Quote

Could you post the SQL for the query?

Reply | Quote