my access many to many design will not let me add records

[Shell]

I am making a horse show database. Setting up one to many relationships
between the tables. The problem is that when I try to add records in data
sheet view I recieve an error that says cannot change records because I need
a related field in the mail list table. I have tried defining and adding new
fields to the tables to solve this and it still gives me the error. Oh I
have tried the join types too with no luck. Any help would be appreciated.
Thanks
Shell

 

[John Vinson]

I am making a horse show database. Setting up one to many relationships
between the tables. The problem is that when I try to add records in data
sheet view I recieve an error that says cannot change records because I need
a related field in the mail list table. I have tried defining and adding new
fields to the tables to solve this and it still gives me the error. Oh I
have tried the join types too with no luck. Any help would be appreciated.
Thanks
Shell

Don't use table or query datasheets for updating. That's not what
they're for!

Instead, use a Form (based on your Mail List table) with a Subform
(based on the many to many resolver table). If your mainform is based
on the other "one" side table, you may need to have a separate form to
enter new mailing list entries; see the Orders form in the Northwind
sample database for an example of how to do so, using the Not In List
event of a combo box on the subform.

John W. Vinson[MVP]

 

[Shell]

Thanks I will make a form to see if it will work. Shell

 

[Shell]

I made two forms. I can enter in both forms. My related fields are not
populated. I thought that the inner join was default and the related same
fields in all of the tables would contain data. I went to the options and
under tables tab the output all fields was not checked so I checked that box.
That did not work though. Is it something with my entity sub types? I do have
object depencancy among my tables. I'm getting confused.

 

[John Vinson]

I made two forms. I can enter in both forms. My related fields are not
populated. I thought that the inner join was default and the related same
fields in all of the tables would contain data. I went to the options and
under tables tab the output all fields was not checked so I checked that box.
That did not work though. Is it something with my entity sub types? I do have
object depencancy among my tables. I'm getting confused.

That's not how relationships work.

A Relationship between two tables PREVENTS you from entering data into
the "child" table unless there is a matching record in the "parent"
table. It does not, and should not, automagically create a record in
the child table.

