Re: Using two columns as a primary key

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" <baseballfan@xxxxxxxxxxx> wrote in message
news:tf-dnedEWvKXvv3ZRVn-iw@xxxxxxxxxxxxxx
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


.

Relevant Pages

  • Re: Linked table query
    ... Thank you Duane for answering me so promptly and so late. ... the primary Key fields, and all relationships were set up on the back end. ... query based on multiple linked tables with SQL 2K as the back end. ... info about an invoice such as dollar amount, dates, and so forth. ...
    (microsoft.public.access.queries)
  • Re: 2nd try: have default items on every report
    ... view the product (plant) description in the combo box ... As for printing the complete product listing on every invoice, ... to make it as simple as possible for the customer. ... TrackingNumber <primary Key> ...
    (microsoft.public.access.forms)
  • Re: Using two columns as a primary key
    ... record in the InvoicePaid table that has no matching entry in the Invoice ... choose the first field of the index. ... When Access opens the ... possible to add an AutoNumber as the primary key if you wish. ...
    (comp.databases.ms-access)
  • Re: 2nd try: have default items on every report
    ... sort of invoice system, and that TrackingNumber is the invoice ID. ... LineNumber <Primary Key> ... numbers printed on the report as well and have them recorded in Table2. ...
    (microsoft.public.access.forms)
  • RE: Invoice Form Question
    ... OrdDetails table (odetID as Primary key, ordID as foreign key, products ... Invoice table ...
    (microsoft.public.access.forms)
Loading