NEW PROJECT - Patient List system: Tables and relationships

Dear All,

I am a doctor and I'm creating a new List system for my Orthopaedic (broken bones etc!) team in Hospital. The goal is to create a robust list system which can manage the emergenc and elective (planned) intake of patients of various consultants in one specialty.

I'm rebuilding an existing database which I've advanced quite far but realised limitations of due to the fundamental design flaws... one of which was the concept that patients can have multiple "episodes" - e.g. admissions to hospital or clinic.

Main tables description of purpose:

tblPatientDatabase: core data about the patient is stored DOB etc

tblPatientEpisode: data pertaining to each patient episode... Patients can have multiple episodes all of which start and stop.... whilst their episode is open they become an active record on the database "active".

tblDailyrecords: for each day a patient is in hospital various data is recorded such as blood tests, clinical progress information, outstanding jobs, and other parameters pertaining to the level of severity of the patient status etc. This data is specific to each patient and specific to each episode.

tblOperations: patients can undergo undergo operations which take place within a given patient episode - I have therefore linked

tblConsultants: each patient must have an assigned consultant for every episode that they have.

tlbTeamInfo: each patient is assigned to a Team - each team works for a specific consultant or consultants however sometimes teams look after patients belonging to consultants were not are not in their team.

tblWard: patients are admitted to specific wards which relate to specific episodes. Obviously each ward will have multiple records of different patients have their "episodes" within the wards... However patients also move between wards during "episodes".

I've created this JPEG demonstrating the proposed relationships - you can see the essence of the tables as well as some of the fields in the tables i'm creating. The sub-constituents of the individual tables are not that important at this stage

Main Issues:

1. Please can someone tell me if this is a correct start to the database based on the information I've provided in my description of the tables?
2. I'm conceptually struggling with the idea that when patients move between wards and consultants.... That they would need a new record - in my present system it seems that only one consultant and ward can be ascribed to each episode... Can someone suggest a solution to this?
3. Is there a more elegant way to deal with the relationship between the consultants and the teams? than the one that I have currently proposed? It needs to be as generic as possible as I'd like to use this database in as wide number of settings as possible...

It's a good start. It is especially good since this isn't technically your area of expertise There are some changes I would make though.
1. PatientID doesn't belong in tblDailyRecord. The relationship to tblPatientDatabase can be inferred through tblPatientEpisode. I know it seems like it would shorten the path (and it would) but we don't do it because it opens the possibility of the link pointing to a different patient than you would get to if you went through episode.
2. Personally, I don't like data type prefixes for two reasons. One - sometimes you have to change the datatype but no one ever changes the prefix so they can be unreliable. And two - it requires you to widen every column by 3-4 characters just to get to see something meaningful. It also means you need to type 3-4 characters before you have a shot at getting intellisense to autofill. It is not wrong to use the prefixes but I don't like them. I have less of an objection to suffixes because at least they are out of the way.
3. I don't believe that consultant belongs in the episode table. This limits the number of consultants to 1 and I suspect that at some point you might need more. You might also want history I would create a junction table with a date range so you know when the consultant was actively working on the case. Same thing for ward and team. If a person might end up being moved from one ward to another during a single episode. Junction tables with date ranges would be more flexible and provide history. With the current schema, you would have to overwrite the consultantID if the consultant changed and you would never know that there was previously a different consultant.
4. I can't tell if consultants belong to teams. If they do, then the relationship from episode to team is inferred though the consultant table rather than directly.

It looks like you are consistent in your use of ID and Date as a suffix. That shows good technique. I also use suffixes like CD (to indicate that this PK/FK is actually a string rather than a number) and FLG (to indicate a y/n field). So, although I don't use suffixes to indicate data type specifically, the ones I use do imply a data type.

Dear Pat,


Many thanks for your very helpful comments and encouragement! I've spent quite a few hundreds of hours in the last year trying to get my head around Access and database design in general... If you believe it the majority of medical teams use Word-based hand and assistance which really are the pits! I'm Trying to develop something which I'm aiming to spread in my trust in a modular fashion (i.e. an orthopaedic version, a medical version, a surgical version etc).... It's in response to a real need - our handover system is really very flimsy! It's also a formative process for me to learn techniques of database design the hard way - despite the hours I've put in... there's still so much to learn!!!



