I have been trying for the past several hours to get this work but for some reason I am not getting the same options that are shown on Microsoft Office's website (i.e. the tutorial) on multivalued fields. Supposedly, as per Microsoft, there is an option when you select "Lookup Column" in Datasheet View that asks you whether or not you want to store multiple values in that particular field; however I am using Microsoft Access 2007 and I am getting no such option. Are multivalued fields really this complicated/tricky? I am building a software for my HCM (i.e. that is HR to some of you) Department and when an employee can speak multiple languages the same needs to be marked and recorded in the system. How can I go about doing this?
Multivalued Fields
- Thread starter zyousafi
- Start date
boblarson
Smeghead
- Local time
- Today, 13:10
- Joined
- Jan 12, 2001
- Messages
- 32,040
I prefer to avoid multi-valued fields simply because they make it more difficult to work around than just having your own table which stores the values. Yes, it makes for a nice interface, but when you start needing to do things with the data it can become a pain.
When you use a multi-valued field you need to select that as the datatype. It then stores the data as a variant. Also, you really should avoid using the lookups directly at table level (outside of the multi-valued fields).
When you use a multi-valued field you need to select that as the datatype. It then stores the data as a variant. Also, you really should avoid using the lookups directly at table level (outside of the multi-valued fields).
- Local time
- Tomorrow, 08:10
- Joined
- Jan 20, 2009
- Messages
- 12,832
You won't get the option if you are are using an mdb database.
I also agree wholeheartedly with Bob and most professional developers. Use a related table.
Not only are multivalue fields more difficult to query and particlarly hard to manage in code they are not available in any other database format. So when you eventually need to upsize to a server backend you would have to redesign tables forms and reports.
Moreover most HR databases are held on a server backend because Access simply does not support the sort of security expected for the privacy considerations of HR.
I also agree wholeheartedly with Bob and most professional developers. Use a related table.
Not only are multivalue fields more difficult to query and particlarly hard to manage in code they are not available in any other database format. So when you eventually need to upsize to a server backend you would have to redesign tables forms and reports.
Moreover most HR databases are held on a server backend because Access simply does not support the sort of security expected for the privacy considerations of HR.
