• Duplicates in Query (2002)

    Author
    Topic
    #399436

    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???

    Viewing 1 reply thread
    Author
    Replies
    • #771718

      Could you post the SQL for the query?

      • #771863

        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. brickwall .

        Thanks for any help you can give me

        • #771873

          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.

          • #771885

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

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

          • #771886

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

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

        • #771874

          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.

      • #771864

        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. brickwall .

        Thanks for any help you can give me

    • #771719

      Could you post the SQL for the query?

    Viewing 1 reply thread
    Reply To: Duplicates in Query (2002)

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information: