Hi,

Patients at our hospital can have up to 12 surgical procedures per episode. The fields are [Primary procedure], [2nd procedure], [3rd procedure] etc. For each record in my database, none, some or (rarely) all of these may contain codes like A999.

I need to find patients that have codes (like Q07* or like Q08*) AND have another code (like R17* or R18* or R21* or R22* or R24* or R251).

The problem is that these codes could be in any of the 12 fields and they may or may not have other codes too. So there could be a patient with Q072 as the [primary procedure] and R228 as [5th procedure], but equally there could be one with Q072 as [5th procedure], with R228 as [2nd procedure] and something else entirely as the [primary procedure]

This is all I have come up with so far. I didn't WRITE this SQL, but created my query in design view and then copied the SQL.

SELECT [tbl Inpatients & Daycases 2007_2008].[Discharge Date], [tbl Inpatients & Daycases 2007_2008].[Primary Procedure], [tbl Inpatients & Daycases 2007_2008].[2nd Procedure], [tbl Inpatients & Daycases 2007_2008].[3rd Procedure], [tbl Inpatients & Daycases 2007_2008].[4th Procedure], [tbl Inpatients & Daycases 2007_2008].[5th Procedure], [tbl Inpatients & Daycases 2007_2008].[6th Procedure], [tbl Inpatients & Daycases 2007_2008].[7th Procedure], [tbl Inpatients & Daycases 2007_2008].[8th Procedure], [tbl Inpatients & Daycases 2007_2008].[9th Procedure], [tbl Inpatients & Daycases 2007_2008].[10th Procedure], [tbl Inpatients & Daycases 2007_2008].[11th Procedure], [tbl Inpatients & Daycases 2007_2008].[12th Procedure], [tbl Inpatients & Daycases 2007_2008].[Episode Number], [tbl Inpatients & Daycases 2007_2008].[UR Number]
FROM [tbl Inpatients & Daycases 2007_2008]
WHERE ((([tbl Inpatients & Daycases 2007_2008].[Primary Procedure]) Like "Q07*" Or ([tbl Inpatients & Daycases 2007_2008].[Primary Procedure]) Like "Q08*")) OR ((([tbl Inpatients & Daycases 2007_2008].[2nd Procedure]) Like "Q07*" Or ([tbl Inpatients & Daycases 2007_2008].[2nd Procedure]) Like "Q08*")) OR ((([tbl Inpatients & Daycases 2007_2008].[3rd Procedure]) Like "Q07*" Or ([tbl Inpatients & Daycases 2007_2008].[3rd Procedure]) Like "Q08*")) OR ((([tbl Inpatients & Daycases 2007_2008].[4th Procedure]) Like "Q07*" Or ([tbl Inpatients & Daycases 2007_2008].[4th Procedure]) Like "Q08*")) OR ((([tbl Inpatients & Daycases 2007_2008].[5th Procedure]) Like "Q07*" Or ([tbl Inpatients & Daycases 2007_2008].[5th Procedure]) Like "Q08*")) OR ((([tbl Inpatients & Daycases 2007_2008].[6th Procedure]) Like "Q07*" Or ([tbl Inpatients & Daycases 2007_2008].[6th Procedure]) Like "Q08*")) OR ((([tbl Inpatients & Daycases 2007_2008].[7th Procedure]) Like "Q07*" Or ([tbl Inpatients & Daycases 2007_2008].[7th Procedure]) Like "Q08*")) OR ((([tbl Inpatients & Daycases 2007_2008].[8th Procedure]) Like "Q07*" Or ([tbl Inpatients & Daycases 2007_2008].[8th Procedure]) Like "Q08*")) OR ((([tbl Inpatients & Daycases 2007_2008].[9th Procedure]) Like "Q07*" Or ([tbl Inpatients & Daycases 2007_2008].[9th Procedure]) Like "Q08*")) OR ((([tbl Inpatients & Daycases 2007_2008].[10th Procedure]) Like "Q07*" Or ([tbl Inpatients & Daycases 2007_2008].[10th Procedure]) Like "Q08*")) OR ((([tbl Inpatients & Daycases 2007_2008].[11th Procedure]) Like "Q07*" Or ([tbl Inpatients & Daycases 2007_2008].[11th Procedure]) Like "Q08*")) OR ((([tbl Inpatients & Daycases 2007_2008].[12th Procedure]) Like "Q07*" Or ([tbl Inpatients & Daycases 2007_2008].[12th Procedure]) Like "Q08*"));

Of course, this just gives me the records with Q07 and Q08 codes, but I can't work out what to do next. My brain has run smack up against a brick wall.

I'd be really grateful for some help.

Thank you.

I think that you are going to have to use 2 queries or a sub query.

Brian

The approach that you are taking is not very normalized, and that is the result of your confusion.

If you had a setup like:

PatientTable
PatientID AutoNumber { No Duplicates }
.
{ Other Patient data }

StaffTable
StaffID AutoNumber { No Duplicates }
.
{ Other Staff data }

LocationTable
LocationID AutoNumber { No Duplicates }
.
{ Other Location data }

Procedure Table
ProcedureCode AutoNumber { No Duplicates }
.
{ Other Procedure Data }

Incident Table

PrimaryKey AutoNumber { No Duplicates }
IncidentID Number
PatientID Number
ProcedureCode Number
StaffID Number
LocationID Number

This approach allows Multiple IncidentIDs with the same PatientID, and you would need to limit the count of ProcedureCodes to 12 or less. You may also need to verify that each of the ProcedureCodes is unique for the IncidentID if this is required.

