Coupled Fields

[slrphd]

I looked through the history of the Tables forum and could not find anything on this topic. I am either not asking the question correctly or just too ignorant to really understand what I am doing (hmmm … no surprise there). I would like to couple two fields of a table together so that when I reference the first, I also get the second. The general organization of my database is to have site names in a look-up table, which I refer to as a primary table. This look-up table is referenced by several other tables, which I refer to as a secondary table. In one of the secondary tables, the same site name can be used in more than one record. To distinguish between plants at the same site, unit numbers are assigned in a separate field. So the combination of a site name and a unit number uniquely define a plant (good candidates for primary keys?). I want to use this combination of site name / unit number in other tables. How do I link these two fields and call them together or can't I. Is concatenated text my only choice?

On a different topic, several of the mentors in this forum provided considerable help to me while getting the security for this database set-up and operating correctly. For that, my unending thanks. One of you provided a document written by a former supplicant that described in sixteen pages the fundamentals for how the security in MS Access works. I have rendered that document into one of my own that is much briefer (two to three pages) and includes a few of my observations and some minor deviations of the supplied procedure. If you think this would be of benefit to anyone, I would be happy to provide it, especially if it helps reduce the enormous debt I am building up here.

 

[neileg]

I looked through the history of the Tables forum and could not find anything on this topic. I am either not asking the question correctly or just too ignorant to really understand what I am doing (hmmm … no surprise there). I would like to couple two fields of a table together so that when I reference the first, I also get the second. The general organization of my database is to have site names in a look-up table, which I refer to as a primary table. This look-up table is referenced by several other tables, which I refer to as a secondary table. In one of the secondary tables, the same site name can be used in more than one record. To distinguish between plants at the same site, unit numbers are assigned in a separate field. So the combination of a site name and a unit number uniquely define a plant (good candidates for primary keys?). I want to use this combination of site name / unit number in other tables. How do I link these two fields and call them together or can't I. Is concatenated text my only choice?

You could use these as a multi field key. In table design view hold down the Ctrl key and select the fields and then click the primary key button.

However, that doesn't mean it's a good idea. Your question rather suggests you are working at the table level. You will end up with a much better application if you work at a form level with your data, in which case it's not a good idea to define your lookups at a table level.

In this case there is no advantage in using these fields as key values and an autonumber would be a better bet. You still might like to define a multifield index with no duplicates to keep your ID's unique.

 

[slrphd]

You could use these as a multi field key. In table design view hold down the Ctrl key and select the fields and then click the primary key button.

However, that doesn't mean it's a good idea. Your question rather suggests you are working at the table level. You will end up with a much better application if you work at a form level with your data, in which case it's not a good idea to define your lookups at a table level.

In this case there is no advantage in using these fields as key values and an autonumber would be a better bet. You still might like to define a multifield index with no duplicates to keep your ID's unique.

=======
Thanks for responding. I guess I am not too certain regarding using key fields and their benefits. You surmise that I am working at the table level and that is true. I chose to work there becuase I will need to use the coupled fields as the source for several other secondary tables.

 

[neileg]

You surmise that I am working at the table level and that is true. I chose to work there becuase I will need to use the coupled fields as the source for several other secondary tables.

I suspect you're working at a table level because you are familiar with spreadsheets. I also suspect that it's not really the 'coupled fields' that you need to use, but the relationship between the records in your tables. I don't know how to help but I think you need to 'raise your game' in respect to relational databases.

 

[slrphd]

I suspect you're working at a table level because you are familiar with spreadsheets. I also suspect that it's not really the 'coupled fields' that you need to use, but the relationship between the records in your tables. I don't know how to help but I think you need to 'raise your game' in respect to relational databases.

Thanks for your response. I fully agree with your assessment that I need to 'raise my game,' as you put it. At the moment, this is a clandestine project so I will not get any money or approval for training. What I ended up doing was writing a make table query that concatenated the text and used the resulting table as a source for a look-up column. Your response causes me to think that a better way to go is to write an event procedure that makes this concatenation each time I call up the form. Please educate me a little as to why that is a better approach. Thanks.
Steven Ross

 

[neileg]

Thanks for your response. I fully agree with your assessment that I need to 'raise my game,' as you put it. At the moment, this is a clandestine project so I will not get any money or approval for training.

