Importing Excel into multivalue

[roccogrand]

How you import values from Excel into a multivalue field. I tried using
commas to separate the values.

Thx.

LDN

 

[Arvin Meyer [MVP]]

How you import values from Excel into a multivalue field. I tried using
commas to separate the values.

Multivalue fields are the Devil's spawn. Use them only if you are going to
create lists in SharePoint from an Access database. Other than that they
have no purpose and are the antithesis of good database design.

If your values are already in Excel, you should import them into a plain
text field large enough to handle the longest value, then use a parsing
routine to separate them into values in a separate table that's been
designed as the many-side table in a one to many relationship. Here's a
parsing function that will allow you to remove 1 element at a time. Use it
to extract the Key field and each element of the multivalue field:

Function ParseIt(strfrom As Variant, cntr As Integer, sep As String) As
Variant
On Error Resume Next

Dim intpos As Integer
Dim intpos1 As Integer
Dim intcount As Integer

intpos = 0
For intcount = 0 To cntr - 1
intpos1 = InStr(intpos + 1, strfrom, sep)
If intpos1 = 0 Then
intpos1 = Len(strfrom) + 1
End If
If intcount <> cntr - 1 Then
intpos = intpos1
End If
Next intcount
If intpos1 > intpos Then
ParseIt = Mid(strfrom, intpos + 1, intpos1 - intpos - 1)
Else
ParseIt = Null
End If
End Function

 

[roccogrand]

Thanks Arvin,

If the valid values are set to being three characters long and are comma
separated, then can I modify your parsing routine and load the values into
the .Value field of a multivalue record. I would have to duplicate the other
fields to maintain the multivalue functionality of the table.

Will that work? I really don't want to loose the multivalue functionality
that I have built into the application.

Thanks again.

LDN

 

[Arvin Meyer [MVP]]

Now modifications are necessary. So here's how you use the query to separate
the multi-value. In a new query, you would add the key and a columns with
the function:

ParseIt([MultiFieldName], 1, ",")

ParseIt([MultiFieldName], 2, ",")

ParseIt([MultiFieldName], 3, ",")

etc.

Add as many columns as you have maximum number of values. In other words, if
you have A, B, C, D you would add 4 columns, 1 for each value in the set.

The return for the query will look like:

ID Expr1 Expr2 Expr3 Expr4
1 A
2 A B C D
3 A B

You then run an append query to the new many-side table for each of the
columns. The final records in the new table will look like:

ID NewColumnName
1 A
2 A
2 B
2 C
2 D
3 A
3 B

Once that's done, create the relationship between the original table and the
new one on the Key (ID) column, test it to see if a query will give you
results from both, then delete your multi-valued column from the original
table.

Yes, you do want to lose the multi-value "functionality". You can always run
code and put it back if you use SharePoint, which is the ONLY possible good
reason to use muti-values. You will find that while it may seem easier to
just add a comma and a new value, that you are asking for trouble.

1. You will have numerous records where the user forgot to add a separator.
2. You may not be able to search.
3. If you can search, your searches will take many times longer.
4. You will never be able to upsize or change back-ends.
5. You will get unexpected results from queries.

It may not matter to you, but no really good professional programmer or
developer will ever want to work on your database.

--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

 

[roccogrand]

Thanks Arvin. I will study these instructions and implement it in my
database.

BTW, I am not concerned about someone else modifying the db. It only
contains one main table and the data has a shelf life of about two months
maximum.

As for your point number 1, this is not an issue. The multivalue
functionality built into Access automatically adds the commas.

As for point number two, Access has had no problem doing searches on
multivalue fields in the way that I implement them. We argued about
constructing lookup fields at our local users group meeting a couple months
bac and my way ensures successful searches because I don't use autonumber
fields in my lookups. Other developers do use them. Like you said in point
2 they can cause problems. In fact, I trashed the first version of the db
because of this.

As for point 3, my 1.6 Ghz laptop is the least powered machine that will use
the db. The searches fly on it now that I have removed a few pesky programs
and reinstalled Vista.

Point 4 is well taken. I know no one using SQL Server or another enterprise
database so I am OK for now. I have another similar app that is much more
sophisticated than this one and I will be very careful migrating it to Access
2007.

I have observed no problems such as Point 5. All 119 queries that I have
created in the past week work fine, especially since I eliminated the
autonumber fields from the lookups.

I do appreciate your comments and will use multivalue fields only when
absolutely necessary.

LDN

Now modifications are necessary. So here's how you use the query to separate
the multi-value. In a new query, you would add the key and a columns with
the function:

ParseIt([MultiFieldName], 1, ",")

ParseIt([MultiFieldName], 2, ",")

ParseIt([MultiFieldName], 3, ",")

etc.

Add as many columns as you have maximum number of values. In other words, if
you have A, B, C, D you would add 4 columns, 1 for each value in the set.

The return for the query will look like:

ID Expr1 Expr2 Expr3 Expr4
1 A
2 A B C D
3 A B

You then run an append query to the new many-side table for each of the
columns. The final records in the new table will look like:

ID NewColumnName
1 A
2 A
2 B
2 C
2 D
3 A
3 B

Once that's done, create the relationship between the original table and the
new one on the Key (ID) column, test it to see if a query will give you
results from both, then delete your multi-valued column from the original
table.

Yes, you do want to lose the multi-value "functionality". You can always run
code and put it back if you use SharePoint, which is the ONLY possible good
reason to use muti-values. You will find that while it may seem easier to
just add a comma and a new value, that you are asking for trouble.

1. You will have numerous records where the user forgot to add a separator.
2. You may not be able to search.
3. If you can search, your searches will take many times longer.
4. You will never be able to upsize or change back-ends.
5. You will get unexpected results from queries.

It may not matter to you, but no really good professional programmer or
developer will ever want to work on your database.

--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Thanks Arvin,

If the valid values are set to being three characters long and are comma
separated, then can I modify your parsing routine and load the values into
the .Value field of a multivalue record. I would have to duplicate the
other
fields to maintain the multivalue functionality of the table.

Will that work? I really don't want to loose the multivalue functionality
that I have built into the application.

Thanks again.

LDN