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]

Re: Normalizing with an Autonumber as a PK

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" <meganrobertson 22@hotmail.com> wrote in message
news:5c14c12b.0 404241118.3f36e 727@posting.goo gle.com...[color=blue]
> 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[/color]

[John Winterbottom]

Re: Normalizing with an Autonumber as a PK

"Megan" <meganrobertson 22@hotmail.com> wrote in message
news:5c14c12b.0 404241118.3f36e 727@posting.goo gle.com...[color=blue]
> 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.
>[/color]

I don't know anything about the "relationsh ips 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(outcom eid),
constraint PK_caseOutcomes
primary key (caseid, outcomeid)
);
---------------------------

[Larry Linson]

Re: Normalizing with an Autonumber as a PK

"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)
tblCaseAndOutco meJunction, 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 tblCaseAndOutco meJunction. I clicked
OutcomeID in tblCaseOutcomes and dragged to CaseOutcome in
tblCaseAndOutco meJunction. 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" <meganrobertson 22@hotmail.com> wrote in message
news:5c14c12b.0 404241118.3f36e 727@posting.goo gle.com...[color=blue]
> 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[/color]