best practice for lookups in tables?

[theDBguy]

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

Hi. Thanks for the additional info. Can you please post the SQL statement of your query too? The dropdown alone would/should not autopopulate those other columns for you.

 

[got_access:]]

Here it is:

SELECT tblOrders.CustomerID, tblCustomers.FirstName, tblCustomers.LastName
FROM tblCustomers INNER JOIN tblOrders ON tblCustomers.CustomerID = tblOrders.CustomerID;

 

[theDBguy]

Here it is:

SELECT tblOrders.CustomerID, tblCustomers.FirstName, tblCustomers.LastName
FROM tblCustomers INNER JOIN tblOrders ON tblCustomers.CustomerID = tblOrders.CustomerID;

Hi. Thanks! This is exactly why people say lookup fields can cause confusion. You are basically pulling all the information from tblCustomers. So, the names from tblCustomer match the ID you assigned to the order because you linked the two tables using CustomerID. When you view your query, you can see the name plus whatever that is in the first column (company name?). Now, if you export this query into Excel, what do you see?

 

[gemma-the-husky]

A lookup in a table isn't the end of the world, and it's easy to change it anyway.

The thing is, a lot of us probably like to see the REAL data in the table, not the referenced lookup. Also, the lookup in the table will be used by default for any control referencing the field, which is all well and good, but often needs amending anyway. In practice it's as easy not to have the lookup. and to set it up manually when necessary.

 

[got_access:]]

Hi. Thanks! This is exactly why people say lookup fields can cause confusion. You are basically pulling all the information from tblCustomers. So, the names from tblCustomer match the ID you assigned to the order because you linked the two tables using CustomerID. When you view your query, you can see the name plus whatever that is in the first column (company name?). Now, if you export this query into Excel, what do you see?

Yes - thank you the DBguy,
But I would like to know what this feature in Access is called?
Is it simply called an "AutoLookup" query?
Based upon the fact that the lookup created the link?
I've not read anything in any Access documentation that refers to the system auto-populating fields.

I'm assuming without the lookup established - an auto-population feature like this would need to be done by coding?

 

[gemma-the-husky]

When you create a field, set it's type as lookup wizard. You will then be asked to pick the linked table, and lookup details.

It will create a field of the type you define as the bound column of the lookup table, but will display the value from the data column you select, rather than the bound column. ie, if you pick a number column to be the bound column, the data type in your main table will be a number. If you pick a text field as the display field, your table field will show text (although it is actually storing the ID number.)

The field properties in the table will show as "combobox" in the lookup tab. Change combobox to text box, and you get rid if the lookup. The REAL value in the TABLE stays the same as it was before you changed the lookup.

 

[theDBguy]

Yes - thank you the DBguy,
But I would like to know what this feature in Access is called?
Is it simply called an "AutoLookup" query?
Based upon the fact that the lookup created the link?
I've not read anything in any Access documentation that refers to the system auto-populating fields.

I'm assuming without the lookup established - an auto-population feature like this would need to be done by coding?

Hi. I think you or I are confused a little bit here. I don't think creating a lookup field auto creates any link, and the query you posted does not auto-populate any fields either. You explicitly asked to view those fields from the correct table, and that's why you can see them. To bring home this point, try removing the first column from your query, and you'll see the names would still show up. It has nothing to do with the lookup field in the query. Which is another thing to clarify, the lookup field is created at the table level. It has nothing to do with any queries. Again, I think, to avoid all these confusions, we probably should just ignore lookup fields for the moment. Once more, links or relationships between tables are created on the backend using the Relationship Window. Creating lookup fields simply gives you a way to display related data from the same record at the table level, which of course, could translate to other objects as well like queries, forms, or reports, which would then add to the confusion. Did you try to export your query to Excel like I asked? Just curious...

 

[got_access:]]

You've got me convinced about not using lookups. So If that is the reason for extracting data to excel - I understand your thinking and don't need to do that.

But I've never heard of this feature where the system will auto-populate fields.
And I was curious about what its really called.

There is a youtube video showing an auto-population if you care to look at it.
With no indication it has anything to do with a lookup.

https://www.youtube.com/watch?v=bqh3Q7re2a4

 

[June7]

Another vote for no lookups in table, whether built manually or with Lookup wizard - don't use them. Especially if alias is involved.

Build combo and list boxes on form.

