By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,656 Members | 1,762 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,656 IT Pros & Developers. It's quick & easy.

Access Project - Primary Key / Foreign Key question

P: n/a
I have a created a SQL Database with a table called Tbl_Customer which
includes lots of Rows of customer information.

The primary Key is CustID which is an Identity (Auto Number)

I want to be able to create a new table called Tbl_Address which has
CustID as a foreign Key and I want to be able to add between 1 and 4
addresses (different types such as home, term time etc).

Eventually I want to be able to create a form so you can view the
customer details, and skip through the addresses in a sub form.

My problem is that as soon as I set the relationship as 1 to many, I
can no longer add any addresses; all I can view are the headers.

If I add a different primary key field to the Address table it will
allow me to do it, but I don't know what to set as the other primary
key field, as more than one customer may live at the same address, so
i cant choose the 1st line of address for example.

I was able to do this in Access, but it will not work for me in Access
Project / SQL Server Enterprise Manager.

Any Ideas / Help Appreciated.
Nov 13 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
The primary key for your Tbl_Address table should be a composite primary key
(more than one field making up the key). At a minimum, CustID plus your
street address. This gets tricky, though.. what if you have 2 John Smiths,
at 123 Main St., in 2 different cities? If that's a possibility, you'll
probably want to add City and State to the primary key fields.

CustID will still be a foreign key to the Tbl_Customer table.

I don't know what the user interface is that's preventing you from adding
new addresses, but be sure you are using the CustID column from the
Addresses table, not the Customer table, in the underlying query.

Hope this helps,

Anne

"Chris" <ch****************@national-ice-centre.com> wrote in message
news:45**************************@posting.google.c om...
I have a created a SQL Database with a table called Tbl_Customer which
includes lots of Rows of customer information.

The primary Key is CustID which is an Identity (Auto Number)

I want to be able to create a new table called Tbl_Address which has
CustID as a foreign Key and I want to be able to add between 1 and 4
addresses (different types such as home, term time etc).

Eventually I want to be able to create a form so you can view the
customer details, and skip through the addresses in a sub form.

My problem is that as soon as I set the relationship as 1 to many, I
can no longer add any addresses; all I can view are the headers.

If I add a different primary key field to the Address table it will
allow me to do it, but I don't know what to set as the other primary
key field, as more than one customer may live at the same address, so
i cant choose the 1st line of address for example.

I was able to do this in Access, but it will not work for me in Access
Project / SQL Server Enterprise Manager.

Any Ideas / Help Appreciated.

Nov 13 '05 #2

P: n/a
"Anne Nolan" <an***************@AOL.COM> wrote:
The primary key for your Tbl_Address table should be a composite primary key
(more than one field making up the key).
Why?
At a minimum, CustID plus your
street address. This gets tricky, though.. what if you have 2 John Smiths,
at 123 Main St., in 2 different cities? If that's a possibility, you'll
probably want to add City and State to the primary key fields.


Yup, but I'd just as soon have a primary autonumber key on all tables. Let the user
decide if a duplicate address exists.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Nov 13 '05 #3

P: n/a
Well it's the old "natural key/surrogate key" debate.. even if you choose
the surrogate key route, he'll probably want a unique constraint along the
lines of the PK I suggested. Addresses as part of keys or indexes are
always a bit of a hassle, I agree.

Generally, multi-field PKs are not really a problem unless you start having
child tables referencing these PKs. In those cases I go with the surrogate
PK every time.

Anne

"Tony Toews" <tt****@telusplanet.net> wrote in message
news:9o********************************@4ax.com...
"Anne Nolan" <an***************@AOL.COM> wrote:
The primary key for your Tbl_Address table should be a composite primary key(more than one field making up the key).
Why?
At a minimum, CustID plus your
street address. This gets tricky, though.. what if you have 2 John Smiths,at 123 Main St., in 2 different cities? If that's a possibility, you'll
probably want to add City and State to the primary key fields.


Yup, but I'd just as soon have a primary autonumber key on all tables.

Let the user decide if a duplicate address exists.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

Nov 13 '05 #4

P: n/a
"Anne Nolan" <an***************@AOL.COM> wrote in
news:2i************@uni-berlin.de:
Well it's the old "natural key/surrogate key" debate.. even if
you choose the surrogate key route, he'll probably want a unique
constraint along the lines of the PK I suggested. Addresses as
part of keys or indexes are always a bit of a hassle, I agree.


Do addresses belong in that table?

I don't think so!

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Nov 13 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.