Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Query Help (2003)

    I have a query. The fields are Client Number, Group Number, Market Status.
    There are some records that have different Client Numbers with the same group numbers. So client 111 can have a group number of 25 and client 112 can have a group number of 25. The difference between them is one has a Market Status of 2(cancelled) and one has a Market Status of 100 (Active). I need to get rid of the one that has a Market Status of 2 (cancelled) and keep the one that has a Market Status of 100 (active). I also have the same scenario but both groups have a Market status of 2. For those I need to keep either one but get rid of the other. I think this is more like a delete query or any suggestions on how to do this.

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

    Re: Query Help (2003)

    Is it possible that there are two Client Numbers with the same Group Number, both having Market Status 100? If so, what should happen with them - keep both?

  3. #3
    Lounger
    Join Date
    Sep 2003
    Posts
    33
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Help (2003)

    No, just the two scenarios. Two different client numbers with the same group number - one is active (100) and one is cancelled (2). Two different client numbers with the same group number - both are cancelled.

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

    Re: Query Help (2003)

    You can use a series of queries for this. See the attached database.

  5. #5
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Help (2003)

    I have another idea but don't know how to implement it. If I use qryDup from Han's example database. First i would sort group number ascending and then I would sort Market Status descending. This is where I need help - I need another column to give me a running count of clients per group. Then I would put =1 in that column and it should give me just the first one of each set of group numbers. How would I set up a running count column?

  6. #6
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Help (2003)

    I tried the queries but I think it is deleting the duplicate client numbers. There can be duplicate client numbers - client numbers are not unique, but the group numbers are supposed to be unique except in the situations described. I want to delete the duplicate group numbers - When a group number appears twice, it will have different client numbers. Those client numbers can also be for other groups too. When a group appears twice one can be cancelled and one can be active or both can be cancelled. If one is cancelled and one is active, I want to delete the one that is cancelled (2). If one is cancelled and the other is cancelled too, I want to delete either one - it doesn't matter. I don't know if I am confusing this.

    I am finding that the data is even more confusing. What it boils down to is if a group number appears more than once, I want the one that is active (100). If a group number appears more than once (even 3 times) and has 2(cancelled) for the group numbers - I just want one of those - it doesn't matter which one if they all are cancelled. There can only be one group number no matter how many times it appears. In a group of three like group numbers, I want the active. A group number cannot have more than one active (100). Most of the data contains unique group numbers. The client numbers are not unique.

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

    Re: Query Help (2003)

    Can you provide information about the structure of the relevant table(s)? If possible, post a stripped down and zipped copy of your database

  8. #8
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Help (2003)

    I just used your database as an example. In the query, I just need a column that does a running count of client numbers per group. I sorted the group number field in ascending order and the status field in descending order. So I should get a 1 2 3 or however many number of clients per group number. I would put a 1 in that field and it should just give me one group number either active (100) or cancelled (2).

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

    Re: Query Help (2003)

    To be able to determine which records to delete, you need a unique identifier. I added an AutoNumber field ID to the table.
    The attached modified version hopefully does what you want. Create a backup copy before trying it out, so that you can test multiple times.

  10. #10
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Help (2003)

    I guess what I want to do can't be done in an Access query but just in a report. I thought you could create another column in the query to do a running count on client number for group number. I have to do it on the whole table not just the query. Thanks for your help.

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

    Re: Query Help (2003)

    Doesn't the delete query in the database I attached do what you want? If not, please explain what it fails to do.

  12. #12
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Help (2003)

    Maybe I don't understand what it is doing. I need to use the whole table not just where the groups are >1. I will work with it on my data to see if it works. I will get back to you. Thanks

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

    Re: Query Help (2003)

    After running the delete query, each group number should occur only once in the table - duplicates are deleted, while group numbers that already were unique are left alone. I thought that was what you wanted.

  14. #14
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Help (2003)

    That sounds like what I want. There are 224,304 records in the table and the query was taking so long I ended it. It may have to run overnight.

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

    Re: Query Help (2003)

    The query runs slowly because it uses Not In (...). This is not very efficient.

Page 1 of 2 12 LastLast

Posting Permissions

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