Designing a table - Lookup wizard quick question

[105ben]

Hello,

I'm creating a database for where I work. Basically, there is a staff table and a staff_role table.

There will be predefined staff roles, each with an ID for example 1 team member, 2 manager, and so on

When a new staff member is then added, I have set up a lookup wizard column so that the user can just pick the name from the drop down.

Just wondering - how/where is the data in the drop down stored? Is it just referenced then pulled from the staff_role table when needed, or copied and stored twice (once in each table)

I'm asking this, as another part of the database which I havent created yet is going to use the lookup wizard too, but this part may use up a lot of space if the data is stored in both tables!

Thanks in advance for any help!

 

[StarGrabber]

Welcome in the forum, 105ben!

To answer your question: they are copied and stored twice. Normally you only store the ID (as the bound column) in the staff members table. But... sorry for saying it, you should not use any lookup wizard columns. In order to get a proper table structure which - as the database grows (i.e. more tables) and gets more and more complex - remains understandable, you should create a relationship between these tables. The role ID (primary key in roles table) serves as foreign key in the staff members table.

Picking items from a drop down list is something for forms not for tables, even if Access makes you believe.

 

[105ben]

Thanks StarGrabber, and cheers for you're reply!

OK, I'm an amateur with Access as I'm sure you can tell but trying to learn.

I have created relationships - how do I add the role ID as a foreign key in the table?

and then in my data entry form, how do I get it to present the role_name rather than the ID? As I wont expect the users to memorize the role ID..

 

[StarGrabber]

Have a look...

 

[StarGrabber]

I always use value lists (up to appr. 500 items) because my user interfaces aren't (directly) bound to any data backend! The combos are filled by the AddItem method. I say this to you, Pat, because I consider you as an expert. For beginners (and small size applications) a table bound combobox is surely the best choice.

But... thank you for explaining the basics.

 

[105ben]

Thanks for the example, but how do you get the combo to just show the role name when you're source code:

SELECT tblStaffRoles.ID, tblStaffRoles.Role FROM tblStaffRoles ORDER BY tblStaffRoles.Role;

includes both id and role in the select??

And also, how did you get the staff table to show the role name, yet store the role ID??

Sorry to ask more Q's, but I want to understand it rather than just steal your example

 

[StarGrabber]

No problem.

I got the combo to just show the role name by setting the columnwidths property to 0, a not really logic Access trick. Normally you do it as explained by Pat, you set the ID column to 0 only (example for two columns: 0in;1,532in). If you have a combobox with 3, 4 or more columns you have to determine the width for each column.

Regarding the staff table I have to apologise, I forgot to remove the "lookup wizard column" I had first. At the beginning I preferred to reproduce your situation because I never use such columns. I've attached the corrected accdb version.

Don't hesitate to post new questions.

 

[105ben]

Ok then, I think I'm on the right track now!

How did you get your form in that format though? Mine's full screen and I can't work out where to change it?

 

[105ben]

Looks like I've done it, set Pop Up to yes. Is that right way to do it?

 

[StarGrabber]

No, go to the file tab, click Options, go to Current Database. In the first section you should see under Window Options some buttons. Click the option Overlapping Windows. I hope you'll find it because I'm not working with the english Access version. Afterwards you are asked to restart your application.

Use Pop Up only when you really need it.

 

[105ben]

Ok! All working now I think...

Thanks again, appreciated!

 

[StarGrabber]

You are welcome.

 

[StarGrabber]

Pat, your questions are actually entitled. But the answers are quite simple.

First of all, I'm hard-coding nothing. Neither I load the value list from a table, I load it from a collection (of class objects). Why is that?? Imagine the following: you want to fill a combobox depending on the user rights (or other constraints). How would you arrange that? Would you put various queries onto your (server-) backend for the same thing? In bigger (business-) applications I aspire to avoid that.

Write code? Yes! In the projects I'm involved (appr. 20,000 to 100,000 code lines) there is one single procedure which fills all the combos. This procedure is contained in a module which has one public routine with two ByRef parameters: the combobox and the collection. Depending on the type of objects in the collection (derived from the number of combo columns), this routine delegates the fill work to private procedures in the same module. A lot of code? Yes, but it is reusable! - Maybe you know something about the Single Responsibility principle, the Separation Of Concerns principle and some other programming principles. I don't have to tell you that most of the applications all over the world tend to grow over the years. Following these principles you gain scalable, maintainable and - even more important - flexible applications.

Now you surely want to know where the collections come from. They are created in the entity corresponding business layer module of the application (this is the location where it's decided which data find their way into the collection). There the data where requested from the data access layer. Finally, this layer pulls the data from the database. - Even more code??!? Of course, but mostly reusable.

You are right, Access provides a lot of things natively. The question is if you really can take some benefit of them. I just described a scenario in which you can't.

To make things clear, as I said in post # 6, I do not recommend such an approach to small applications (or to beginners) but after you've said to 105ben that you rarely use value lists I wanted to point out one of their advantages.