importing from excel, 12 columns in one... 1

[chrisjones99]

Hi everyone,

I have a spreadsheet with columns looking like this:

Country,Client,Product,Apr,May,Jun,Jul,Aug etc....

I'm trying to import it in access but what i want is have only one column with the months meaning for april : 01/04/04 may:01/05/04 etc... in one single column

Do you know how to transform 12 columns into one with this format via access, or does it has to be done trough excel formulas ?

Thank you for your answers

 

[BobStubbs]

I would approach the problem like this - it's a 'step at a time' approach which should help you to understand the processes involved.

1. Import the data into a 'holding table' which has the same column / field structure as the original spreadsheet.

2. Create an empty 'final table' with the column structure which you require - i.e. with a single 'date' column

3. Write an Append Query which just takes (say) the 'April' data and appends records from the holding table to the final table. Test this and make sure it works.

4. Look at this query in SQL view, to get the correct SQL syntax to append the April records.

5. Set up a convenient control e.g. a button on a form, to start the complete append process.

6. Copy the SQL from the query, and attach this to the button's On-Click event by using the DoCmd.RunSQL command.

7. Test this to make sure it still works.

8. Copy / paste this VBA code within the button's On_Click as required, changing the 'month' column being worked on each time.

You should end up with a button which, when clicked, will perform a number of Append processes, one for each month. This will leave you with a 'Final Table' containing the records which you require.

If you are not familiar with any of the steps which I have listed, write back and I'll do my best to provide more details.

I hope that this will help.


Bob Stubbs

 

[chrisjones99]

Thank you Bob ! This is what I was looking for...still have some small problems with writing the VBA code and the syntax of docmd.runsql but I'll search a little..
A big thank you, this really helps a lot!!!