Using two columns as a primary key

[Paul Hunter]

I am new to databases and thus to Access. I have a situation where I am
trying to figure out how to key some tables I am working with. Consider that
I have a database of my own records which are invoices I work on. These
invoices are from four companies with their own invoice numbers. So, I
cannot key by invoice number because there is a likelihood of invoice
numbers duplicated by different companies. The records in this table of
joined invoices are referenced in other tables, and I am not fond of using a
created index to reference each record.

Is it possible in Access (and SQL) to set the primary key to be a composite
of two columns? If so, how do I create a relationship between that table and
other tables? Say I have a separate table that keeps track of whether I've
been paid for processing each invoice. I would like to use the company name
and the company invoice number to create the relationship instead of my own
fabricated index. However, I'm not sure whether that can be done, never mind
the question of whether it is a good idea.

Thanks for the help, folks!

Paul

[Allen Browne]

To create a 2-field primary key, open your table in design view.
Select both fields (the "record selector" at left of field names.)
Click the Key icon on the toolbar.

If the 2 fields are not contiguous, you can also use the Indexes dialog to
create the multi-field index:
Open the Indexes dialog (View menu.)
In the first column, enter a name for the index (e.g. PrimaryKey)
In the 2nd column, choose the first field of the index.
In the lower pane, set Primary to Yes.
On the next *row* of the dialog, leave the Index Name blank, and choose the
2nd column.

To create relationships to other tables, drag the first field from this
table onto the matching field of the related table. When Access opens the
Create Relation dialog, you can match the 2nd field of the relation on the
2nd row of the dialog.

A 2-field index sometimes makes good sense, and the situation you describe
sounds like a good candidate. But if you have lots of other related tables,
which themselves have further relations, it is also possible to add an
AutoNumber as the primary key if you wish. You can still mark the Company
and InvoiceNumber fields as required, and create a Unique index on the pair
through the Indexes dialog described above. Just set Primary to No, and
Unique to Yes in the lower pane of the Indexes dialog.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Paul Hunter" <ba*********@comcast.net> wrote in message
news:tf********************@comcast.com...

I am new to databases and thus to Access. I have a situation where I am
trying to figure out how to key some tables I am working with. Consider
that
I have a database of my own records which are invoices I work on. These
invoices are from four companies with their own invoice numbers. So, I
cannot key by invoice number because there is a likelihood of invoice
numbers duplicated by different companies. The records in this table of
joined invoices are referenced in other tables, and I am not fond of using
a
created index to reference each record.

Is it possible in Access (and SQL) to set the primary key to be a
composite
of two columns? If so, how do I create a relationship between that table
and
other tables? Say I have a separate table that keeps track of whether I've
been paid for processing each invoice. I would like to use the company
name
and the company invoice number to create the relationship instead of my
own
fabricated index. However, I'm not sure whether that can be done, never
mind
the question of whether it is a good idea.

Thanks for the help, folks!

Paul

[David W. Fenton]

"Paul Hunter" <ba*********@comcast.net> wrote in
news:tf********************@comcast.com:

Consider that
I have a database of my own records which are invoices I work on.
These invoices are from four companies with their own invoice
numbers. So, I cannot key by invoice number because there is a
likelihood of invoice numbers duplicated by different companies.
The records in this table of joined invoices are referenced in
other tables, and I am not fond of using a created index to
reference each record.

I would never do this with a 2-column PK, even though it's the
theoretically appropriate way to do it.

Instead, I'd use my own routines to generate the invoice number and
include the company code as part of the number (either as text or as
numbers). You're going to have to write this kind of code anyway,
unless you're using an Autonumber for the second column of your
2-column key, and if you're doing that, I can't see any reason to
use a 2-column key in the first place.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[Bob Quintal]

"David W. Fenton" <XX*******@dfenton.com.invalid> wrote in
news:Xn**********************************@127.0.0. 1:

"Paul Hunter" <ba*********@comcast.net> wrote in
news:tf********************@comcast.com:

Consider that
I have a database of my own records which are invoices I work
on. These invoices are from four companies with their own
invoice numbers. So, I cannot key by invoice number because
there is a likelihood of invoice numbers duplicated by
different companies. The records in this table of joined
invoices are referenced in other tables, and I am not fond of
using a created index to reference each record.

I would never do this with a 2-column PK, even though it's the
theoretically appropriate way to do it.

Instead, I'd use my own routines to generate the invoice
number and include the company code as part of the number
(either as text or as numbers). You're going to have to write
this kind of code anyway, unless you're using an Autonumber
for the second column of your 2-column key, and if you're
doing that, I can't see any reason to use a 2-column key in
the first place.

David, the OP is not generating the invoice numbers. He's
entering invoice numbers he receives. The invoice number is a
foreign key to the system which created it.
--
Bob Quintal

PA is y I've altered my email address.

[David W. Fenton]

Bob Quintal <rq******@sympatico.ca> wrote in
news:Xn**********************@207.35.177.135:

