Displaying related value (not sure how to phrase it) (1 Viewer)

[UserError]

Hello,
My main table contains a foreign key NCAID from tblNCA, which is a lookup table with five records, with two fields (primary key) NCAID and NCAName. The NCAID fields between the tables are related.
It seems to me the purpose of the tables/relationship is to store the NCA as a code (NCAID) rather than the human-readable name, but it obviously needs to be translated back in a report and I can't see how to "decode" the NCAID to a user friendly NCAName for use in a report.
Any tips would be appreciated, Thanks.

 

[gemma-the-husky]

Have a query that joins the two tables together. Then you base your form or report on the query. First, you can sort the query in the order you want (note that this works for a from, but a report carries it's own sort order). Second, you can then show the value of the looked-up data, rather than the ID number, which is what you want.

As a one off, you can use a dlookup function, but generally a query is a lot more efficient.

 

[The_Doc_Man]

Since you claimed uncertainty in phrasing...

What you want is called a JOIN query where you JOIN two (or more) tables based on some field they have in common - in this case the ID code. Then you can pick up the translation of the code from the 2nd table that participates in the JOIN. When building this JOIN, you will first build a relationship between the two tables based on the field they share in common.

When looking up "LOOKUP" you will find this thing called a LOOKUP FIELD. Avoid that like the plague. It works for VERY basic applications but every time you try to do something even slightly more advanced with it, it bollixes up the works really badly.

What you do with a JOIN query is take advantage of having a LOOKUP TABLE. You should be able to search this forum for LOOKUP TABLE or JOIN QUERY to get articles on what is going on here.

More on nomenclature... In the (lookup) table that has the ID and the name for that ID code, the ID field is the primary key (PK) and should be declared as the PK when you build the table. Doing so will improve the efficiency of the lookup operation by a pretty decent amount. In the other table (that holds the ID but you have to look up the name) the ID code field that is in common with the lookup table is called foreign key (FK). It gets that name because the lookup value is a key in a table other than the one where it is located i.e. it is foreign to this table but primary to the table that provides the lookup linkages.

Home that didn't confuse the issue for you.

 

[UserError]

Thank you both. Will work on implementing this now.

 

[Pat Hartman]

t seems to me the purpose of the tables/relationship is to store the NCA as a code (NCAID) rather than the human-readable name

Not really. You can use a text field as a primary key. It is less efficient for joins if there are a lot of rows but otherwise, there isn't a problem. The real reason is that the NCAID field is not under your control. It is defined and managed by some other entity and therefore may change if they decide to change their rules. If it does change, you don't want that change to have to ripple through all your tables. By making an autonumber the PK, there is never a reason to cascade a change like this throughout the database. You just change the name in the one table and it automatically changes every other place because you use a query to join to the table where it is defined every time you use it.

Think of this as good defensive programming. You are isolating your application from a change that you cannot control. This is one of the things that separates the adults from the children. The experienced developers understand the need for situational awareness and ensuring that change can be accommodated without breaking stuff. The more experience you have, the easier it will be to identify future changes and try to handle them today. It's one of the reasons we are all so insistent that if you have more than one of anything, you have many and many requires a separate table. Your user assures you there are NEVER more than two contacts. What's the harm in making two fields rather than a separate table and form to manage the contacts? You'll find out next Thursday when they want to add a third contact for one special client when you find out exactly how many objects are affected by this change.

 

[UserError]

Not really. You can use a text field as a primary key. It is less efficient for joins if there are a lot of rows but otherwise, there isn't a problem. The real reason is that the NCAID field is not under your control. It is defined and managed by some other entity and therefore may change if they decide to change their rules. If it does change, you don't want that change to have to ripple through all your tables. By making an autonumber the PK, there is never a reason to cascade a change like this throughout the database. You just change the name in the one table and it automatically changes every other place because you use a query to join to the table where it is defined every time you use it.

Think of this as good defensive programming. You are isolating your application from a change that you cannot control. This is one of the things that separates the adults from the children. The experienced developers understand the need for situational awareness and ensuring that change can be accommodated without breaking stuff. It's one of the reasons we are all so insistent that if you have more than one of anything, you have many and many requires a separate table. Your user assures you there are NEVER more than two contacts. What's the harm in making two fields rather than a separate table and form to manage the contacts? You'll find out next Thursday when they want to add a third contact for one special client.

WRT the nature of the PK, I read this earlier today - obviously stuff on the interweb is to be taken carefully but if one doesn't know, it's hard to sort the wheat from the chaff... " 3. Never update the primary key. In fact, because the primary key has no other purpose except to uniquely identify a row, there is no reason to update it. If the primary key needs to be updated, the principle that the primary key should be meaningless to the user has been violated." https://blog.krybot.com/a?ID=00150-55aede4b-ddf5-4922-ae3f-193f7417652d"
I don't mind what the key is and agree fully that one doesn't want it to change...

 

[UserError]

Thank you to Gemma the husky & The Doc Man, I've joined the tables through a query and it seems to be working although I'm not sure if I've done the right thing. Time will tell.