Access Project - Primary Key / Foreign Key question

[Chris]

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.

[Anne Nolan]

Re: Access Project - Primary Key / Foreign Key question

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" <chris.charlesw orth@national-ice-centre.com> wrote in message
news:4537bfac.0 406090358.5edbd 9aa@posting.goo gle.com...[color=blue]
> 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.[/color]

[Tony Toews]

Re: Access Project - Primary Key / Foreign Key question

"Anne Nolan" <anolan1952NO_S PAM@AOL.COM> wrote:
[color=blue]
>The primary key for your Tbl_Address table should be a composite primary key
>(more than one field making up the key).[/color]

Why?
[color=blue]
>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.[/color]

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

[Anne Nolan]

Re: Access Project - Primary Key / Foreign Key question

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" <ttoews@teluspl anet.net> wrote in message
news:9omhc0tv68 lb33u8dmgedsa6l 0tabbui9e@4ax.c om...[color=blue]
> "Anne Nolan" <anolan1952NO_S PAM@AOL.COM> wrote:
>[color=green]
> >The primary key for your Tbl_Address table should be a composite primary[/color][/color]
key[color=blue][color=green]
> >(more than one field making up the key).[/color]
>
> Why?
>[color=green]
> >At a minimum, CustID plus your
> >street address. This gets tricky, though.. what if you have 2 John[/color][/color]
Smiths,[color=blue][color=green]
> >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.[/color]
>
> Yup, but I'd just as soon have a primary autonumber key on all tables.[/color]
Let the user[color=blue]
> 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[/color]

[David W. Fenton]

Re: Access Project - Primary Key / Foreign Key question

"Anne Nolan" <anolan1952NO_S PAM@AOL.COM> wrote in
news:2is98nFr1b q2U1@uni-berlin.de:
[color=blue]
> 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.[/color]

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