indeterminate, not updateable

[greg]

In an effort to solve a relationship/update problem, I
have created 2 test tables. The each contain an
autonumber id field (system assigned as key field) and an
InvNum field set to long integer, same format, indexed,
dupes ok. That's it.

When I link the InvNum fields in relationships window, I
get indeterminate type. I'm baffled; but I do guess
that's what is causing my not updateable issue. What
gives?

Any help from anyone is greatly appreciated.

Thanks.

 

[Andrew Smith]

You have set both the InvNum fields to allow duplicates. Think about it - if
you have several identical numbers in both tables, how could you (or Access)
tell which links with which?

You need to have at least one of the fields as unique - normally the primary
key field. If the field in the linked table is set to allow duplicates then
you will get a one to many join. If the second table's field is also unique
then you will get a one to one join - this is less common as you could often
just use one table. If you need to create a many to many join then you
actually need to create two one to many joins to a third table.

 

[greg]

Thanks. That does make sense.

My real tables are Invoice Receipts and Employee Pay
(based on the receipts). There are multiple receipts. I
have an invoice number common to both tables with a
second field (common to both) that identifies the
receipt. If there is receipt, there is probably pay; if
pay there must have been receipt.

For example:
Receipts table -- InvNum and InvNumExt
Employee pay table -- InvNum and InvNumExt

How would I link them using both fields, given that not
all receipts do have associated pay?

Thanks, hopefully this is easy.

Greg

 

[Andrew Smith]

I'm not sure I quite follow what you're trying to do here - it's been a long
day and I'm not at my best.

I assume you must also have an invoices table with one record for each
invoice and a unique InvNum field? Presumably there can be several receipts
per invoice and a combination of InvNum and InvNumExt provides the primary
key in the receipts table?

If this is the case then I would add an autonumber primary key to the
receipts table and use this to link to the employee pay table. (You would
have to remove the InvNum and InvNumExt fields from the employee pay table
and add another field to match the autonumber). Alternatively, as this looks
like a one to one join, you could keep the employee pay data in the same
table as the receipts and accept that you will have some empty fields.