Creating new record

[jzwp11]

No, you are not dumb. Do you have participants that live at the same address and have the same phone #? If so, then your structure is valid.

[ellixer]

It is a possibility, but most shouldnt. The other thing I am now confused about is forms. Do I just do the same thing i did before, or do i make seperate forms for each thing? Brain hurts.

[jzwp11]

I would not recommend even starting your forms until your table structure is complete. If you create a form now and then make changes to the table structure, you would probably have to rebuild the form anyway.

[ellixer]

Heres what ive got.

[jzwp11]

It's a little hard to read, but I still see tblDrugs that list beer and wine as individual fields. As I mentioned earlier, specific items should be records in the table not fields.

[ellixer]

Ya, i think im confused about that. How do you mean record?

[jzwp11]

A record is an entry in a table. See the attached database as an example.

[ellixer]

Ohh physically type that in.

[ellixer]

Ok, so with the tables with dates and so forth how am I supposed to do those ones so they connect properly to each participant.

[jzwp11]

I can't tell much about the dates since I cannot read all of the field names in the relationship diagram you posted. How are the dates being used? Are the dates associated with particular events related to the participant? You'll have to provide a little more detail of what your application is designed to do.

[ellixer]

The dates are associated with the start and end of admission, programs, assesments, there are dates for everything. Each participant will have different dates.

[jzwp11]

As you describe them, the dates would not be key fields (i.e. not used to link tables). However, the "admission, programs, assesments" would probably more significant.

You'll have to define these a little further to determine how significant.

Can a participant be admitted & then discharged and then readmitted at a later date? If so, that describes a one-to-many relationship.

With respect to programs, do you have multiple programs in which your participants can participate? Can multiple participants participate in the same program? If both conditions are true then you need a junction table to relate the participants to the programs that they take. The structure would look something like this

tblParticipants
-pkParticipantID primary key, autonumber
-txtFName
-txtLName

tblPrograms
-pkProgramID primary key, autonumber
-txtProgramName

tblParticipantPrograms (the junction table)
-pkPartProID primary key, autonumber
-fkParticipantID foreign key to tblParticipants
-fkProgramID foreign key to tblPrograms
-dteStart (start date)
-dteCompletion (completion date)


Regarding assessments, I assume you do the assessments and that a participant may undergo many assessments. What information is captured other than when the assessment occurs?

[ellixer]

You get most of the persons info from an initial assesment, another one has a score 1 thru I dont know. Some people will have the same assesment dates, as they are often done in groups. There are four or five categories of programs and within those are different programs. More than one person can be in the program, and they can be in multiple, but the dates of the start and end are often different. With the bit you just showed me I'm understanding it a bit more.

[jzwp11]

...another one has a score 1 thru I dont know. Some people will have the same assesment dates, ...

Are you responsible for calculating the score via the database or just recording the final score? If you are responsible for the calculation, you will need to have the assesment factors and the scoring system incorporated into the database. As a general rule, you would not store the actual calculated values, just the raw parameters used to calculate it. You would calculate the value on the fly when you need it in queries, forms or reports.

[ellixer]

I just have to put in the final score