473,326 Members | 2,114 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

Normalizing with an Autonumber as a PK

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
Nov 12 '05 #1
3 1667
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

Nov 12 '05 #2
"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)
);
---------------------------


Nov 12 '05 #3
"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

Nov 12 '05 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: Evan Escently | last post by:
Hi, I've laid out a _very_ simple database that tracks my artwork the table 'works' looks like: +---------+----------+------------+------------+-------------+ | work_id | title | media ...
33
by: Lee C. | last post by:
I'm finding this to be extremely difficult to set up. I understand that Access won't manage the primary key and the cascade updates for a table. Fine. I tried changing the PK type to number and...
3
by: Megan | last post by:
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...
35
by: Traci | last post by:
If I have a table with an autonumber primary key and 100 records and I delete the last 50 records, the next record added would have a primary key of 101. Is there any way to have the primary key...
26
by: jimfortune | last post by:
Sometimes I use Autonumber fields for ID fields. Furthermore, sometimes I use those same fields in orderdetail type tables. So it's important in that case that once an autonumber key value is...
8
by: Richard Hollenbeck | last post by:
I have a recipe database that I've been building but I haven't yet put any of the ingredients in because of a little problem of normalization. If I build a table of ingredients, all the recipes...
11
by: Alan Mailer | last post by:
A project I'm working on is going to use VB6 as a front end. The back end is going to be pre-existing MS Access 2002 database tables which already have records in them *but do not have any...
1
by: Neekos | last post by:
Hey guys, So after some of you (FishVal, Nico, NeoPa) pointed out previously that my tables are not normalized, i've decided to go back to the drawing board, but im not really sure where to start....
6
by: ashes | last post by:
Hi, I am creating an ecommerce website using Microsoft Visual Studio, VB.Net and MS Access 2003. I am new to VB.Net When someone wants to register on the website, they fill out a form and the...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.