junction table setup

[vircalendar via AccessMonster.com]

I have two tables that track physician specialties and the category of
procedures they perform: tbl_service and tbl_category. Each table has two
fields: service and svc_ID, category and cat_ID. The ID fields are
autonumbered keys. Each table holds about 15 records.

All of the records in tbl_svc need to have a connection to more than one
record in tbl_category and vice versa. In addition, each of these tables has
relationships with several other tables that are not involved in the current
issue.

I followed the directions of several posts and created a junction table
called tbl_serviceandcategory. It consists of two fields: svc_id and cat_ID,
which are long integer number fields and both identified as key fields in the
new table. I went to the relationship window and created one-to-many
relationships between each of the existing tables and the new one.

Now what? I open the new table and see only one record, which has two fields
that both have the value 0. I'm not really sure what I was expecting, but
that was not it.

 

[Beetle]

Now what?

That depends on how the Service and Category are related to
other entities in your application and what your intended data
entry process is.

For example, do you want to select a Physician and then
enter/view their related Services/Categories? If so you will
need a PhysicianID field in the junction table and you would
set up a main form based on Physicians with a sub form based
on the junction table, using PhysicianID as the Master/Child link.
In the sub form you would use combo boxes for selecting the
Service and Category.

That's just one example though. There are a lot of possibilities
depending on how you want it to work.

 

[Fred]

You have a few steps to go.