Well I'm self taught, so don't give up!

What I ended up doing was writing a make table query that concatenated the text and used the resulting table as a source for a look-up column. Your response causes me to think that a better way to go is to write an event procedure that makes this concatenation each time I call up the form. Please educate me a little as to why that is a better approach.

Neither the make table nor the event procedure is necessary. I'll set aside whether this is the right way to link your tables for now, and look at the method. What you need to use is a select query and include a calculated field which concatenates the two fields. Any place in Access where you would use a table, you can use a query. A make table or event procedure will create data that you don't need to store since you can always calculate it. Stored calculations become out of date if the data is amended or added to.

Here’s a sample database that might give you some pointers. Look at frmMainForm. Note that tblPlant has a multifield index based on SiteID and UnitNumber that prevents you duplicating the combination. In the qryPlant I have concatenated the Site Name and the unit number for display purposes. You will see that in tblPlant there is an autonumber for each item. If you need to pull up the site name & number combination in another place, use this autonumber, and simply look up the values you need.

Let me know if this is not clear.

 

[slrphd]

Neil,
Wow. You went through a lot. Thanks. I read through your explanation and believe I have gotten the essence of it. qryPlant makes the link-up between the site name and the plant number and assigns that value to the variable UniqueNo. qrySite is used to order the list. This leads me to the question why not do the ordering in the same query? I have not learned to use subforms, yet, so I am guessing that you separate them so that you can present an ordered list in the subform. You can see that I work at this at a rudimentary and unsophisticated level. I find your brief example instructive and helpful and have learned much from it quickly.

I briefly considered the use of a query but decided against it for two reasons. The first is that I am not very good at writing queries (even though I know the way to improve is to jump in with both feet and learn). The second is that there are one or two exceptions in my naming scheme and how well does a query handle that? My very limited knowledge of SQL does not include conditionals, if they can be done at all. However all that works out, I greatly appriciate the effort you put forth in my behalf.

I am building a considerable debt in this forum and need to find a way to repay it. I wrote a brief descriptive document on security (attached). One of my first experiences in this forum had that as its subject and one of the mentors provided a document describing how to set-up security but purposely omitting the keystrokes. My document has the keystrokes plus a few observations. If it is worthwhile, it can be distributed. If not, with some guidance, I certainly am willing to make corrections so that I can return something useful to a group that has helped me so much. Again, thanks for your help.
Steven Ross

 

[neileg]

Neil,
Wow. You went through a lot. Thanks. I read through your explanation and believe I have gotten the essence of it. qryPlant makes the link-up between the site name and the plant number and assigns that value to the variable UniqueNo. qrySite is used to order the list. This leads me to the question why not do the ordering in the same query?

No, qrySite is the record source for the main form. I ordered the records for neatness, you needn't.

I have not learned to use subforms, yet, so I am guessing that you separate them so that you can present an ordered list in the subform.

Kind of. The data in the main form and the subform are from different data sets but with a relationship, in this case the site ID.

I briefly considered the use of a query but decided against it for two reasons. The first is that I am not very good at writing queries (even though I know the way to improve is to jump in with both feet and learn). The second is that there are one or two exceptions in my naming scheme and how well does a query handle that? My very limited knowledge of SQL does not include conditionals, if they can be done at all.

You are going to get nowhere until you learn to use queries, they are fundamental to a relational database. None of the example was written in SQL, just in the query grid in design view. I'm pretty poor at SQL, myself. When you work in design view, Access will handle any field names you have used, wrapping them in [] if you have included spaces. Up to a point, it will autocorrect sytax errors you make in referring to other objects such as forms.
If by naming scheme you mean your references to units or sites, anything you put in a table can be handled in a query. In fact, when you view a table in Access, you aren't looking at the table itself, but a query view of it. Any place you want to use a table, you can use a query.

Good luck. I hope I have helped. Much of my own knowledge has come from the good people on this forum. Pass it on when you gain a little more experience!

 

[slrphd]

Thanks for the explanination. I have always understood that queries were a major tool for relational databases but most of what I have done was to load data into a table and call it back in a form. It is only now that I am starting to add more complexity in my thinking and design of data bases. At this rate, I should be a talented database designers in six or seven hundred years. Still, you have been very helpful. Thanks.