best practice for lookups in tables? (1 Viewer)

[got_access:]]

Hi All,
A lookup in a table for data-entry can be created from a table or a query.

In the case the lookup is created from a table, it establishes a relationship link between the two tables. And the lookup-wizard gives the developer the option of selecting referential integrity for that link. Then if one goes to the Table-Relationships one will find that link established between the two tables.

And if a link already exists (without the lookup) - the lookup wizard with give a notification concerning the conflict.

The other method is to build the lookup from a query. And this type of lookup does not create a link. So if one opens the Table-Relationships - one will not find any indicator of a link existing between the two tables.

Can anyone speak to the difference between these two methods - in regard to best practices?

Thanks!

 

[theDBguy]

Hi. If you're referring to using the Lookup Wizard while in table's Design View, "best practice" approach is to not use it. So, whether it creates a relationship link or not does not really matter because the recommended approach is "don't use it (the Lookup tab)."

 

[Mark_]

Best practice is also dictated by how you want changes in the table you draw data from to be reflected in the target table.

IF you want to have your lookup be by reference, you would be saving the ID of the looked up record, not its value, in your target table. As an example, if you have a table of colors, you would store the ColorID for "Green", not the value of "Green". This means if the user changes the source table so "Green" becomes "Verde" you don't have to change any data in your target table.

IF you want to use the lookup table more like a dictionary and actually look the value, then you would copy in the value from your source table. You would NOT want a link established in this case.

For both, you would NEVER want to have the value itself ("Green") be the value you try to link with.

 

[got_access:]]

Thanks very much!

Since it is not recommended to use the Lookup Wizard - and since it would at least seem logical that data integrity would be increased by forcing end users to select values from a list rather than manually entering them - (risking differently spelled instances) is there anything recommended as a replacement for the lookup wizard.
Or is there a better solution than having the end user pull from a list?

Thanks!

 

[CJ_London]

you manage data integrity in the relationships window. Create the link then double click on it to apply the required integrity.

you manage data through forms, so you create your lookup there as a combo or listbox.

If you are using the form wizard to create your forms, it will create a textbox control. right click on it and select change to convert it to a combo

 

[got_access:]]

you manage data integrity in the relationships window. Create the link then double click on it to apply the required integrity.

you manage data through forms, so you create your lookup there as a combo or listbox.

If you are using the form wizard to create your forms, it will create a textbox control. right click on it and select change to convert it to a combo

OH I see!
So its not the use of a drop-down list that problematic - because this is a recommended solution within the environment of a data entry form.

So the there must be something about developing a list via the lookup wizard - that is avoided due to some kind of risk. I can understand its not a best practice to have a data entry person using the table view to enter data. Better to limit end users access to forms and reports. Is that it?

 

[CJ_London]

tables should show data 'in the raw'. No formatting, no lookups etc so you can see what you actually have.

If you do format, use lookups etc you will end up with all sorts of confusion.

Say the lookup is for a product. So you have a productid (which you hide) and a product name - what are you going to call the field? productid which is what it actually is, or productname which is what you see? And when you try to match on that field something like 'smith' you get an error because it is text and you are actually matching to a number.

Or perhaps you format a date as dd/mm/yyyy, then wonder why nothing seems to equal it - the reason will be because the field contains a time element which you don't see because of the format

or you have a decimal number formatted to 2 decimal places, and then can't understand why 1.20+2.30 doesn't equal 3.50

etc

Better to limit end users access to forms and reports. Is that it?

absolutely. Get your tables and queries working as you require, then build the forms and reports

 

[got_access:]]

Excellent!
Thanks very much!! :-]

 

[theDBguy]

Excellent!
Thanks very much!! :-]

Hi. And just for the sake of completeness, here are some reasons why it’s not recommended.

 

[Micron]

dbg - I think you nailed it with that link. Every post seemed to be skirting around the issue and it didn't seem to me that the OP was being given the main point.
got access: don't confuse lookup field with "lookup table". It's quite common to have a table that provides list rows for a combo (or listbox), which isn't the same thing as a table lookup field. While I'm at it, I'll mention that I think most here would agree that multi value fields (something else again) should be avoided. In Access, just because you can does not mean you should. I would not be surprised if one day, MV and table lookup fields go the way of replication. (You read it here first).

 

