Currently have a FK field filled with numbers, but would like to replace those with corresponding text values (1 Viewer)

Bettany

New member
Local time
Today, 06:28
Joined
Apr 13, 2020
Messages
21
I was storing FK ID's in a field in a table, but would like to replace those with the actual values. What is this best way to accomplish this? Thank you :)
 

bob fitz

AWF VIP
Local time
Today, 11:28
Joined
May 23, 2011
Messages
4,222
IMHO that's not a good idea unless the "actual values" are the PK of a master table.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 03:28
Joined
Oct 29, 2018
Messages
14,539
Hi @Bettany. I agree with @bob fitz. Leave the FK as number and use a query to show the "text/description" value on your form or report. Cheers!
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 06:28
Joined
May 21, 2018
Messages
4,763
Tables need to have proper FKs. NO ONE needs to look at what is in the table. Use a query to show the text.
 

onur_can

Member
Local time
Today, 03:28
Joined
Oct 4, 2015
Messages
155
What do you call real values? I don `t get it.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 06:28
Joined
May 21, 2018
Messages
4,763
Can you tell us what your table is? Sometimes I have seen people over use IDs when a simple value does makes sense. However, that is only when the reference table has very few fields. Also At times a natural key is fine which can simplify.
 

Bettany

New member
Local time
Today, 06:28
Joined
Apr 13, 2020
Messages
21
Can you tell us what your table is? Sometimes I have seen people over use IDs when a simple value does makes sense. However, that is only when the reference table has very few fields. Also At times a natural key is fine which can simplify.

Hi again, Maj. The other table is 9 options and it's creating problems in my split form and in my subforms, so I just want to get rid of the number representation of those 9 options and just simplify with the actual values. I know I could do an update query to change those ID numbers to the real value, but was wondering what the best option was. It's really unnecessary for me to have this table setup, and I'm just trying to simplify things.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 06:28
Joined
May 21, 2018
Messages
4,763
If it is simply a lookup table for options then a related table is not necessary. I seen people do this for example.
Code:
TblColors
ColorID  Color
1            Red
2            Blue
That is just overkill and saves nothing. I had someone give me a database that had this
Code:
tblPriorities
  PriorityID
  PriorityValue
with values of
Code:
1  1
2  2
3  3
4  4
5  5
Which became so confusing when someone deleted the first record and the Priority one had an ID of 2.

So do what you said. Creat a new column. Give it the same name as your old FK column but add the word "_New" to the field. Do your update query. Verify the results for the new column. If all good delete the old column. In the new field remove the "_New" from the name.
 

Galaxiom

Super Moderator
Staff member
Local time
Today, 20:28
Joined
Jan 20, 2009
Messages
12,268
If it is simply a lookup table for options then a related table is not necessary. I seen people do this for example.
Code:
TblColors
ColorID  Color
1            Red
2            Blue
That is just overkill and saves nothing.

Definitely not overkill if you need to convert the database to another language.

A table is required for selection of the colors anyway so having a ColorID is not a big overhead. Indeed storing an integer can make storage more efficient.

There might be a requirement that the colors are displayed in a particular order eg spectrum order.
This would support queries like warm or cool colors using greater than or less than a chosen ID.

That is perhaps a fairly trivial example but the principle should be considered.

This one is serious use and unfortunately not uncommon poor design.
I have seen processes where they store the name of the process step and then have queries like:
Code:
 WHERE ProcessStep IN("Checked In", "Assessed", "In Progress")
when it would be vastly more efficient and maintainable to use:
Code:
 WHERE ProcessStepID BETWEEN 2 AND 5
 

Users who are viewing this thread

Top Bottom