Limitations of Access?

[auto.pilot]

I have never used access, but just started to investigate the limitations for a potential project.

My data sets will have approximately 13 million fields quarterly, and I will eventually need 60 quarters worth of data. That's around 260 million data points. Nearest I can figure, it's about 1.3 GB. Can access handle this type of data set?

Thanks

Jim

 

[Norie]

Jim

13 million fields?

 

[baderms]

Access has a 2GB size limit, this includes all forms, tables, queries, ect. You can get more space by splitting the database so only the backend holds data or you could use MS Sql Express to hold your data which has a 10GB limit.

 

[Joe4]

My data sets will have approximately 13 million fields quarterly

I hope that you actually meant "records" and not "fields". Access only allows for 255 field in a single table.
https://support.office.com/en-us/ar...ications-0cf3c66f-9cf2-4e32-9568-98c1025bb47c

 

[auto.pilot]

I hope that you actually meant "records" and not "fields". Access only allows for 255 field in a single table.
https://support.office.com/en-us/ar...ications-0cf3c66f-9cf2-4e32-9568-98c1025bb47c

Thank you for the link. I'm not familiar with Access terminology (yet). Fields to me is more of an Excel-type term. Each quarterly download includes the equivalent of 13.5 million populated excel cells.

Thanks

Jim

 

[Joe4]

What you call a row in Excel is known as a record in Access and other database programs.
What you call a column in Excel is known as a field in Access and other database programs.

So, now you know how to talk database!

Access has no limit to the since of records in a table, it is bound more by the 2 GB memory limit.
However, it does limit the number of fields in any single table to 255.

Each quarterly download includes the equivalent of 13.5 million populated excel cells.

So, how many Excel columns does that comprise?

Typically, in a well designed database, you will not see repeating similar columns in a table, and the number of columns should usually not increase with each file that you import (unless you were doing some overhaul to the process).

So, in Excel, it might be structured like this, with the following columns:
- Company Name
- 1st Quarter 2017
- 2nd Quarter 2017
- 3rd Quarter 2017
- 4th Quarter 2017
and then next year, you would simply add 4 more columns for the 4 new quarters.

That is NOT how you would do it in Access. Is this simple example, you would only have three fields:
- Company Name
- Quarter Code
- Value

So, for 2017, each company would have 4 records (rows), one for each quarter. So the next year, you don't need any more fields (columns). You just have different Quarter Codes and keep adding more records (rows).

As you can see, the thought process of Access is much different than Excel. It is a whole different beast.

 

[Micron]

Much about the size limitations, but no questions about what you'd do with the data anyway?
Filter, summarize, group, report? A database is usually the right tool.

Pivot tables, graphs, financial functions? Not so much; especially graphing. While Access can do these things to one degree or another, Excel does exactly that - excels over Access. Sometimes, the right solution is to use both, each for it's better part.

 

[auto.pilot]

Much about the size limitations, but no questions about what you'd do with the data anyway?
Filter, summarize, group, report? A database is usually the right tool.

Pivot tables, graphs, financial functions? Not so much; especially graphing. While Access can do these things to one degree or another, Excel does exactly that - excels over Access. Sometimes, the right solution is to use both, each for it's better part.

Excellent points. The project involves filtering, summarizing and running some trend analysis. After extracting the data from Access, I will dump it into Excel, make some minor calculations and prepare eye-catching reports.

Thanks!

Jim