Access query to match values in a field

[JWNewbie]

Good morning,

I'm trying to write an Access query (2003 version) and I'm not sure if its possible to do what I would like.

Amoung others I have the following fields RequestID, SM24_No and RqGenotypeAnalysis and an example table would look something like this:

RequestID SM24_No RqGenotypeAnalysis

20100103-2 1
20100103-2 1

20090306-1
20090306-1 2
20090306-1 2
20090306-1 15

20100315-3 21
20100315-3 21
20100315-3 21

What I would like is to fill in the RqGenotypeAnalysis field to say "Match to requested" in cases where the SM24_No is the same for a RequestID group.

So for group 20090306-1 the RqGenotypeAnalysis for SM24_No 2 would be "Match to requested" as well as all the RequestID group 20100315-3.

I'm going around in circles trying to figure out how I could do this so any help is much appreciated.

Thank you so much

 

[Golom]

In your sample data the field [blue]RqGenotypeAnalysis[/blue] contains no data for any of the records.

Is that always the case and, if not, what should happen in those cases where that field does contain data?

 

[SkipVought]

Hi,

This is the sort of information that you do in a Query Report rather than STORE in a table, since add/delete from table can affect the value of that field.

Select RequestID, SM24_No, 'Match to requested'
From YourTable
Group By RequestID, SM24_No
Having Count(*)>1;

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[JWNewbie]

Sorry I should explain further what I'm trying to do. We receive requests for strain typing a particular bacteria, to see if patients having matching types. When the request comes in they are given a RequestID. Once the genotyping is complete they are assigned an SM24_No which indicates what strain type they have.

What I would like to do is automatically populate the RqGenotypeAnalysis so the technician doesn't have to manually do this on a form. I have a form that is attached to this query and once a few things are selected they will generate a report of the final results. If there is no SM24_No for a patient it will say "No match to requested" - but that iif statment I know how to write. Its the one's that have an SM24_No but it doesn't match anyone within the RequestID group which would again be "No match to requested", or the one's that have matches in the group that I want the result to read "Match to requested".

Thanks again for the help.

 

[SkipVought]

Select RequestID, SM24_No, 'Match to requested'
From YourTable
Group By RequestID, SM24_No[b]
Having Count(*)>1[/b]
UNION ALL
Select RequestID, SM24_No, 'NO Match to requested'
From YourTable
Group By RequestID, SM24_No[b]
Having Count(*)=1[/b]
;

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[JWNewbie]

Fantastic I got it working, thanks for the help!!!!