How to deal with multi-value fields?

I'm exporting data from Lotus Notes into tab-delimited format that can be imported into Access

This is great for single value fields but in multi-value fields, queries treat the contents as a single value, i.e. "value1 value2 value3" instead of "value1", "value2", "value3"

is there any way to specify a delimiter to designate separate values within a field when doing Access queries?

-MC
PS I know nothing about Access programming

I guess I don't quite understand what you are asking. Access doesn't perfrom queries off of text files, only off of Tables and thus you wouldn't specify a delimiter in a query. And when you import text files into an access table it walks you through the process of using tabs as your delimiter and thus would already be seperate values, if they come as seperate fields from Lotus Notes

Please clarify what exactly is the problem.

S-

From Notes I export/produce a tab delimited text file

e.g.

value1<tab>value2<tab>value3 value4 value5<tab>etc.

value1 is in FieldA and value2 is in FieldB

value3 value4 value5 are three entries contained within a Notes multi-value field (field that accepts multiple values/entries) which
we'll call FieldC

I import the tab delimited text file first into Excel and from Excel I copy/paste it into a predefined MS Access table

The problem is that when I perform MS Access queries on FieldC, it considers the value in FieldC to be "value3 value4 value5" (all one string) rather than "value3", "value4", "value5" (separate entries within the same field)

any ideas?

-MC

Is there a way to logically seperate the three values in field C

For example: each value is only 4 characters each
3485ijth09iu -->3485 ijth 09iu
OR
space is used to seperate each value
3485 ijth*09iu
Etc

If not, then there is nothing you can do in Access. This is a compatablity problem with Notes. You would be better off finding out is Lotus Notes can seperate these values in to seperate fields before exporting to a txt file.

Food for thought. It takes about the same effort to import into Access as it does for Excel. IF you are going to do this a lot, just by pass using Excel

S-

it could be a space or a semicolon or something else but there is a way to determine the distinct values

thanks,

-MC

The the distinction is the same for every record then why don't you post some sample data and lets see if we can help you out.

S-

Field1 | Field2 | Field3 | Field4
a | b | c d e | f
g | h | i j k | l
m | n | o p q | r

Fields 1, 2, & 4 are single value, if I were to query Field1, I'd get:
a
g
m

Field3 is multi value (space delimited), if I were to query Field3, I'd get:
c d e
i j k
o p q

but I'd want to get:
c
d
e
i
j
k
o
p
q

Hope this clears things up

-MC

Okay

You have two options

1.
If you try to seperate it in a MS Query the best you can get is the following

table
Field1,Field2,Field3,Field 4
34,rt,5 7 9,rt

Query
Field1,Field2, Field3, Field4, Field5, Field6
34,rt,5,7,9,rt

Not possible in a query
Field1,Field2,Field3,Field 4
34,rt,5,rt
34,rt,7,rt
34,rt,9,rt

OR
2. Write VB code (either in or outside Access) to give you

table1
Field1,Field2,Field3,Field 4
34,rt,5 7 9,rt

table1New
Field1,Field2,Field3,Field 4
34,rt,5,rt
34,rt,7,rt
34,rt,9,rt


You would pull the dataout into variables, seperate the field3, and add the new records to a new table

serperation Syntax for 2.

Value1 = left(OrginalFieldAll,Insrt(1,OrginalFieldAll," ")-1)

OrginalFieldright2 = Mid(orginalfield,Insrt(1,OrginalField," ")+1)

Value2 = left(OrginalFieldright2 ,Insrt(1,OrginalFieldright2 ," ")-1)

OrginalFieldright1 = Mid(OrginalFieldright2 ,Insrt(1,OrginalFieldright2 ," ")+1)

value3 = trim(OrginalFieldright1)




The syntax for 1. does something similar to the above, but done as columns in the query and not as variables

Hope this helps

S-