Creating comboboxes in split database

[jaryszek]

Hi Guys,

I have split database and i want to have in my front-end comboboxes with drop down lists.

And there will be a lot of columns to put into comboboxes.

So there will be for example Topic, Number, Priority and about 10 more.
How is the best to organize it?

Make one big dictionary table with all columns and simply adding or removing fields?

Or make a lot of small tables as dictionaries?

In excel there are a lot of lists in one sheet like here:

But in access i have to do in other way, any suggestions?

Please help,
Best Wishes,
Jacek Antek

 

[CJ_London]

in access it depends on how the lists are to be used. You would normally have an autonumber PK field which is what your combo would actually store and a field for the value - these are often called lookup tables.

You can do it either way. Each of your lists could be in a separate table, or you can have one table with two fields (plus the PK field). The first field has a value to indicate the type of list - Topic, Number, Priority etc and the value in the second field.

Which is better depends on your design. A single table involves more work in needing to filter it for the required type of list, whilst the multiple list can involve more work in maintaining multiple tables. I use both methods depending on requirements.

 

[Ranman256]

Combobox is irrelevant to a split db.
The combo pulls from a table/query list. Normally you only need 1 column. 2 possibly.

You could get complicated by using a cascading combo that alters the combo list depending on another combo.

 

[jaryszek]

Thank you Guys for your help,

Ranman256 i have to do friendly-user form in Access. What do you propose instead of comboboxes?

in access it depends on how the lists are to be used. You would normally have an autonumber PK field which is what your combo would actually store and a field for the value - these are often called lookup tables.

Could you explain more this CJ_London?

The first field has a value to indicate the type of list - Topic, Number, Priority etc and the value in the second field.

You mean Table like this:
PK Type of list Value
1 Topic "Topic1"
2 Topic "topic2"
3 Topic "topic3"
4 Priority 1
5 Priority 2
6 Number 100

Thank you in advance for your help!

Jacek

 

[CJ_London]

for a single table yes - but I would change type of list to a number - they index quicker

 

[jaryszek]

ok thanks CJ_London.

But i have to know that number 1 will be a Topic, number 2 will be the Priority etc.
How access will know that 1 is a Topic etc.?

Sorry for this beginner question...

Jacek

 

[CJ_London]

you know because you give it the number - keep a note somewhere, the user will never see them anyway. Or you can do something like this

PK..ListType..ListValue
1....0............Topic
2....0............Priority
3....0............Number
4....1............Topic1
5....1............topic2
6....1............topic3
7....2............1
8....2............2
9....3............100

where you include the listtypes in the table (as listtype=0 in this example)

However I note in your original post, all your list values were text. In your next example you have numbers. You cannot mix text and numbers in the same field - although there are ways to convert text to numbers.

So it may be in this case better to have separate tables for each list

 

[jaryszek]

Thank you CJ_London.

I don't know how to create the combobox connected with our table yet. But i will try to find it out .

Second issue:

Why this method like in the picture can not be done? I can assing all dictionaries into columns and make comboboxes based on them.

I can use select statement with where clause and specify which field is not equal to null.

Jacek

 

[CJ_London]

you can do it that way but be aware that Access is not a bigger Excel. It is a completely different application and manages data in a different way. If you try to use excel methods in access you will almost certainly fail or at best have a high maintenance and inefficient application.

In simple terms, Excel stores data 'wide and short'. Access stores data 'tall and thin'

 

[The_Doc_Man]

I don't know how to create the combobox connected with our table yet. But i will try to find it out .

Nothing could be easier. If you have the Access wizards installed and enabled (which they normally are, because that is the default), you just create the combo box. It will offer you a couple of choices for the KIND of combo box you want, and then will inquire as to the source of your data. The answer to that question is where you specify the source "translation table" that you are going to use. Other options in that wizard dialog will offer you the option to show one value (perhaps in text format) but to store a different value (perhaps a number to be associated with that text). So you CAN choose a name but store a number.

