Advice on creating a database

[forest8]

Hello

I need to create a Case Management Database.

This database has 4 subject areas:

Personal Info
Personal Assessment
Case Notes

Within the Personal Info area, there are several sub-areas which must be
included:

General Info, Current Info, Legal Involvement, Substance Use, Food Needs,
Health Needs, Relationships, and Supports. [These can be updated multiple
times]

Within the Personal Assessment are 3 surveys which must be conducted with
each participant: A screening survey, the intro survey, and post surveys at 6
months intervals. With the exception of the poat survey, the screening and
intro survey will only be conducted once.

Within the Case Notes section, there are short term and long term goals,
contact logs, Case notes, and Case logs. [These will be updated multiple
times]

Each participant will have a unique identifier which I have created
(ClientID). This is based on whether they are the control group or not.

Each category mentioned above has its own table at the moment.

I am having difficulties in creating relationships between my tables. I
thought that my primary key should be ClientID since it appears in every
table and is the link to all other tables but I cannot create a relationship
based on referential integrity.

Any help will be appreciated.

Thanks

 

[Allen Browne]

You will have a Client table that contains the client's name and
biographical details. This table will have an autonumber field (typically
called ClientID) as its primary key.

The table that needs to relate back to the client will also have a ClientID
field, of type Number. In the lower pane of table design, the field size
should show as Long Integer, and remove the zero if you see it beside
Default Value. (Older versions of Access defaulted to zero.)

Now open the Relationships window, and include both tables. Drag
Client.ClientID and drop onto the ClientID in the other table. You should
now be able to check the box for Relational Integrity.

If you can't see what error message you get. If it tells you there's a
problem with existing data, you already have records in the related table
that don't match (e.g. they may have zero as the ClientID.) If it says the
fields don't match in size, you've not set them up as described above.

In the end, there is a great deal of work in creating an application such as
you describe. If you are doing this part time (along with other work) and
have no experience with creating Access databases, expect to poke along with
this for about 2 years before you get something that's behaving as you
describe.