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" <baseballfan@comcast.net> wrote in message
news:PYmdnY4PKq-l0__ZnZ2dnUVZ_vmdnZ2d@comcast.com...[color=blue]
>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" <AllenBrowne@SeeSig.Invalid> wrote in message
> news:4460434c$0$3278$5a62ac22@per-qv1-newsreader-01.iinet.net.au...[color=green]
>> 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@comcast.net> wrote in message
>> news:tf-dnedEWvKXvv3ZRVn-iw@comcast.com...[color=darkred]
>>>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.[/color][/color][/color]
