Copying from Excel column into Access combo box field

[australia3]

Hi all, I'm hoping you can help. I have a column of data in Excel where each cell has multiple data entries separated by commas. I'd like to copy this into an existing (empty of records) combo box field. The combo box field is linked to a table and every record in Excel exists in the list. When I try and paste nothing seems to happen. Can someone help please? Thanks in advance

 

[SkipVought]

Hi,

Is your excel file/table linked?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[MajP]

I have a column of data in Excel where each cell has multiple data entries separated by commas.

So if you have a column of data and each cell has multiple items.

a,b,c
d,e,f
g,h,i,j

Then in the combobox what do you want?

a
b
c
d
..
j

or
a,b,c
d,e,f

or something else?

 

[australia3]

No it's not linked. In Excel I have data stored like this: -
column a
a,b,c
c,a,b
e,f,g

In the combo box field in Access the data source is a table of data with the letters in
a
b
c
d
e
f
g

I'd like to copy the Excel column to the Access field so that I have 3 records (as example) that contain

a,b,c
c,a,b
e,f,g

I hope that makes sense. Thanks for looking into it for me.

 

[MajP]

I am still confused. To help could you

1)provide real names for the following.
Combobox name:
Combobox rowsource:
Combobox control source (field name):

2)Can you explain what the data really is, and why the comma seperated values? One thing that is causing confusion is that in databases you almost never have multiple values in a single field. That violates database basic principles. So if you explain the data a little better maybe we can suggest a better approach.

However, to bring your data into access you can link to it or import it into a table. When linked you can work with the excel table as if it is a Access table. When imported you just bring in the excel data to a new table.

Once you link or import (similar wizard to opening a .csv,.txt in Excel), then you can run append queries to add the Excel data to your fields. Appending your column of excel data into access field is a simple query, but need to know what you really want to do with the comma separated values.

From your description the combo box has choices like
A
B
C
...
But the bound field has values
A,B,C
C,A,B
So not sure how these two things would tie together.

 

[MajP]

One thing. If you label your excel column with the same name as the field in your table you can use the import wizard and import "into an existing table". To make that go smoothly I would first copy your excel column into a seperate worksheet, this will help avoid the insert trying to import other columns.

 

[australia3]

I have a postcode labelled column of data in Excel. The data looks like this, CF8, CF9, NP10. I have an Access table called Postcode with a combo box field called Postcode. The row source is a query on another table
SELECT [Postcode district].District FROM [Postcode district] ORDER BY [Postcode district].District;
which has a list of postcode districts e.g. CF8.

I can't paste the data. When I import into a new table and add the combo list properties after the event. The different entries aren't recognised as such. Does this make any sense?

 

[MajP]

I am sorry, but I am still having a hard time understanding. Let me see if I have it.

You have
table "PostCode"
With a field "PostCode"
The field in the table uses a lookup combobox
The lookup combobox has a rowsource of: SELECT [Postcode district].District FROM [Postcode district] ORDER BY [Postcode district].District
The row source values are single values (not comma seperated)

You want to input the comma seperated values from excel into field Postcode of table Postcode.

If that is correct, then I do not understand the logic, and it will not work.

If a field has a value like "a,b,c" but there is not a value of "a,b,c" in the rowsource then the lookup can not display it. It may actually be in your table, but you just will not render it with the lookup. You would have to actually have a record "a,b,c" in the rowsource.

This is one of the problems with using lookups in tables, it gets very confusing. You will see many recommendations to not use lookups in tables. Use them on the forms only. The other thing, if the lookup has values
A
B
C

How do you expect to make that into a comma seperated value in your table. If you want to assign multiple post codes to a record then you should have a child table.

 

[MajP]

Disregard my last about it not rendering. That would be only if you were saving a certain value in the table, but displaying a different field. Such as storing an employeeID, but displaying the name. So I still do not understand the issue. Did you import the data into Access like suggested?