Am I Crazy ?!? Do I even need to use access?

[Grefcon901]

Hello awesome People!! THIS IS MY FIRST WEEK WITH ACCESS!!!



I am currently attempting to build an Access database linked to 21 sheets ( thus 21 tables) that are all identical by column but obviously not all the same data. Data format and dimensions are all the same. Each sheet is a specific stock and holds historical data (open close high low...) by date ascending and a of the actual work has already been done by the excel workbook. I have given categories that are already calculated by the Work book and are presented as just the text label in the database tables so there is not a lot access needs to do.

My boss has asked me to import this workbook into access in order to create a sandbox like program to look up values using forms to calculate new reports accordingly. This presents a problem as forms are to change or amend the database, not directly correlate to report creation. The queries i can make in opinion do not seem to be valuable because all the data is already present in the tables. There is nothing new to add as it all updates daily because it is linked. New data created by looking up new dates and conditions through these hypothetical forms will only be in the reports. I am at a loss and honestly looking for someone to help me with some VBA to do these functions or tell me I am not crazy and this is impossible.

-Thanks Jon

[CJ_London]

throw away all your excel thinking and learn about how databases work

google normalisation to find out more but in summary you don't need 21 tables, you need 1, but the table will have an additional column to indicate stock

once you have achieved this, you may need additional tables to remove duplications from the single table - without knowing the detail, it is not possible to advise but for example your table may contain a stock code and a stock name - the stock name can be stored just once in a different table and linked on stock code.

once you have done this you then only need one form/one report per function utilising criteria/filters to view a single stock.

None of the above requires any vba in principle, but inevitably forms and reports will need tweaking.

You also need to consider how the data is to be used - one person only? multiuser?

[Grefcon901]

Hello Francis!!

Each one of those sheets has over 2000 cells per stock though and it grows daily. I really have to put them all into 1 though ?!? That is a 50,000 plus table :0
This is for a multi user DB.

-Jon

[CJ_London]

50000+ is small, I regularly deal with 5m+

if multi user, then you will need to split your db into front end and backend. backend is tables only and goes on the network where everyone can access it and a copy of the front end (which contains forms, reports, queries and code) goes on each users machine. Even with a single user, better to split the db for all but noddy applications.

if you need more storage, consider using sql server as a backend rather than access.

Also, be aware you do not store calculated values in a table - that is what queries are for

You have a very steep learning curve ahead of you

[Grefcon901]

oh...oh my. So I need to dump Excel completely and have Access do all the lifting for calculations? This sounds like a monster...

[CJ_London]

yes - or stay with excel - the nearest comparison would be to merge your 21 workbooks into 1

at the moment you have 21 workbooks - what happens when you add another stock? you create another workbook - in a db, there is nothing to do other than add the data
what happens if you want to add another calculation - you do it 21 times. In access you do it once
what happens when you add another days trading - you update 21 workbooks - in access you do it once

[ssanfu]

No, you're not crazy and Yes, you need access.

Welcome to the forum!


Follow advice by Ajax - I concur 100%.

A couple of good starting tutorials are at Rogers Access Library - http://www.rogersaccesslibrary.com/forum/forum46.html


Have fun

[Grefcon901]

Thanks Guys! Sounds like the light of day may not be something I get to see for the next few weeks.
Ill look at that tutorial!
-Jon