1. Thanks for your helpful correction here. I actually partly intentionally put this in because I was wondering if there was any need to do it this way... I had previously anticipated that it could be an inferred situation - i.e. one cannot generate daily records from episode of a different patient than is ascribed to the episode.... However I wanted to test the water and put this out there to see if there was some -possibility that if I didn't specify the patient in the daily record that the daily record could be signed to a different patient??? I suppose it all depends on the way the data is loaded via forms is this correct?
Is the same true for the operations? .... Is it true that For reporting purposes one can infer the patient data via the same route for each given operation?


2. Thank you for your comments about the prefixes. I was suggested to use them by a chap I was asking for some advice from once upon a time. He mentioned that this might be easier when entering into the more advanced stages of VBA coding etc.... However it does cluster the database and it makes the terms themselves difficult to read. It is comment about coding valid? Presumably with respect to the IntelliSense which you talk about (which, yes I've been using) .... It just allays the process of getting into what you want to type? Is there any advantage at all?


Re 3 and 4. Consultants are part of teams - but ultimately each patient is only ever assigned under one consultant at a time (for simplicity sake this the case here although in practice there exists something called joint care whereby different specialties each headed by one consultant may look after a patient - this may be two or more - although typically two is the most common permutation - -an example would be someone with a broken hip who also has suffered a heart attack).


So ...I sat back and rethought the concept of consultants and teams... Especially with respect to History - from both directions... With the the guiding principle is that one patient is assigned one consultant. Each consultant has a team constituted by junior doctors , registrars, senior house officers and house officers.... These junior members change much more frequently than to the consultants. Consultants however also come and go from hospital so their history needs to be represented as well.


Does the new relationship that I proposed make any sense? This way per episode a patient can be under multiple consultants and therefore multiple teams as well.


5. The absolute ultimate version of this database would be something which could integrate across specialities.... However given the size and the number of patients admitted to a sizeable hospital (hundreds per day, thousands per month and tens of thousands per year) is this an advisable proposition - to create such a database?

I've done some Extensive work on the design

Would be grateful for feedback!

I would add dates and possibly even date/time to the "tests" tables. There will be multiple tests over time and although you can order them by the ID to get them into the sequence in which they were administered, a date/time carries much more information.

EpisodeID CANNOT go in the patient table. This creates a pathological connection between the two tables in addition to limiting the number of episodes per patient to 1.

ProblemID doesn't go in the shift table. ShiftID goes in the problem table. And again, we shouldn't include EpisodeID. It should be inferred via the shift table.

The role links in the shift table should probably be a junction table rather than having them as discrete fields in the shift table. So you would have a table named something like tblShiftStaff. You also wouldn't have separate tables for staff specialties. You would have only one table for people. So all the people are in one table. If they can have only one specialty, then there would be a column for that. If a person could have multiple specialties, then you need another junction table and the tblShiftStaff would link to the junction table. You have a complex data model and it is getting more complex by the moment. This same type of change affects tblTeams. The way you have it now, only 5 people can be assigned per shift and only three per team. You need more flexibility than that. New specialties are added, some cases require additional supervision. You want a schema that allows that to happen without any structural changes. Don't make design decisions that limit the numbers of things.

To answer the earlier questions.
1. Having the extraneous join field opens up the possibility for error. Since it isn't necessary and doesn't give you any information you can't get elsewhere, my best advice is don't do it. You may think you can control it but at some point, you're going back to your day job and someone else will take over this app. Some things are simply too dangerous to allow.
2. The programmer suggested the prefixes because sometimes when you are coding, you need to know the data type of a field. I've been coding for over 40 years and I find this type of prefix an annoyance. As I said, a suffix is less objectionable but still unnecessary. If I have to know a data type and I don't know it intuitively, I'll take the hit and look it up. I have seen too many situations over the years where the prefix/suffix is inaccurate so I don't use them. I have a simplistic style. I don't do slick. I don't do elegant. I don't obfuscate. I do solid, reliable, readable, changeable by a trainee.
3/4. When designing a relational database keep in mind that if you can have more than one of something, you have many and many means another table. Don't worry one iota about how many (unless you are trying to estimate disk space). More than one is many, period.

[FONT=&quot]Dear Pat, I've good deal of work on the database to try and incoporate your suggestions. Very helpful. [/FONT]

[FONT=&quot]I've tried to respond paragraph by paragraph....
[/FONT]
[FONT=&quot]I would add dates and possibly even date/time to the "tests" tables. There will be multiple tests over time and although you can order them by the ID to get them into the sequence in which they were administered, a date/time carries much more information.[/FONT]

[FONT=&quot]I've Added this Thanks! (please see my note about Logons bellow)[/FONT][FONT=&quot]

"EpisodeID CANNOT go in the patient table. This creates a pathological connection between the two tables in addition to limiting the number of episodes per patient to 1."[/FONT]

[FONT=&quot]RESOLVED! This was a product of staying up too late into the night![/FONT][FONT=&quot]

ProblemID doesn't go in the shift table. ShiftID goes in the problem table. And again, we shouldn't include EpisodeID. It should be inferred via the shift table.[/FONT]

[FONT=&quot]Sorry to keep creating these kinds of bad joins... it just seemed to make sense to me that A problem could be represented in multiple shift instances.... the way you propose it - and the way that It's built into this model states that an episode .... May have multiple shifts which have multiple problems associated with it for any given patient ..... Conceptually I thought that "episodes" have multiple "problems" (potential or formal diagnoses) associated with them which they themselves may be represented multiple times in a ongoing shift record system.... It looks problematic in the diagram but it made sense to me at four o'clock in the morning yesterday. [/FONT]

[FONT=&quot] The role links in the shift table should probably be a junction table rather than having them as discrete fields in the shift table. So you would have a table named something like tblShiftStaff. You also wouldn't have separate tables for staff specialties. You would have only one table for people. So all the people are in one table. If they can have only one specialty, then there would be a column for that. If a person could have multiple specialties, then you need another junction table and the tblShiftStaff would link to the junction table. You have a complex data model and it is getting more complex by the moment. This same type of change affects tblTeams. The way you have it now, only 5 people can be assigned per shift and only three per team. You need more flexibility than that. New specialties are added, some cases require additional supervision. You want a schema that allows that to happen without any structural changes. Don't make design decisions that limit the numbers of things.[/FONT]

[FONT=&quot]I have revised this as stated... Don't know what I was thinking at the time. It makes much more sense to amalgamate everyone into one group. I've also added junction tables to this and a number of other relationships with the "episode". Such as for Ward and Consultant.
[/FONT]
[FONT=&quot]
To answer the earlier questions.
1. Having the extraneous join field opens up the possibility for error. Since it isn't necessary and doesn't give you any information you can't get elsewhere, my best advice is don't do it. You may think you can control it but at some point, you're going back to your day job and someone else will take over this app. Some things are simply too dangerous to allow. [/FONT]

[FONT=&quot]Understood - the "babysitting" will certainly be difficult - I'd like to keep this as rock solid as possible - all of your input here is absolutely invaluable.[/FONT]
[FONT=&quot]
2. The programmer suggested the prefixes because sometimes when you are coding, you need to know the data type of a field. I've been coding for over 40 years and I find this type of prefix an annoyance. As I said, a suffix is less objectionable but still unnecessary. If I have to know a data type and I don't know it intuitively, I'll take the hit and look it up. I have seen too many situations over the years where the prefix/suffix is inaccurate so I don't use them. I have a simplistic style. I don't do slick. I don't do elegant. I don't obfuscate. I do solid, reliable, readable, changeable by a trainee. [/FONT]

[FONT=&quot]I've removed them - looks much cleaner! If anything's unclear I'm adding description of it's purpose to the notes in the table design - so that any developers/coders I work with can figure out what I'm driving at. I like your group of sentences at the end - a grand motto! [/FONT]
[FONT=&quot]
3/4. When designing a relational database keep in mind that if you can have more than one of something, you have many and many means another table. Don't worry one iota about how many (unless you are trying to estimate disk space). More than one is many, period. [/FONT]

[FONT=&quot]I am starting to realise this slowly but surely!! (In my original database design I only ever had 4 tables! I was having to do all sort of impossible and complicated things to get sense out of it!)[/FONT]



[FONT=&quot].... Now onto some of the new bits that I'm struggling with[/FONT]
[FONT=&quot]
[/FONT]
[FONT=&quot]New area pertaining to "theatre lists"[/FONT]

[FONT=&quot]The database has indeed rapidly expanded from my original simple conception. However it's getting to the stage where it will be able to do almost everything I hoped it could do. I am struggling in the area of "theatre lists".... The way operation theatres work is that "lists" of operations are grouped together - normally under one consultant. Operations are normally formed by the patient's own consultant but not infrequently are they operated on by other consultants who have space on their list.[/FONT]

[FONT=&quot]I'm struggling in this area to describe the relationships on the one hand between a patient who has an episode where they undergo one or more operations - and theatre lists which are assigned to consultants where in one or more consultants may undertake operations on patients who themselves normally belong to the same consultant but may occasionally belong to different consultant than who's list it is. [/FONT]

I suspect that I've done it wrong again by creating a superfluous join between the consultants table and the theatre lists table.... Just - how do I describe which consultant is in charge of the list? --- This is an independent phenomenon from the consultants looking after patients you see and whilst typically the patients on that list were or will become that consultant's concern they are not nessicarily.

[FONT=&quot]
[/FONT]
[FONT=&quot]Logon-system[/FONT]

[FONT=&quot]I intend to build a logon system with a password so that changes in the database can be recorded by whichever user is logged on. Can you suggest how this might take place? I would ideally like it to involve tblStaff - such that any staff member can select themeselves on login... - The system should then log the fact that this user has gone to the system. Note... Not everyone in the staff file will be using the system is a nevertheless the theory would be the any person staff member who is listed should be able to logon. This thing said not all of them will use the system and not all of them will require password.[/FONT]

[FONT=&quot]The main reason behind this would be to allow default values in lots of areas which require users to declare who they are. I'd also like to use this logon information to automatically stamp onto records who is changing the database and what time they changed it time accruing a history as we go along so that changes can be tracke. From a design point of view - I'm uncertain how to begin.... does this mean that tblStaff needs to have a relationship with every table that records such data? e.g. tblEpisodes, tblAntibiotics etc etc? [/FONT]
[FONT=&quot]I have made one link to demonstrate what I'm intending to describe between tblStaff and tblAntibiotics.... although once again I suspect that I will need a junction table to Log the change. - If this is the case will I need multiple Junction tables between tblStaff and their respective partners e.g. tblPatient, tbleEpisode etc... Or is there a sneaky way to do it?
[/FONT]


[FONT=&quot]For the default values i mentioned and for this as well.... is it possible to infer by some means once the user has Logged onto the system? - - - i.e some kind of current user table? or would this all be query/coding driven?[/FONT]

In a hospital situation, I'm going to guess that the same computer is used by multiple people. If your procedure is to give everyone his own Windows log on and require that they log out when their shift ends (and it should be), then I would use an environment variable to obtain the ID of the logged on user -

Me.UserID = Environ("UserName")

You could make people type it in but I think that is less secure since one person can log in as another from his own computer.

Under no circumstances would I give users a combo to pick their name from. I know it is more convenient but it is less secure. I either use the environment variable or make people type in their ID. Then using the password they type in, I look up their record from tblStaff. The DLookup() uses two selection fields.

Notice the ambiguous message. If you tell the person which value is wrong, he knows which one to concentrate on if he is attempting to break in.

You can use a simplistic UserLevel system that segregates people into read-only, update his own records, update all records, update system data. To implement "update his own records", you would need to compare the CreateBy field from the table with the UserID and only allow users to change records that they added. You would also have an ChangeBy field so you can log who updated the record in case it wasn't the original owner.

I see by your address that you are in the UK. In the US, Access (Jet and ACE - the database engines) wouldn't pass muster under HIPPA (who knows what the letters stand for but it protects patient privacy) and although your front end (forms/reports) could be Access, the back end (data) would need to be something securable such as SQL Server. Converting to SQL server might be a little much for you at the moment so stick with ACE. Do search for articles on optimizing Access for client/server though. That will keep you on the right track when you are designing forms, etc and converting to SQL Server could be as simple as running the upsizing wizard. I have many applications that can actually use either ACE or SQL Server and switch at will but you do have to direct your design efforts to the client/server environment which won't bother ACE at all.

Each table in all my databases has at least two logging columns - ChangeBy and ChangeDT. In the cases where I also allow only update by owner, I include CreateBy and CreateDT. This isn't true logging but it does tell you the last person to change a record. If you need transaction history, that is a whole other process. These columns are all updated in the FORM's BeforeUpdate event just before a record gets saved.

I'm not sure I can comment on theater lists off the cuff. I think we've exceeded my knowledge of hospital procedure gleaned from many years of watching Dr. Kildare and other medical dramas along with a few operations of my own. Just remember my advice about many. Since more than one surgeon can participate, you need a junction table. In that table, one would be marked primary and others would be marked as assistants.

Dear Pat,

Many thanks again for your detailed response. Sorry for my delayed reply I was working nights this weekend (including the extra hour when the clocks went back!)

In a hospital situation, I'm going to guess that the same computer is used by multiple people. If your procedure is to give everyone his own Windows log on and require that they log out when their shift ends (and it should be), then I would use an environment variable to obtain the ID of the logged on user -

Me.UserID = Environ("UserName")

You could make people type it in but I think that is less secure since one person can log in as another from his own computer.

Under no circumstances would I give users a combo to pick their name from. I know it is more convenient but it is less secure. I either use the environment variable or make people type in their ID. Then using the password they type in, I look up their record from tblStaff. The DLookup() uses two selection fields.

The latter of the two methods you've suggested is the most appropriate for our setting. As a doctor I visit multiple wards and departments around the hospital so need to be able to access the database on machines which I am not nessicarily the "logged on" user. Thank you for stressing the point with respect to a user list drop down!

I would like to be able to control what users can see what depending on their role within the application.... I am assuming there is a way to set permissions based on user criteria to be able to or at least open various forms.... I was planning this so that doctors can edit certain bits of the database, nurses other and other users the ability to run various commands such as print lists etc but not the ability to edit any of the fields.

Code:
Me.UserLevel = DLookup("UserLevel","tblStaff", "UserID ='" & Me.UserID & "' AND Pswd = '" & Me.Password & "'")
If IsNull(Me.UserLevel) Then
Msgbox "Your User ID or Password is invalid, please try again.",vbOKOnly
Exit Sub
End If

Notice the ambiguous message. If you tell the person which value is wrong, he knows which one to concentrate on if he is attempting to break in.

You can use a simplistic UserLevel system that segregates people into read-only, update his own records, update all records, update system data. To implement "update his own records", you would need to compare the CreateBy field from the table with the UserID and only allow users to change records that they added. You would also have an ChangeBy field so you can log who updated the record in case it wasn't the original owner.

The patients and their episodes will constitue the core of the records in the database. they will need to be edited by various people throughout their inpatient stay/... locking the record to the person who added it I can imagine causing more problems that the potential benefits. I can see where this can head but my head cannot quite contain all the required cavets! I will basically need Admin level (all levels of acces), Doctor level (access to the majority but not all forms), Nurse level - access to only a few forms, and Admin staff (access to only read only functions - running lists and reports etc).

I see by your address that you are in the UK. In the US, Access (Jet and ACE - the database engines) wouldn't pass muster under HIPPA (who knows what the letters stand for but it protects patient privacy) and although your front end (forms/reports) could be Access, the back end (data) would need to be something securable such as SQL Server. Converting to SQL server might be a little much for you at the moment so stick with ACE. Do search for articles on optimizing Access for client/server though. That will keep you on the right track when you are designing forms, etc and converting to SQL Server could be as simple as running the upsizing wizard. I have many applications that can actually use either ACE or SQL Server and switch at will but you do have to direct your design efforts to the client/server environment which won't bother ACE at all.

thanks for this - this is crucial steering! I have has something of a read today and I must say I got a bit fogged by the technical aspects. Can you suggest a good guiding article to begin to bite into this?

Each table in all my databases has at least two logging columns - ChangeBy and ChangeDT. In the cases where I also allow only update by owner, I include CreateBy and CreateDT. This isn't true logging but it does tell you the last person to change a record. If you need transaction history, that is a whole other process. These columns are all updated in the FORM's BeforeUpdate event just before a record gets saved.

When I started to think about the "logging" process for the database It became apparent how complex this would become. The goal of this database is to facilitate workflow and handover rather than "snoop" on who'd done what... but having this log could be useful for auditing the database later on...

Would it work with a single junction table?

tblUpdateLog
PK UpdateLogID
UpdateLogdtm
ChangeBy
FK StaffID
FK EpisodeID
FK PatientID
FK .... etc

....Such that every change in a table logs an individual date and time in this table. Or would I need multiple tables to make it work?

I'm not sure I can comment on theater lists off the cuff. I think we've exceeded my knowledge of hospital procedure gleaned from many years of watching Dr. Kildare and other medical dramas along with a few operations of my own. Just remember my advice about many. Since more than one surgeon can participate, you need a junction table. In that table, one would be marked primary and others would be marked as assistants.

I've been thinking long and hard about this and have come up with a model which describes this corner - I understand the requirement for multiple operators to be logged - I will amend the table so that It junctions with the consultant table (tblConsultants ONE >> MANY tblOperation and the tblStaff ONE >> MANY tblOperation) such that consultants and

I am also going to have to add consultants as users as well - they'll need to be placed in the tblStaff as well.

[FONT=&quot]Ok I'll have a look into this and come up with a model which can describe this corner.

Thanks again for all of your help!
[/FONT]

Overall at this stage I'm feeling a bit overwhelemed with the scale of the task i've set myself!! ....

I am assuming there is a way to set permissions based on user criteria to be able to or at least open various forms.... I was planning this so that doctors can edit certain bits of the database, nurses other and other users the ability to run various commands such as print lists etc but not the ability to edit any of the fields.

Click to expand...

In versions of Access prior to A2007, there was something called ULS (User Level Security). It allowed you to control all objects in the database using a fairly intuitive although tedious interface. However, the tool (aside from the actual settings interface) was extremely confusing and many people simply never got it right so it caused a lot of service issues. Instead of fixing it, Access got rid of it. That means that you are totally on your own with internal security. I would stick with something much more simple than ULS. Given the nature of the app, everyone should be able to see everything. That solves a major complication. Updating is more easily controlled but, you'll be writing a lot of code if you want some people to update some controls and other people to update others. Again, simplify this as best you can. A lot of the data entry will be "add" rather than "change". Each time a technician takes readings, he will log them but he would never go back to change old ones although you probably need to allow for "delete" to eliminate errors.

Regarding adhering to the UK version of HIPPA, since you apparently have the same type of law, you won't be able to use Access to keep the data. You can use Access as the FE. Something just came to mind. I'm not sufficiently conversient with HIPPA to know if this will work but if the database has no personally identifiable information, Access might work as a BE. Each patient would have an ID but there would be nothing in the database that ties that ID to a specific individual. It's just a thought. You can always start your development with ACE as the BE and switch to SQL Server later. In either case, search for papers on how to optimize Access for client/server. ACE (Access' internal data engine) is a relational database and for purposes of how you use it with Access, works exactly the same was that SQL Server does. So, if it works with ACE, it will almost certainly work with SQL Server (or Oracle, or DB2, or Sybase, or whatever RDBMS you end up with) works. That is an over simplification but for your current stage, it works.

