How to copy data from one column to other using query.

[Sumanth.Ganjam]

Hi All,



I am having excel file where it contains multiple sheets (Sheet 1 , Sheet 2, Sheet 3 etc).

When i import the excel to Access, File contains one Value in Cell A1 in Sheet 1 and i need to copy the same date to all the Values under Column A and need to copy the same data to all other sheets for Column A.

Please advise how to write the query for this one.

Thanks.

[June7]

Why is this necessary? What data is in the sheets? Why are there multiple sheets?

This is a periodic process? The value in cell A1 is different each time? Why does only the one cell in the column have value?

Easiest approach would be to fill the cells in Excel before import.

[Sumanth.Ganjam]

Hi June 7 and All,

Please find the below comments :

Why is this necessary?
Because there will be 11 sheets and in single sheet only there are more than 50 lines, so avoid manual work in excel.
Like this 22 countries will be sending the excel sheet.

What data is in the sheets?
Headers will be.
Ticket Id,Material No.,Plant No so on.
Ticket ID is the header A1.


This is a periodic process?
No it is not Periodic process, it is daily process.

The value in cell A1 is different each time?
For each time when uploading the Excel, the value in cell A1 will be different.

I hope this answer all your questions.

Please help.

[June7]

A simple copy down in Excel would quickly populate the cells.

Otherwise, after the import, run an UPDATE sql action.

Have to somehow retrieve the TicketID just imported into the table. Will the TicketID be a larger value with each import?

DoCmd.Execute "UPDATE tablename SET [TicketID='" & DMax("TicketID", "tablename") & "' WHERE TicketID Is Null"

If the field is a number data type, remove the apostrophe delimiters.