IF you are going to use the wizard, I would very strongly suggest that a few short translation tables would be easier than one larger table with an extra filtering field because the "one big table" approach will require some work in VBA to adjust a filter to select the type of filtration.

Using separate tables? No filter and the wizards work right with no deviation from the "standard" way of doing things. This makes the form more likely to be user-friendly, not to mention being faster to set up.

Take it as one man's opinion.

 

[jaryszek]

Thank you Guys for your help very much.

1. Ok so better approach is to do a few translation tables and use them as source for combobox.
Even if i have more then 10 different dictionaries, even 15 or 20?
It is better to have a lot of small tables?

I have attached an example database. Sorry Guys but i have to know how to do it in the best way.

In attached database there are two translation tables: one with Topic list and one with Priority List. In Form1 I have done combobox connected to it.
In these tables there are only ID(autonumber) and one main column (Topic, Priority).
Is this the attituide we are talking about?

2. One more thing - i would like to do one big table approach as comboboxes source table (only to find out how to do this) so i prepared table: One Big Table approach and One Big Table form connected to this.
I want to have the same result here as in the Form1. So two comboboxes (with Priority and Topic) but from one source table. It is possible to do it without VBA?

Thank you for your help and support once again!

Best Wishes,
Jacek Antek

 

[The_Doc_Man]

It is better to have a lot of small tables?

When they are "translation" tables (number to code or code to number), definitely YES, particularly because such tables will almost be "set it and forget it" type, with minimal maintenance needs. But also, particularly if the tables are short and/or have only two or at most three columns, you are VERY likely to be able to fit the whole table in only one or two disk buffers (which is the way that Access wants to physically read things). If so, those small tables will give you VERY good odds of very fast lookups.

By contrast, if you have a merged table with 15 or 20 contributing separate translation sets residing in that table and distinguished only by a filter value, Access will have to scan through the whole table looking for the selecting filtering value, and the odds of the whole table being in a single disk buffer become much smaller.

So to the "is it better?" question, ... multiple small non-overlapping tables is MUCH better than one big merged table, performance wise. And also concept-wise, it is easy. Having a single code number (autonumber or natural number or heck, even a randomly selected number) plus a string name for that code is the perfect layout for this kind of thing.

To your #2 question, you can do what you want without VBA but it becomes trickier because you must manually modify the .Rowsource of the combo boxes to select different filter values to keep the two implied sub-tables from overlapping in the box. The wizard won't help much in this case because of something it does for combo boxes. It will synthesize a query with the tilde (~) as the first character of the name. The query will be named as the .RowSource. You will have to enable the visibility of hidden objects because these "virtual" queries are definitely hidden. You could then find the name of the query and edit it. OR you could right-click on the .RowSource property box to get a drop-down that includes ZOOM as an option. That would allow you to edit the query, but don't forget to save it.

 

[jaryszek]

Thank you The_Doc_Man very much !

I understand now why to use a lot of small tables insted of one big one.

1. To end the topic: please confirm if my attitude in the attached example is ok, should i use maybe different columns in my small tables? Maybe some PK? If yes, how to do this?

2. I had done it - simple i used SQL statement with where clause - this is easy.

Thank you for your help,
Jacek Antek

 

[jaryszek]

Hi Guys,

how are you ?

I have brilliant idea. The idea of creating 20 translation tables to my database is also connected with user-friendly form for changing data.

So i think that I can do one excel file with dictionaries - one for each sheet.
After that i will link this tables into Access. And thanks to it User can make changes in each table even to more then one record (multiple records also!),

What do you think?

Jacek Antek

 

[CJ_London]

access cannot edit data in excel using linked tables

 

[jaryszek]

Thank you CJ_London. But user can edit data in Excel and automatically this data will be uptaded in Access.

Jacek

 

[CJ_London]

true - but not sure whether user can edit it if access has the workbook open - you'll need to test it and see

 

[jaryszek]

I will find it CJ_London

thank you !!

Jacek