Normalising Repeated Columns in Imported Data (1 Viewer)

[andygryce]

Hi, I have been able to normalise imported data which is in the form as in the table below (the number of actual Date columns goes up to 30 or so)

ID Date1 Date2 Date3 Date4 Date5 Date6 etc
123 15-Feb-11 28-Feb-11 03-May-11 17-Jun-11
234 02-Apr-11 04-May-11 14-Jun-11 27-Jun-11 23-Jul-11
345 11-Jun-11 27-Jul-11 18-Aug-11 13-Sep-11
456 26-Oct-10 20-Dec-10 07-Mar-11 13-May-11 25-Jun-11 06-Jul-11

to a table:

ID Date
123 15-Feb-11
123 28-Feb-11
123 03-May-11
123 17-Jun-11
234 02-Apr-11
234 04-May-11
234 14-Jun-11
234 27-Jun-11
etc

using a VBA module (attached as a txt file) which I found online. I only had to change the field names and types.


But how do I do the same for where there are two repeated columns (dates and hours) as in the imported table below (again the number of repeated columns goes up to 30).

ID Date1 Hours1 Date2 Hours2 Date3 Hours3 etc
123 15-Feb-11 2 28-Feb-11 4 03-May-11 4
234 02-Apr-11 4 04-May-11 2
345 11-Jun-11 2 27-Jul-11 4 18-Aug-11 2
456 26-Oct-10 3 20-Dec-10 2

I hope you can help

Thanks

Andy

 

[spikepl]

First marry each Date & Time in a corresponding Datetime field, and then do the same as before

 

[Mr. B]

If the "hour1" is relative to the "date1" then I would think you could just add an "hours" field to the table where you are already writing the dates.

 

[andygryce]

First marry each Date & Time in a corresponding Datetime field, and then do the same as before

Thanks for your suggestion. Unfortunately I didn't explain the data type properly. The Hours fields in my example are meant to be numbers (of hours worked on that day), not times. I want to end up with a table like this.

ID Date Hours
123 15-Feb-11 2
123 28-Feb-11 4
123 03-May-11 4
123 17-Jun-11 2
234 02-Apr-11 4
234 04-May-11 2
234 14-Jun-11 4
234 27-Jun-11 4

I've not an expert in VBA, far from it, but was thinking that the VBA module I attached could be edited to achieve this, or is there some other method.

Thanks