Decision support systems

[cooljool]

I have a question regarding databases and decision support systems.

If a patient/procedure database was being used in a hospital for decision support what storage considerations may have to be made?

and what two features of this database will aid retrieval of information for decision support purposes.

Also I am trying to formulate a query that will extract patient information for those patients who have not been assigned a procedure. So far I've not been able to do that. any tips or hints will be appreciated

thx

[Stewart Ross]

Sorry, cooljool, you have not provided anywhere near enough information for us to provide you with an informed answer. As presented above, your question looks very like an extract from a coursework assignment. If it is, our site rules and the simple immorality of plagiarism would prevent us from providing you with a fleshed out answer with no input from you. See our FAQ linked here for chapter and verse on our policy.

If this is not a coursework question, your request is too broad for us to answer. We can assist you with specifics if you can tell us what you need to know, but in an Access forum it is inappropriate to ask us to work out volumetrics on an unknown system for an unknown purpose - decision support as a term does not help us know what you are trying to do.

If you can ask us a specific question we'd be delighted to help you, within the boundaries of the site rules of course.

Regarding extracting data please post the SQL for what you have tried so far, along with the metadata for your tables (the names of the fields and the PK/FK relationships between them) so we can see where you may have gone wrong so far.

-Stewart

[cooljool]

thanks for the reply.

regarding the database, it basically shows three tables. a patient information table. A procedure table and a patient-procedure table.

the relationship is many to many meaning that the same patient can have more than one procedure code.

what I'm trying to do is extract information about all those patients who have no procedure code. I've tried putting 0 in the criteria for the Code field when trying to formulate the query. but it has returned blank results.

I think I might need a function but i am not sure which function to choose

many thx

[Stewart Ross]

Hi. Reading between the lines a little here if you have a many-many relationship between patient and procedure, you must have resolved this into two 1-manys to implement it relationally (presumably with the patient-procedure link table). if you want to find all patients with no procedures you need to use a LEFT JOIN between the patient table and the patient procedure table, looking specifically for cases where the joined field at the patient-procedure end does not exist (tested using IS NULL).

The general SQL for this is

SELECT field1, field2, ... FROM patient 
LEFT JOIN [patient procedure] ON
patient.[patient ID] = [patient procedure].[patient ID]
WHERE [patient procedure].[patient ID] IS NULL

A left join returns all rows from one table and all matching rows from the other, with nulls where there are no matches.

You can set up a left (or right) join in the Access query editor by double-clicking the relationship line between the tables to bring up the join type dialogue. You can also enter SQL directly using the SQL View of the query editor.

-Stewart

[cooljool]

Many thanks Stewart.

Ok, if I want to combine those patients with a procedure and those without it with a count of 0 how would I go about doing that?

many thx again

[Stewart Ross]

A variant of the left-joined query can be used to count the number of procedures for each patient, returning 0 for those who have no associated procedures. It is easiest to create it in the Access query designer, using View, Totals to turn on totalling and grouping.

The SQL is along the lines of

SELECT patient.[patient ID], Count([patient procedure].[procedure ID]) as ProcCount
FROM patient LEFT JOIN [patient procedure] ON patient.[patient ID] = [patient procedure].[patient ID])
GROUP BY patient.[patient ID]

-Stewart