"David W. Fenton" <XX*******@dfenton.com.invalid> wrote in
news:Xn**********************************@127.0.0. 1:

"Paul Hunter" <ba*********@comcast.net> wrote in
news:tf********************@comcast.com:

Consider that
I have a database of my own records which are invoices I work
on. These invoices are from four companies with their own
invoice numbers. So, I cannot key by invoice number because
there is a likelihood of invoice numbers duplicated by
different companies. The records in this table of joined
invoices are referenced in other tables, and I am not fond of
using a created index to reference each record.

I would never do this with a 2-column PK, even though it's the
theoretically appropriate way to do it.

Instead, I'd use my own routines to generate the invoice
number and include the company code as part of the number
(either as text or as numbers). You're going to have to write
this kind of code anyway, unless you're using an Autonumber
for the second column of your 2-column key, and if you're
doing that, I can't see any reason to use a 2-column key in
the first place.

David, the OP is not generating the invoice numbers. He's
entering invoice numbers he receives. The invoice number is a
foreign key to the system which created it.

OK, I missed that.

If there are no child records for these entries, then I *might* go
with the 2-column PK (though I have the reservations noted below).

If there are child records, though, I'd definitely use a surrogate
key, then.

I would never turn over my PK generation to any outside source,
unless I knew that source was without question creating unique
numbers. As this cannot be guaranteed, I would never introduce that
dependency, as over time (like US Social Security Number) it's
likely to break.

That does not mean I wouldn't put a 2-column unique index on the two
fields, but I would not use them as the PK for my data table.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[Paul Hunter]

I created two primary keys for the tables (my invoice table and the
invoice-paid table). The invoice table is unchanged in its datasheet view:
selecting the + next to the company name on the left shows the values in the
invoice-paid table. However, that no longer exists in the invoice-paid
table. In the Relationships tool, two relationships are graphed between the
tables, both showing a one-to-many relationship. When I select the
relationships, the Edit Relationships dialog shows both relationships and
lists the relationship as one-to-one. Why doesn't Access recognize the
one-to-one relationship in anything other than the Edit Relationship dialog?

Thanks for your help, folks.
Paul

"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
news:44**********************@per-qv1-newsreader-01.iinet.net.au...

To create a 2-field primary key, open your table in design view.
Select both fields (the "record selector" at left of field names.)
Click the Key icon on the toolbar.

If the 2 fields are not contiguous, you can also use the Indexes dialog to
create the multi-field index:
Open the Indexes dialog (View menu.)
In the first column, enter a name for the index (e.g. PrimaryKey)
In the 2nd column, choose the first field of the index.
In the lower pane, set Primary to Yes.
On the next *row* of the dialog, leave the Index Name blank, and choose
the 2nd column.

To create relationships to other tables, drag the first field from this
table onto the matching field of the related table. When Access opens the
Create Relation dialog, you can match the 2nd field of the relation on the
2nd row of the dialog.

A 2-field index sometimes makes good sense, and the situation you describe
sounds like a good candidate. But if you have lots of other related
tables, which themselves have further relations, it is also possible to
add an AutoNumber as the primary key if you wish. You can still mark the
Company and InvoiceNumber fields as required, and create a Unique index on
the pair through the Indexes dialog described above. Just set Primary to
No, and Unique to Yes in the lower pane of the Indexes dialog.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Paul Hunter" <ba*********@comcast.net> wrote in message
news:tf********************@comcast.com...

I am new to databases and thus to Access. I have a situation where I am
trying to figure out how to key some tables I am working with. Consider
that
I have a database of my own records which are invoices I work on. These
invoices are from four companies with their own invoice numbers. So, I
cannot key by invoice number because there is a likelihood of invoice
numbers duplicated by different companies. The records in this table of
joined invoices are referenced in other tables, and I am not fond of
using a
created index to reference each record.

Is it possible in Access (and SQL) to set the primary key to be a
composite
of two columns? If so, how do I create a relationship between that table
and
other tables? Say I have a separate table that keeps track of whether
I've
been paid for processing each invoice. I would like to use the company
name
and the company invoice number to create the relationship instead of my
own
fabricated index. However, I'm not sure whether that can be done, never
mind
the question of whether it is a good idea.

Thanks for the help, folks!

Paul

[Allen Browne]

The relationship is one-to-one because you linked the 2 primary keys. A
primary key field must be unique (by definition), hence there can be no more
than 1 value in each table (hence 1-1.)

Even a one-to-one relationship is directional. One table is the primary one
(probably Invoice in your case). There may or may not be a *related* record
in the InvoicePaid table. The converse cannot occur: you cannot have a
record in the InvoicePaid table that has no matching entry in the Invoice
table.

You can still set up the interface to show the other direction as well.
Open the InvoicePaid table in design view.
Open the Properties dialog (View menu.)
Set the Subdatasheet Name property to the name of your other table.
Now you can click the + beside an InvoicePaid record to see the invoice
data.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Paul Hunter" <ba*********@comcast.net> wrote in message
news:PY******************************@comcast.com. ..