Note that this is a quick and rough sketch of what might work. It is not intended to be a complete solution, and will probably need some work before you can implement it, but it should provide a good start.

Thanks Rookie, but the problem is I can't normalise the database. I'm stuck with it as it is, because its primary purpose is for reporting data to the UK Dept of Health and I have to retain EXACTLY this structure and these fieldnames. It's a pain in the ass, but I have to work with what I've got. I'm just trying to get some useful local info out of this same dataset.

Brian, is your idea to have qry1 look for Q07* or Q08*, then have qry2 based on qry1 and look for those other R-codes? Now that I think about it, that should work. Too late in the day though! I'll give that a go tomorrow.

Thanks.

Yep Pat that's the idea, I too have had to live with database structures as given and learnt that many queries and temp tables may be needed, usually driven by a macro, the people on the forum would go nuts but pragmatism rules at times.

Brian

Thanks Brian that worked.

This is an unusual combination of codes, so I had to search through three years of data - which have to be stored in three separate tables (Grrrr!) - before I found one. But I did find one, so I'm happy that approach worked. Thanks again.

The first thing to do is to create a union query

Select UR_Number, Procedure1 As ProcCode from TableName
Union Select All UR_Number, Procedure2 As ProcCode From TableName
Union Select All UR_Number, Procedure3 As ProcCode From TableName

...Etc

Where ProcCode Is Not Null

This will convert you horizontal layout to vertical and only return values for fields that have data in them.

Then use this query as the basis for you searches.

One major word of warning is your naming conventions. Using an & as part of a table name is a big No No. Don't use reserved words and characters. See help about this. Also try and avoid using spaces in field names.

CodeMaster::cool:

Wow! Thanks Codemaster,

It took me a few minutes to get my head round what the UNION was doing. I thought you meant to union the three years tables, but you mean to pull all those procedure fields into one. I understand it now. That's pretty clever!

I even managed to work out how to put in an extra column "1 AS ProcNo", "2 AS ProcNo" etc. on each SELECT line. That is now such a usable source.

That's like a little door opening in my brain and I can now see possibilities I hadn't thought of before and this approach will come in handy for lots of things. In fact I'm off to see if I can union the last three years tables as well as union-ing (if that's a verb!) all the procedure fields.


As to naming conventions: Don't get me started. If I had a penny for every time I've moaned about the names of tables, queries etc. in these databases, well...I'd need some of those little bags the banks use for collecting pennies!:D

But they're not my databases and I've been told to kepp my mitts off more than once!!

Glad you are on your way to more learing.

One suggestion

Make a query based on the main table that has the bad naming conventions

Add all the fields into this select query use alias names for the fieldnames such as:

Proc10:[10th Procedure]
Proc11 :[11th Procedure]

Save the query with a more meaningful name and base all you other queries on this query. It is acting as a pass through query but when you design other queries, whether in VBA or QBE you do not need to employ extra []'s etc.

CodeMaster::cool:

As an added footnote, you can unionise more than one table at a time. Lets say you got a request to analyse the primary procedures over a 3 year period

You would write the query as follows

Select Procedure1 as Proc1, "Year 0506" As FinYear 1 As Cntr From Table0506
Union Select All Procedure1 as Proc1, "Year 0607" As FinYear, 1 As Cntr From Table0607
Union Select All Procedure1 as Proc1, "Year 0708" As FinYear, 1 As Cntr From Table0708
Where Proc1 Is Not Null Order By Proc1

Save the query

Then open the query using a new query and Group by FinYear By Proc1 Sum Cntr

You would then get something on the lines of

Year 0506 | W89 | 123
Year 0607 | W89 | 200
Year 0708 | W89 | 300

Its a case of playing around with the union query after you created it.

BOL

David

Excellent suggestion. I may be stuck with the raw data as it is, but I can give myself a much more user-friendly view of that data before I work on it in future.

A quick question about terminology if I may: In several other dbs that I DID create myself and need to query periodically, I use a query called "qryPeriodData" which takes all the data I need from various tables, based on two dates I input via a form. Then all my other queries and reports are based on that first query. But I never *manually* run that query itself. Is that what's known as a pass through query? I've heard the phrase and always assumed it meant something quite advanced that I had never learned. But maybe I've been using these all along.

I'm really grateful for your help. I've learned so much on this forum and I'd be lost without folks like you.

Thats how intepret it. Others may have different view on it. You mentioned having a query that is used at various points in the system but the date range is dependant on the values from a form. A bit of the beaten track but here is a document on how to handle this with more effiency.

David

If I knew what a startup module was, that might be useful:D, but I've learned what I know from an old Access 97 book and using this forum.

Is your version really much different from mine attached? I have a form with two input boxes for the dates and those are referred to in the query. This is then like my "master" query for the week/month/quarter/whatever and everything else is based this.

I really need to go and read up on this stuff. I'm sure I've taken up enough of your time.

When I first saw Pat's post I thought we could do with reorganising that into a vertical layout, but didn't know a quick method , but as Pat said you can learn so much from the forum. Union queries weren't required by me, I think :), during my limited exposure to ACCESS, so it was quite an eye opener to see them used like that.

Brian

Pat,

Click on the modules option from the control centre and select new.

You will now be presented with a white screen.

This is where you can store items such as variables, constants, Functions, etc that are used thoughout the open application. delcared variable listed here remain available whilst the application is on, whereas variables declared at form level are only available whilst the form is open.

You can give this any name you want but do not call anything within it the same name.


David

Thanks David

I'll give that a bash over the next few days.