Which forms do I need to look at?
frmLogStrokeIscThrombAll The others are just subforms. frmLogStrokeIscTPAAll has fewer subforms but I have same problems with both forms. These are the largest forms in my db. all others have no problems.
OK I now have both forms open at the same time (together with all subforms) and still have 245 connections available
Please can you clarify exactly what I should do to trigger the issue with the unsplit database
I've also split your database - opening the form frmLogStrokeIscThrombAll (whatever that means!) its now dropped to 25 connections
Closing it recovers all connections
Opening the other form, the number drops to 23
Clicking the Open ...Log button & it recovers to 187
I can't test the blue Open ... Log button as that form is missing from the upload
There are clearly some significant issues with this database
I admire your ability to cram so much into each subform but having a huge number of combos with Yes/no values is draining Access resources.
Looking at your table designs indicates further significant issues.
Almost all tables have a spreadsheet type structure - lots of very similar fields often ending in numbers
For example, tblHem1 has 15 of each of the following fields: Date1, Time1, Complete1, VS1, BP1, GCS1, Pupils1, MS1, HA1, Comment1, Angio1, EmployeeID1 & 2 other fields
That's a total of 182 fields
Table tblHem2 has EXACTLY the same structure but with the number 2 in each case: Date2 etc
Then tables tblThromb_Q1, tblThrombQ15, tblThrombQ30, tblPA_Q1,tblPA_Q15, tblPA_Q30 repeat the same structure another 6 times
So this isn't what you want to hear but there is little point tinkering with this database.
You need to read up about normalisation of data and completely redesign your tables which have a huge number of similar fields.
If you ignore this issue now, your database will at some point become utterly unuseable.
UPDATE: Half an hour later!
I've just recovered my post after having to do the three fingered salute (Ctrl-Alt-Del) to restart my computer.
Your database had caused my PC to run out of resources & I couldn't manage to recover using Task Manager!
I suppose I should have expected something like that with hindsight
Many thanks Matrix for the 'recover auto saved content' feature
Ridders52,
First off,I would like to thank you for your time and input. I am very sorry that it crashed your computer. Thank God for the three finger salute. As you can imagine, I am very frustrated anddisappointed in my build. This programis to base an audit on charting and each field is to be for an individualentry. Based on that entry into eachfield, it will eventually trigger “fallout” from what is expected. I understand that the way I designed thisprogram needs normalization. In readingup on normalization, I am afraid that trying to normalize this “spreadsheet” like a database may be futile. From what I read, the only items I have notput into another table is the “Yes/No”. I was afraid that this would put further strain on the connections asthere would be a lot of connections used for this process with tables. Do you think it is possible to make thiswork? Am I barking up the wrong tree andtrying to make a db from “spreadsheet” type info? Each field in the tables represent a differententry. If you have any suggestions, Iwould love to hear them. I am at a lossand have been working on this program for months. I guess I have been working on this so long,I have tunnel vision at this point. I amself taught with Access and have limited knowledge. Can you send me down a path? Any help would be appreciated. Again, I am sorry about the crash!
I'm not blaming you for the crash - I should have seen it coming & planned for it! As it was, nothing was lost.
I understand how you must feel at the moment - I've been in a similar situation myself.
I've also tried repairing databases for others with similar issues but in all such cases, a complete redesign was the evential solution
Clearly at the very least, the 8 tables with numerous repeated fields need a complete redesign.
I can't advise you how to do that without understanding what the exact purpose of your database is.
So I suggest you step back & explain exactly what you are trying to do in simple language & without using jargon.
That will allow one or more forum members to give you advice on reconstructing & moving forward.
For example, both ssanfu & micron are excellent at database design (as are many others here)
There are also several websites that offer a wide variety of designs for different purposes
For example: http://www.databaseanswers.org/data_models/
The best advice I can give is to not rush into any changes until you have thought them through including careful planning on paper.
You can then upload your design plans at various points to help ensure you get it right
I also suggest you make backups before changing anything
Good luck
I have been working on a db for the last couple of months. Unfortunately, I am new to Access and have created a db that is not normalized (thank you for the input Ridders52) which has caused havoc on my current db. Let me better explain what I am attempting to do. I am attempting to develop a database that will gather information from auditing charts as to make sure the information required is being documented correctly based on 3 different procedures. We have approximately 5 charts a day to review. If the charting is not completed appropriately, then I address the problems. There could be multiple individuals involved with this process. Each of the audits are done in incremental periods. Each document has the following information:
1. Name and Date of Birth (the ID is the patient’s MRN) (tblMRN)
2. Specific data based on the type of audit (such as date/times of procedures and arrivals and medications)
3. Baseline assessment scores (tblNIHSS)
4. Ordered vital sign parameters
5. Hospital (tblHospital)
Each of the procedures (3) must have an assessment done at different incremental periods based on the procedure. Currently, we are using Excel to try and track that information. As you can imagine, this is a nightmare and very inconsistent. All of the procedures audited there needs to be the following (or variations of):
1. Every 15 minutes the following needs to be documented for 2 hours
a. Date of entry
b. Time of entry
c. Vital Signs (Yes/No)
d. BP Parameters (Yes/No)
e. GCS (Yes/No)
f. Pupils (Yes/No)
g. Motor/sensory (Yes/No)
h. Headache (Yes/No)
i. NIHSS (tblNIHSS)
j. Employee making the entry (this is based on tblEmployee)
k. Angio site checked (Yes/No)
l. Comment area
2. Every 30 minutes the following needs to be documented for 6 hours
a. Date of entry
b. Time of entry
c. Vital Signs (Yes/No)
d. BP Parameters (Yes/No)
e. GCS (Yes/No)
f. Pupils (Yes/No)
g. Motor/sensory (Yes/No)
h. Headache (Yes/No)
i. NIHSS (tblNIHSS)
j. Employee making the entry (this is based on tblEmployee)
k. Angio site checked (Yes/No)
l. Comment area
3. Every 1 hour the following needs to be documented for 16 hours
a. Date of entry
b. Time of entry
c. Vital Signs (Yes/No)
d. BP Parameters (Yes/No)
e. GCS (Yes/No)
f. Pupils (Yes/No)
g. Motor/sensory (Yes/No)
h. Headache (Yes/No)
i. NIHSS (tblNIHSS)
j. Employee making the entry (this is based on tblEmployee)
k. Comment area
I am including my tables and a couple of forms for a better visual in order to obtain some insight into improving the development of my tables. My limitation in the development of the tables was the number of fields allowed in a single table. Because of this, I had to break up the tables:
Procedure Case Information Entry forms Hem tblAuditStrokeHem tblHem1 tblHem2 TPA tblAuditStrokeIsc tblTPAQ1 tblAuditStrokeIscAdmin tblTPAQ15 tblTPAQ30 Thromb tblAuditStrokeIsc tblThrombQ1 tblAuditStrokeIscAdmin tblThrombQ15 tblThrombQ30
As I am going through the process for the changes, this is my new thought. Instead of forming a field for each of the individual procedures, I will break it down into the following:
Table #FieldNames DataType Definition tblCaseType 3 text Types of Case (TPA, Thromb,Hem) tblCaseInformation 65 Txt, yes/no,number All detailed information needed for the case tblDate 150 Date/Time All Date tblTime 150 Date/Time All Times tblComplete 75 Yes/No This is used to automatically mark all yes if charting is completed correctly tblVS 75 Text (value list of yes or no) Are the VS documented per protocol tblBP 75 Text (value list of yes or no) Is the BP within the ordered parameters tblGCS 75 Text (value list of yes or no) Is the GCS documented tblPupils 75 Text (value list of yes or no) Are the Pupils documented tblMS 75 Text (value list of yes or no) Was the MS documented tblHA 75 Text (value list of yes or no) HA Assessed tblNIHSSD 75 Text (value list of yes or no) NIHSS assessed tblAngioD 20 Text (value list of yes or no) Angio site assessed tblComment 75 Text Comment forentry tblEmployee 75 number Employee Documenting tblAEmployee 4 All employees and their departments tblANIHSS 10 number NIHSS score numbers tblAUnit 8 number Units involved tblMRN 4 text Patient Information
The thought behind this is to normalize the database by grouping the information together based on similarities. In other words, if I have a TPA audit, I will assign it a Case Type 1 and do the following.
tblCaseType 1 tblCaseInformation Use only info pertinent info from this table for TPA tblDate Use all the dates pertinent info from this table for TPA tblTime Use all the times pertinent info from this table for TPA tblComplete tblVS Use appropriate fields from this table for TPA tblBP Use appropriate fields from this table for TPA tblGCS Use appropriate fields from this table for TPA tblPupils Use appropriate fields from this table for TPA tblMS Use appropriate fields from this table for TPA tblHA Use appropriate fields from this table for TPA tblNIHSSD Use appropriate fields from this table for TPA tblAngioD Use appropriate fields from this table for TPA tblComment Use appropriate fields from this table for TPA tblEmployee Use appropriate fields from this table for TPA
Is this the right thought process? I hope I was clear and any help would be appreciated very much!StrokeAudit1.0_ReducedZipped.zip
I've been looking at your tables trying to understand the relationships... and thoroughly confused myself.
You said:
Procedure Case Information Entry forms Hem tblAuditStrokeHem tblHem1 tblHem2 TPA tblAuditStrokeIsc tblTPAQ1 tblAuditStrokeIscAdmin tblTPAQ15 tblTPAQ30 Thromb tblAuditStrokeIsc tblThrombQ1 tblAuditStrokeIscAdmin tblThrombQ15 tblThrombQ30
Which procedure, Hem, TPA or Thromb, applies to "1. Every 15 minutes the following needs to be documented for 2 hours"?
Which procedure, Hem, TPA or Thromb, applies to "2. Every 30 minutes the following needs to be documented for 6 hours"?
Which procedure, Hem, TPA or Thromb, applies to "3. Every 1 hour the following needs to be documented for 16 hours"?
Or do all 3 procedures apply to all 3 monitoring times?
-----------------------------------------------------------------------------------------------------------
For tables tblHem1 & tblHem2, I normalized them into a new table tblHem. The normalized structure looks like
Note: I also removed the Lookup FIELDS. See The EVILS of Lookup Fileds in Tables
Excel worksheets are normally short and wide.
Access tables are tall and narrow.
What is the meaning of the table names "tblThrombQ1", "tblThrombQ15" & "tblThrombQ30"? "tblTPAQ1", "tblTPAQ15" & "tblTPAQ30"??
Is the table "tblMedicalRecord" the same as table "tblMRN"? ("tblMRN" is not in the posted dBs.)
It looks like the data entry forms are missing....
I've been looking at your tables trying to understand the relationships... and thoroughly confused myself.
You said:
Procedure Case Information Entry forms Hem tblAuditStrokeHem tblHem1 tblHem2 TPA tblAuditStrokeIsc tblTPAQ1 tblAuditStrokeIscAdmin tblTPAQ15 tblTPAQ30 Thromb tblAuditStrokeIsc tblThrombQ1 tblAuditStrokeIscAdmin tblThrombQ15 tblThrombQ30
Which procedure, Hem, TPA or Thromb, applies to "1. Every 15 minutes the following needs to be documented for 2 hours"?
Which procedure, Hem, TPA or Thromb, applies to "2. Every 30 minutes the following needs to be documented for 6 hours"?
Which procedure, Hem, TPA or Thromb, applies to "3. Every 1 hour the following needs to be documented for 16 hours"?
Or do all 3 procedures apply to all 3 monitoring times?
-----------------------------------------------------------------------------------------------------------
For tables tblHem1 & tblHem2, I normalized them into a new table tblHem. The normalized structure looks like
Note: I also removed the Lookup FIELDS. See The EVILS of Lookup Fileds in Tables
Excel worksheets are normally short and wide.
Access tables are tall and narrow.
What is the meaning of the table names "tblThrombQ1", "tblThrombQ15" & "tblThrombQ30"? "tblTPAQ1", "tblTPAQ15" & "tblTPAQ30"??
Is the table "tblMedicalRecord" the same as table "tblMRN"? ("tblMRN" is not in the posted dBs.)
It looks like the data entry forms are missing....
ssanfu,
Thank you very much for your response. So, I relooked at my process and realized I was confusing myself and now see that I made it more difficult (at least I think) than it needed to be. Let me clarify. First, there are two types of strokes Ischemic and Hemorrhagic. The Ischemic Stroke can have two procedures TPA and/or Thrombectomy. The hemorrhagic stroke there is only one procedure. I would like the Ischemic stroke to have to same ID so that I can pull the corresponding procedures to be audited and reviewed.
Which procedure, Hem, TPA or Thromb, applies to "1. Every 15 minutes the following needs to be documented for 2 hours"? TPA and Thrombectomy
Which procedure, Hem, TPA or Thromb, applies to "2. Every 30 minutes the following needs to be documented for 6 hours"? TPA and Thrombectomy
Which procedure, Hem, TPA or Thromb, applies to "3. Every 1 hour the following needs to be documented for 16 hours"? TPA and Thrombectomy
Or do all 3 procedures apply to all 3 monitoring times? The Hemorrhagic is every one hour for 24 hours
What is the meaning of the table names "tblThrombQ1", "tblThrombQ15" & "tblThrombQ30"? "tblTPAQ1", "tblTPAQ15" & "tblTPAQ30"?? This was the old thought process as I didn't normalize the tables. I was basically grouping the time frames into one table as there was a limit on the number of fields allowed in the table. For example tblTPAQ1 was for the procedure TPA with the every one hour assessments needing to be done.
Is the table "tblMedicalRecord" the same as table "tblMRN"? ("tblMRN" is not in the posted dBs.) Yes it is the same. I just moved one over and should be tblMedicalRecord
With that all being said and after reading the feedbacks, I created a new db with new tables. If you could look at what I have created and give feedback, I would appreciate. In addition, I have always used lookups and am not familiar with how to link the tables without it. Would you just connect with the relationships and under the row source put the location (ex: SELECT [tblHospital].[HospitalID], [tblHospital].[HospitalName], [tblHospital].[HospitalActive] FROM tblHospital ORDER BY [HospitalID] or would just put the table? I have enclosed a copy of the word document they have been using and my new db for your review.
Again, thank you very much for your help.
tPA Audit Form.zipStrokeAudit2.0_Dawn.accdb.zip
I see that you have moved on with this problem, but I will go ahead and post here.
The tables tblDVS, tblDDate, tblDTime, tblDBP, tblDGCS, tblDPupils, tblDMS, tblDHA, tblDAngio, tblDComment & tblDEmployee are TOTALLY wrong designs so I deleted them.
Not sure where table "tblAuditStrokeInfo" fits.
I added table "tblHem" and created 3 sub forms, one for Q15Minutes, one for Q30 minutes and one form Q1 hour.
I corrected some linking fields and also deleted Look up FIELDS.
Maybe this will give you some ideas.
Good luck with your project.