It's my first time using Access, and I have my first of what I assume will be many questions. Yay!

So I imported some data from Excel to a table, including a column with a comma separated tagging system (let's say x, y, z). After some poking around, I went to design view and did the following in the Lookup tab for this "tagging" field:

Lookup tap settings

What happened: entries with just an x, y, or z tag worked. When I click the drop-down arrow for a given entry, I see x, y, and z as choices with the correct one selected. For entries with multiple tags though, it shows up as a new box to be checked. So if I have x, z in the cell, there's a x, y, z, and x, z selection in the drop-down box.

So my question is, how can I make the boxes with multiple tags work correctly?

  • What does "correctly" mean? Do you want the dropdown to have every combination? Do you not want a dropdown at all? Not sure what your desired outcome is. Please elaborate. – paulbarbin Jan 21 '16 at 14:48
  • I do want a dropdown, and I want it to have the correct boxes selected automatically out of the options x, y, and z. If I have x, z in a cell right now, instead of the options of the dropdown box being x, y, z, and x,z with x,z being ticked, I want the "x" and "z" boxes to be ticked. – g_raham Jan 21 '16 at 14:57
  • Sorry, I was hoping to help without having a bunch of knowledge in Access. I don't currently have it installed or I'd play with it and see if I could solve it. – paulbarbin Jan 21 '16 at 15:13
  • Have you tried changing the Allow Value List Edits to No? That might do it. – paulbarbin Jan 21 '16 at 15:14

I think your best bet would be to use a many to many relationship. Generally this is done with a table for the records (like you have now), a table for the comma delimited field and a primary key field with rows for each of the entries (one for x, one for y and one for z) and a third table with nothing but that keys from the other 2 tables.

So, in your join table, you might have a section of 3 rows with the same key on the main table but different entries for the reference table. This link explains it reasonably well.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.