Question Access help urgent pl

[AdamB]

Hi there,

I have a set of data that i need to manipulate for query purposes.

My data looks like below. first column with ID and upto 15 columns iwth new value each.

see fromfile.xls attached

I want to convert this in to rows. as below.

see tofile.xls attached



Have you got any thoughts, ideas, VBA code that you can help with.
Thanks,
A

 

[Poppa Smurf]

You have a couple of options
1. Create an UNION QUERY to join all the fields for the output. This involves copying the sql code 14 times and changing the field name.

2. Read the input table into an array and write the array to an output table where each column in the array is not null.

I have attached a sample database for you. Query qry_output_union is the union query for option 1, query1 is the query used to build the union query. The module mod_transpose_input is coding for option 2

 

[AdamB]

Thnx Poppa Smurf. will look at this now.