Counting text occurences

[katie_88]

Hi all, ok well I don't think this should be difficult at all but i can't seem to find a way to do it...

Basically i've got a field on a table with a number of different medical surgery names next to the patient's name. All I want to do is run a query counting how many times each surgery is mentioned in the field i.e. how many times they've referred a patient, looking something like this I guess...

The Beatles Surgery Kiss Surgery The Cure Surgery New Order Surgery
9...............................2................. ...5........................10


Obviously these aren't the surgery names, the field is called Referring Gp Practice and the table is Quarter 2.


Oh and if there's anyway i'd like to make 3 queries so to be quarter specific, ie with a date range. july,aug,sept for q2.... oct,nov,dec for q3....jan, feb, march for q4. This is of secondary importance though.



Thanks!!

Katie x

[jzwp11]

I would recommend having a table of the surgery names rather than someone having to type the name of the surgery each time it is recommended (prone to typographical errors and thus erroneous counts)

tblSurgeryNames
-pkSurgeryNameID primary key, autonumber
-txtSurgeryName


I assume that a patient can be recommended for many surgeries

tblPatientSurgeries
-pkPatientSurgeryID primary key, autonumber
-fkPatienID foreign key to a patient table
-dteReferral (date of the recommendation for a surgery)
-fkSurgeryNameID foreign key to tblSurgeryNames

With this structure, you can create a query that uses the datepart() function and group by quarter and get a count for each fkSurgeryNameID

[katie_88]

Hi thanks for the reply but that wouldn't work as the name of the patient needs to be next to the surgery they were referred by.

The surgery entry is part of a combo box so no concerns over typos there.

[jzwp11]

I don't understand what you mean by the following:

the name of the patient needs to be next to the surgery they were referred by.

On a form? In a query? What happens if a patient has many surgeries?

Can you provide the table structure (tables & field names) from which you are working.