Importing Multivalues into Access 2007

[Thomas]

Hi

I am not an IT professional and am new to Access. I would like to import
multivalues from Excel to Access. I understand that Microsoft states
(http://office.microsoft.com/en-us/access/HA012194191033.aspx)

"Access does not enable support for multiple values in a field, even if the
source column contains a list of values separated by semicolon (. The list
of values will be treated as a single value, and placed in a text field"

Does anyone know a workaround to this problem, and might explain this in a
way that I might understand?

Thanks

 

[dan dungan]

Hi,

It would help if you provided a sample of the data.

Dan

 

[Thomas]

Hi,

It would help if you provided a sample of the data.

Dan

Hi Dan
The structure follows this:

TableBoatTrp: Trp-ID, Date, Crew (multivalue lookup field)
TablePersons: PrsnID, Name

Within the Crew field I have values per field such as 1;2;3 or 4;9;92

Cheers...

 

[Albert D. Kallal]

Does anyone know a workaround to this problem, and might explain this in a
way that I might understand?

Sure, I can help a bit here.

The first thing to keep in mind is MS access has what's called a multi value
data type field. You should not confuse this data type with someone placing
some text values that are delimited by ";" or "," like you have an excel.

You can most certainly import that data into MS access, but you won't be
importing it to an multi value field. soda clarify, does nothing inherent
in MS access that prevents you from importing this data into a field.

what is confusing here however is, ensuring values into a field separated by
commas or; SP is absolutely no use to anyone, and in fact breaks all the
rules of database management systems.

I mean why enter more than one record into MS access then. Why not have one
big reocrd, and enter a thousand names, but we enter the 1000's names into
ONE reocrd, and we seperte the names, address etc for each name by a ";" for
exmple.

let's assume that we have a database and we enter your name in it and your
favorite colors

fields:
FirstName: Albert
LastName: Kallal
FavColors: red, green, blue, sky blue

In the above I decided to staff my 4 favorite colors into that one field.
The problem is is how will I search for who favorate color is blue?

I can't stress in the above that the above is not a multi value field in the
database definition term that applies to MS access.

Wtih the above, I can build a query that goes:

select * from tblCustomer where FavColor = "blue"

as you can see the above will not work, because I've messed up the favor
colors field, and now we cannot search and sort by persons favotrae colors.
(you can't sort on that above field...it makes no sense to do so).

Furthermore, when you stuff a bunch of values into one field, then you can
not build summary ports, or even get stats on how many people have their
favotre color as blue. Once again I can't stress that there's a huge
difference between the internal defined multi value fields in MS access, and
a simple field with a bunch of values separated by some space or whatever.
While you would say that that field has multiple values in it, it's not a
multi value field in the MS access terms.

It's not clear if you've actually truly defined a multi value field in MS
access here or not. Sicne your actually importing data, then I would suggest
that you don't use multi values in MS access, but use correct database
designs here, and build a table to hold the values, and relate it back to
you main table.

What this means you'll have to import the data that you're getting
incorrectly from excel, pull into a simple table in MS access, and then
write some code to take those values split them out into another table that
is related back to the main table.

There is absoulte nothing stopping you from importing that excel data as you
please. You simply wind up with a text field (string) that is separated by
a comma or space (or semicolon). There's nothing stopping you from doing
that import, it's just that a field with a bunch of values in a separated by
spaces, comma, or ";" is pretty much useless (and htis type of field is NOT
a multi-valued field in ms-access terms either).

I suspect the confusion here stems from the fact that you have of a cell in
excel that has multiple values in it, but that's not the same terminology in
MS access as what a multi-value data type is....

 

[Thomas]

Hi Albert

I actually do want a multivalue field for the variable "Crew". The Access
database I made has them and works fine, I just have to tick the names I want
in the lookup table and they are inserted into the field. I can also use
queries, say to find out all trips with John onboard. The problem is that my
database was formerly in Excel (you are right, in Excel this is just a text
field wthout much use except for record keeping) and I cannot import the
values to Access.
Meanwhile I did, however, find an unelegant solution to the problem. Just
insert two rows into the Excell column, in the 1st write SEARCH#, in the 2nd
the variable name CREW, and paste the lot into the Access variable. The
result is two lines off, but you can correct that in Access. But this is not
a very elegant solution.

Cheers

 

[Albert D. Kallal]

Ah, ok...now that you clarified this.....

Ok, here what to do:

Import the data into your main table, or a temp table....

You need that "extra field" with the text data separated by ","

Then, we write some code. I will look like:

Dim rst As DAO.Recordset
Dim rstMV As DAO.Recordset
dim vBuf as Varient

Set rst = CurrentDb.OpenRecordset("contacts1")

Do While rst.EOF = False

if isnull(rst!MyExtraDataField) = false then
set rstMV = rst!MyRealMultiValueField.Value
vbuf = split(rst!MyExtraDataField,",")
for i = 0 to ubound(vbuf)
rstMV.addNew
rstMV.Value = vbuf(i)
rstmv.update
next i
end if
rst.MoveNext
Loop
rst.Close

The above code does not empty out the data field, but, that could be
easily added to the above, or you run a separate query.

The above is "air" code, but it shows how you can use code to add values to
a multi-value field.

Note that if the values in your excel cell/field have to be converted into a
"id", then we have to add a extra step in the above (eg: do you store the
text "red" in the above, or does it translate to a table with id and red?
(thus, we have to convert the "red" to a number before we set the .value in
the above)