Access newbie

[beng986]

Afternoon all,

I am looking for some help. Unsure access is what I need but hoping you can help me with this.

I have a tonne of excel data which is currently stored in various excel documents which are simply getting to big. Much of what I am trying to do is speed up the excels by storing the data somewhere else as well as getting more out of the data.

I am looking to store these documents in access then link them into excel to do the analytical work. Is that a good thing to do?

Beyond that the data also has a number of uses within it. For instance it says how much stock of a certain item I have in one shop and how much has been bought of that item in that shop.

Whilst I understand that I would be able to analyse the data of stock in each shop, is it possible to query the data in excel so I could also analyse how much of the stock is bought in total/ in each shop and apply the excel formula to this?

Many thanks

 

[Smitty]

Sounds like a great job for Access. Regarding stock/shop, as long as you have a shop ID for each record then I'd put all of the like data into a single table. You can then query it out based on Shop. You can also set up parameter queries in Access where you feed the criteria to Access from Excel.

 

[beng986]

Thanks - can I use access like this-

Use it as in effect a data dump for the 500 stores information. Then use excel to analyse to stores information through queries?

Can access also create additional data stores that would class the sheet by product instead of by store?

In effect using access as the data dump and running an api off it into excel to do the analysis?

At the moment this is all done excel and is so slow with the added issue that I can't get the additional product filter datasets.

Lastly can I create something that is dynamic. Obviously I get daily amounts of data. Would I be able to automate the process of extending the history of hr search out?

 

[Micron]

I'd say yes to #1; #2 yes if you create the relationships correctly. #3 - you would not use any API calls for this - either automation, Shell (without API) or Access methods to transfer data such as transferspreadsheet function. I don't know what a "hr search out" is so no suggestion from me on that. As for automating the process, this can be done by activating a database event or even just opening Access with Windows Task Scheduler. If you're talking 500 stores, 1000's of products, lots of data fields and years of data, ensure you are not nearing the 2GB limit of Access before you start. Also, if you are going to tackle this by yourself, I strongly suggest you read up on database normalization and save yourself some grief down the road.