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?

 

[SkipVought]

Hi,

Is your data in Excel in a table: one table and only one table in a sheet headings in row 1 starting in colimn A?

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

 

[dhookom]

You can link an Excel sheet (hopefully in a consistent format) to Access as a linked table.

BTW: Typing in all CAPS is considered shouting. You have posted a number of times in the past with appropriate mixed case which is much preferred. Please preview your posts.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[billheath]

Sorry about all caps. I wrote that as I was leaving for lunch and did not realize the caps key was on until you mentioned.
The data ids in several cloumns. The first contains the item description, a 3 or 4 letter designation. The secon throuhj 30 columns contain a date and the information I need is in the middle. It looks like this:

Jan 1 Jan 2 Jan 3
BATI 1.19 2.44 .98
JEN 4.44 1.98 2.05
PAN .22 .24 .26

I need the " 1.19, 2.44, and .98, etc"
In excel, I just use a lookup formula. I'm not sure what to do in Access. thanks

 

[dhookom]

Your data view lacks structure. Please use TGML to provide us with decent column distinctions. Also if you have spaces in your field names, please enclose them in []s. Is this what your data looks like in Excel?

[pre] [Jan 1] [Jan 2] [Jan 3]
BATI 1.19 2.44 .98
JEN 4.44 1.98 2.05
PAN .22 .24 .26[/pre]

In Access, you would possibly use DLookup() or queries which are more efficient. How do you want to use the Excel data? Is there a column title for the first column? Can you answer Skip's questions?

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[SkipVought]

The secon throuhj 30 columns contain a date and the information I need is in the middle.

You ONLY have data in Excel for the first 30 days of the year?

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

 

[billheath]

No, There is a different excel sheet for each month. There is no spaces in the dates. Also, the dates are in short form "1/2/18" etc. Thanks for the mention of Dhlookup. I had forgotten about that one.

Thanks

 

[Andrzejek]

So your Excel data actually looks like this:

[pre]
A B C D ... AF
1 1/1/18 1/2/18 1/3/18 ... 1/31/18
2 BATI [BLUE] 1.19 2.44 .98 ... 2.34[/BLUE]
3 JEN [BLUE] 4.44 1.98 2.05 ... 5.76[/BLUE]
4 PAN [BLUE] .22 .24 .26 ... 6.66[/BLUE]
[/pre]
And you want to retrieve [BLUE]BLUE[/BLUE] data staring from cell B2 - AF2 (for January)
and go down the data. Is that what you are after?


---- Andy

There is a great need for a sarcasm font.

 

[billheath]

yes. that's it exactly. thanks

 

[SkipVought]

BIG mistake To use a report or summarized data for a table source like you have.

BIGGER mistake for a table source to be chopped up into multiple pieces.

Your workbook ought to have a single table for the data, from which monthly summary reports can be gleaned as well as a source for your Access.

It seems that this is a new workbook: “1/2/18”. Save yourself some grief. Make one table with Date, Item, Amount. Then each month’s REPORT can each be on a sheet via a PivotTable report. But your source table will be accessible for other analysis and reporting.

BTW, where are you getting the Item and Amount from for each day? Are you entering these values by hand?

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

 

[billheath]

You are absolutely correct, Skip. But I had nothing to do with the excel table. I have just been asked to get the correct data to include in the access reports. have been copying the data manually which is a big waste of time! I was hoping there was a simpler way to transfer the data. The data above is not real. I just made it up to show you the way it is laid out. Guess I didn't do a very good job!! Thanks

 

[SkipVought]

The key question is, where does the data that someone puts into the Excel file come from? More than likely its comming from some other corporate table. Sombody’s manager wanted or wants to see a REPORT for each month. Fine.

I had a job that was outside IT. In several different companies. There are tables, usually rehosted: hours old, days old, weeks old “production” data that users, like yourself, can get access to. Or, if necessary, get to know an IT programmer who can set up a simple program to periodicly generate a file that you could import. There are ways for a user to get access to source data. THAT would sure help with your Access. Much, MUCH better than an Excel REPORT scattered throughout sheets.

BTW, once you have ALL your data in one excel sheet/table, File > Get External Data > Link Tables.

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

 

[dhookom]

Billheath,
I can't believe you are telling us the full story on this. The blue numbers from Andy's post have no meaning without the other data. They are just numbers without any reference to employees or dates. I suppose you could count or sum all the numbers but that seems odd to me.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[Andrzejek]

I am with Skip - get the source data. Or - I perfect world - get just the query so you can just access the data you are interested in, and don't even keep your own 'version'/copy of it.

To add to Skip's experience - I had seen the process where data was used to create a report, report was printed, then scanned, and OCR'ed to get the data back in 'electronic' format. Seriously.


---- Andy

There is a great need for a sarcasm font.

 

[billheath]

Actually I am. I have an access form with the date and the various initials entered automatically when I enter the form. I have a space for each value which I wanted to enter automatically from from the excel spreadsheet. The resultant table will look exactly like the one above with some additional information which I have to enter. It will be one table which lists the initials and pricing per the date. I hope this makes sense.

 

[dhookom]

Andy

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[billheath]

I think the query idea might work. I can link the excel spreadsheet and do a query on it and use the query info as the basis for the report.
Thanks guys

 

[combo]

You need normalized data in access. If you really can do nothing about the data you receive and you have excel 2016 or 2013, you can do the preprocessing fully in excel, with power query. In:
- excel 2016 you have it built-in as get&transform feature,
- excel 2013 this ia a microsoft free com add-in, that you need to download, install and activate. See for starting:

https://support.office.com/en-us/ar...cel-2016-881c63c6-37c5-4ca2-b616-59e18d75b4de

Before switching to power query:
- make sure that table headers are proper excel dates,
- convert monthly data to tables (select any cell inside data and choose insert>table, you should see them with alternating colours rows), give tables meaningful names (say tblJan, tblFeb, etc.).

Create query for each table that normalizes monthly tables:
- create query "from table" from excel get&transform (2016), you should get power query interface,
- select columns without date headers, right-click and unpivot other columns,
rename defauld headers if you like to,
- close and create a connection only (if excel added new sheet with table, you can delete it).
As a result you should get 12 queries named tblJan, tblFeb etc, as your table names. Again, you can rename them.

Now combine queries:
- add blank query, in power query interface write in formula line =tblJan (should be query name for January data),
- add February data from February query ("append" action),
- repeat steps for other months queries.
You can output to worksheet or create connection only.

This seems to be complicated, but you complete it in 15 minuts. You can connect access to connection or table in excel.

combo

 

[Andrzejek]

The resultant table will look exactly like the one above" - are you saying you have a table in your data base for January with 31+ fields (one field per day), February table with 28+ fields (29 fields every 4 years or so)? Really....?

I hope I've got it all wrong...

@Duane - I wanted to whoever set this process....


---- Andy

There is a great need for a sarcasm font.

 

[SkipVought]

Andy, its a REPORT! Most Excel users don’t know the difference between a report and a proper normalized table. In fact most Excel users would rather have a nice looking report than a proper table. They don’t realize that a multi-sheet report is an awful source for a database, data analysis and reporting. SAD.

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