Not using lookup fields (1 Viewer)

[SueBK]

Okay, my head is about to explode. I was taught to design my database using lookup fields in tables. I read that's a big no-no and the issues listed on http://access.mvps.org/access/lookupfields.htm are pretty clear.

However, all very well in theory, and in practice if you're a seasoned designer, but I'm not and I'm confused.

1. I know how to 'reset' my fields and remove the lookup link. That's the easy part. Simply reset to text box.

2. I then have a bunch of tables with numbers in fields. I'm really confused about the logistics of getting users (and in particular append queries) to populate the field.

Is there a good online tutorial somewhere?

 

[Isskint]

The lookups need to be on the forms not the tables. When you put a lookup in a table, do you reference another table or list the values? You simply replicate this lookup on the form. Now I try to avoid AutoNumber PK in LookUp tables due to the issue you mention at 2. I either have a single column or alter the RowSource of comboboxes to not include the PK autonumber, so your text fields still read as text.

As to conversion of current tables, add an extra field with the correct type. Then run an UPDATE query to lookup() the Text value to the Numeric value and puts that in your new field. Now your table has all the right values and you can delete the numeric field and rename the new field.

Data Pig Technologies have some good video tutorials. Not sure they have this specifically but worth a look. http://www.datapigtechnologies.com/AccessMain.htm

 

[Galaxiom]

Now I try to avoid AutoNumber PK in LookUp tables due to the issue you mention at 2. I either have a single column or alter the RowSource of comboboxes to not include the PK autonumber, so your text fields still read as text.

The whole point of the lookup on the form is to store the key instead of the text! The combo displays the text while the value from the bound column is stored in the table.

This structure allows the displayed text to be changed without touching the data. It also keep the data storage size lower.

I would very strongly advise against following any of Isskint's advice on this.

 

[Isskint]

Galaxiom, I appreciate your sentiment, however i think you mis understand my comment.

Lets say you have a table of ProductDetails and one field in the table is TypeOfProduct. I would create a table of ProductTypes, so that new Types can be added, and lookup this table on any forms to assist the user with selecting valid ProductTypes. This table will only have 1 field - TypeOfProduct - so what is the point of haveing an Autonumber PK? It means from this point on always having to use a dlookup() to return the meaningful field in the table (on reports or queries).

That is not an efficient alternative because the lookup table has to be queried every time the current record changes.

Dlookup() - by your own comments in this previous post - causes the table to be requeried and in doing so slows performance.

I would appreciate it if you could direct your criticism directly to me and where appropriate i would happily remove or edit the incorrect post. Criticising me puplicly like this and suggesting people not follow my suggestions, is unfair as i have helped many people since joining this forum and seeing a post from somone long established like yourself may mean people no longer accept any suggestions from me.

Thank you, Isskint

 

[gemma-the-husky]

changing the table to use text box, rather than lookup won't make any difference to existing forms.

what it does do, is mean that if you create a form IN FUTURE using the field you will have to create the combo box yourself, which is not hard, and often the auto created combo box is not exacltly what you want anyway.

the benefit is that your actual data in the table becomes clearer to understand.

note that users should NEVER interact directly with tables.

 

[SueBK]

So, let me get this straight. You're saying that if I convert all my lookup fields in my tables, everything else I've already designed is cool bananas? I don't have to change anything anywhere else?

 

[Galaxiom]

So, let me get this straight. You're saying that if I convert all my lookup fields in my tables, everything else I've already designed is cool bananas? I don't have to change anything anywhere else?

That is correct. There is nothing wrong with having lookups on forms.

It is good practice to store the key from a lookup in place of relatively long text strings. This reduces storage and speeds up indexing.

 

[Galaxiom]

Galaxiom, I appreciate your sentiment, however i think you mis understand my comment.

I understood what you were saying completely. I just strongly disagree with it because it goes against well established database design practice of abstracting such data.

Lets say you have a table of ProductDetails and one field in the table is TypeOfProduct. I would create a table of ProductTypes, so that new Types can be added, and lookup this table on any forms to assist the user with selecting valid ProductTypes. This table will only have 1 field - TypeOfProduct - so what is the point of haveing an Autonumber PK?

