Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2008

    Unanswered: Append records to same table based on listbox selection


    I have been working on this observational database for awhile now, and have run into another hurdle.

    I am trying to get a 'checklist' to show up.

    This is how I've set things up:
    There are tblchecklistname, tblchecklist, and tblchecklistdetail table. The tblchecklistname contains 2 fields: checklistname and checklistnameid. tblChecklist contains 5 fields: checklistid, observationdetailid (points to observationdetail table), categoryid, behaviorid, and checklistnameid. TblChecklistDetail contains 4 fields: checklistdetailsid, checklistid, ratingid, and reason.

    The user creates a checklist using a form, and the data is stored in the checklist table. Checklists contain categories and behaviors (to be observed). The checklistname table allows for the user to create more than one checklist.

    The user needs to input observational data. That data consists of a list of set behaviors (to be used for several observations), each with individual ratings (hence, the checklistdetails table).

    Now the problem:
    On the data entry screen, there is a listbox of the checklist names. I want the user to be able to select the name of (previously designed) the checklist they used during observation. Then there is a subform on the form, based on a query that selects the observationdetailid, categoryid, and behaviorid for tblchecklist, and the ratingid and reason from tblChecklistDetails (in order to get an individual rating for each behavior). Now, I want that subform to populate the list after the name of the checklist is chosen in the listbox. I figure I need an append query, but confused when I realized I wanted to append to the same table.

    I figured that this was hard to follow, so I attached my work in progress.
    The forms to pay attention to are frmObservationDetails and sfrmObservationChecklist <--these are the ones I am working on
    Other forms to look out, for a little FYI, would be frmChecklistSetup and sfrmChecklist <--this is where the user defines things

    To anyone willing to take a look at this and help me: Thank you so much.
    Attached Files Attached Files

  2. #2
    Join Date
    Dec 2004
    Madison, WI
    I can see you've been studying normalization rules. I will try to answer your question later but I have to make a comment on your structure and to consider this:

    Having 1 tblObservationDetails table with all the text fields in that 1 table (such as Area, Department, First Name, LastName, Sub-Area, etc..) with some reduncy in data
    Multiple tables with multiple fields (ie. ID fields and the Text fields) plus having to link all the tables together each time to get all the data.

    Although you've followed rules of normalization, I hate to see a linked table with just 1 or 2 texts field in it with these kind of one-to-many relationships. If you were to have a few million records (as an extreme), a query to return all the fields would be a lot slower than having 1 table with all the text fields in that 1 table plus it makes it more difficult working with multiple tables for data entry versus 1 table. And I only bring this up because most of your linked tables only store 1 text field of data in them and the one-to-many is not many of the linked tables data to 1 tblObservationDetail record but the opposite.

    Again, there will be many who will agree with your design but just consider returning ALL the fields for the tblObservationDetails and what it entails or entering 1 data record (6 linked tables, and storing 18 fields of data (including all the ID fields)) versus 1 Table with 6 text fields and some reduncy in data (which isn't necessarily a sin - I mean, after all, you are duplicating ID fields when you split out all the tables like you did). Just something to think about. For example, some people would split out an address into multiple tables (1 table for City, 1 for State, 1 for Zip, etc...) I personally wouldn't do this as the cost of putting all those tables together to return 1 simple address (or enter it) just isn't worth the complication in design. Consider if you had a few million records and I asked you to return all the Observation Details. You'd have to link 6 tables together to get all the data and the query would be a tad slower than if it were all in 1 table. Again, just something to think about.

    Now if you had multiple (for example) Departments for 1 tblObservationDetail record, then I'd stick with your design but that's not the way you have it set up. You have 1 (for example) Department for multiple tblObservationDetail records (and the same for your other linked tables to the tblObservationDetails table). I might consider your design as more practical if you had multiple Observers, multiple Departments, multiple Areas, etc...for 1 tblObservationDetails record. (kind of like having a separate linked table for multiple customer phone numbers for 1 customer record.)

    I personally, would have made it easier for query returns for your tblObservationDetails and gone the route of 1 table and a bit of reduncy in data versus all the linked tables but that's just me and I wanted to give you something to think about in your future designs.

    I would've made my tblObservationDetails more like this:
    Comments (although here I might split this as a linked table if I had multiple comments for 1 tblObservationDetail record.)

    Although I may have 2 or more records which have the same Department, Area,'s soo much easier for data returns/queries if you want all the fields.

    But I don't fully know the works of this database and perhaps you've intentionally made all the tables separate with these types of relationships for some other purpose.
    Last edited by pkstormy; 06-09-08 at 20:58.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    Jun 2008
    Originally, I had it set up the way you described.

    However, after handing off a test version, I decided it would be best to spilt the tables because:

    One of the main purposes of this database is to run reports of %safe behaviors by EVERYTHING, by department, area, sub-area, shift, observer, date, all of it. Having the user input various spellings and capitalizations of the same anything (dept, area, etc) would defeat that goal of having accurate reports. I left the date, time, and number of people observed in the observationdetail table because these were the only fields I felt confidant the user could not mess up.

    What I have now are forms for the user to manage each of those tables with an id field and name field. I figured this would pave the way for more accurate reports.

    Now, the relationships, I don't know...they mess me up sometimes and I have to change them to simple joins to do what I want without a bunch of "I need this becuase it is required in tbl whatever". It gets really annoying when you're trying to make some headway.

    I appreciate that you took the time to look over this.


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts