Simple (?) query...complicated by badly-organised data

[Big Pat]

Hi,

I get two weekly files 'StudySummary' and 'Recruitment'. 'StudySummary' contains a list of clinical studies, 'Recruitment' contains the organisations in our area with the dates they have recruited patients to those studies. (It’s all anonymised and in the public domain)

I’ve attached a stripped down version but I have no control over the format of the relevant fields.

Every study has one Main Specialty but it can have several All specialties. And – here’s where it gets tricky – they put all the specialties in one field. For example Study No 8513: The Main Specialty is “Mental Health” and the All Specialties field reads “Health services and delivery research, Mental Health, Primary Care” with the commas in the data. I know!

Specialties are related to Divisions (30 specialties in 6 Divisions) and I have a mapping table which shows that.

So, given that set-up, I can select a Division and easily find records where the Main Specialty maps to that Division. That’s query 1 in the database and that’s working fine.

Now I need to find records where the All Specialties field contains any of the Specialties related to the selected Division. I started something in query 2, but for the life of me I can’t figure out what to do. I do also have a query 0 which shows those related specialties, but there will be a different number of results in that query depending on the Division selected and I have no idea how (or even whether!!) to join qry0 to the tables in qry2. And I don't know what, if anything, to put on the criteria line. Some kind of complictated, nested IIF(inStr()....IIf... maybe!

Is it possible to get what I need from the data in this format? The real database has almost half a million recruitment records, so I need something that will work reliably.

 

[Royce]

I looked at your data, and a few quick observations.

First, assuming that "StudySummary is the raw data you receive, I'd write a vba routine to process the raw data into a normalized database. Then the problem become trivial. (Studies, StudySpecialties Where Studies has fields: StudyId, MainSpeciality, and StudySpecialties has fields StudyId, Speciality)

Second, it appears that you could add a field to SPECIALTIES table 'DivisionNo' and eliminate the Mapping Divisions-Specialties table. (That assumes that each Speciality is related to one, and only one, Division.)

I'd also clean up the naming and make it use a consistent naming standard. It will greatly reduce maintenance headaches down the road. In my experience, even small projects benefit from a naming standard.

 

[Big Pat]

StudySummary and Recruitment are both raw data files that I receive. They are supplied by a national "head office" (kind of) to various regional clinical research networks which all have different systems, some as basic as Excel pivot tables. So I suppose they have to cater to the lowest common denominator. The upshot is that they are not going to change the structure at source.

"a vba routine to process the raw data into a normalized database". Sounds impressive! Trouble is, that's completely beyond my level of expertise. I'm no developer, just a hack who can usually get things done by hook or by crook. I have huge gaps in my Access knowledge and I'm sure I have terrible habits too. So I can visualise the structure you recommend but I have no hope of achieving it.

Even if I did, the same tables are linked to at least 5 other Access databases which run specific queries and reports for other people. Changing the structure now would mean a huge amount of backtracking. Not good enough in the ideal world, I know, but we are where we are.

Mapping table: In the stripped down version I attached, yes I could actually do without that table, but in the real version, that table has other fields and stores mappings to other entities such as groupings of organisations.

Without changing any structures, is there a way I can query the All Specialties field to search for Specialties related to the selected Division?

I really do appreciate your input so far. Thank you.