Because the main table stores the (often but not necessarily numeric) PK of the TypeofProduct records rather than the long text description. The visible columns in the combo show the text description while the BoundColumn is stored.

This permits the TypeOfProduct descriptions to be changed in a single record without updating the Product table. This can useful for several purposes.

Say a new product is added which requires a new TypeOfProduct description that would be ambiguous against an existing one. The new one is added and the old one edited for clarification. Job done.

It is also provides for multiple language support. The key remains the same in all languages but the combos RowSource query is modified to accomodate the other languages. Different users can simultaneously view the same data in their own language.

It means from this point on always having to use a dlookup() to return the meaningful field in the table (on reports or queries).

No it does not. The report can use the same combo structure to display the text even though the RecordSource query is returning the key.

Queries should not be viewed directly for the same reasons a user should not view a table directly. However if the query is being exported and requires the text description then the text can be displayed by using a join to the lookup table.

The join in the RecordSource query is also an alternative for reports allowing the text to be displayed in a textbox rather than a combo.

I would appreciate it if you could direct your criticism directly to me and where appropriate i would happily remove or edit the incorrect post.

That is not how forums work. It isn't about being right all the time but sharing our knowledge and impressions. Debate about best practices is the most informative part of it and showing others how conclusions are arrived at througgh discussion is all part of their learning too.

Criticising me puplicly like this and suggesting people not follow my suggestions, is unfair as i have helped many people since joining this forum and seeing a post from somone long established like yourself may mean people no longer accept any suggestions from me.

You are worrying too much. Like most of us I have had said my fair share of dumb things.

I feel it was important to say that your suggestions on this particular topic should not be followed. In no way did I intend any implication of criticism of your other very valuable posts.

"I would very strongly advise against following any of Isskint's advice on this."

 

[SueBK]

Taking deep breaths here, and trying to not hyperventilate. I copied my database. Removed the look-up criteria from all my table fields. Everything appears to still be working.
Now, to move on to new forms/reports. Let me check I've got this straight:
Say I've got tblAssessProjects. It has a field PrjID, previously a lookup to the ID key field in tblProjects and a field AssessID, previously a lookup to the ID key field in tblAssessIssues. These two fields are now populated with numbers in the table.

My coding on my command button that filters the report to specific project doesn't change?

I attach the tblAssessProjects to my report. Where I want the Project info to appear on my report, I drop the ProjectID field, but I set the row source to tblProjects, with the bound column being the ID field, and then whatever info I want to drag from that table. (BTW, this is a question.)

 

[Galaxiom]

Say I've got tblAssessProjects. It has a field PrjID, previously a lookup to the ID key field in tblProjects and a field AssessID, previously a lookup to the ID key field in tblAssessIssues. These two fields are now populated with numbers in the table.

The table field was always populated with numbers. Lookups on fields just change what is displayed in the table.

My coding on my command button that filters the report to specific project doesn't change?

That is correct. Any code would have been referring to the actual value in the field. This is one of the reasons why the use of lookups is discouraged. Sometimes members here ask why their query or Find is not working and it turns out they are searching for the displayed lookup rather than the underlying value which the code requires.

I attach the tblAssessProjects to my report. Where I want the Project info to appear on my report, I drop the ProjectID field, but I set the row source to tblProjects, with the bound column being the ID field, and then whatever info I want to drag from that table. (BTW, this is a question.)

Convert the ProjectID textbox to a Combo. The RowSource is the tblprojects. Change the ColumnCount and ColumnWidths properties so that the text will display instead of the ID. Hidden columns will have zero width.

BTW It is often better to use a query based on the table for the RecordSource. This way you can use parameters to limit the data and get many version of the reports from the same design.

The other report display alternative is to use textboxes for the report controls and include their ControlSource as the textfield of the lookup in the RecordSource query by joining the data table to the lookup table.

Hope that makes sense.

 

[SueBK]

I leaped in with both feet; after testing in a dummy copy; and removed all my in-table lookups. Let's see how I go from here.
Thanks for the encouragement.