Help w/ DB from scratch.

[ellixer]

I have been charged with creating a DB for patients in a recovery program. There is a lot of specific information they want to keep track of for each person in order to gather stats.

I am a first time access user and have managed to build a database with tables and forms, but I dont know if i correctly disseminated the fields, and I cant seem to get the relationships right. Any ideas?

[RuralGuy]

Best advice I can give you is pick an existing template from among those that ship with Access or are on-line at MS Office that is close to what you want. Then modify it to your specifications.

[ellixer]

Here is a screen shot of my relationships.

[RuralGuy]

Okay, you don't want to start over. Where to start? Every table should have a PrimaryKey (PK), preferably an AutoNumber field and it should be named something that identifies the table. This key is placed in other tables as a ForeignKey (FK) which will define the relationship for Access. You seem to have put the same key in several tables as the PK unless I simply do not understand your diagram. At least the PK fields are named the same which is never a good idea.

[ellixer]

Ahhhh, ok got that. But how do i put the foreign key in the main tbl?

[RuralGuy]

It looks to me like your "Main" table is tblParticipants and it doen *not* need an FK. I hope you named it something useful like ParticipandID instead of DOC#. You then only need the ParticipantID in the [tblDischarged Participants] table instead of duplicating all of that data. In fact that whole table may just be a single field in the tblParticipants table named Discharged.

[ellixer]

Ok, thanks. It's a bit clearer now.

[RuralGuy]

Reading up on Normalization should help. Google know a lot about that subject. Basically data should *only* exist in one table in an RDBMS. You can put the PK of that table anywhere you need a reference to it and a query join will make it as if the data is all in the same table.

[ellixer]

Yeah, ive been trying to figure out normalization, most of the examples they have are very easy basic stuff so its hard to try and put that with all the info i have.

[RuralGuy]

You goal should be Third Normal Form: http://www.phlonx.com/resources/nf3/