Table with 2 joins to the same field name

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have tblCustomers [CustomerID] with a one to many join to
tblSales[CustomerID]. I want to occassionally populate another field in
tblSales that relates to a limited group of those same customers (queried via
the field tblCustomerCategories.CategoryName), while leaving a null value
(important) if I choose not to populate it. Access doesn't allow me to add
another [CustomerID] field to tblSales, so I'm stymied how to accomplish what
I need to do. Help!
 
I have tblCustomers [CustomerID] with a one to many join to
tblSales[CustomerID]. I want to occassionally populate another field in
tblSales that relates to a limited group of those same customers (queried via
the field tblCustomerCategories.CategoryName), while leaving a null value
(important) if I choose not to populate it. Access doesn't allow me to add
another [CustomerID] field to tblSales, so I'm stymied how to accomplish what
I need to do. Help!
Click to expand...

I'm perplexed. Will a particular record in tblSales have two different
values for CustomerID, i.e. will a given sale refer to two different
customers at the same time?

If so, just add another field of the same datatype as CustomerID -
Long Integer if it's an autonumber. It can be named whatever you like;
the name of the field is of value to you as the developer, but Access
doesn't care, you could name it B3123CCX_ax4 if you want! You can then
establish RI from tblCustomers.CustomerID to this field. There's no
problem leaving the field NULL; RI will insist that any value entered
be valid, but does not insist that you enter anything. Do set the
field's Default property to blank, however - Access "helpfully" (grr!)
gives all numeric fields a 0 default, which you don't want in this
case.

John W. Vinson[MVP]
 
Sorry John, my description was lacking. Hope this helps: Potentially, I
occassionally will have a sale to one customer that "involves" another
customer, and I need to be able to querie the non-null values in tblSales of
those"occassional" customers and have access to the rest of their customer
data. If, I use the same CustomerID # (albeit with a unique field name
joined to Customers.CustomerID), I should be able to accomplish this.
Correct? Hope that makes sense.
--
Tom


John Vinson said:
I have tblCustomers [CustomerID] with a one to many join to
tblSales[CustomerID]. I want to occassionally populate another field in
tblSales that relates to a limited group of those same customers (queried via
the field tblCustomerCategories.CategoryName), while leaving a null value
(important) if I choose not to populate it. Access doesn't allow me to add
another [CustomerID] field to tblSales, so I'm stymied how to accomplish what
I need to do. Help!
Click to expand...

I'm perplexed. Will a particular record in tblSales have two different
values for CustomerID, i.e. will a given sale refer to two different
customers at the same time?

If so, just add another field of the same datatype as CustomerID -
Long Integer if it's an autonumber. It can be named whatever you like;
the name of the field is of value to you as the developer, but Access
doesn't care, you could name it B3123CCX_ax4 if you want! You can then
establish RI from tblCustomers.CustomerID to this field. There's no
problem leaving the field NULL; RI will insist that any value entered
be valid, but does not insist that you enter anything. Do set the
field's Default property to blank, however - Access "helpfully" (grr!)
gives all numeric fields a 0 default, which you don't want in this
case.

John W. Vinson[MVP]
Click to expand...
 
Sorry John, my description was lacking. Hope this helps: Potentially, I
occassionally will have a sale to one customer that "involves" another
customer, and I need to be able to querie the non-null values in tblSales of
those"occassional" customers and have access to the rest of their customer
data. If, I use the same CustomerID # (albeit with a unique field name
joined to Customers.CustomerID), I should be able to accomplish this.
Correct?
Click to expand...

Correct. You might call this second field InvolvedCustomer, and use a
Combo Box (based on a query selecting the valid customer ID's) on the
form to select the appropriate CustomerID.

John W. Vinson[MVP]
 
Thanks John. Works like a charm
--
Tom


John Vinson said:
Correct. You might call this second field InvolvedCustomer, and use a
Combo Box (based on a query selecting the valid customer ID's) on the
form to select the appropriate CustomerID.

John W. Vinson[MVP]
Click to expand...
 
Back
Top