finxing table-level lookups (1 Viewer)

[wiklendt]

hi,

i want to remove evidence and shame in the form of table-level lookups from some really EARLY databases i made back before i knew about the evils of table lookups.

there's one particular database that is now HUGE and will take a lot of work to fix the referencing (lots of dependent queries and, ergo, forms). however, i wanted to know how gurus would recommend fixing up 'lookups' in a database?

that is, instead of storing the FK in a table, i had (embarrassingly) stored the value from another table. what's the easiest way (IS there an easy way?) to change this back to the correct FK for that record?

any suggestions welcome.

 

[ChrisO]

G’day Woy Woy.

If it ain’t broke don’t fix it, unless you are being paid by the hour.
If you are being paid by the hour then why do it the easiest way?

Regards,
Chris.

 

[Galaxiom]

I believe that one only needs to remove them from the tables. They appear in the queries only because they are in the underlying table. The real query value is what is provided to the forms and reports where the lookups are fine.

 

[wiklendt]

I believe that one only needs to remove them from the tables. They appear in the queries only because they are in the underlying table. The real query value is what is provided to the forms and reports where the lookups are fine.

huh. seems to work just like you've said i've gingerly attacked one of my main tables, and the world still exists

some queries need tweaking (where they used to show the value now they're showing just the FK, but that's ok ) slow but steady work ahead of me now if i stuff up i'll just resuscitate my backup

 

[wiklendt]

You must spread some Reputation around before giving it to Galaxiom again.

 

[gemma-the-husky]

its NOT a big job - all you need to do is change the field type in the TABLE from lookup to textbox. I assume that you need any lookups already placed on forms, and these will still continue to work.

having the table lookup saves you time, by automating the generation of lookup fields on forms - but also causes the irritation of not being able to easily see the true data being stored.

 

[wiklendt]

its NOT a big job - all you need to do is change the field type in the TABLE from lookup to textbox. I assume that you need any lookups already placed on forms, and these will still continue to work.

having the table lookup saves you time, by automating the generation of lookup fields on forms - but also causes the irritation of not being able to easily see the true data being stored.

yah, you haven't seen this DB LOL it's the kind where the boss constantly wants new things bolted on or changes his mind on what data to store, what data to extract, "oh can we also...", etc... so there are hundreds of queries in this monster - i will need to check all of them to make sure they still filter for the right things with no data-type mismatches - some of them are standalone, but then some my boss has used to base on another flurry of queries and some then get incorporated into forms. like i said... "monster".

i have a feeling they'll be ok, but i'm not going to be here for ever to check them on-the-fly.

in any case, i have just been changing the "lookup" from combo to textbox in the table design. seems to be pretty straightforward and so far non-impactual on the actual usability of any of the forms. it more the stand-alone queries i'm worried about - the sort where where we're filtering for 'pink' isolates, and it's now displaying '2' instead - so still filters ok, but my boss ain't gonna know what "2"'s about, it's just a matter of adding the tblColour table and matching the PK to FK and changing which field is displayed, but like i said, hundreds of queries to check...)

edit: that is to say, changing the lookups at table level's not a big job at all, it's the flow-on effect that's going to take time to check...