Table relationship

[Greg]

I am trying to establish a relationship between 2 tables. The primary keys
of table 1 are established on fields "Billing Account No" and "Billing Date"
.. I have a field called "Billing Account No" in table 2 that is not a key
field. I tried to establish a relationship between these two tables
expecting to get a one to many relationship between table 1 and table 2.
However, when I establish the relationship I receive "indeterminate" as the
type of relationship.
Can anyone explain the reasons why someone would receive a relationship type
of "indeterminate"
Thanks,
Greg

 

[Jeff Boyce]

Greg

If your primary key in Table1 is a 2-field composite key, and you only use
one of the values ([Billing Account No]) as a "foreign key" in Table2, how
is Access to know which row in Table1 is being referred to?

Or have I misunderstood your post?

Regards

Jeff Boyce
<Access MVP>

 

[Greg]

Jeff,
You are correct and I feel ignorant.
Now I must think about how to do what I need to do..
Maybe you can give me a suggestion. I have the table (table 1) with a
composite key, "Billing Account No" and "Bill Date".
This table represents a telephone bill common account information eg payment
due, outstanding balance, payments made, Taxes, FCC charges, etc
The second table(table 2) is for each telephone line/number on the account.
There may be dozens of lines represented on one bill each month. Each record
in the table represents one telephone line/number. The primary key for this
table is telephone number.
We have one bill per month.
There will be one record in table 1 for each month.
There will be as many records in table 2 as there are lines/numbers each
month.
Do you have any idea on how to set up a relationship between these two
tables.
I thought it should be a "one to many" because each bill could have dozens
of telephone line/numbers.
It is late and I may be completly off base
Thanks,
Greg

Thanks,
Greg

Greg

If your primary key in Table1 is a 2-field composite key, and you only use
one of the values ([Billing Account No]) as a "foreign key" in Table2, how
is Access to know which row in Table1 is being referred to?

Or have I misunderstood your post?

Regards

Jeff Boyce
<Access MVP>

I am trying to establish a relationship between 2 tables. The primary keys
of table 1 are established on fields "Billing Account No" and "Billing
Date"
. I have a field called "Billing Account No" in table 2 that is not a key
field. I tried to establish a relationship between these two tables
expecting to get a one to many relationship between table 1 and table 2.
However, when I establish the relationship I receive "indeterminate" as
the
type of relationship.
Can anyone explain the reasons why someone would receive a relationship
type
of "indeterminate"
Thanks,
Greg

 

[Jeff Boyce]

Greg

Sorry for the delay in responding -- I've been out.

If the PK in your "one" table consists of two fields, the FK in your "many"
table will have to have the same two fields.

As an alternate approach, you could use an Autonumber field in your "one"
table as your PK, and a "Long" (numeric) field in your "many" table to hold
the FK.

Regards

Jeff Boyce
<Access MVP>

Jeff,
You are correct and I feel ignorant.
Now I must think about how to do what I need to do..
Maybe you can give me a suggestion. I have the table (table 1) with a
composite key, "Billing Account No" and "Bill Date".
This table represents a telephone bill common account information eg
payment
due, outstanding balance, payments made, Taxes, FCC charges, etc
The second table(table 2) is for each telephone line/number on the
account.
There may be dozens of lines represented on one bill each month. Each
record
in the table represents one telephone line/number. The primary key for
this
table is telephone number.
We have one bill per month.
There will be one record in table 1 for each month.
There will be as many records in table 2 as there are lines/numbers each
month.
Do you have any idea on how to set up a relationship between these two
tables.
I thought it should be a "one to many" because each bill could have dozens
of telephone line/numbers.
It is late and I may be completly off base
Thanks,
Greg

Thanks,
Greg

Greg

If your primary key in Table1 is a 2-field composite key, and you only
use
one of the values ([Billing Account No]) as a "foreign key" in Table2,
how
is Access to know which row in Table1 is being referred to?

Or have I misunderstood your post?

Regards

Jeff Boyce
<Access MVP>

I am trying to establish a relationship between 2 tables. The primary keys
of table 1 are established on fields "Billing Account No" and "Billing
Date"
. I have a field called "Billing Account No" in table 2 that is not a key
field. I tried to establish a relationship between these two tables
expecting to get a one to many relationship between table 1 and table
2.
However, when I establish the relationship I receive "indeterminate" as
the
type of relationship.
Can anyone explain the reasons why someone would receive a relationship
type
of "indeterminate"
Thanks,
Greg