Could you describe your Tables (tablename, primary key, relationships
indicating what field is joined to which), and your Forms (what is
each Form's Recordsource property).


John W. Vinson[MVP]

 

[Shell]

That's not how relationships work.

A Relationship between two tables PREVENTS you from entering data into
the "child" table unless there is a matching record in the "parent"
table. It does not, and should not, automagically create a record in
the child table.

Could you describe your Tables (tablename, primary key, relationships
indicating what field is joined to which), and your Forms (what is
each Form's Recordsource property).


John W. Vinson[MVP]
MailList Table

Name (one side to the many info table) Primary Key
Street
City
Zip
State
Email
I made a form with the wizard to enter info.

Info_Contestant Table
Class number (PrimaryKey (related to the class tables (the one side))
Name
Division Lookup field with Junior or Senior values
HighPoint#
C1(These are all Yes/No Depending if they entered the class)
C2
C3
C4
C5 (though all 25 classes)
Total Classes entered
I have a form that I made with the wizard to enter records.


Class Tables I will have 25 tables with the naming conventions Class_1
Class_2 and so on. The will have different fields depending on the class.
The two fields that will be important are TPC (total points class) and Place.

Class_1 table
ClassNumber (foriegn back to ClassNumber in the Info table (many side))
TPC1 (Primary the one side to the High Point Table)
PlaceC1

High_Point Table

TPC1 (foriegn keys for the Class tables )
TPC2
continue for all 25 classes and class tables
TotalPoints
ClassNumber

The record source property would be what the wizard uses as a default. John
i have been looking at your other posts and I cant find anyone who is as
confused as I am! Sorry. Thank you for your time. I currently do not have a
primary Key in the HighPoint table. I had played with highPoint autoNumber
fields to use as the primary there. Should I put that back in?

Thanks
Shell

 

[John Vinson]

On Thu, 20 Apr 2006 04:44:02 -0700, Shell

Comments inline.

Name (one side to the many info table) Primary Key

Name is NOT a good primary key. A PK should have three
characteristics: it should be unique, stable, and short. People's
names fail on ALL THREE counts - I know three people here in little
Parma all named Fred Brown; and people do change their names on
marriage or for other reasons. I'd suggest using an autonumber
PersonID instead.

Street
City
Zip
State
Email
I made a form with the wizard to enter info.

Info_Contestant Table
Class number (PrimaryKey (related to the class tables (the one side))
Name
Division Lookup field with Junior or Senior values
HighPoint#
C1(These are all Yes/No Depending if they entered the class)
C2
C3
C4
C5 (though all 25 classes)
Total Classes entered
I have a form that I made with the wizard to enter records.

This table is not properly normalized either. If you have a Many
(people) to Many (classes) relationship, you should NOT have one
*field* per class - that's spreadsheet logic! Instead you should have
one *record* per class. In addition, you're missing the point of the
primary key. If the Class Number here is the primary key then one and
only one person can enter that class! Not what you want at all.

Instead try:

Classes
ClassNo (e.g. 1, 2, 3)
Description (whatever is meant by C1, C2 etc.)

Signups
PersonID <link to the People table, who's in the class>
ClassNo <link to the Classes table, what class they're in>
Points <I'm guessing that an entrant earns points in each class?>

I do not know what HighPoint# might be, but if it can be calculated
(as the high score for all classes entered) then calculate it on the
fly in a Totals query; similarly, Total Classes Entered should not be
stored in ANY table field, it can be calculated as needed.

Class Tables I will have 25 tables with the naming conventions Class_1
Class_2 and so on. The will have different fields depending on the class.
The two fields that will be important are TPC (total points class) and Place.

This is similarly incorrect. Storing data in a tablename is NEVER a
good idea, and - in this case - is the source of your problems
updating.

Class_1 table
ClassNumber (foriegn back to ClassNumber in the Info table (many side))
TPC1 (Primary the one side to the High Point Table)
PlaceC1

High_Point Table

TPC1 (foriegn keys for the Class tables )
TPC2
continue for all 25 classes and class tables
TotalPoints
ClassNumber

This table should not exist. The high points can and should be
calculated on the fly.

The record source property would be what the wizard uses as a default. John
i have been looking at your other posts and I cant find anyone who is as
confused as I am! Sorry. Thank you for your time. I currently do not have a
primary Key in the HighPoint table. I had played with highPoint autoNumber
fields to use as the primary there. Should I put that back in?

No. You should normalize your entire table structure from scratch!

John W. Vinson[MVP]

 

[Shell]

On Thu, 20 Apr 2006 04:44:02 -0700, Shell

Comments inline.


Name is NOT a good primary key. A PK should have three
characteristics: it should be unique, stable, and short. People's
names fail on ALL THREE counts - I know three people here in little
Parma all named Fred Brown; and people do change their names on
marriage or for other reasons. I'd suggest using an autonumber
PersonID instead.


This table is not properly normalized either. If you have a Many
(people) to Many (classes) relationship, you should NOT have one
*field* per class - that's spreadsheet logic! Instead you should have
one *record* per class. In addition, you're missing the point of the
primary key. If the Class Number here is the primary key then one and
only one person can enter that class! Not what you want at all.

Instead try:

Classes
ClassNo (e.g. 1, 2, 3)
Description (whatever is meant by C1, C2 etc.)

Signups
PersonID <link to the People table, who's in the class>
ClassNo <link to the Classes table, what class they're in>
Points <I'm guessing that an entrant earns points in each class?>

I do not know what HighPoint# might be, but if it can be calculated
(as the high score for all classes entered) then calculate it on the
fly in a Totals query; similarly, Total Classes Entered should not be
stored in ANY table field, it can be calculated as needed.


This is similarly incorrect. Storing data in a tablename is NEVER a
good idea, and - in this case - is the source of your problems
updating.


This table should not exist. The high points can and should be
calculated on the fly.


No. You should normalize your entire table structure from scratch!

John W. Vinson[MVP]
Thank you John I will normalize from scratch. Your right I was trying to structure like a spread sheet and when I used Name I thought It was a good idea because many of our contestants our from the same family. So you are saying calculate onthe fly will make the database better. Thanks again.

Shelly