|
tech
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
junction table setupprocedures 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. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-gettingstarted/200906/1 > Now what? That depends on how the Service and Category are related toother 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. -- Show quoteHide quote_________ Sean Bailey "vircalendar via AccessMonster.com" wrote: > 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. > > -- > Message posted via AccessMonster.com > http://www.accessmonster.com/Uwe/Forums.aspx/access-gettingstarted/200906/1 > > 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. Beetle wrote: Show quoteHide quote >> 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. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-gettingstarted/200906/1 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. 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. Fred wrote: Show quoteHide quote >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. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-gettingstarted/200906/1 On Sat, 27 Jun 2009 02:28:06 GMT, "vircalendar via AccessMonster.com"
<u4313@uwe> wrote: >This sounds like what I'm looking for. Interesting that I followed the There isn't a "foreign key property" that you can apply to a field or see in a>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. 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 [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 vircalendar via AccessMonster.com wrote: Show quoteHide quote > 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. > On Fri, 26 Jun 2009 18:34:07 GMT, "vircalendar via AccessMonster.com"
<u4313@uwe> wrote: >I have two tables that track physician specialties and the category of Does each record in tbl_svc connect to ALL FIFTEEN records in tbl_category?>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. 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 I'd suggest opening the junction table in design view, selecting each of these>that both have the value 0. I'm not really sure what I was expecting, but >that was not it. 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 [MVP] 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. John W. Vinson wrote: Show quoteHide quote >>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. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-gettingstarted/200906/1 On Sat, 27 Jun 2009 02:55:36 GMT, "vircalendar via AccessMonster.com"
<u4313@uwe> wrote: >Okay, I've read all of the answers here (which I really appreciate), but I How are categories and services related to one another in real life, outside>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. 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 Exactly. That's what we've been trying to explain.>somewhere to designate which categories go with which services, and vice >versa. > What I have is a junction table--created according to a stepwise Again:>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. 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 W. Vinson [MVP] 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 '==================================================== vircalendar via AccessMonster.com wrote: Show quoteHide quote > 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. > > John W. Vinson wrote: >>> 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. >
Show quote
Hide quote
On 26 June, 19:34, "vircalendar via AccessMonster.com" <u4313@uwe> You will need to fill the junction table with the promary keys of thewrote: > 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. > > -- > Message posted via AccessMonster.comhttp://www.accessmonster.com/Uwe/Forums.aspx/access-gettingstarted/20... 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/  
Other interesting topics
Access Database Setup
Macro - one at a time, please! Copy Field Data can it be done in access? Add a Footer to a Table add primairy key into txt field How do I set up a column to populate... Guide Me loading Access when I already have a 2007 student Office pkg loade multiple users updating the same table and sharing |
|||||||||||||||||||||||
