HELP! Bad excel layout import into Access

[Cathi]

I have an excel spreadsheet that I need to import into an access table.
HOWEVER, the spreadsheet is not laid out correctly; what would be the field
names in access are rows in one column in excel and the data field is another
column with rows. For example:

Column "A" Column B
Field 1 10%
Field 2 20 %
Field 3 10 %

Column A is what I need to be the field name in Access and Column B is the
data that should populate that field.

How do I write this so that it imports correctly???? I have tried

INSERT INTO TABLE A (FIELD1, FIELD 2)
SELECT F1, F2
FROM <excel path and info here>

This DOES NOT work, what am I missing?

Cathi

 

[Cathi]

That won't work for what I need. Already thought of that This is an
ongoing process and I need to automate it for the users. Now, if I can
perform the "transpose" function in code through access than I could get it
to work. Do you know if that is possible.

 

[John Nurick]

Hi Cathi,

One general approach is

(a) link the Excel sheet as a two-column table
tblXL
F1 Text
F2 Number

(b) use a crosstab query to transpose this

(c) base an append query on the crosstab.