Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Jan 2003
    Location
    Dallas, Texas
    Posts
    139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Duplicates in Query (2002)

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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Duplicates in Query (2002)

    Could you post the SQL for the query?

  3. #3
    2 Star Lounger
    Join Date
    Jan 2003
    Location
    Dallas, Texas
    Posts
    139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Duplicates in Query (2002)

    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. <img src=/S/brickwall.gif border=0 alt=brickwall width=25 height=15> .

    Thanks for any help you can give me

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Duplicates in Query (2002)

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

  5. #5
    2 Star Lounger
    Join Date
    Jan 2003
    Location
    Dallas, Texas
    Posts
    139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Duplicates in Query (2002)

    Thanks Charlotte!!
    I looked in master. I never thought about a duplicate in the PSA table. It's fixed. <img src=/S/bananas.gif border=0 alt=bananas width=33 height=35>

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •