Strange Count

[Kila]

I am trying to mark the "qualifying visits" in a list of patient visits by putting "Yes" in a particular field.

The qualifying visits are the 1st 10 visits during the time frame.

BUT, only up to 5 of the 1st 10 visits can have a particular CreditDSSID (179).

So visits 11 and 12 could count if visits 4-10 were from CreditDSSID 179 (visits 9 & 10 would not count as qualifying in this example since they are 179 visits 6 and 7).

Or, in a different example, you COULD count visit 11 qualifying if it was FROM CreditDSSID 179 (since it was not part of the 1st 10 visits)

I am very close, but I cannot quite get it to count corectly. I am sure that there is a less convoluted way to do this than some of the things I have tried!

 

[Galaxiom]

It is a little difficult to fully understand your post but it appears you are trying to make an update query to mark the qualifying visits. By putting a mark against qualifying visits iou could be breaching normalization because it is a derived value.

Ideally, if practical you should be calculating the qualifying visits each time you need that information.

I would suggest you try to describe your goal again using a small sample database.

 

[Kila]

Thanks for your reply! Actually, I AM calculating the qualifying visits each time I need that information. I'm sorry I was not clear. I am merely writing a query to pull data out of the data base. The query uses a series of temporary tables to arrive at a final temp table I can paste into Excel to provide the data. So the fields I am "marking" are fields in the temporary table. Once I have written the query, I can rerun it every time I need more recent data.