Can Access be used for non-relational data sets?

[Factotum]

I'm very new to Access, but fairly comfortable with Excel (including VBA). Maybe there's a better way to do this, but I basically want to use Access the same as Excel (non-relational data sets), since Access can handle more data.

I maintain an Excel spreadsheet with 6 sheets. I want to be able to have six non-relational tables/data sets in Access, each with company wide data. I then want to build a macro in an Excel spreadsheet that will go to each of the six tables in Access, extract data for a certain Department ID, and pull that data into Excel.

So I'll have six non-relational data sets in Access with several hundred thousand rows of data in each. A macro will extract a couple hundred lines from each data set and import it to corresponding sheets in Excel. This will allow users to easily extract data specific to their assignment from data sets that contain everything.

Is this even possible/advisable? Or is there a better approach to take? Thank you in advance!

 

[alansidman]

So long as there is a common key that links the tables, you may be able to get what you need. You do need to understand that Access does not operate in a manner similar to Excel. You need to have your data tables normalized, no repeating data and Primary keys and Foreign keys to join tables in queries (SQL) to get your results.

 

[Factotum]

Thanks for your feedback. I know Access is not intended to work like Excel - I was just wondering if it could be forced to for this one project. I could come up with a common key to link all the tables (department ID), but I don't think I could normalize the data, not without doing enough work to defeat the whole purpose.

Would you happen to know of anything else that could house nearly a million rows of data and then use Excel to just extract the subset I need?

 

[alansidman]

You can do what you want to do, but it will require you to write SQL statements in your VBA or do all your queries in Access and then export the information to Excel. If your data is not normalized, it may convolute your results unless they are straight forward extractions. I would start with a small sample of data to test. Link your current or sample excel data tables to Access and run some queries to see if you get the desired results. I would not try to build the whole operation until I knew that samples worked and delivered the expected results.

 

[HiTechCoach]

I'm very new to Access, but fairly comfortable with Excel (including VBA). Maybe there's a better way to do this, but I basically want to use Access the same as Excel (non-relational data sets), since Access can handle more data.

I maintain an Excel spreadsheet with 6 sheets. I want to be able to have six non-relational tables/data sets in Access, each with company wide data. I then want to build a macro in an Excel spreadsheet that will go to each of the six tables in Access, extract data for a certain Department ID, and pull that data into Excel.

So I'll have six non-relational data sets in Access with several hundred thousand rows of data in each. A macro will extract a couple hundred lines from each data set and import it to corresponding sheets in Excel. This will allow users to easily extract data specific to their assignment from data sets that contain everything.

Is this even possible/advisable? Or is there a better approach to take? Thank you in advance!

Yes, you can use Acess for what you need. What you have described is more like Data Warehousing than a Access Application for data entry.

From what you described, I would recommend upsizing to the free Microsoft SQL Server Express. You can still use Access to build forms and reports if needed.

 

[Factotum]

Thank you for the idea. I know nothing about Microsoft SQL Server Express, but I'll have to look into it. Thanks again!

 

[HiTechCoach]

Thank you for the idea. I know nothing about Microsoft SQL Server Express, but I'll have to look into it. Thanks again!

You're welcome.

This links may also be helpful: Excel Power Pivot (Click Here)