Query fundamentals: Please elucidate and educate!

[sear100]

[FONT=&quot]Hi all, [/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]Struggling with quite a fundamental concept.[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]I have designed a new structure to a database which I'm building for my hospital. (there's a graphic about half way down which sums it up)
[/FONT]
[FONT=&quot] [/FONT]
http://www.access-programmers.co.uk/forums/showthread.php?t=255708


Also direct image to the desgin... [FONT=&quot] [/FONT]
http://imgur.com/UnDMudF
[FONT=&quot][/FONT]
[FONT=&quot]
[/FONT]
[FONT=&quot]Pat Hartman has been really helpfully guiding me in The design process of tables etc... It's relatively complex as but hopefully obeys normalisation (?).[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]Overall I would describe myself as an Intermediate access user. However this one really stumps me. I've done a good amount of reading but still can't grasp this fundamental concept. I've tied myself in knots over it![/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]So - I have my design...[/FONT]
[FONT=&quot]Internally there will be a main form where the majority of data will be added (divided by tabbed controls so that It's not overwhelming to the user).... So I figured that the best way to "power" this form would be a query as It will enable me to load patients who are current Inpatients (as determined by active episodes in my design) to user. [/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]Likewise I'd like to be able to freely display fields from from this query in reports. [/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]My main issue is that when designing my queries I am only able to generate READ ONLY queries therefore undermining my wish to use the query the power the form...
[/FONT]
I've had a look at

http://allenbrowne.com/ser-61.html

To no avail...[FONT=&quot]
[/FONT] [FONT=&quot]In the past I've resorted to using subforms to get around this problem but seeing as I'm starting from scratch I though I'd try and see what It was that I was doing wrong. [/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]Can anyone walk me through what I need to do to make this query?
[/FONT]

[FONT=&quot][/FONT]
[FONT=&quot]For simplicity sake say I'd like to display fields for addition of tblPatients tblEpisode and tblShiftRecord[/FONT]


I can't even get this to work so I'm clearly missing a trick or two!
[FONT=&quot][/FONT]
[FONT=&quot]does the answer involve only listing Foreign Keys in the query for the tables I want to use?
[/FONT]

[FONT=&quot][/FONT]

[FONT=&quot][/FONT]
[FONT=&quot]
[/FONT]

 

[MarkK]

I think you still have some problems with table design and normalization. Might you not have multiple complaints for a single admission, for instance? So how do you record complaints, and is that an important feature of your system? Is the Discharge date/time really an attribute of the episode?

Your concept of a shift seems overly broad. Who creates a shift record, and when? And if I'm the dietician mentioned near the bottom do I usually have to find and edit an existing shift record? This suggests there should be some other structure to record events as they occur, rather than find the current shift record, if it exists, and edit it. Probably most of the time most shift records would be mostly empty?

Shouldn't there be a "therapeutic events" table, where a person can order an event, like blood work, and someone can execute the order? Then you can track compliance and all sorts of good things, but as it stands the existing tblBlood is connected directly to an episode. And a shift contains a BloodsOrdered field, and a date, but structurally those things are much more poorly connected than you would want.

I recommend you re-look at how things happen in time, and break the meaningful units of information you record into smaller chunks, so allow a doctor to order something at a particular date and time. Allow a single dietician to record a therapeutic event at a particular date time in respect to an order, or an episode, or a prescribed course of action.

And there are lots of things like this, like you have an EpisodeProblems table, but it it only linked to the episode via the shift, which seems too indirect. And your episode problem can be dated differently than the shift it belongs to? That doesn't make sense. And then that single EpisodeProblem can have many diagnoses? See what I mean? Shouldn't diagnosis be more tightly coupled with the complaint, more independent of the shift? And won't diagnosis occur in time, and wasn't it authored by someone specific?

 

[sear100]

Dear Lagbolt
Thanks for your reply...I agree it's not perfect - but I was the best that I could come up with. As always happy to have these suggestions

Firstly my overriding purpose for the database is:

an integrated handover system facilitating workflow for doctors and nurses to increase safety, efficiency and enable live audit.

1. I think you still have some problems with table design and normalization. Might you not have multiple complaints for a single admission, for instance? So how do you record complaints, and is that an important feature of your system?

Mutilple complaints are indeed a feature of medical admissions and they are important in the database.... I had previously modelled the problems along these lines .... (focusing on the centre right... the relationship between episodes >> episodeProblems and Diangnosis.... but according to Pat Hartman the design was somewhat problematic....Problems are are indeed independent of the "shifts" - but they do incrementally evolve as the diagnosis becomes more refined - eventually they become a logged final "diagnosis" which would be entered when the patient is discharged.... thus the extension from "shift related problems" >>> final diagnoses on discharge"
Can you think of a more apt way of organising this?

2.Is the Discharge date/time really an attribute of the episode?

Discharge date is an attribute of the episode.... no? An episode starts when the patient is admitted - though not nessicarily when someone is admitted - we occasionally have "planned" admissions for which jobs need to be done in advance. And patients are likewise discharged from hospital as part of the episode (again the episode may be "open" as there may be urgent outpatient things that need to be pushed by the hospital team).

3. Your concept of a shift seems overly broad. Who creates a shift record, and when? And if I'm the dietician mentioned near the bottom do I usually have to find and edit an existing shift record? This suggests there should be some other structure to record events as they occur, rather than find the current shift record, if it exists, and edit it. Probably most of the time most shift records would be mostly empty?

The main point of the database is to produce up to date handover sheets so that people who are working have the latest info as updated by their colleague normally at the end of a shift.... this is particually required when the night shift for doctors happens as you'll be responsible for lots of patients who you will not know intimately. We operate a paper notes system so that up to date events are captured in the paper notes. These records are summative accounts of what happens over the course of a shift.

Hospitals in the UK rotate around 12 hour shifts. Nurses run 0700-1900 and doctors run either 0800-2000 (surgical specialities) and 0900-2100 (medical specialities) Each Shift record will house the events of a 12 hour shift for both doctors and nurses. I could split the table so that there are nurse and doctor sections... however It thought that it would be easier to do it in one table. I need to probably introduce some code to limit the addition of a new shift record each 12 hours... I am going to introduce some code that when adding a new record all the existing fields in the immediate previous record are copied into the new row. So... the documentation evolves and the "empty fields" you refer to are not nessicarily empty if they are indeed pertinent to the current situation. Thus the most relevant and up to date info is stored in the fields of each shift record providing the user enters things appropriately.

... finally whilst other professionals will have read only access they will not be making additions... Dieticians for example will not be generating entries using the database... or if eventually they are they will only fill in one field which will is part of the nursing/doctor handover documentation.

4. Shouldn't there be a "therapeutic events" table, where a person can order an event, like blood work, and someone can execute the order? Then you can track compliance and all sorts of good things, but as it stands the existing tblBlood is connected directly to an episode. And a shift contains a BloodsOrdered field, and a date, but structurally those things are much more poorly connected than you would want.

I recommend you re-look at how things happen in time, and break the meaningful units of information you record into smaller chunks, so allow a doctor to order something at a particular date and time. Allow a single dietician to record a therapeutic event at a particular date time in respect to an order, or an episode, or a prescribed course of action.


This steps somewhat outside the requirements of the database and more towards a total integrated system... There are robust hospital systemens for this which we must use for ordering but they do not have any facility to record the kind of information that this database seeks to capture...Good Handover is the CRUICAL thing to ensuring efficiency, safey for patients. Having it in a database like this also enable us to analyse (audit) various aspects of care..... the bloods table is merely for recording this information so that It is readily availble on our handover sheets - it is already documented on a discrete system...

5. And there are lots of things like this, like you have an EpisodeProblems table, but it it only linked to the episode via the shift, which seems too indirect. And your episode problem can be dated differently than the shift it belongs to? That doesn't make sense. And then that single EpisodeProblem can have many diagnoses? See what I mean? Shouldn't diagnosis be more tightly coupled with the complaint, more independent of the shift? And won't diagnosis occur in time, and wasn't it authored by someone specific?

Hopefully this is somewhat addressed in points 1 and 2?
To answer the Multiple diagnoses bit - the diagnosis table will be "FIRM DIAGOSIS" reference - thus each possible diagnosis can indeed be represented many times as a "problem becoming a final/confirmed/firm diagnosis".... there are often many possible

Does this explain things any better?

What about the issue of queries therefore.... I remain unable to produce a query that works!!!

 

[MarkK]

Database tables model one-to-many relationships between entities. When you join multiple tables in a query these relationships cannot be enforced, so the query is presented by the system as read-only.

Edit data in one table at a time.

 

[sear100]

So does this confirm my suspicion that I must opperate via sub-forms?

What about reports? I suppose that these are "read" forms of the data - so It should be possible to report on this data without issue? Is this correct?

 

[MarkK]

So does this confirm my suspicion that I must opperate via sub-forms?

No. Looking at your tables it seems likely you'll have many users, each of whom will need to perform very different tasks in the system. I would generally create a distinct interface for each task that, as much as possible, allows the user to create and edit one record in one table via one form. One record represents, in accurate design, one entity, and links to parent entities can be defaulted and/or selected from lists and combos.

What about reports? I suppose that these are "read" forms of the data - so It should be possible to report on this data without issue? Is this correct?

Yes.

But, and in response to your original invitation to elucidate and educate, I restate that I think you have serious problems with your table design, and to proceed with development of the user interface without understanding and correcting those structural errors will be like building a house on a crooked and unstable foundation. Your table structure is essentially a model of the one-to-many relationships between the real-world entities that your system is designed to represent. You understand modelling a problem, right? And if your model is not accurate you're never going to get a satisfactory result. There is a reality, and there is a correct and incorrect way to model that reality in relational database tables.

If you get your tables right, your system will do more--and do it more easily--than you had imagined. If you get your tables wrong, . . . , don't get your tables wrong.

Hope this helps,

 

[jdraw]

Just to say I concur 100% with Mark (lagbolt). Proper table design and relationships that support your business rules makes database easier to maintain, give you what you ask and adjust if necessary. Failure to get the tables and relationships right will result in way too many workarounds, and many, many hours of frustrating, debugging....

Here is a good tutorial on entities,attributes, tables and relationships. Well worth working through to understand concepts and design.
http://www.rogersaccesslibrary.com/Tutorials/Entity_Relationship.zip

If you are having difficulty with queries, it's time to verify/confirm your tables and relationships.

Good luck.