Question Multivalue field in a split database design with front & back end.

[Xystren]

So attempting to add in a requested feature for a membership database. Currently it is setup with the data tables on the backend, and the front-end (user interface) has the relevant forms, queries, etc.

I've been asked to add in a few other pieces of data - a primary and secondary language field (easy, just a couple short text fields, and a other language field. The other language field would work great as a multivalue field (yes, I know, not the best DB design, but I'm working within the existing database). I have the other fields select from a languages look up table to populate.

In a quick test in a non-split database, it seems to work great, when it's added on the form - the combo box presents with the checkboxes that can be selected, and OK to populate the field.

On the split design, I don't seem to have the option for selecting multiple options.

Thoughts? Work arounds? Ultimately, the end result for for the LanguageOther field, would ultimately contain something like 'English, French, Spanish, German' depending on what was selected on the front end Form.

Tia,
Xyst (Greg)

 

[Mark_]

Personally I would normalize your data to either be a single field for "Preferred Language" or a child file for "Languages".

Because of the nature of what you are doing, I'd really suggest a "Tag" file. This is a simple one to many child file that holds "Tags" for different things that come us as useful, such as additional languages, Colour preference, or what have you.

File is formatted as either
ID AutoNumber
Parent ID Intiger
Tag Value Text

OR
ID AutoNumber
Parent ID Intiger
Tag Value Text
Tag Value Text

Difference is how often you need to use these. Think of them as the "Sticky Notes" you find on physical files. Odds and ends that are relevant but highly variable in nature.

Often they are not relevant but are useful for creating lists. You seldom check one person's record to see if they speak an obscure language but you can be asked for a list of speakers.

I've found tag reports immensely helpful in the past. Often you have a small hand full of records that would need a given "Tag" and you don't really want to add a field to your parent file for it. If this sounds like what you are after I'd really suggest adding it rather than using a multi-value field.

 

[Pat Hartman]

You don't say whether your BE is ACE or SQL Server. I presume ACE because SQL Server doesn't support MV fields.

I wouldn't use them. They look pretty and have a nice interface but they cannot be upsized and even if upsizing would never be required, you have to learn new SQL and VBA to work with them. They are not worth the trouble.

Go with the normal m-m relationship with a lookup table to hold the range of values and a junction table to connect the two tables.

 

[Xystren]

Thanks for the thoughts...

I ended up running into another problem and in the process of figuring that one out, I completed a rebuild of the back-end and reloaded the data back into it. and the tried it again, and it worked as expected - previously it wouldn't when building the query, it gave an expanded view on that field when adding it to the query. So it ended up working.

The whole database need to be rebuilt and redesigned, but that takes time, and time is not what I have access to with the other projects I have going on. So yes, long term would like to redesign, using full normalization and other best practices.

Thanks for the responses and suggestions.
Cheers,
Xystren (Greg)