You are correct. Logging can get quite complicated. You need to decide up front what type of queries you will need to satisfy. I agree, snooping isn't really the issue. What is the issue is having to answer to a board of inquiry regarding what you did and how you did it. A simplistic solution with minimal code is to duplicate all tables with the addition of a new PK field and a loggedDate field. To use this method, you either copy the unchanged record in the BeforeUpdate event and save it to the appropriate log table. Or you copy the changed record in the AfterUpdate event and save it to the appropriate log table. Either way works. This saves a lot of extra data at the expense of using a lot of code to save only the actual columns that changed.

In versions of Access prior to A2007, there was something called ULS (User Level Security). It allowed you to control all objects in the database using a fairly intuitive although tedious interface. However, the tool (aside from the actual settings interface) was extremely confusing and many people simply never got it right so it caused a lot of service issues. Instead of fixing it, Access got rid of it. That means that you are totally on your own with internal security. I would stick with something much more simple than ULS. Given the nature of the app, everyone should be able to see everything. That solves a major complication. Updating is more easily controlled but, you'll be writing a lot of code if you want some people to update some controls and other people to update others. Again, simplify this as best you can. A lot of the data entry will be "add" rather than "change". Each time a technician takes readings, he will log them but he would never go back to change old ones although you probably need to allow for "delete" to eliminate errors.

