Linking to one table 3 times in same main table

  • Thread starter Thread starter Jane
  • Start date Start date
J

Jane

Hello,

I have a table, tblEquipment with the following fields:
W_ServiceProvider
C_ServiceProvider

Both fields link to a table called tblServiceProvider that contains the
following two fields:
SPName
SPAddress

For data entry into tblEquipment I can get W_ServiceProvider to attach to a
record in tblServiceProvider, but trying to also have the field
C_ServiceProvider attached to a different record in tblServiceProvider does
not work.

I could maintain two tables of Service Provider information (one for each of
the fields tblEquipment.W_ServiceProvider and tblEquipment.C_ServiceProvider)
but the information would be the same in each.... there must be another way!

Can you help?

Thank you very much.

Jane
 
Jane said:
... but trying to also have the field
C_ServiceProvider attached to a different record in tblServiceProvider does
not work.
Click to expand...

What does that mean? Please be more specific. How do you go about
"attaching"? What happens when it "doesn't work"?
 
Thank you for your reply tedmi.

Both fields:
tblEquipment.W_ServiceProvider and
tblEquipment.C_ServiceProvider

are set up the same:
Type=Interger
Display Control: Combo Box
Row Source Type: Table/Query
Row Source = SELECT tblServiceProvider.[Record#],
tblServiceProvider.[SPName], tblServiceProvider.[SPAddress];

When entering data into TblEquipment, I can choose for
tblEquipment.W_ServiceProvider and tblEquipment.C_ServiceProvider, via a
dropdown list, two differnet SPNames from tblServiceProvider. This is my
intent. For each record in tblEquipment, there are two associated Service
Providers (W_ServiceProvider, and C_ServiceProvider), which may or may not be
the same. Regardless, the look-up for both fields is from the same table
(tblServiceProviders) which lists all possible Service Providers.

But my problem is that I want my form to display the information from
tblServiceProvider (ie: tblServiceProvider.address) for each of the fields
tblEquipment.W_ServiceProvider, and tblEquipment.c_ServiceProvider. And to
no surpise the only tblServiceProvider.address that is displayed is for the
first of the fields (tblEquipment.W_ServiceProvider rather than the second
(tblEquipment.C_ServiceProvider).

The only way around this that I can think of is to have two tables for the
ServiceProviders Information (say: tbl_W_ServiceProvider and
tbl_C_ServiceProvider). But these two tables would have identical contents.
Is there anyway I can have only one table with the ServiceProvider
information when I am linking to that table twice from tblEuipment?

I hope I have explained this clearly!

Thanks again,
jane
 
Thank you for your reply tedmi.

Both fields:
tblEquipment.W_ServiceProvider and
tblEquipment.C_ServiceProvider

are set up the same:
Type=Interger
Display Control: Combo Box
Row Source Type: Table/Query
Row Source = SELECT tblServiceProvider.[Record#],
tblServiceProvider.[SPName], tblServiceProvider.[SPAddress];

When entering data into TblEquipment, I can choose for
tblEquipment.W_ServiceProvider and tblEquipment.C_ServiceProvider, via a
dropdown list, two differnet SPNames from tblServiceProvider. This is my
intent. For each record in tblEquipment, there are two associated Service
Providers (W_ServiceProvider, and C_ServiceProvider), which may or may not be
the same. Regardless, the look-up for both fields is from the same table
(tblServiceProviders) which lists all possible Service Providers.

But my problem is that I want my form to display the information from
tblServiceProvider (ie: tblServiceProvider.address) for each of the fields
tblEquipment.W_ServiceProvider, and tblEquipment.c_ServiceProvider. And to
no surpise the only tblServiceProvider.address that is displayed is for the
first of the fields (tblEquipment.W_ServiceProvider rather than the second
(tblEquipment.C_ServiceProvider).

The only way around this that I can think of is to have two tables for the
ServiceProviders Information (say: tbl_W_ServiceProvider and
tbl_C_ServiceProvider). But these two tables would have identical contents.
Is there anyway I can have only one table with the ServiceProvider
information when I am linking to that table twice from tblEuipment?

I hope I have explained this clearly!

Thanks again,
jane

tedmi said:
What does that mean? Please be more specific. How do you go about
"attaching"? What happens when it "doesn't work"?
Click to expand...
Click to expand...

I would think along these lines for table design:

Sub CreateTables()
With CurrentProject.Connection

..Execute _
"CREATE TABLE Equipment (" & _
" EquipmentNumber VARCHAR (10) NOT NULL" & _
",EquipmentName VARCHAR (50) NOT NULL" & _
",CONSTRAINT pk_Equipment" & _
" PRIMARY KEY (EquipmentNumber));"

..Execute _
"CREATE TABLE ServiceProviders (" & _
" ProviderNumber VARCHAR (10) NOT NULL" & _
",ProviderName VARCHAR (50) NOT NULL" & _
",CONSTRAINT pk_ServiceProviders" & _
" PRIMARY KEY (ProviderNumber));"

..Execute _
"CREATE TABLE EquipmentServiceProviders (" & _
" EquipmentNumber VARCHAR (10) NOT NULL" & _
",ProviderNumber VARCHAR (10) NOT NULL" & _
",ServiceType CHAR (1) NOT NULL" & _
",CONSTRAINT pk_EquipmentServiceProviders" & _
" PRIMARY KEY (EquipmentNumber, ProviderNumber, ServiceType)" & _
",CONSTRAINT fk_EquipmentServiceProviders_Equipment" & _
" FOREIGN KEY (EquipmentNumber)" & _
" REFERENCES Equipment (EquipmentNumber)" & _
",CONSTRAINT fk_EquipmentServiceProviders_ServiceProviders" & _
" FOREIGN Key (ProviderNumber)" & _
" REFERENCES ServiceProviders (ProviderNumber)" & _
",CONSTRAINT ck_ServiceType_allowed_values" & _
" CHECK (ServiceType IN ('C', 'W')));"

End With
End Sub
 
OOOps.... I am way over my head here!

Michael Gramelspacher said:
Thank you for your reply tedmi.

Both fields:
tblEquipment.W_ServiceProvider and
tblEquipment.C_ServiceProvider

are set up the same:
Type=Interger
Display Control: Combo Box
Row Source Type: Table/Query
Row Source = SELECT tblServiceProvider.[Record#],
tblServiceProvider.[SPName], tblServiceProvider.[SPAddress];

When entering data into TblEquipment, I can choose for
tblEquipment.W_ServiceProvider and tblEquipment.C_ServiceProvider, via a
dropdown list, two differnet SPNames from tblServiceProvider. This is my
intent. For each record in tblEquipment, there are two associated Service
Providers (W_ServiceProvider, and C_ServiceProvider), which may or may not be
the same. Regardless, the look-up for both fields is from the same table
(tblServiceProviders) which lists all possible Service Providers.

But my problem is that I want my form to display the information from
tblServiceProvider (ie: tblServiceProvider.address) for each of the fields
tblEquipment.W_ServiceProvider, and tblEquipment.c_ServiceProvider. And to
no surpise the only tblServiceProvider.address that is displayed is for the
first of the fields (tblEquipment.W_ServiceProvider rather than the second
(tblEquipment.C_ServiceProvider).

The only way around this that I can think of is to have two tables for the
ServiceProviders Information (say: tbl_W_ServiceProvider and
tbl_C_ServiceProvider). But these two tables would have identical contents.
Is there anyway I can have only one table with the ServiceProvider
information when I am linking to that table twice from tblEuipment?

I hope I have explained this clearly!

Thanks again,
jane

tedmi said:
:

... but trying to also have the field
C_ServiceProvider attached to a different record in tblServiceProvider does
not work.

What does that mean? Please be more specific. How do you go about
"attaching"? What happens when it "doesn't work"?
Click to expand...
Click to expand...

I would think along these lines for table design:

Sub CreateTables()
With CurrentProject.Connection

..Execute _
"CREATE TABLE Equipment (" & _
" EquipmentNumber VARCHAR (10) NOT NULL" & _
",EquipmentName VARCHAR (50) NOT NULL" & _
",CONSTRAINT pk_Equipment" & _
" PRIMARY KEY (EquipmentNumber));"

..Execute _
"CREATE TABLE ServiceProviders (" & _
" ProviderNumber VARCHAR (10) NOT NULL" & _
",ProviderName VARCHAR (50) NOT NULL" & _
",CONSTRAINT pk_ServiceProviders" & _
" PRIMARY KEY (ProviderNumber));"

..Execute _
"CREATE TABLE EquipmentServiceProviders (" & _
" EquipmentNumber VARCHAR (10) NOT NULL" & _
",ProviderNumber VARCHAR (10) NOT NULL" & _
",ServiceType CHAR (1) NOT NULL" & _
",CONSTRAINT pk_EquipmentServiceProviders" & _
" PRIMARY KEY (EquipmentNumber, ProviderNumber, ServiceType)" & _
",CONSTRAINT fk_EquipmentServiceProviders_Equipment" & _
" FOREIGN KEY (EquipmentNumber)" & _
" REFERENCES Equipment (EquipmentNumber)" & _
",CONSTRAINT fk_EquipmentServiceProviders_ServiceProviders" & _
" FOREIGN Key (ProviderNumber)" & _
" REFERENCES ServiceProviders (ProviderNumber)" & _
",CONSTRAINT ck_ServiceType_allowed_values" & _
" CHECK (ServiceType IN ('C', 'W')));"

End With
End Sub
Click to expand...
 
When you need to use a lookup table multiple times in a query, you need to
add it multiple times to the QBE grid. Add the first table instance, draw
the first join line. Add the second instance. Access will suffix the table
name with _1. Draw the second join line to the second table. when you
select columns from the lookup tables, be sure to alias the names so you
don't end up with tablename.somefield and tablename_1.somefield as your
field names. Access doesn't handle these names properly in several
situations.

This does not duplicate the data. It only tells Access that you have
multiple relationships to the same table. You handle this the same way when
defining relationships in the Relationship window. Add an new instance of
the table for each time you need to define a new relationship. Access
suffixes the table names so it can keep them straight. This also does not
duplicate the table but is used simply for convenience in defining the
relationship.
 
Hello,

I have a table, tblEquipment with the following fields:
W_ServiceProvider
C_ServiceProvider

Both fields link to a table called tblServiceProvider that contains the
following two fields:
SPName
SPAddress

For data entry into tblEquipment I can get W_ServiceProvider to attach to a
record in tblServiceProvider, but trying to also have the field
C_ServiceProvider attached to a different record in tblServiceProvider does
not work.

I could maintain two tables of Service Provider information (one for each of
the fields tblEquipment.W_ServiceProvider and tblEquipment.C_ServiceProvider)
but the information would be the same in each.... there must be another way!
Click to expand...

If you're using table datasheets for routine interaction with your data...
DON'T. Tables are for data storage; you'll need Forms for productive use of
your database.

And if you're trying to put three Lookup Fields in your table... *REALLY*
don't. See
http://www.mvps.org/access/lookupfields.htm
for a critique of this Microsoft misfeature. It's very easy to define three
relationships to the same table in the relationship window, and then use three
combo boxes (or other tools) on a form, but I suspect it's way beyond the
limited capabilities of the lookup wizard.

John W. Vinson [MVP]
 
Back
Top