0

Currently property block and lot information is entered into our ms-access database in separate input boxes which then populate a table. One box for block and one for lot. If an application contains one block but multiple lots then the user enters the lot information separated by commas.

Like this:

Table Example

The table in the end is very messy and cannot be queried properly. Is there a way in ms-access to still have the user input the data separated by commas but to unnest the rows in the table so each block and lot has its own row?

Like this:

After Table

Krizzle
  • 21
  • 3
  • You could try multivalue fields. From what I've read they're not brilliant - they just place the multivalued parts in hidden tables. Basically what you're trying to do but hidden in the inner workings of Access (and then really hard to transfer your DB to a real system). Anyway, here's a link - [Guide to multivalued fields](https://support.office.com/en-gb/article/Guide-to-multivalued-fields-7c2fd644-3771-48e4-b6dc-6de9bebbec31) – Darren Bartrup-Cook Dec 01 '17 at 16:40
  • Split the data on the comma, loop and insert one by one. – Kostas K. Dec 01 '17 at 17:33
  • As @Kostas suggests, build normalized table structure then use VBA code to save the user CSV input to table. However, this depends on user consistency - if they forget to type a comma the whole approach goes to hell. And splitting on spaces has the same risk. As much as I don't like MVF, it would be preferable. – June7 Dec 01 '17 at 20:26

1 Answers1

0

For ease of querying later, i have never much luck with multi-valued fields, and a loop may work better separating on both commas and spaces to be sure. I would have a statement that pulled out all commas followed by spaces first ", ", then all commas alone, then look for any spaces by themselves. After that, you could provide your user with a confirmation screen if they enter more than one lot to confirm their entry. Something simple, like in your example above, would popup and state "You entered 3 Total Lots (7, 9, and 13), is this correct? And if they say 'no' then you can give them the entry form back to correct their entry.

Just a thought. Let me know if this helps. Also, if you can provide screenshots of the input form i might be able to provide further coaching.

Destin Consulting
  • 86
  • 5