i NEED TO TRANSFER DATA FROM EXCEL

[billheath]

i NEED TO TRANSFER DATA FROM EXCEL TO ACCESS. iN PARTICULAR, EMPLOYEES ENTER PRICE ND QUANTITY INFORMATION TO A DAILY SALES SPREADSHEET. i NEED TO TRANSFER CERTAIN INFORMATION, NOT ALL TO A REPORT IN ACCESS. iS THERE A LOOKUP FORMULA IN ACCESS THAT WILL TRANSFER DATA FROM EXCEL?

 

[MajP]

If I follow this, I do not think it would be to complicated to do via code. I think a SQL solution would be very time consuming You recieve 12 tables in this format:

   A       B       C       D      ...  AF
1        1/1/18  1/2/18  1/3/18   ... 1/31/18
2 BATI     1.19    2.44     .98   ...  2.34
3 JEN      4.44    1.98    2.05   ...  5.76
4 PAN       .22     .24     .26   ...  6.66

I am guessing you want to import the data into a normalized access table and persist the data in Access. Now you can do traditional db stuff on the data.

EmployeeID  Price_ND  Entry_Date
Batti       1.19      1/1/2018
JEN         4.44      1/1/2018
...
PAN         .26       1/31/2018
...
BATTI       99        12/31/2018

The best solution would be to pre-process this an Excel, but I am not sure if that is easy. Normalizing this in SQL would also be difficult and time consuming.
I would link each workSheet as a linked table. Then I would in vba loop my tables, loop the rows and loop the columns and use an insert query to insert the data into a normalized format. You would convert the Field name into a real date. If the data does not change after it is inputted you can only enter new data. If it does you would have to update everything. The code to do this would not be very long or involved. If that makes sense, I could demo real quick.

 

[combo]

The best solution would be to pre-process this an Excel, but I am not sure if that is easy.

Normalizing is easy in power query, also appending tables. Howewer, this requires recent excels (2013-2016).

combo

 

[SkipVought]

Faq68-5287

Using any version of Excel.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[MajP]

OK, everyone says it is easy to normalize your data in excel. So definitely do it that way. Make a new worksheet that normalizes the data from the 12 worksheets into 1 worksheet that looks like this.

EmployeeID  Price_ND  Entry_Date
Batti       1.19      1/1/2018
JEN         4.44      1/1/2018
...
PAN         .26       1/31/2018
...
BATTI       99        12/31/2018

Then link to that worksheet. Now you can use it in any report, query, or form.