[got_access:]]

Hope you don't mind if I ask one more question.

In enterprise systems its quite common to have various levels of user read/write permissions. And a system having tables that contain critical values such as would be presented in a data-entry forms drop-down list - would perhaps be good candidates for "manager" type users who maintain integrity of those values.

Does Access have a facility for prohibiting user access to specific forms?
Perhaps with a table containing userIDs and that user's permissions?

 

[CJ_London]

Nothing built in, you need to grow your own. Access can be made pretty secure, but as with all applications someone with enough determination can 'break in'.

You need to develop a security policy - what you want to protect, from whom, by what means and then design your app accordingly.

If data security is an issue, consider using sql server as a back end. With regards the rest, there are plenty of threads on this and other forums as to how you can secure access.

prohibiting user access to specific forms

usually done by hiding or disabling navigation - so think about this before building your forms and how they interrelate. And yes can be controlled from a table or perhaps windows login.

 

[got_access:]]

Thanks very much! :-]

 

[theDBguy]

Hope you don't mind if I ask one more question.

In enterprise systems its quite common to have various levels of user read/write permissions. And a system having tables that contain critical values such as would be presented in a data-entry forms drop-down list - would perhaps be good candidates for "manager" type users who maintain integrity of those values.

Does Access have a facility for prohibiting user access to specific forms?
Perhaps with a table containing userIDs and that user's permissions?

Hi. There are several demos available to help get you started. Please let us know if you don't find any.

 

[got_access:]]

Thanks theDBguy
It would be nice to at least look at one of these to check out the look/feel etc.

 

[theDBguy]

Thanks theDBguy
It would be nice to at least look at one of these to check out the look/feel etc.

Sure. You can take a look at several demos here.


Here's one of those examples.


And here's an article describing how you could create one yourself.


Cheers!

 

[got_access:]]

OK - I have another question about the AUTOLOOK UP query feature
Which I believe is a derivative of the lookup feature.
I have 2 tables.

tblCustomer
tblOrders

The tblOrders table has the field tblOrders.CustomerID - which is linked via lookup to the tblCustomer.CustomerID

So in datasheet view of the tblOrders table, the CustomerID is now a drop-down - established by the lookup.

Then I created a query which selects:
- tblOrders.CustomerID
-tblCustomers.FirstName
-tblCustomers.LastName

And now this query in DataSheet view has a curious feature, which I believe is called an "AutoLookup" query.

In DataSheet view - when I select a customerID the system auto-populates the Customer.Firstname, and Customer.LastName into their perspective fields.

Its a very interesting feature - but is it a specific byproduct of creating a lookup?

 

[theDBguy]

OK - I have another question about the AUTOLOOK UP query feature
Which I believe is a derivative of the lookup feature.
I have 2 tables.

tblCustomer
tblOrders

The tblOrders table has the field tblOrders.CustomerID - which is linked via lookup to the tblCustomer.CustomerID

So in datasheet view of the tblOrders table, the CustomerID is now a drop-down - established by the lookup.

Then I created a query which selects:
- tblOrders.CustomerID
-tblCustomers.FirstName
-tblCustomers.LastName

And now this query in DataSheet view has a curious feature, which I believe is called an "AutoLookup" query.

In DataSheet view - when I select a customerID the system auto-populates the Customer.Firstname, and Customer.LastName into their perspective fields.

Its a very interesting feature - but is it a specific byproduct of creating a lookup?

Hi. I'm not sure I follow what is happening. Can you post some screenshots for us? As we have already mentioned, though, using lookup fields is not recommended, so I'm not sure why you still created one (if that's what you did).

 

[Mark_]

So in datasheet view of the tblOrders table, the CustomerID is now a drop-down - established by the lookup.

Something to note; you would normally never allow a user to edit the customer when they are on an order. You would rather JOIN in the customer data based on the customer ID to display the parent records information but would disable editing these fields.

Most programmers on this forum discourage using a form in datasheet mode for editing as you are limited in the validation you can do. This is one example of where a careless user could cause a major issue for your database.

 

[got_access:]]

I won't be using the lookup feature - as we've discussed.
However I was experimenting and did establish a lookup.
Then I happened to notice when I created this query - and was in datasheet mode the system would auto populate the FirstName and LastName fields, when I select the CustomerID