Access or Excel?

Forum guidelines BT super discount
Other Resources My Cup of Joe » Access or Excel? Rss Feed  
Moderators: k9car363, the bear, DerekL, alicefoeller Reply
2013-01-01 2:17 PM

User image

Expert
1456
10001001001001002525
Central New Jersey
Subject: Access or Excel?
Here is what I need (do?) I collect data for 23 locations in our territory. (Each with varying # of meetings/week anywhere from 1 to 25). 3 fields never change (location, day, time) I collect 10 additional pieces of data for each meeting. 5 of these 10 have a "goal" from corporate. 2 fields are calcuated.

For the last 2 years I have maintained the data in Excel. However I suspect this is cumbersome ( 1 sheet for each week of the year (need summaries by week) then I copy to a YTD sheet and use a pivot table for summary/specific meeting data. However, now I also track if goals were or were not met (I was given all the goals for 2013)

I am wondering if setting up something in Access would be a wiser way to go? Excel is a known for me, but I have an IT background and can learn quick. I'm thinking of a table with the goals, queries would work!?! But do I set up one table for each week or one for each location?

If anyone knows it will be COJ friends - so any advise would be appreciated!

Lani

2013-01-01 2:25 PM
in reply to: #4556526

User image

Veteran
399
100100100252525
Puyallup, WA
Subject: RE: Access or Excel?

Sounds like Access would be more efficient but couldn't answer your specific question. I'd suggest a quick one or wo day intro class and you would probably have what you need to set up the tables, queries, reports, forms, etc to suit your purpose. Have fun!

2013-01-01 2:26 PM
in reply to: #4556526

User image

Pro
9391
500020002000100100100252525
Omaha, NE
Subject: RE: Access or Excel?

The big advantage of using a database (Access) over excel is the flexibility to access the data through custom queries.  Data entry is data entry so entering the data doesn't change a whole lot between the two, but you'll have a lot more customization you can do with Access.

I'd say just play around with it a little and see what you think, but based on what you've described I'd say Access gives you more of what you need on accessing your data.

2013-01-01 3:26 PM
in reply to: #4556526

Master
2083
2000252525
Houston, TX
Subject: RE: Access or Excel?
I would probably set up by location rather than week. But I look at things on a MTD, M-O-M, YTD, and Y-O-Y basis per location. I work in both access and excel and always lean HEAVILY toward excel because I know it better, but it sounds like access would benefit you better moving forward. If you look at a lot of reports, I like access better for that.
2013-01-01 3:43 PM
in reply to: #4556526

Master
2083
2000252525
Houston, TX
Subject: RE: Access or Excel?
Btw, the assumption with that is that the location (in some form) is your primary key.
2013-01-01 5:42 PM
in reply to: #4556526

User image

Member
78
252525
Pineville, North Carolina
Subject: RE: Access or Excel?

Definitely go with Access. Once you get the data set up correctly, you can run queries to pull whatever data you need in whatever format you need.

I work with Access every day, and I prefer it over Excel when managing data. You can't beat excel when working with complex models, but it gets cumbersome (and increases the chance of errors) when handling a large volume of data.

Access was built to handle data. The time you spend to learn it will pay dividends if you work with data often...

PM me if you need specific help...

 

2013-01-01 6:58 PM
in reply to: #4556798

User image

Expert
1456
10001001001001002525
Central New Jersey
Subject: RE: Access or Excel?
roserc - 2013-01-01 6:42 PM

Definitely go with Access. Once you get the data set up correctly, you can run queries to pull whatever data you need in whatever format you need.

I work with Access every day, and I prefer it over Excel when managing data. You can't beat excel when working with complex models, but it gets cumbersome (and increases the chance of errors) when handling a large volume of data.

Access was built to handle data. The time you spend to learn it will pay dividends if you work with data often...

PM me if you need specific help...

 



I totally agree with the cumbersome and SLOW the more data/sheets I have...sending PM
2013-01-01 7:22 PM
in reply to: #4556526

Member
114
100
Los Angeles
Subject: RE: Access or Excel?

wwlani - 2013-01-01 12:17 PM Here is what I need (do?) I collect data for 23 locations in our territory. (Each with varying # of meetings/week anywhere from 1 to 25). 3 fields never change (location, day, time) I collect 10 additional pieces of data for each meeting. 5 of these 10 have a "goal" from corporate. 2 fields are calcuated. For the last 2 years I have maintained the data in Excel. However I suspect this is cumbersome ( 1 sheet for each week of the year (need summaries by week) then I copy to a YTD sheet and use a pivot table for summary/specific meeting data. However, now I also track if goals were or were not met (I was given all the goals for 2013) I am wondering if setting up something in Access would be a wiser way to go? Excel is a known for me, but I have an IT background and can learn quick. I'm thinking of a table with the goals, queries would work!?! But do I set up one table for each week or one for each location? If anyone knows it will be COJ friends - so any advise could be appreciated! Lani

You set up only one table. Date and Location are also fields in the table. When you need to see data for a specific location and/or date you just execute a query.

Every year or whatever you can have a process that "archives" or purges old information and you continue using the same table.

Feel free to PM if you need more help.

2013-01-01 11:49 PM
in reply to: #4556526

User image

Master
1439
100010010010010025
Calgary, AB
Silver member
Subject: RE: Access or Excel?

You have such low volume of data, I would stick with excel but structure it more like a table/dbase. Excel 2010 is fine for 2+ million rows.

Instead of sheets for each week, just use one sheet for everything, but have a "week" column, and a proper date column. Then any reports/pivots can use those columns to filter which is way easier than referencing a bunch of different sheets IMO. And if you change your mind and go sql or access, it's easier to dump.

 

 

2013-01-02 11:17 AM
in reply to: #4557203

Extreme Veteran
861
5001001001002525
Northbridge, Massachusetts
Subject: RE: Access or Excel?
Khyron - 2013-01-01 9:49 PM

You have such low volume of data, I would stick with excel but structure it more like a table/dbase. Excel 2010 is fine for 2+ million rows.

Instead of sheets for each week, just use one sheet for everything, but have a "week" column, and a proper date column. Then any reports/pivots can use those columns to filter which is way easier than referencing a bunch of different sheets IMO. And if you change your mind and go sql or access, it's easier to dump.

 

 

This is what I would do.  To achieve YTD remove the week from the Pivot and you automatically have YTD.

New Thread
Other Resources My Cup of Joe » Access or Excel? Rss Feed