Exclude additional duplicate account numbers

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,

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.


"Chuck W" <ChuckW.TakeThisOut@discussions.microsoft.com> wrote in message
news:01F3C1CA-48FA-441E-9973-1E9B7D5052AC@microsoft.com...
> 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,
>