Page 2 of 2 FirstFirst 12
Results 16 to 24 of 24
  1. #16
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Which forms do I need to look at?
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  2. #17
    mlrucci is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2018
    Posts
    202
    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.

  3. #18
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    OK I now have both forms open at the same time (together with all subforms) and still have 245 connections available

    Click image for larger version. 

Name:	Capture.PNG 
Views:	29 
Size:	10.4 KB 
ID:	33726

    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

    Click image for larger version. 

Name:	Capture1.PNG 
Views:	27 
Size:	12.3 KB 
ID:	33725
    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
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  4. #19
    mlrucci is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2018
    Posts
    202
    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!

  5. #20
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    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
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  6. #21
    mlrucci is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2018
    Posts
    202

    Normalizing my db

    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

  7. #22
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    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
    Click image for larger version. 

Name:	Design1.png 
Views:	28 
Size:	104.3 KB 
ID:	33742
    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....

  8. #23
    mlrucci is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2018
    Posts
    202
    Quote Originally Posted by ssanfu View Post
    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
    Click image for larger version. 

Name:	Design1.png 
Views:	28 
Size:	104.3 KB 
ID:	33742
    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

  9. #24
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    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.
    Attached Files Attached Files

Page 2 of 2 FirstFirst 12
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 11-17-2016, 10:55 AM
  2. Replies: 1
    Last Post: 09-07-2015, 08:00 AM
  3. Replies: 4
    Last Post: 08-11-2014, 01:18 PM
  4. Replies: 2
    Last Post: 07-31-2014, 08:56 PM
  5. Replies: 2
    Last Post: 07-14-2014, 10:34 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums