Transpose data from form to table

[ijswalker]

Hi,

I have a continous form that starts off with the data entered in a crosstab manner. e.x.

Client Car Wash , Car Detail, Service 3 Service 4 ........Service 15
John Smith 1 2

This is how the client wants to enter their information. However, I would like to be able to store the information as follows

Client Service Hours
Client1 Car Wash 1
Client1 Car Detail 2


And so on.

Is there a way I can do this using VBA?

Ideally I would like them to enter using a subform in the manner that I want to store the info but they don't want that. Storing the info in the crosstab manner creates lots of efficiency issues when creating queries and reports.


Thanks

Ian

 

[pbaldy]

I have an application like that. I use a local table to store the un-normalized data input, and append data into the normalized tables when the user is done entering. You just have to test for Null fields and only append when not Null.

 

[ijswalker]

Hi Paul,

Do you do this through a query or do you use VBA to do this?

Ian

 

[pbaldy]

I use recordsets in VBA because in my situation it's an AR master/detail situation, and I need the ID from the master record to include in the detail records. It can just be a series of append queries if it's all into one table (one query per "type" column).

 

[pbaldy]

It occurs to me it could be one query, an append based on a UNION query to put the columns into rows (with each clause excluding Nulls).