Page 1 of 2 12 LastLast
Results 1 to 15 of 26
  1. #1
    freeskier81 is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jan 2012
    Posts
    12

    form to table - index problem?

    hi and thanks in advance for your kind help!



    info:
    - built a db with several tables, queries, forms and under-forms.
    - my central entry-form is linked to multiple tables.
    - i got one index (patient number) that is identical in all tables.
    - i was able to enter 4 datasets without any problems but than, the additional datasets, after being entered in the form, appear in the table, but, when reopened, have disappeared from the form. and, because the index is already existing in the table, i cannot enter any additional data via the form.

    tia

  2. #2
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    Welcome to the forum!

    - my central entry-form is linked to multiple tables.


    i was able to enter 4 datasets without any problems but than, the additional datasets, after being entered in the form, appear in the table, but, when reopened, have disappeared from the form. and, because the index is already existing in the table, i cannot enter any additional data via the form.
    Are you using subforms in your central entry form? I assume that you did not use subforms. If you base a single form on multiple tables, Access actually binds the form to a query that joins the multiple tables. I assume that for the first four datasets, you entered data such that at least one field in each of the tables had something entered. For the additional datasets, if you did not enter data in at least 1 field from all of the tables then a matching record was not created and thus does not satisfy the joins of the query and thus are not shown when you reopen the form. To avoid this, what is typically done is that you would have a main form with various subforms. The main form would be bound to the primary table which in your case is the patient table. Each subform would then be based on the related tables.

    Now, you did not mention much about how your tables are related. At the heart of every successful relational database is the table structure. Before doing any other forms, queries or reports, it is critical that your table structure is correct.

    For example, let's say that you work for a doctor's office and that you are tracking patient visits to that office. Most people visit the office more than once. So, a patient has many visits. This describes a one-to-many relationship between the patient and visits. To represent that in forms, you would have a main form based on the patient table with a subform based on a table that holds the info about the various visits.

    Let's say for example that you want to track patients' medications. A patient can take several medications. Also, a medication can be taken by many patients. So, you have 2 one-to-many relationships. When you have 2 one-to-many relationships between the same two entities (patients and medications) you have a many-to-many relationship. This is typically handled with 3 tables (patient table, medication table, and a table that joins both patients and their medications: also called a junction table).

    Now, to make sure you are on the right track relative to your table structure can you zip & post a copy of your database (with any sensitive data removed) or at least your table structure?

    Something similar to this

    tblPatients
    -pkPatientID primary key, autonumber
    -txtFName
    -txtLName

    tblMedications
    -pkMedID primary key, autonumber
    -txtMedName

    tblPatientMedications
    -pkPatientMedID primary key, autonumber
    -fkPatientID foreign key relating back to tblPatients
    -fkMedID foreign key relating back to tblMedications

  3. #3
    dblife's Avatar
    dblife is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Nov 2011
    Location
    South Shields, UK
    Posts
    88
    If this was Access 2003, I would ask, in the properties box for the form, do you have 'DataEntry' selected as 'Yes' - I do not know if this is the same in Access 2010?
    This option allows data entry in the form but does not show exisiting records when opened.

  4. #4
    freeskier81 is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jan 2012
    Posts
    12
    Quote Originally Posted by jzwp11 View Post
    Welcome to the forum!



    Are you using subforms in your central entry form? I assume that you did not use subforms. If you base a single form on multiple tables, Access actually binds the form to a query that joins the multiple tables. I assume that for the first four datasets, you entered data such that at least one field in each of the tables had something entered. For the additional datasets, if you did not enter data in at least 1 field from all of the tables then a matching record was not created and thus does not satisfy the joins of the query and thus are not shown when you reopen the form. To avoid this, what is typically done is that you would have a main form with various subforms. The main form would be bound to the primary table which in your case is the patient table. Each subform would then be based on the related tables.

    Now, you did not mention much about how your tables are related. At the heart of every successful relational database is the table structure. Before doing any other forms, queries or reports, it is critical that your table structure is correct.

    For example, let's say that you work for a doctor's office and that you are tracking patient visits to that office. Most people visit the office more than once. So, a patient has many visits. This describes a one-to-many relationship between the patient and visits. To represent that in forms, you would have a main form based on the patient table with a subform based on a table that holds the info about the various visits.

    Let's say for example that you want to track patients' medications. A patient can take several medications. Also, a medication can be taken by many patients. So, you have 2 one-to-many relationships. When you have 2 one-to-many relationships between the same two entities (patients and medications) you have a many-to-many relationship. This is typically handled with 3 tables (patient table, medication table, and a table that joins both patients and their medications: also called a junction table).

    Now, to make sure you are on the right track relative to your table structure can you zip & post a copy of your database (with any sensitive data removed) or at least your table structure?

    Something similar to this

    tblPatients
    -pkPatientID primary key, autonumber
    -txtFName
    -txtLName

    tblMedications
    -pkMedID primary key, autonumber
    -txtMedName

    tblPatientMedications
    -pkPatientMedID primary key, autonumber
    -fkPatientID foreign key relating back to tblPatients
    -fkMedID foreign key relating back to tblMedications
    Thank you for your input.
    I'have posted a .zip with the db. Please consider that this db has been built in a german ms access. I hope this isn't interfering with your efforts.

    tia

  5. #5
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    You have some serious normalization issues with your database. I would recommend that you read up & understand normalization before doing anything. This site has a basic overview as a start. I would recommend setting your current database aside and starting with a new one; no forms, queries or reports should be created until your table structure is normalized.

    I assume that the PNR field you have in every table as the primary key somehow relates to a patient. You cannot do that. The PNR can be the primary key in 1 table but will be a foreign key in related tables.

    In your lab results table you have a field for every test, that is incorrect as well. A test should be a record in a table and you would relate the test to the patient. I assume that a patient can have many visits where these tests can be repeated again and again, so it would be better to relate the patient to the visit and then relate the tests to that visit/patient combination. Something like this

    tblPatients
    -pkPatientID primary key, autonumber
    -txtFName
    -txtLName

    tblPatientVisits
    -pkPatientVisitID primary key, autonumber
    -fkPatientID foreign key to tblPatients
    -dteVisit (date of visit)

    tblTests (holds only the tests as records)
    -pkTestID primary key, autonumber
    -txtTestname

    tblPatientVisitTests
    -pkPatientVisitTestID primary key, autonumber
    -fkPatientVisitID foreign key to tblPatientVisits
    -fkTestID foreign key to tblTests
    -TestResult

    Not knowing the details of the process you are modeling, the above table structure should only be considered as an example.

    In order for us to help you achieve a normalized table structure,you will have to explain the medical process for which you are trying to capture data.

  6. #6
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    In response to your IM, the basic structure I proposed is a good start, you would only need to insert the observations and parameters relative to a visit.

    A few questions for you on that.

    Are the parameters of an observation in anyway related to the tests or are the tests only related to the observation?

    Can the another patient undergo the same observations as another patient?

    Are the parameters only applicable to 1 observation?
    Are the parameters unique to a patient/observation?

  7. #7
    freeskier81 is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jan 2012
    Posts
    12
    Quote Originally Posted by jzwp11 View Post
    In response to your IM, the basic structure I proposed is a good start, you would only need to insert the observations and parameters relative to a visit.

    A few questions for you on that.

    Are the parameters of an observation in anyway related to the tests or are the tests only related to the observation?

    I hope i got the question.
    At every given time point ( day 1 - baseline, +1h, +2h, +4h, +6h, +10h; day 2 - +24h, day 8 - +168h) a certain number of lab tests are done in every single patient. The same lab tests in all patients. so the results of every observation (e.g. +1h, +2h,....) are connected to the certain time point of the observation. kind of complicated and hard to describe...


    Can the another patient undergo the same observations as another patient?

    all patient are undergoing the 100% same procedure.

    Are the parameters only applicable to 1 observation?
    Are the parameters unique to a patient/observation?
    the parameters/lab tests are repeated at every time point/observation and are the sam in all patients.

  8. #8
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    The one thing I am confused about is that you mention observations, parameters and tests. I now understand the observations as being time periods and I understand that the tests are medical tests you conduct at the various observation times, but what are the parameters? Perhaps a couple examples of each would help?

  9. #9
    freeskier81 is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jan 2012
    Posts
    12
    Quote Originally Posted by jzwp11 View Post
    The one thing I am confused about is that you mention observations, parameters and tests. I now understand the observations as being time periods and I understand that the tests are medical tests you conduct at the various observation times, but what are the parameters? Perhaps a couple examples of each would help?
    You're totally right - I've messed up a couple of terms. So I will try now to define a persistent terminology.

    1. visit = each day a patient attends the clinic; 4 per patient, 1 screenig visit and 3 study visits.
    2. observation = time point when blood is drawn to be used in lab tests or physical exams are conductes, respectively. 9 per patient.
    3. tests = different lab tests. repeated for every observation.
    4. parameter = lab test result.

    have I cleaned up te mess?

  10. #10
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    Yes, that cleans things up nicely. Starting from what we had previously:

    tblPatients
    -pkPatientID primary key, autonumber
    -txtFName
    -txtLName

    tblPatientVisits
    -pkPatientVisitID primary key, autonumber
    -fkPatientID foreign key to tblPatients
    -dteVisit (date of visit)


    Now add the observations. Since the times are fixed and apply to every patient, we'll set up a table to hold them. Each time period would be a record, so a record for each baseline, +1h, +2h, +4h, +6h, +10h...
    I assume that you just go by hours and the actual day does not matter correct?

    tblObservations
    -pkObsID primary key, autonumber
    -longHour (long integer number, baseline=0, then just the hours since baseline after that)

    Now we have to relate the observation period to the patient visit

    tblPatientVisitObs
    -pkPatientVisitObsID primary key, autonumber
    -fkPatientVisitID foreign key to tblPatientVisits
    -fkObsID foreign key to tblObservations

    We still need the test table

    tblTests (holds only the tests as records)
    -pkTestID primary key, autonumber
    -txtTestname


    Now relate the tests to each patient/visit/observation period combination

    tblPatientVisitObsTests
    -pkPatientVisitObsTestID primary key, autonumber
    -fkPatientVisitObsID foreign key to tblPatientVisitObs
    -fkTestID foreign key to tblTests
    -Parameter

  11. #11
    freeskier81 is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jan 2012
    Posts
    12
    hi again!

    I'm afraid i don't get your proposed structure.
    Maybe it's because I had a completely wrong idea of how my DB should look like from the start.
    I put many hours work in the design of my input forms - is that all lost or can I use them with the new design?
    In the end I need to get the data out of the DB in some fashion like:
    see postet tbl.

  12. #12
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    What you posted looks like a spreadsheet; data is not handled that way in a relational database, but you can extract it like that using queries. In my original response to your post, I included a link to a site that explained normalization. The rules of normalization are at the heart of relational databases. These rules are used to create the proper table structure. Once the table structure is created properly then and only then should forms be created. Unfortunately, any forms you may have created prior to the rework of the tables will not function properly and will have to be recreated.

  13. #13
    freeskier81 is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jan 2012
    Posts
    12
    Quote Originally Posted by jzwp11 View Post
    What you posted looks like a spreadsheet; data is not handled that way in a relational database, but you can extract it like that using queries. In my original response to your post, I included a link to a site that explained normalization. The rules of normalization are at the heart of relational databases. These rules are used to create the proper table structure. Once the table structure is created properly then and only then should forms be created. Unfortunately, any forms you may have created prior to the rework of the tables will not function properly and will have to be recreated.

    thougt so.
    the spreadsheet I posted should gave been an example for one of the "results querries" I'd like to generate for using them with e.g. SPSS.
    What I tried to point out with the example was, how I need the generated spreadsheet to look like and further to know if this is possible with your structure.

    thanks!

  14. #14
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    Yes, outputting the data in the format you showed is possible with the use of queries with probably subqueries.

  15. #15
    freeskier81 is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jan 2012
    Posts
    12
    I don't get the observation table. I've got 9 time points/observations, do I need 9 variables in the tbl? or just one variable? and how do I connect the table?

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

Similar Threads

  1. 'Id' is not an index in this table - Help
    By waldock9 in forum Access
    Replies: 3
    Last Post: 11-08-2011, 01:48 PM
  2. Access 2010 - Tab Index problem
    By ldare2000 in forum Forms
    Replies: 2
    Last Post: 09-07-2011, 05:26 AM
  3. Table Without PK Switching My Index Orders
    By ajetrumpet in forum Access
    Replies: 5
    Last Post: 09-07-2010, 06:11 PM
  4. Replies: 4
    Last Post: 06-10-2010, 07:51 AM
  5. Table / Form problem
    By mayler in forum Access
    Replies: 1
    Last Post: 05-15-2009, 07:09 PM

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