Yes I've read about this... I think i've hit upon a guide which should be able to take me through what I'm seeking....LINK. Simple is the KEY! We're doubly "protected" anyway as the only access would be those that can get onto the network in the first place....on this note...I am assuming that I can build some kind of "timeout" function for people who walk away leaving the app open?

Regarding adhering to the UK version of HIPPA, since you apparently have the same type of law, you won't be able to use Access to keep the data. You can use Access as the FE. Something just came to mind. I'm not sufficiently conversient with HIPPA to know if this will work but if the database has no personally identifiable information, Access might work as a BE. Each patient would have an ID but there would be nothing in the database that ties that ID to a specific individual. It's just a thought. You can always start your development with ACE as the BE and switch to SQL Server later. In either case, search for papers on how to optimize Access for client/server. ACE (Access' internal data engine) is a relational database and for purposes of how you use it with Access, works exactly the same was that SQL Server does. So, if it works with ACE, it will almost certainly work with SQL Server (or Oracle, or DB2, or Sybase, or whatever RDBMS you end up with) works. That is an over simplification but for your current stage, it works.

Given the problems that we face in terms of how things are so shambolically organised issues with data security complicance to HIPPA are something of a lower order....I am very grateful for you pointing this out to me as I was not something that I was previously aware of. I think it's probalbly something that I need to address with my IT guys... I will do the reading and am currently sitting through an SQL course to get my head around it a bit. In any case at present everyone using this database will be accessing it through our network...

