Extract From Duplicate Values Based on Criteria

[databasedonr]

Thanks in advance for help with this query - I'm stumped.

I have a table of account numbers that has other columns, including "name" and "status". The status basically has two values - "subscriber" and "user" - basically, who is using the account and who pays for the account. Where the subscriber is the person using the account, I only have one record, but where the user and subscriber are unique, I have two records. What I need is a list of only users - returning the subscriber record if there is no user record, otherwise returning the user.

This seems simple but I'm not getting the results I want - essentially distinct values for each account number, but toggling between the user or subscriber depending on the value of status.

Any wizardry would be welcome. All data is in one table at them moment, which gives me duplicate values on the account number.

 

[theDBguy]

Hi. Sounds like you can do this with a UNION query. For example, create a query to return all the users. Then, create a query of subscriber records that are not in the list of users. Then, union the two queries. Hope it helps...

 

[Deleted member 147267]

All data is in one table at them moment

by any chance is user in one field and subscriber in another? If you had joined tables I could understand your problem, but you don't. Even if there is only one table and one field for status, it still sounds like the design is wrong. You'd have to be repeating the account info for each status, and that's not the way to do it.

 

[databasedonr]

Thanks, I've inherited this and agree that the design is not great. However, thanks to theDBguy as a union query as he described worked perfectly.

Thanks theDBguy - that got me over the hump!

 

[theDBguy]

Thanks, I've inherited this and agree that the design is not great. However, thanks to theDBguy as a union query as he described worked perfectly.

Thanks theDBguy - that got me over the hump!

Hi. Congratulations! Glad to hear you got it to work. Good luck with your project.

 

[Deleted member 147267]

I knew it would work, but it's like placing a bucket to catch the rain leaking through the ceiling instead of fixing the roof.
Good luck with your inheritance.