How do I remove the table level lookup and have a query return data instead of the primary key
Since I'm just now starting to understand db normalization and using forms to lookup data from one table to reference it into another table, I now understand that my database has a lot of structural flaws, but, it's working. Or in this particular case, I'm getting it to work.
On a part of the database that is not too critical, (not a lot of interactive connections) I want to start to understand how I should have structured things.
My end desire is to have the Banks in the quniClassAndIndividual query display the bank letter instead of the primary key when I use the query in the report, rpt_Training_Employees.
In this Sample database that I've uploaded, I used to have a table level lookup in the AM_Operators table to the Banks table. I changed it to a textbox. So now, the bank column in the AM Operators table lists the primary key, which, as I understand, it should. My questions are several, since I'm making these changes:
1. Should I change anything relationally between the AM Operators and Banks tables?
2. Do I need to change any formats in the Bank fields (Text, Long Integer)?
3. What do I need to change in the query, quniClassandIndividual, in order to have it output the Bank letter instead of the primary key?
Thank you for your help in better understanding!
SampleSkills.accdb
On a part of the database that is not too critical, (not a lot of interactive connections) I want to start to understand how I should have structured things.
My end desire is to have the Banks in the quniClassAndIndividual query display the bank letter instead of the primary key when I use the query in the report, rpt_Training_Employees.
In this Sample database that I've uploaded, I used to have a table level lookup in the AM_Operators table to the Banks table. I changed it to a textbox. So now, the bank column in the AM Operators table lists the primary key, which, as I understand, it should. My questions are several, since I'm making these changes:
1. Should I change anything relationally between the AM Operators and Banks tables?
2. Do I need to change any formats in the Bank fields (Text, Long Integer)?
3. What do I need to change in the query, quniClassandIndividual, in order to have it output the Bank letter instead of the primary key?
Thank you for your help in better understanding!
SampleSkills.accdb
you have to add the tblBanks to your query with a join on field BankID and select the field bank from tblBanks
see Query1 on the ulpoaded file
SampleSkills.accdb
see Query1 on the ulpoaded file
SampleSkills.accdb
1. The relationship didn't change. It was always ID to ID. You just couldn't see it.
2. If the primary key is autonumber, then the foreign key MUST be long integer. If the primary key is text, then the foreign key would also be text. The foreign keys will always match the primary key they point to as to data type with the exception of autonumber/long integer. In reality an autonumber is a long integer, you just see it by a different name.
3. You don't necessarily need to change the queries. If the queries are used by forms and the columns are bound to combo or listboxes, then they will continue to function properly. However, if the query was used by a report, you may have to do the join suggested by Rey because usually reports don't display data using combos. If you are exporting the query to Excel, you would also need to join to the lookup table so you can include the text field.
Users should never be looking directly at queries or tables so the lookup was only a crutch for you. If you feel better having the text value when you open a query and view data, then by all means modify the query to join to the lookup table. As far as opening a table in DS view, there is no way to show the lookup value now that you have removed the lookup.
2. If the primary key is autonumber, then the foreign key MUST be long integer. If the primary key is text, then the foreign key would also be text. The foreign keys will always match the primary key they point to as to data type with the exception of autonumber/long integer. In reality an autonumber is a long integer, you just see it by a different name.
3. You don't necessarily need to change the queries. If the queries are used by forms and the columns are bound to combo or listboxes, then they will continue to function properly. However, if the query was used by a report, you may have to do the join suggested by Rey because usually reports don't display data using combos. If you are exporting the query to Excel, you would also need to join to the lookup table so you can include the text field.
Users should never be looking directly at queries or tables so the lookup was only a crutch for you. If you feel better having the text value when you open a query and view data, then by all means modify the query to join to the lookup table. As far as opening a table in DS view, there is no way to show the lookup value now that you have removed the lookup.
Pat,
To clarify for myself, so even though the data type is number in both tables so that they can have a relationship, the data in the field can still be text? That's how it is in the table I uploaded, but it just seems quirky that the data type would be identified as number and still allow alpha.
To clarify for myself, so even though the data type is number in both tables so that they can have a relationship, the data in the field can still be text? That's how it is in the table I uploaded, but it just seems quirky that the data type would be identified as number and still allow alpha.
Rey,
This query grew from a design query where it was easy to see the logic behind the inner joins; that was several iterations ago of this query. I'm trying to wrap my head around the changes you made so that I can implement them for the Skill_ID, Shift, and Status to see the data instead of the primary key, but I'm getting lost in the trees. Plus, it seems you reordered some of how the query is structured - I can't say for sure. Would you mind posting the query in a line by line manner that pulls out the hinge details. Or maybe write out the thought process used. Or, some way that I can understand how to follow the logic? If you have time for that, I think it would help me in catching the rest of the fish, myself.
This query grew from a design query where it was easy to see the logic behind the inner joins; that was several iterations ago of this query. I'm trying to wrap my head around the changes you made so that I can implement them for the Skill_ID, Shift, and Status to see the data instead of the primary key, but I'm getting lost in the trees. Plus, it seems you reordered some of how the query is structured - I can't say for sure. Would you mind posting the query in a line by line manner that pulls out the hinge details. Or maybe write out the thought process used. Or, some way that I can understand how to follow the logic? If you have time for that, I think it would help me in catching the rest of the fish, myself.
Relational databases are different from spreadsheets in that they are quite rigid regarding data definition. If you define a column as a numeric data type then there is no way the database engine would allow you to put text in that column. You must be conscious of the difference between "codes" that look like numbers and real numbers. For example, in the US, zip codes are formed from numeric characters but they are not numbers. They are text strings. If you deal with zips in the northeast you will be familiar with the problem of the leading zero. My zip is 06614. If someone makes the mistake of defining zip as a number, then my zip becomes 6614 since numbers don't have leading zeros. Only numeric strings have leading zeros.
Relationships between tables are not limited to numeric fields. Although the current consensus is that surrogate keys such as autonumbers are best and they would always be numeric, the columns may have other data types. The only requirement is that both parties of the relationship have the same data type. Occasionally, you will have data that comes from a legacy application and you wish to use that data as a primary key. Perhaps someone formatted customer numbers as a combination of letters and numbers because the original system didn't have a good search feature and they thought it was easer to do something like hartp43318. If you chose to use this customer number as the PK to the customer table, you would use the same text string as the FK in the Order table.
Relationships between tables are not limited to numeric fields. Although the current consensus is that surrogate keys such as autonumbers are best and they would always be numeric, the columns may have other data types. The only requirement is that both parties of the relationship have the same data type. Occasionally, you will have data that comes from a legacy application and you wish to use that data as a primary key. Perhaps someone formatted customer numbers as a combination of letters and numbers because the original system didn't have a good search feature and they thought it was easer to do something like hartp43318. If you chose to use this customer number as the PK to the customer table, you would use the same text string as the FK in the Order table.
Premium Content
You need an Expert Office subscription to comment.Start Free Trial