I created two primary keys for the tables (my invoice table and the
invoice-paid table). The invoice table is unchanged in its datasheet view:
selecting the + next to the company name on the left shows the values in
the invoice-paid table. However, that no longer exists in the invoice-paid
table. In the Relationships tool, two relationships are graphed between the
tables, both showing a one-to-many relationship. When I select the
relationships, the Edit Relationships dialog shows both relationships and
lists the relationship as one-to-one. Why doesn't Access recognize the
one-to-one relationship in anything other than the Edit Relationship
dialog?

Thanks for your help, folks.
Paul

"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
news:44**********************@per-qv1-newsreader-01.iinet.net.au...

To create a 2-field primary key, open your table in design view.
Select both fields (the "record selector" at left of field names.)
Click the Key icon on the toolbar.

If the 2 fields are not contiguous, you can also use the Indexes dialog
to create the multi-field index:
Open the Indexes dialog (View menu.)
In the first column, enter a name for the index (e.g. PrimaryKey)
In the 2nd column, choose the first field of the index.
In the lower pane, set Primary to Yes.
On the next *row* of the dialog, leave the Index Name blank, and choose
the 2nd column.

To create relationships to other tables, drag the first field from this
table onto the matching field of the related table. When Access opens the
Create Relation dialog, you can match the 2nd field of the relation on
the 2nd row of the dialog.

A 2-field index sometimes makes good sense, and the situation you
describe sounds like a good candidate. But if you have lots of other
related tables, which themselves have further relations, it is also
possible to add an AutoNumber as the primary key if you wish. You can
still mark the Company and InvoiceNumber fields as required, and create a
Unique index on the pair through the Indexes dialog described above. Just
set Primary to No, and Unique to Yes in the lower pane of the Indexes
dialog.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Paul Hunter" <ba*********@comcast.net> wrote in message
news:tf********************@comcast.com...

I am new to databases and thus to Access. I have a situation where I am
trying to figure out how to key some tables I am working with. Consider
that
I have a database of my own records which are invoices I work on. These
invoices are from four companies with their own invoice numbers. So, I
cannot key by invoice number because there is a likelihood of invoice
numbers duplicated by different companies. The records in this table of
joined invoices are referenced in other tables, and I am not fond of
using a
created index to reference each record.

Is it possible in Access (and SQL) to set the primary key to be a
composite
of two columns? If so, how do I create a relationship between that table
and
other tables? Say I have a separate table that keeps track of whether
I've
been paid for processing each invoice. I would like to use the company
name
and the company invoice number to create the relationship instead of my
own
fabricated index. However, I'm not sure whether that can be done, never
mind
the question of whether it is a good idea.

[Fred Zuckerman]

"David W. Fenton" <XX*******@dfenton.com.invalid> wrote in message
news:Xn*********************************@127.0.0.1 ...

Bob Quintal <rq******@sympatico.ca> wrote in
news:Xn**********************@207.35.177.135:

"David W. Fenton" <XX*******@dfenton.com.invalid> wrote in
news:Xn**********************************@127.0.0. 1:

"Paul Hunter" <ba*********@comcast.net> wrote in
news:tf********************@comcast.com:

Consider that
I have a database of my own records which are invoices I work
on. These invoices are from four companies with their own
invoice numbers. So, I cannot key by invoice number because
there is a likelihood of invoice numbers duplicated by
different companies. The records in this table of joined
invoices are referenced in other tables, and I am not fond of
using a created index to reference each record.

I would never do this with a 2-column PK, even though it's the
theoretically appropriate way to do it.

I would never turn over my PK generation to any outside source,
unless I knew that source was without question creating unique
numbers. As this cannot be guaranteed, I would never introduce that
dependency, as over time (like US Social Security Number) it's
likely to break.

That does not mean I wouldn't put a 2-column unique index on the two
fields, but I would not use them as the PK for my data table.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

I've come across another related point many times lately. If you use an
outside source for you PK knowing it can be trusted to be unique, you still
run the risk of somebody making a typo when entering the data. If additional
records are also created in related tables, then when the typo is dicovered,
all of the related PK's in the other tables must be found and changed. It's
a pain. I'm now using Autonumber a lot more than I used to...
Fred Zuckerman

[David W. Fenton]

"Fred Zuckerman" <Zu********@sbcglobal.net> wrote in
news:fL******************@newssvr14.news.prodigy.c om:

I've come across another related point many times lately. If you
use an outside source for you PK knowing it can be trusted to be
unique, you still run the risk of somebody making a typo when
entering the data. If additional records are also created in
related tables, then when the typo is dicovered, all of the
related PK's in the other tables must be found and changed. It's
a pain. I'm now using Autonumber a lot more than I used to...

Well, except for the fact that it's not true, you've got a point.

If you set CASCADE UPDATES, it will update the child replicas.

But I don't believe in using as a PK data that can be updated as a
user. That data is way to important to allow users to be mucking
around with it. That's why I'm pretty much philosophically opposed
to natural keys in general.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/