Importing Data with More than One Item per Field

C

CJ

I have a database that has been setup to accept multiple answers for Field A
(combo-box form auto-populated by data in another table). To get the database
started with content, I need to dump an Excel spreadsheet into it. But
here's the issue: I'm unsure how to import multiple items into a single
field. Do I just separate the values with commas or semi-colons? Or must
they be manually changed after the records are dumped in?
 
P

pietlinden

I have a database that has been setup to accept multiple answers for Field A
(combo-box form auto-populated by data in another table). To get the database
started with content, I need to dump an Excel spreadsheet into it. But
here's the issue: I'm unsure how to import multiple items into a single
field. Do I just separate the values with commas or semi-colons? Or must
they be manually changed after the records are dumped in

Why do you want to import multiple items into one field?
Denormalization is _usually_ a bad idea. Why not create a child table
and dump the list with the primary key from the parent record? Then
your data will be really queryable.
 
C

CJ

Well, I have some items that belong in multiple categories; i want to be able
to import all the categories an item might belong to at once. Do I just
separate the values with commas or semi-colons when i am importing them? Or
is this not something Access can do?
 
P

pietlinden

Well, I have some items that belong in multiple categories; i want to be able
to import all the categories an item might belong to at once. Do I just
separate the values with commas or semi-colons when i am importing them? Or
is this not something Access can do?





- Show quoted text -

You would have to probably import into a temporary table and then use
Split on the repeating field and then write the results of the Split
and the foreign key to a child table...

Basically, you loop through the records in the source table, and use
Split to break up the repeating data. Then you loop through the items
in the array (from LBound to UBound), writing each subscripted value
to somewhere (another table). You would continue through the source
table until all the records were processed.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top