You are correct. Logging can get quite complicated. You need to decide up front what type of queries you will need to satisfy. I agree, snooping isn't really the issue. What is the issue is having to answer to a board of inquiry regarding what you did and how you did it. A simplistic solution with minimal code is to duplicate all tables with the addition of a new PK field and a loggedDate field. To use this method, you either copy the unchanged record in the BeforeUpdate event and save it to the appropriate log table. Or you copy the changed record in the AfterUpdate event and save it to the appropriate log table. Either way works.

I hadnt even thought about this! I will see if I can reason out this in the spiderweb of relationships

Thanks again for your help!

In addition to network access, your IT people can add extra security for the directory that holds the BE. Tell them each user needs CRUD permissions. The Create and Delete are required because Access creates a lock file when someone opens the database and then deletes it when the last person logs off. If the first user does not have permission to create the lock file, Access restricts all other users to read-only. The reason that Jet/ACE is not HIPPA compliant is because the entire file can be copied and removed from the network. With a "real" RDBMS, users don't have access to the underlying database files.

Indeed it should be possible to seggregate the BE into a locked area of the network. Which will provide some kind of security and stop grade 1 bozos from deleting the BE! With respect to the lock file... when i've split previous databases and had multiple users logged on it normally created a lock file for every front end that was open... my solution for this was to distribute a copy of the front end to each user's team...This worked - without any "special" dispensation from IT.

Because we also have segreated directory permissions depending on our individual log ons this was also a route I'd planned for creating bespoke front ends without a risk of crossover for nurses and doctors seperate as my model requires....

Thanks again for your help!

Access creates only a singe lock file for each open database. Subsequent users are logged into the open lock file. You would never see multiples. The lock file is always created in the same folder as the database and is given the same name but with a different extension. So MyDB.accdr has a lock file named MyDB.laccdb. The lock file is used by Access to manage simultaneous users.