Multiple entries in to a field 1

[germain2]

I am building a database for an agency but having a problem because one field has several subfields. For example, a field that is program i.e., the name of the program requires entering the names of more than one program, how do I resolve this problem?

 

[Remou]

With another table.

[tt]tblA
AID

tblProgrammes
ProgID
Programme

tblAProgrammes
AID
ProgID[/tt]

You may wish to read

http://www.r937.com/relational.html

 

[germain2]

Remou
Thanks for your help. I am new to this and need some more insight to help as i read the article you recommend. My problem is this.
The form I am building has the information about the agency, address etc. which is easy to do!!
I get to Programs and this is the information i need to store
Prog name (A)
Avg length of treatment (B)
Average frequency of appts. (C)
A variable with an option list (D)

Do I create the table using the link relationship and if so what of the options do i select from the dialogue box before creating the relationship.
Can you walk me through this? Thanks

 

[Remou]

You need to make some decisions. Are:

Prog name (A)
Avg length of treatment (B)
Average frequency of appts. (C)
A variable with an option list (D)

Properties of the programme or properties that change from person to person? If they are properties of the programme, they should go in the programme table, if they change from person to person, they should go in the linking table.

The relationships between the tables go like this (use the relationship window):

[tt]tblAgency tblAgencyProgrammes tblProgrammes
ProgAgencyID (PK)*
AgencyID (PK) -> AgencyID (FK)
ProgID (FK) ----> ProgID (PK)[/tt]

* You may prefer to use the combined foreign keys as your primary key (

http://r937.com/20020620.html)

Once you have the tables set up, you can create a subform on your Agency form. The subform's record source will be the link table, tblAgencyProgrammes, which will allow you to have many programmes for each agency.

There are wizards that will guide you through much of the basic set-up.

It is also a good idea to study the Northwind sample database that ships with Access.

It is usually best to put a good deal of time and thought into the structure of the database before you start building forms and so forth.