Exclude additional duplicate account numbers

[Chuck W]

Hi,
I have a query with three fields: MRN which is an account number, Month and
another field called AMI which has values of either D or E. There are
instances where for a given month there will be two records that have the
same MRN and month but one will have D and the second will have an E

MRN Month AMI
1030 7/1/09 D
1030 7/1/09 E
1040 7/1/09 E

I want to write a query that will give me a count of all of the Ds and Es
for a given month but will exclude all records (such as the second one) where
there already is a D for the same MRN (1030). I don't want to exclude
records such as the third record where there is no D value for MRN=1040.

Can someone help?

Thanks,

 

[Jeff Boyce]

Chuck

I'll try to paraphrase what you're after ...

Find all the (unique) MRNs per date (by the way, calling a date/time field
[Month] is asking for trouble! Not only is what's stored there not a month,
but a date, but Access treats the word "month" as a reserved word).

For all unique MRN - date combinations, if there's only a "D", show it. If
there's only an "E", show it. If there's both, only show the "D".

Is that a fair paraphrasing?

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or psuedocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[John Spencer]

PERHAPS you can use something like the following.

SELECT MRN, [Month], Min(AMI) as FirstAMI
FROM SomeTable
GROUP By MRN, [Month]

If you are doing this in query design view
== Select View: Totals From the menu
== Change Group by to Min under the AMI field


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County