Normalizing with an Autonumber as a PK

[Megan]

hello everybody-

i'm normalizing a database i inherited. i'm breaking up a huge table
named case into several smaller tables. i am creating several many to
many relationships between the new case table and the other newly
created tables.

however, i have run into a problem when trying to create a many to
many relationship between 2 of my tables.

i have a table named case and a table named outcome. there is a many
to many relationship between these 2 tables. the bridge table is named
caseoutcome.

the primary key for the table case, caseid, is an autonumber.
the primary key for the table outcome, outcomeid, is a number.
i created a composite primary key for my bridge table, caseoutcome,
from the 2 tables, case and outcome. so my primary key for the table,
caseoutcome, is caseid and outcomeid.

when i'm in the relationships window, i can create a one to many
relationship between my table, outcome, and my bridge table,
caseoutcome.

however, i'm not allowed to create a one to many relationship between
my table, case, and my bridge table, caseoutcome, because the primary
key, caseid, in both tables is an autonumber.

what should i do? should i just make the caseid part of the composite
primary key for my bridge table, caseoutcome, a number?

thanks for taking the time and effort to read my post.

megan

p.s. i bought a few ms access books to read. none have good examples
of many to many relationships and how to implement them. are there any
good web sites with examples?

p.s.s. how about creating a search form to search through all of the
cases and retrieve the record selected. but that's another post.

thanks again

[Douglas J. Steele]

The caseid field in the caseoutcome table should be a Long Integer, not an
Autonumber. You're going to be storing the value of the Autonumber field
caseid from the case table in it, not generating its value.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(No private e-mails, please)

"Megan" <me**************@hotmail.com> wrote in message
news:5c**************************@posting.google.c om...

hello everybody-

i'm normalizing a database i inherited. i'm breaking up a huge table
named case into several smaller tables. i am creating several many to
many relationships between the new case table and the other newly
created tables.

however, i have run into a problem when trying to create a many to
many relationship between 2 of my tables.

i have a table named case and a table named outcome. there is a many
to many relationship between these 2 tables. the bridge table is named
caseoutcome.

the primary key for the table case, caseid, is an autonumber.
the primary key for the table outcome, outcomeid, is a number.
i created a composite primary key for my bridge table, caseoutcome,
from the 2 tables, case and outcome. so my primary key for the table,
caseoutcome, is caseid and outcomeid.

when i'm in the relationships window, i can create a one to many
relationship between my table, outcome, and my bridge table,
caseoutcome.

however, i'm not allowed to create a one to many relationship between
my table, case, and my bridge table, caseoutcome, because the primary
key, caseid, in both tables is an autonumber.

what should i do? should i just make the caseid part of the composite
primary key for my bridge table, caseoutcome, a number?

thanks for taking the time and effort to read my post.

megan

p.s. i bought a few ms access books to read. none have good examples
of many to many relationships and how to implement them. are there any
good web sites with examples?

p.s.s. how about creating a search form to search through all of the
cases and retrieve the record selected. but that's another post.

thanks again

[John Winterbottom]

"Megan" <me**************@hotmail.com> wrote in message
news:5c**************************@posting.google.c om...

hello everybody-

i'm normalizing a database i inherited. i'm breaking up a huge table
named case into several smaller tables. i am creating several many to
many relationships between the new case table and the other newly
created tables.

however, i have run into a problem when trying to create a many to
many relationship between 2 of my tables.

i have a table named case and a table named outcome. there is a many
to many relationship between these 2 tables. the bridge table is named
caseoutcome.

the primary key for the table case, caseid, is an autonumber.
the primary key for the table outcome, outcomeid, is a number.
i created a composite primary key for my bridge table, caseoutcome,
from the 2 tables, case and outcome. so my primary key for the table,
caseoutcome, is caseid and outcomeid.

when i'm in the relationships window, i can create a one to many
relationship between my table, outcome, and my bridge table,
caseoutcome.

however, i'm not allowed to create a one to many relationship between
my table, case, and my bridge table, caseoutcome, because the primary
key, caseid, in both tables is an autonumber.

I don't know anything about the "relationships window", maybe someone else
will be able to help you there, but if you want to do this in SQL: the
following should work
(you have to run them as separate statements).

---------------------------
create table cases
(
caseid counter not null
constraint PK_cases
primary key
);

create table outcomes
(
outcomeid counter not null
constraint PK_outcomes
primary key
);

create table caseOutcomes
(
caseid int not null
constraint FK_caseOutcomes_caseid
references cases(caseid),
outcomeid int not null
constraint FK_caseOutcomes_outcomeid
references outcomes(outcomeid),
constraint PK_caseOutcomes
primary key (caseid, outcomeid)
);
---------------------------

[Larry Linson]

"Composite" key? I use "multi-field" keys in my junction tables... a foreign
key to each of the tables with the pertinent data. The tables with the
pertinent data can have either natural or surrogate (AutoNumber) key fields.
I don't have any trouble creating a one-to-many from each of the data tables
to the junction table.

In fact, I just did one to refresh my memory:

tblCase, PK: CaseID (an AutoNumber)
tblCaseOutcomes, PK: OutcomeID (an Autonumber)
tblCaseAndOutcomeJunction, PK: Case (Long Integer) and Outcome (Long
Integer), each foreign key to CaseID and OutcomeID respectively.

In the Relationships Window, I added the three tables. I clicked CaseID in
tblCase and dragged to Case in tblCaseAndOutcomeJunction. I clicked
OutcomeID in tblCaseOutcomes and dragged to CaseOutcome in
tblCaseAndOutcomeJunction. Whether I leave the joins as the default
equi-join or change them to Left Join (that is, all records from the data
tables and only those records from the junction table that match), I still
get a "One to Many" on the relationship.

Larry Linson
Microsoft Access MVP

"Megan" <me**************@hotmail.com> wrote in message
news:5c**************************@posting.google.c om...

hello everybody-

i'm normalizing a database i inherited. i'm breaking up a huge table
named case into several smaller tables. i am creating several many to
many relationships between the new case table and the other newly
created tables.

however, i have run into a problem when trying to create a many to
many relationship between 2 of my tables.

i have a table named case and a table named outcome. there is a many
to many relationship between these 2 tables. the bridge table is named
caseoutcome.

the primary key for the table case, caseid, is an autonumber.
the primary key for the table outcome, outcomeid, is a number.
i created a composite primary key for my bridge table, caseoutcome,
from the 2 tables, case and outcome. so my primary key for the table,
caseoutcome, is caseid and outcomeid.

when i'm in the relationships window, i can create a one to many
relationship between my table, outcome, and my bridge table,
caseoutcome.

however, i'm not allowed to create a one to many relationship between
my table, case, and my bridge table, caseoutcome, because the primary
key, caseid, in both tables is an autonumber.

what should i do? should i just make the caseid part of the composite
primary key for my bridge table, caseoutcome, a number?

thanks for taking the time and effort to read my post.

megan

p.s. i bought a few ms access books to read. none have good examples
of many to many relationships and how to implement them. are there any
good web sites with examples?

p.s.s. how about creating a search form to search through all of the
cases and retrieve the record selected. but that's another post.

thanks again