Tags: access, created, custid, customer, database, foreign, key, lots, microsoft, mysql, oracle, primary, project, rows, sql, table, tbl_customer, whichincludes

Access Project - Primary Key / Foreign Key question

On Database » Microsoft Access

6,439 words with 4 Comments; publish: Mon, 31 Dec 2007 00:13:00 GMT; (250105.47, « »)

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.

All Comments

Leave a comment...

  • 4 Comments
    • 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.charlesworth.ms-access.questionfor.info.national-ice-centre.com> wrote in message

      news:4537bfac.0406090358.5edbd9aa.ms-access.questionfor.info.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.

      #1; Mon, 31 Dec 2007 00:14:00 GMT
    • "Anne Nolan" <anolan1952NO_SPAM.ms-access.questionfor.info.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

      #2; Mon, 31 Dec 2007 00:15:00 GMT
    • 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.ms-access.questionfor.info.telusplanet.net> wrote in message

      news:9omhc0tv68lb33u8dmgedsa6l0tabbui9e.ms-access.questionfor.info.4ax.com...

      > "Anne Nolan" <anolan1952NO_SPAM.ms-access.questionfor.info.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

      #3; Mon, 31 Dec 2007 00:17:00 GMT
    • "Anne Nolan" <anolan1952NO_SPAM.ms-access.questionfor.info.AOL.COM> wrote in

      news:2is98nFr1bq2U1.ms-access.questionfor.info.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

      #4; Mon, 31 Dec 2007 00:18:00 GMT