Importing Data and Transformation

[marcuscoker]

Hi

I am hoping that somebody could help me find a solution to a problem i am having importing data from an excel spreadsheet to Access then transforming that data using VBA.

The data in the excel spreadsheet comings a a strange format.

Basically, instead of having different spreadsheets for different tables all data is bundled into one excel spreadsheet.
(Please see attached example)
The problem is the spreadsheet will have personID,FirstName,Activity1,Activity2

If a person has more than 2 activities then a line will be created underneath their record with blank patientid,firstname and surname and a value in Activity, what i need to do is have a script that imports this data in, and finds any blank personID and take the personID from the above row, is this possible?

Thanks

Marcus

 

[CrystalSurfer]

Marcus,
Is there any way of pre-populating the spreadsheet by putting in the PersonID on every line?
Otherwise, I guess you have to use a record set in VB and write in the value of the last PersonID read.

 

[marcuscoker]

Hi

When i recieve the extract it comes without the person ID on each line, so i am just trying to work out the best way of doing that, How would i go about doing this using a recordset as you suggested?

Thanks

Marcus