Don't count if duplicate!

[ajhts]

I have a query to where I added a column called visits. I need it to place a 1 in the visits column. The trick is, I can only have a 1 per day per patient for the same disc. How do I have it place a one on the first record of the group. I hope this example helps show you what I need.
I need it to look at the patient, the disc, and the date, then place a 1 in the visit column on the first record of the match! The code field means nothing in this problem.

Patient Code Disc date visits
tom 971 PT 08/01 1
tom 963 PT 08/01
tom 865 OT 08/01 1
tom 866 OT 08/01
tom 867 OT 08/01
tom 767 ST 08/01 1

Thanks,
AJ

 

[PaultheS]

Is the code field unique? Does it matter which record gets the 1, or is it only important that one of them does? If it does matter which one gets it, how do we determine which one it is?

 

[ajhts]

No it does not matter which code gets it. The codes are not unique.

 

[gol4]

See this artice on ranking follow the same logic but suppress anything >1

http://support.microsoft.com/default.aspx?scid=kb;en-us;208946

 

[ajhts]

I am sorry, but the article didn't make sense to me. I still can't get it. Is there any other resources out there that might explain it a little better. Sorry again,

AJ

 

[JoeSmoe55]

are you doing it programmatically or through sql. through sql it could get alittle resource intensive. programmatically I would make a count to see if there were aany visits for that disc type. if so I wouldn't bother with it. if not you need to order it and update the first of that recordset. I am not sure this is possible through sql.

 

[gol4]

the easy way using dcount. I did not know the name of your table so where ever it reads items replace it with the name of your table.

Visits: IIf(DCount("pat","items","items.pat='" & [pat] & "' and items.discs='" & [discs] & "' and items.[date]=#" & [date] & "# and items.code<" &

)=0,1,"")