Best approach with concatenation and counting?

[kryptkitten]

Hi All,

I am trying to help out another department with a query and am not sure what would be the best approach to get them what they want.

So they want a count of the number of patients that have had more than 2 visits. All the data they are querying on is in one table, but the patient first and last name are separate columns, which I think is what the count needs to be based on. So it would seem to me that the first and last name should be concatenated and then I would need the count. So my question is could this all be done within one query and if so how should it be structured?

Thanks!

Stacy

 

[DCrake]

Is this the only thing that identieis the different patients? Their name? What happens if you have more than one John smith for example?

 

[boblarson]

So they have the name stored and not a patient ID? That is very bad. So, how do you separate John Brown born 6/15/1958 from John Brown born 11/07/1991?

You need something other than just name to key on. Names can be duplicated amongst individuals. But there should be some unique patient identifier which you can key on.

Then it's a matter of putting a query together like this:

SELECT TableNameHere.IDFieldNameHere, Count(IDFieldNameHere) AS CountOfIDFieldNameHere
FROM TableNameHere
GROUP BY TableNameHere.IDFieldNameHere;

 

[boblarson]

Is this the only thing that identieis the different patients? Their name? What happens if you have more than one John smith for example?

Hmmm, great minds think alike....

 

[vbaInet]

Both of you seem to have used "John" too.

I think the OP has a PatientID field but he didn't think to use or mention it.

 

[kryptkitten]

Oh I know they should have a patient ID I mentioned this when initially looking at the data when they asked for assistance. Unfortunately it's not part of their design so just working with what they gave me. So I chatted with them a little bit more and the only other identifier that would help narrow it down is a product ID. So essentially it would be a composite of last name, first name and product ID. It truly does need to do a composite because the product ID can change from visit to visit and we need a count of the combination of all three which would count as one visit.

Thanks!

Stacy

 

[boblarson]

Again, just so they recognize that they aren't going to be counting individual patients, necessarily. If a patient shares the same name, then you would have no idea really who is who unless product ID is tied to a specific piece of serialized hardware (like a CPAP machine).

But you can build a field in the query with

MyNewFieldName:[FirstNameFieldHere] & " " & [LastNameHere] & " " [ProductIDHere]

And then you can, use that field to do counts. To make it really simple I just save that as a separate query and then use that query in another to take that field twice, select the GROUPING button (greek sigma) and then select Count on the second instance of that same field leaving the other as GROUP BY.

Then you can select all with 2 or more visits by putting >=2 in the criteria.

 

[kryptkitten]

I completely get what you are saying and have tried to convey this to them as well and encouraged them to consider adding the patient ID. It needs to be a count of the number of products from that patient during a specified time frame so that way we can figure out which patients have more than 2 products in the specified time frame. Each product ID is also tied to a specific date and in turn, while it's not in this particular database, they could track it to a piece of equipment if they had to.

 

[kryptkitten]

Actually I just happened to notice they do have a DOB that I could also pull off of. Still do same approach you think of concatenate last name, first name, and DOB as one query and then use that for field counts?

 

[boblarson]

Yes, I would use those three fields.

 

[vbaInet]

If you have a postcode or zip field that might also help.