Your form RecordSource is simply a query that includes two tables joined on key fields. This allows retrieval of related data. That is all that YouTube video is demonstrating. Then on your form, textboxes are bound to fields of the Customers table and display the data from that table. This is often how data is retrieved for presentation on report. Has nothing to do with having or not having lookup field in Orders. Should set the Customers textboxes as Locked Yes and TabStop No to prevent editing.

An alternate approach is to remove tblCustomers from the RecordSource. Then use expression in textbox referencing column index of combobox to display customer info. Index begins with 0 so if customer name is in column 2, index is 1.
=[cboCustomer].[Column](1)

In either case, when a customer is selected in combobox, related info will then be available for display because of linking keys. (Or in case of the video, ID is typed into foreign key field in query, lookup/combobox dropdown is not even involved.)

 

[theDBguy]

You've got me convinced about not using lookups. So If that is the reason for extracting data to excel - I understand your thinking and don't need to do that.

But I've never heard of this feature where the system will auto-populate fields.
And I was curious about what its really called.

There is a youtube video showing an auto-population if you care to look at it.
With no indication it has anything to do with a lookup.

https://www.youtube.com/watch?v=bqh3Q7re2a4

Oh, alright, more confusions, I think. I watched the video and really didn't understand what the author is saying but I think I get the idea. Unfortunately, I am not sure I would agree to calling it "autolookup." Besides, it's another recommended "best practice" not to let users work directly with queries. However, if you'll notice, what the video showed is not the same as the one you were asking about. I don't think the video used a lookup field like you did. If you replace your lookup field with a regular field, it would still show the correct names as before. And this is what I was saying just causing confusion. The two topics are separate and not related to each other but you are somehow connecting them in your mind. I'd say forget auto lookup and the lookup fields for now. Later, once you understand more about the rest of Access, maybe you can go back to this topic and better understand it as well.


PS. By the way, there are plenty of "features" or approaches to automatically populate other fields. But, we always recommend using forms to do it.

 

[got_access:]]

Yup!
I totally agree!
Someone told me that that feature was predicated on the lookup being established.
However, I just did my own experiment and built a few new tables with similar data and then tried different variants to fine out which one would be needed for the system to auto-populate fields.

A lookup was definitely not needed. But (given a parent/child and one-to-many relationship) link between the Customer table and the Orders table. Where the tblCustomer.CustomerID (primary key) is linked to tblOrders.CustomerID (foreign key) - that did the trick.

It appears to be a feature of the query in datasheet mode. And you're right its not really performing an insert of those fields into a table. The only data value that is inserted is the customerID value inserted by the user. The other fields are simply being displayed as the results of the query - kind of like an auto-refresh - because that link is established. My curiosity was getting the better of me - so I had to find out what it was going on there.

But you're absolutely right - users should not be given access to a query or a table for data-entry. They should be limited to forms and reports as a rule of risk-assessment.

Huge thanks for taking the time on this for me!!

 

[theDBguy]

Yup!
I totally agree!
Someone told me that that feature was predicated on the lookup being established.
However, I just did my own experiment and built a few new tables with similar data and then tried different variants to fine out which one would be needed for the system to auto-populate fields.

A lookup was definitely not needed. But (given a parent/child and one-to-many relationship) link between the Customer table and the Orders table. Where the tblCustomer.CustomerID (primary key) is linked to tblOrders.CustomerID (foreign key) - that did the trick.

It appears to be a feature of the query in datasheet mode. And you're right its not really performing an insert of those fields into a table. The only data value that is inserted is the customerID value inserted by the user. The other fields are simply being displayed as the results of the query - kind of like an auto-refresh - because that link is established. My curiosity was getting the better of me - so I had to find out what it was going on there.

But you're absolutely right - users should not be given access to a query or a table for data-entry. They should be limited to forms and reports as a rule of risk-assessment.

Huge thanks for taking the time on this for me!!

Hi. Good discussions all around. I just hope no one else gets confused when they read this thread. Access offers a lot of ways to try to help the user, and as Dave said earlier, using them doesn't mean the end of the world. It's just some of them tend to include "surprises" if the user is not aware of them. So, the common advice is to avoid them until you get a better grasp of what they can do and are prepared to handle the included consequences. For now, I say focus on your project and try to build it using only best practice suggestions. Once it's working as it's supposed to, you are then free to tweak it any which way you see fit (or willing to experiment with). Good luck!

 

[June7]

Doesn't matter if form is datasheet, single, or continuous.