Displaying Multiple Values, Storing One.

[greatfallz]

Hi guys,

So I've been pretty stuck with a little hurdle of mine.

Basically the example.jpg shows the basics of a form I've been working on. Essentially the user has to pick, multiple values from the duty table which are then stored as a single value in the main table.



Now the information stored, is just the description (ie. Financial Reports), but because of the way the data was originally gathered all these descriptions were assigned a code (ie. D1). Users need to be able to select these codes, but the database needs the descriptions.

I'm thinking auto populate would be the best way to tackle this problem, but my skills with Access are limited and I haven't been able to figure it out. Any ideas?

Cheers!

[rpeare]

I don't understand, let's say a person selects d1, d2, and d3. On your table this is stored as a text field that says something like:

Financial Reports, Budget, Financial Plans

but when you bring up the record for that person you want it to repopulate that list box at the bottom with the appropriate check mark?

[greatfallz]

Sorry for not being clear.

I just need it to store, financial reports, budget, etc.

The list does not have to be repopulated, the attachment was just to give an idea of what the form looked like.

Thanks.

[rpeare]

So the place where you select the D items is a list box, you are able to highlight (select) items in that list box and you want to store the items selected in a table and retrieve them when you open the record again?

[greatfallz]

Yep that's pretty much it.

Essentially the user selects D1, D2, D3, Access should store Financial Reports, Budget, Financial Plans in the main table. Currently, however, its attempting to store 'D1 Financial Reports, D2 Budget' etc. This violates the one to many relationship between the Duties table and the Main table, however.

I know this is because in the SQL statement, I've asked it to retrieve both sets of values. I'm just looking for a way to still show both sets of values, but only store the latter (ie. Financial Reports).

Hope that clears up the confusion!

[rpeare]

I've got to say that this is not really what list boxes are meant for. List boxes are far more better used for examining or changing already existing data. Secondly you should not have one field in a table that is tracking all of their D reports. You should have a subtable and in the subtable store the foreign key of the first table and store the report types the person needs/has access to. Do you have any control of the structure of the database or are you strictly dealing with something that's been given to you?

Additionally, let's say you store 'financial reports' in your table, but on your report type table (that stores the D value and the report name where I'm assuming D1, D2, D3 etc is the primary key) you change this description to 'financial reports - marketing'. Every record your database that had 'financial reports' now must be updated. If you just store the primary key and the person then you can change descriptions every 5 minutes and it won't matter, the new description will be carried through to all people.

I would really strongly recommend you change your database structure to something that is more normalized, you will find the data capturing and recovery a LOT easier than trying to repopulate a list box from a string where the string values in the table you're trying to look up may change over time.

If you have no control over the structure what you're asking can be done but it is way more complex than you want a data entry form to be and it can not be done on a bound form, it would have to be done through vb script.

[greatfallz]

Given me a lot to think about!

I have full control of the database, I admit that originally when I got given the data I didn't even consider to examine the structure and ensure the database was properly normalised.

I've recreated the database from scratch and properly structured it to normalisation rules. I'll have to add the supplied data again but that should not take longer than two days.

Ironically this also seems to have fixed my initial problem, the entry form now uses combo boxes which allow entry into the appropriate multi-valued duties field. Or would you still advise against this?

Either way, thank you so much for your input! I know you've saved me a lot of grief further along the path, when I could have potentially have been dealing with thousands of records.

[rpeare]

yes, I would still advise against storing multiple values in a single field, the only time it may be handy is if you are storing a set of keywords (basically not using the data in the field as anything but a quick reference for something else). It will complicate *everything* you try to do with that field from retrieving the data, updating it, displaying it on reports. I am a person of the path of least resistance

[greatfallz]

I see the logic in that, just to clarify that, the best way to combat this would be an associative entity?

I could create a bridging table that separately lists the duties, using a composite PK of the Duties Table and the Main table. Which creates one last question =P, is there any way to automatically convert the multivalue field to a separate table?

Thanks for your help mate, you've really helped me to recall the essential rules to database design, will next time first fix the structure!

[rpeare]

Yes you can split the existing fields but you'll have to do it with VB script and a lookup from your existing table. Attached is an example of how to do it, you'll note I provided for a lookup that fails (if you have something in your text list that no longer has a matching value in your main list)

[greatfallz]

Thank you the script doesn't work quite for me yet, (some sort of type mismatch) but i'll just research and play around with it till it works

Thanks again for all your help! Feel a little bit more confident with databases now :P