FYI, those two fields in your original tables are called Primary Keys (PK's)
and should be set as such. And those in your new junction table are called
foreign keys (FK's)

While one does various table level things to create linkages, where the
rubber meets the road, they are created record to record by placing the value
of the PK from one into the FK of the other. In the case of a junction
table, this is doen by creating a record in the junction table that contains
(in it's FK fields) the values of both PK's of the records to be linked. For
learning purposes, you might try this manually for a few records.

Then you'll want to create a form that does this automatically, and then
create forms, queries and reports that show the linked records in any way
that you wish. Of ourse there's a lot to that, too much for this post.

 

[John Spencer MVP]

Creating the table does not automatically populate it.

The zero zero record is probably not a record at all, but a new record with
the default of the field showing - which is zero for number fields unless you
clear the default.

Can you describe what you are trying to track? It seems to me that there
should be an additional field in this junction table. Perhaps this table is
meant to identify legal combinations of services and categories that you want
to associate with physicians.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[John W. Vinson]

I have two tables that track physician specialties and the category of
procedures they perform: tbl_service and tbl_category. Each table has two
fields: service and svc_ID, category and cat_ID. The ID fields are
autonumbered keys. Each table holds about 15 records.

All of the records in tbl_svc need to have a connection to more than one
record in tbl_category and vice versa.

Does each record in tbl_svc connect to ALL FIFTEEN records in tbl_category?
How about vice versa?

If you don't have 225 or so possible connections, you need to *tell* Access
which connections do exist. The typical way to do this is to use a Form based
on one of the "one" side tables - tbl_service let's say - with a Subform based
on the junction table. On the subform you would have a combo box to allow you
to select which categories apply to that particular service.

The mainform could of course also be based on tbl_category if you want to
assign services to categories, instead of assigning categories to services.
You might even want both forms so you can see the data either way.

Now what? I open the new table and see only one record, which has two fields
that both have the value 0. I'm not really sure what I was expecting, but
that was not it.

I'd suggest opening the junction table in design view, selecting each of these
fields, and removing the default 0 value in the DefaultValue field property on
the lower left of the screen. Microsoft made the (rather unwise) decision to
automatically default all number fields to 0, but if the field is a foriegn
key to another table, there won't usually BE a record with a 0! There should
be nothing in the defaultvalue property at all.

 

[vircalendar via AccessMonster.com]

Thanks. The other relationships are already set up. All I want to do with
this table is to create a table that will allow me to populate a dropdown
list for which the list of procedure categories is based upon the service
selected i another field.

Now what?

That depends on how the Service and Category are related to
other entities in your application and what your intended data
entry process is.

For example, do you want to select a Physician and then
enter/view their related Services/Categories? If so you will
need a PhysicianID field in the junction table and you would
set up a main form based on Physicians with a sub form based
on the junction table, using PhysicianID as the Master/Child link.
In the sub form you would use combo boxes for selecting the
Service and Category.

That's just one example though. There are a lot of possibilities
depending on how you want it to work.

I have two tables that track physician specialties and the category of
procedures they perform: tbl_service and tbl_category. Each table has two

[quoted text clipped - 15 lines]

that both have the value 0. I'm not really sure what I was expecting, but
that was not it.

 

[vircalendar via AccessMonster.com]

This sounds like what I'm looking for. Interesting that I followed the
directions on another posting line by line and never came accross the FK/PK
issue. I've since searched the posts to figure out how to designate something
as a foreign key but have been entirely unsuccessful.

 

[vircalendar via AccessMonster.com]

Okay, I've read all of the answers here (which I really appreciate), but I
have no more of an idea of what to do than I did before.

My plan is to use this table to create a combo box with which users will
select from a list of categories (from table tbl_categories) that lists only
those options that apply to a given service (which he or she will previously
have entered in a separate text box). In other words, once the service has
been selected, the contents of the combo box will change based upon the table
that I'm trying to create. Now, I could simply create a table that has
within it all the possible combinations of service and category, but that's
messy and duplicates other tables that already include category and service
and have other relationships that I don't want to mess up.

The junction table seems like the right answer, but I need to have the chance
somewhere to designate which categories go with which services, and vice
versa. What I have is a junction table--created according to a stepwise
process (http://support.microsoft.com/?id=304466)--that contains no data. I
don't know what to do at this point to actually populate it with info from
the other two tables or to establish the specifi many-to-many relationships
that exist among records.

I have two tables that track physician specialties and the category of
procedures they perform: tbl_service and tbl_category. Each table has two

[quoted text clipped - 3 lines]

All of the records in tbl_svc need to have a connection to more than one
record in tbl_category and vice versa.

Does each record in tbl_svc connect to ALL FIFTEEN records in tbl_category?
How about vice versa?

If you don't have 225 or so possible connections, you need to *tell* Access
which connections do exist. The typical way to do this is to use a Form based
on one of the "one" side tables - tbl_service let's say - with a Subform based
on the junction table. On the subform you would have a combo box to allow you
to select which categories apply to that particular service.

The mainform could of course also be based on tbl_category if you want to
assign services to categories, instead of assigning categories to services.
You might even want both forms so you can see the data either way.

Now what? I open the new table and see only one record, which has two fields
that both have the value 0. I'm not really sure what I was expecting, but
that was not it.

I'd suggest opening the junction table in design view, selecting each of these
fields, and removing the default 0 value in the DefaultValue field property on
the lower left of the screen. Microsoft made the (rather unwise) decision to
automatically default all number fields to 0, but if the field is a foriegn
key to another table, there won't usually BE a record with a 0! There should
be nothing in the defaultvalue property at all.

 

[John W. Vinson]

This sounds like what I'm looking for. Interesting that I followed the
directions on another posting line by line and never came accross the FK/PK
issue. I've since searched the posts to figure out how to designate something
as a foreign key but have been entirely unsuccessful.

There isn't a "foreign key property" that you can apply to a field or see in a
fields properties, as there is a "primary key property". A field is a foreign
key on the basis of how it is being used; if there is a relationship between
two tables on a field, that field is the "Foreign Key" in the many side table,
just because that's how you're using it.

 

[John W. Vinson]

Okay, I've read all of the answers here (which I really appreciate), but I
have no more of an idea of what to do than I did before.

My plan is to use this table to create a combo box with which users will
select from a list of categories (from table tbl_categories) that lists only
those options that apply to a given service (which he or she will previously
have entered in a separate text box). In other words, once the service has
been selected, the contents of the combo box will change based upon the table
that I'm trying to create. Now, I could simply create a table that has
within it all the possible combinations of service and category, but that's
messy and duplicates other tables that already include category and service
and have other relationships that I don't want to mess up.

How are categories and services related to one another in real life, outside
of the confines of your database?

And how are "tables that include category and service" working now? Might
there be some redundancy? For example (I don't have any idea if this is true!)
knowing a service lets you determine the category without ambiguity, then
maybe you shouldn't HAVE both fields in that table!

The junction table seems like the right answer, but I need to have the chance
somewhere to designate which categories go with which services, and vice
versa.

Exactly. That's what we've been trying to explain.

What I have is a junction table--created according to a stepwise
process (http://support.microsoft.com/?id=304466)--that contains no data. I
don't know what to do at this point to actually populate it with info from
the other two tables or to establish the specifi many-to-many relationships
that exist among records.

Again:

Create a Form based on the Services table.
Create a Subform based on this junction table, and put it on the Services
form. Use the ServiceID as the master/child link field; this will insert the
currently displayed service ID into the junction table when you create a
record.
Use the Combo Box Toolbox Wizard to create a combo box on the subform bound to
the CategoryID, based on the Categories table.

For an example of how this works, look at the Orders form in the Northwind
sample database. Think of an Order as a Service; a Product as a Category; and
the OrderDetails table as your junction table.

 

[John Spencer]

Ok, you need to use your junction table as a look up table. You will
need to add records to the junction table that have all the "legal"
combinations.

If you need to set up the legal combinations once and don't need to
worry about adding new ones over time (or if it is infrequent
requirement), you can do this directly in the table by typing the svc_id
and cat_id into the table.

Since you will be using this table as a lookup table only this is one of
the rare times I might use the ability of table fields to use comoboxes
(lookup field).

Open the tbl_ServiceAndCategory in design mode
Click on the svc_Id field
Click on the Lookup tab at the bottom
Select Display Control to Combo box
Row Source Type: Table/Query
Row Source: Select tblService
Bound Column:2 (Assuming that is the svc_Id in tblServices
Column Count:2
Column Widths: ;0 (If want to hide the svc_id and just show the text

Do similar thing for the tbl_Category
Close and save

Now you should be able to enter the data easily.

On your entry forms, you would use two combo boxes.
The first would use the service table as its row source
The second would use tbl_ServiceAndCategory as its row source.

You would need some VBA code in the after update event of the first
combo box to reset the list of items in the second combo box based on
the current value of the combo box for choosing a service.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Okay, I've read all of the answers here (which I really appreciate), but I
have no more of an idea of what to do than I did before.

My plan is to use this table to create a combo box with which users will
select from a list of categories (from table tbl_categories) that lists only
those options that apply to a given service (which he or she will previously
have entered in a separate text box). In other words, once the service has
been selected, the contents of the combo box will change based upon the table
that I'm trying to create. Now, I could simply create a table that has
within it all the possible combinations of service and category, but that's
messy and duplicates other tables that already include category and service
and have other relationships that I don't want to mess up.

The junction table seems like the right answer, but I need to have the chance
somewhere to designate which categories go with which services, and vice
versa. What I have is a junction table--created according to a stepwise
process (http://support.microsoft.com/?id=304466)--that contains no data. I
don't know what to do at this point to actually populate it with info from
the other two tables or to establish the specifi many-to-many relationships
that exist among records.

I have two tables that track physician specialties and the category of
procedures they perform: tbl_service and tbl_category. Each table has two

[quoted text clipped - 3 lines]

All of the records in tbl_svc need to have a connection to more than one
record in tbl_category and vice versa.

Does each record in tbl_svc connect to ALL FIFTEEN records in tbl_category?
How about vice versa?

If you don't have 225 or so possible connections, you need to *tell* Access
which connections do exist. The typical way to do this is to use a Form based
on one of the "one" side tables - tbl_service let's say - with a Subform based
on the junction table. On the subform you would have a combo box to allow you
to select which categories apply to that particular service.

The mainform could of course also be based on tbl_category if you want to
assign services to categories, instead of assigning categories to services.
You might even want both forms so you can see the data either way.

Now what? I open the new table and see only one record, which has two fields
that both have the value 0. I'm not really sure what I was expecting, but
that was not it.

I'd suggest opening the junction table in design view, selecting each of these
fields, and removing the default 0 value in the DefaultValue field property on
the lower left of the screen. Microsoft made the (rather unwise) decision to
automatically default all number fields to 0, but if the field is a foriegn
key to another table, there won't usually BE a record with a 0! There should
be nothing in the defaultvalue property at all.

 

[troy23]

I have two tables that track physician specialties and the category of
procedures they perform: tbl_service and tbl_category. Each table has two
fields: service and svc_ID, category and cat_ID. The ID fields are
autonumbered keys. Each table holds about 15 records.

All of the records in tbl_svc need to have a connection to more than one
record in tbl_category and vice versa.  In addition, each of these tables has
relationships with several other tables that are not involved in the current
issue.

I followed the directions of several posts and created a junction table
called tbl_serviceandcategory. It consists of two fields: svc_id and cat_ID,
which are long integer number fields and both identified as key fields inthe
new table.  I went to the relationship window and created one-to-many
relationships between each of the existing tables and the new one.

Now what?  I open the new table and see only one record, which has two fields
that both have the value 0.  I'm not really sure what I was expecting, but
that was not it.

You will need to fill the junction table with the promary keys of the
tables on either side of this.
You could do this with your form, but it depends how you have it set
up.

For total MS Access mastery
http://access-databases.com/ms-access-tutorial/