Data file getting too big

[peter8848]

Hi All,

I have two files one is the data file in excel and another is data file in power pivot. However now the data file is getting bigger and bigger. Any better suggestion we can fix this?

I am thinking about converting data file in excel into access file but not sure how to use another excel file for power pivot links to it? Any suggestion or help page would be much appreciated. Or what would be better way i am also thinking about SQL in Excel but not sure how to design it?

Cheers,

Peter

 

[Joe4]

If you use Access, you may be able to do away with the Power Pivots, and just do Queries in Access.
To me Power Pivot kind of looks like trying to do Access/SQL queries in Excel, so if in Access, just use their native Query functionality, and leave Power Pivot out of it.

 

[peter8848]

If you use Access, you may be able to do away with the Power Pivots, and just do Queries in Access.
To me Power Pivot kind of looks like trying to do Access/SQL queries in Excel, so if in Access, just use their native Query functionality, and leave Power Pivot out of it.

Hi Joe4, thanks for this and i need to learn how to use Access and queries in Access. The good thing about power pivot i can share this report for other people which is quite easy to use for who do not have much excel skills so do query provide the same and all they do just refresh the data and report will update itself automatically?

if so where do i learn it from?

thanks,

Peter

 

[Joe4]

Queries in Microsoft Access are always "real-time", so you never have to worry about refreshing them.
However, I think your other users will have to have Microsoft Access also too.

There are lots of tutorials you can find on-line for learning how to write queries in Access. There are even a bunch of YouTube videos, if you like to go that route.
These can all be found via a simple Google Search.

If you wish to create a working database in Access, that will take a little more effort, as you need to know a bit more about Relational Database Design (especially the Rules of Normalization), and of course need to learn how to create the tables in Access (building the tables isn't too bad, it is more the design of the tables that is the tricky part).

However, if you don't really need to create a Database, you just need to be able to create some queries on a data file, you don't really need to worry about all that database stuff.

 

[peter8848]

Queries in Microsoft Access are always "real-time", so you never have to worry about refreshing them.
However, I think your other users will have to have Microsoft Access also too.

There are lots of tutorials you can find on-line for learning how to write queries in Access. There are even a bunch of YouTube videos, if you like to go that route.
These can all be found via a simple Google Search.

If you wish to create a working database in Access, that will take a little more effort, as you need to know a bit more about Relational Database Design (especially the Rules of Normalization), and of course need to learn how to create the tables in Access (building the tables isn't too bad, it is more the design of the tables that is the tricky part).

However, if you don't really need to create a Database, you just need to be able to create some queries on a data file, you don't really need to worry about all that database stuff.

Hi Joe4,

thanks and sounds like you are suggesting create report (query) or data base in access or both? Is it just because Access can hold up bigger data set?

Any good video to start? Sorry I have zero knowledge about Access but Excel is getting it limit for my work so i need to invest something more.

Cheers,

Peter

 

[Joe4]

Access can handle more data than Excel, and usually handles it more efficiently.
To me, Power Pivot does a lot of the same tasks that Access and SQL can do in their queries, which is why I would stick with that (Access).

There are tons of tutorials that you can on writing queries in Access, which you can find with a simple Google Search. There are lots of YouTube videos too, if you prefer to go that route.
However, I might recommend picking up a good introductory book into Access and working through that.
It sounds like for what you need that you should just be able to focus on Tables and Queries, and probably do not need to worry too much about Forms or Reports at this time.