Append records to same table based on listbox selection

[parkerkay]

OK...

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.

To anyone willing to take a look at this and help me: Thank you so much. Let me know if this is hard to follow and you need more info. THANKS!!!

 

[MajP]

tblchecklistname
checklistname
checklistnameid.

tblChecklist
checklistid
observationdetailid (fk),
categoryid (fk)
behaviorid (fk)
checklistnameid (fk)

tblChecklistDetail
checklistdetailsid
checklistid (fk)
ratingid (fk)
reason

I do not see a reason for the name table. Can a name be associated to more than one checklist? Looks like a one to one relationship and should just be in the tblChecklist

"Now, I want that subform to populate the list after the name of the checklist is chosen in the listbox"

I interpret that to mean if I select old "Checklist One" with checklistNameID = 1
and create a new "Checklist Two"
with checklistNameID = 2

1)I want to create a new name in tblChecklistName and it get a new auto ID of 2 (or whatever).

2) I want to copy all
observationdetailid (fk),
categoryid (fk)
behaviorid (fk)
from tblChecklist where checkListNameid = 1
and for each record append the new checklistnameid = 2

4)For each record created in (2) append a checklistid to tblChecklistdetail

Is that correct?

 

[parkerkay]

The reason I put the checklistnames in a different table is because...well, each checklist can have multiple numbers of behaviors and categories, and...well, it just felt right having that in a separate table.


I have some code that will append records based on an ObservationType (number). Where upon entering the type, and hitting a refresh button, the records (containing behaviors and categories, with blank ratings and reason fields), would append to the checklist with the proper observationdetailID. Well, that observationtype is coded as a long, and when I tried to change it to a string (for a name, instead of a number), the code wouldn't execute (I'm not skilled in VB, but I know programming and was pretty sure that I changed all appropriate things when trying to do that).

Now, I didn't quite understand what you meant in #2. I will try to re-explain my process:

I (the user) need to create two checklists, one for the office workers and one for the production workers. I have a form that allows me to select behaviors and categories and save each checklist. So, two checklists, Office Work with ID = 1, and Production Work, with ID = 2. (lets say there is an ungodly amount of behaviors listed on one of these checklists)

I have to do twenty different observations, of the behaviors specified on the list, with each checklist and determine whether the observed individuals are performing those behaviors as 'safe' or 'at-risk'. I print out a bunch of checklists and go out and observe.

Now, I sit down at my data entry screen. On the main form, I enter data pertaining to the individual observed (dept, area, sub-area, shift, and my name <-- all these fields are id numbers on the ObservationDetail Table, which holds the main data, that point to their own separate tables, in order to reduce redundancy, the user can manage these lists in another form). Now, there is a subform on this form that is based on a query. That query grabs the behaviors and categories from the tblchecklist, and the ratings and reason categories from tblchecklistdetails (in order to have my list of specific behaviors, with blank ratings and reasons for each one).

That is as far as I've got. Ideally, I would like a listbox or combobox on that main form, (there is a field checklistnameID on the observationdetails table <- another reason I liked having a separate table), that the user can select the name of the checklist, say Office Work, then have the subform populate with four fields: categories and behaviors (from the checklist table, with the same records the user defined as Office Work) and ratings and reason (from the checklistdetails table, with blank records ready to recieve the observational rating and reason. The reason I'd like it that way is because a checklist can contain 40 different behaviors, and one observer can spend hours collecting data. It would take a long time for the user to select each category and each behavior for each observation, when in fact the same categories and behaviors were observed the majority of time. The only thing that changes when using the same checklist on many observations are the ratings and reasons for each behavior.

Now, the observationdetailid in the checklist table was there to get that segment of code to execute (what I was talking about earlier), however I did not like that because the user only got to define one checklist, and that had to be within the data entry form, (because of that observationdetailid).

In a nutshell, here is the Process:
Define a checklist of categories and behaviors
Go make twenty separate observations (either of one or many people at once)
Enter the data in a timely manner

Hopefully this helped explain things.

Thank you so much for your help.

kaylee

 

[MajP]

I think I got it. The name table is fine, but you still could have accomplished what you want and simplified it without the name table. There are few cases for a 1 to 1 table except for security of data or speed.

With that said, it would make it a lot faster if you could post on a free file sharing site a portion of your database. I do not endorse any but use 4share.com. Just include some relevant records, tables, and forms. Do not include any private information. This is one of those problems where I can demo the code and queries in 10 minutes or spend a lot longer trying to explain it.

Take a look at horseshowdemo.mdb

http://www.4shared.com/dir/6086223/c26decf2/PartCatClass.html

Read the download instructions carefully; scroll to the bottom of the form. This site has a lot of features but not real user friendly.

Add a new horse show, and it will run a query to append all show classes. The idea is very similar to what you are asking, but instead of appending all records you want to append only those records from the "copy from" checklist. Also you will have to run two append queries one for tblChecklist and one for the checklist details.

Look at the queries and the code to run the queries.

 

[parkerkay]

I have uploading to the file sharing site you use. I am not quite sure how things work, so let me know what to do from here.

I will be studying that horseshow demo.

Thank you very much.