make table query creates separate lines for some information

[Guest]

I have three tables that I join with a make table query. One of the tables
contains data for different types of "phone numbers", i.e. e-mail, cell,
work. The table is set up with very few fields. So one person might have
several lines in that table if they have each type of phone. So, 2=cell;
3=e-mail; 5=work, etc.

When I combine these tables throught the query, it creates a separate line
for each phone type along with all of their other info, i.e. birthdate, etc.
Because of this the report also has separate line for each phone type along
eith the other personal data.

What am I doing wrong?

Thanx

 

[Guest]

You need to normalize your database structure.
TblPersonnel --
PeopleID - Autonumber - PK
LName - text
FName - text
MI - text
Suffix - text
Sex - text - Validate "M" Or "F"
DOB - DateTime
Addr1 - text
Addr2 - text
City - text
State - Text
ZIP - text
Plus4 - text

TblContact --
ContatID - Autonumber - PK
PeopleID - number - integer- FK
Type - text -- Phone, FAX, Pager, Cell, E-mail
Contact - text - phone number, pager, or e-mail address.
RMKS - text

Set relations one-to-many from [TblPersonnel].[PeopleID] to
[TblContact].[PeopleID] with Enforce Referential Integrity and Cascade Update
Related Fields.

Use a form for people data entry with subform for contact information.

 

[Guest]

The Cascade Update Related Fields option is grayed out and will not let me
select it.

You need to normalize your database structure.
TblPersonnel --
PeopleID - Autonumber - PK
LName - text
FName - text
MI - text
Suffix - text
Sex - text - Validate "M" Or "F"
DOB - DateTime
Addr1 - text
Addr2 - text
City - text
State - Text
ZIP - text
Plus4 - text

TblContact --
ContatID - Autonumber - PK
PeopleID - number - integer- FK
Type - text -- Phone, FAX, Pager, Cell, E-mail
Contact - text - phone number, pager, or e-mail address.
RMKS - text

Set relations one-to-many from [TblPersonnel].[PeopleID] to
[TblContact].[PeopleID] with Enforce Referential Integrity and Cascade Update
Related Fields.

Use a form for people data entry with subform for contact information.

I have three tables that I join with a make table query. One of the tables
contains data for different types of "phone numbers", i.e. e-mail, cell,
work. The table is set up with very few fields. So one person might have
several lines in that table if they have each type of phone. So, 2=cell;
3=e-mail; 5=work, etc.

When I combine these tables throught the query, it creates a separate line
for each phone type along with all of their other info, i.e. birthdate, etc.
Because of this the report also has separate line for each phone type along
eith the other personal data.

What am I doing wrong?

Thanx

 

[Guest]

Did you designate a primary key by selecting the table field in table design
view and clicking on the icon that looks like a key?

The Cascade Update Related Fields option is grayed out and will not let me
select it.

You need to normalize your database structure.
TblPersonnel --
PeopleID - Autonumber - PK
LName - text
FName - text
MI - text
Suffix - text
Sex - text - Validate "M" Or "F"
DOB - DateTime
Addr1 - text
Addr2 - text
City - text
State - Text
ZIP - text
Plus4 - text

TblContact --
ContatID - Autonumber - PK
PeopleID - number - integer- FK
Type - text -- Phone, FAX, Pager, Cell, E-mail
Contact - text - phone number, pager, or e-mail address.
RMKS - text

Set relations one-to-many from [TblPersonnel].[PeopleID] to
[TblContact].[PeopleID] with Enforce Referential Integrity and Cascade Update
Related Fields.

Use a form for people data entry with subform for contact information.

I have three tables that I join with a make table query. One of the tables
contains data for different types of "phone numbers", i.e. e-mail, cell,
work. The table is set up with very few fields. So one person might have
several lines in that table if they have each type of phone. So, 2=cell;
3=e-mail; 5=work, etc.

When I combine these tables throught the query, it creates a separate line
for each phone type along with all of their other info, i.e. birthdate, etc.
Because of this the report also has separate line for each phone type along
eith the other personal data.

What am I doing wrong?

Thanx

 

[Guest]

Actually the phone number table has 2 keys. And when I try to delete one of
them, I get an error message "MOA can't save property changes for linked
tables."

Did you designate a primary key by selecting the table field in table design
view and clicking on the icon that looks like a key?

The Cascade Update Related Fields option is grayed out and will not let me
select it.

You need to normalize your database structure.
TblPersonnel --
PeopleID - Autonumber - PK
LName - text
FName - text
MI - text
Suffix - text
Sex - text - Validate "M" Or "F"
DOB - DateTime
Addr1 - text
Addr2 - text
City - text
State - Text
ZIP - text
Plus4 - text

TblContact --
ContatID - Autonumber - PK
PeopleID - number - integer- FK
Type - text -- Phone, FAX, Pager, Cell, E-mail
Contact - text - phone number, pager, or e-mail address.
RMKS - text

Set relations one-to-many from [TblPersonnel].[PeopleID] to
[TblContact].[PeopleID] with Enforce Referential Integrity and Cascade Update
Related Fields.

Use a form for people data entry with subform for contact information.

:

I have three tables that I join with a make table query. One of the tables
contains data for different types of "phone numbers", i.e. e-mail, cell,
work. The table is set up with very few fields. So one person might have
several lines in that table if they have each type of phone. So, 2=cell;
3=e-mail; 5=work, etc.

When I combine these tables throught the query, it creates a separate line
for each phone type along with all of their other info, i.e. birthdate, etc.
Because of this the report also has separate line for each phone type along
eith the other personal data.

What am I doing wrong?

Thanx

 

[Guest]

A table can not have two PRIMARY keys. It can have a compound key that
consist of two fields.
The TblPersonnel should have PeopleID as the primay key so as to link with
TblContact field PeopleID which is call a foreign key.

Actually the phone number table has 2 keys. And when I try to delete one of
them, I get an error message "MOA can't save property changes for linked
tables."

Did you designate a primary key by selecting the table field in table design
view and clicking on the icon that looks like a key?

The Cascade Update Related Fields option is grayed out and will not let me
select it.

:

You need to normalize your database structure.
TblPersonnel --
PeopleID - Autonumber - PK
LName - text
FName - text
MI - text
Suffix - text
Sex - text - Validate "M" Or "F"
DOB - DateTime
Addr1 - text
Addr2 - text
City - text
State - Text
ZIP - text
Plus4 - text

TblContact --
ContatID - Autonumber - PK
PeopleID - number - integer- FK
Type - text -- Phone, FAX, Pager, Cell, E-mail
Contact - text - phone number, pager, or e-mail address.
RMKS - text

Set relations one-to-many from [TblPersonnel].[PeopleID] to
[TblContact].[PeopleID] with Enforce Referential Integrity and Cascade Update
Related Fields.

Use a form for people data entry with subform for contact information.

:

I have three tables that I join with a make table query. One of the tables
contains data for different types of "phone numbers", i.e. e-mail, cell,
work. The table is set up with very few fields. So one person might have
several lines in that table if they have each type of phone. So, 2=cell;
3=e-mail; 5=work, etc.

When I combine these tables throught the query, it creates a separate line
for each phone type along with all of their other info, i.e. birthdate, etc.
Because of this the report also has separate line for each phone type along
eith the other personal data.

What am I doing wrong?

Thanx