?? Use Excel or Access

[Jaylin]

I have an excel data like below. I need to use these data to do different
analysis.

If I use excel, I can do pivot table but it does not allow users to edit the
data easily in pivot table.

If I use Access, then it means I need to break this source table into
different tables to avoid duplication, such as a table for Budget#, a table
for Activity category etc. And since I am not very familiar with Access, I
worry that I may create duplicated data in my analysis.

As I am new to Access, would appreciate expert advice on the choice.


DATA TABLE BELOW
Budget# Activity Source Category January
60349 Promo SAMOA Print 291.67
60349 TV SAMOA Radio 125
24530 TV APAC CB Database 0
68144 Promo APAC CB Agency 50000
99775 Promo APAC CB Other 0
85873 Promo APAC CB Internet 12500
19023 Promo Australia Print 0
95848 Promo Australia Core 23492

 

[Jeff Boyce]

Jaylin

How does having the data stored in Access make it any easier to "edit a
pivot table"?

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

 

[Jaylin]

Hi Mr Boyce

Thank you in advance for your teaching. It really leads me think more
thoroughly on my need.

Actually, the table I provide is a forecast table. And I need to combine
this table with another similar table when the actual comes out. My analysis
will provide actual vs Forecast comparison. Then I will ask the users to
update the forecast of future months.

Using the Access, I can combine data from different table easier (I presume)
and export back to Excel file. Once the user has updated the excel file, I
will import back to Access for record. Using this mothod seems easier for
data manipulation but I still worry for duplication as sometimes I need to do
other analysis using the source and target country.

Using Excel can avoid error better but I may need to create more columns in
order to create relationship between the 2 tables.

Hope you would not mind to provide further enlightenment. Just wondering if
there is good source / book that I can refer for good database design for
Access?

--
Thanks a million for your time and expert advice
Jaylin
*****Jaylin Message ended*******

Jaylin

How does having the data stored in Access make it any easier to "edit a
pivot table"?

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

 

[Jeff Boyce]

(see comments in-line)

Hi Mr Boyce

Thank you in advance for your teaching. It really leads me think more
thoroughly on my need.

Actually, the table I provide is a forecast table.

Calling it a "forecast" table could be interpreted to mean that you are
trying to put data into it that would allow it to display a forecast. If
that is what you mean, you may have tried to make your table look like your
output/report. You don't want to do that with a relational database table.

And I need to combine
this table with another similar table when the actual comes out. My analysis
will provide actual vs Forecast comparison. Then I will ask the users to
update the forecast of future months.

If you have two tables that hold the same data elements ("...another similar
table ... actual ") you are working harder (and making Access work harder)
than you need to. If you use a single table and add an extra field to
designate "actual" values...

Using the Access, I can combine data from different table easier (I presume)
and export back to Excel file.

Depending on your definition of "combine data", yes, you could use a query
to join two tables on their common/shared field(s) and show data from each.
And export the query to Excel.

Once the user has updated the excel file, I
will import back to Access for record. Using this mothod seems easier for
data manipulation but I still worry for duplication as sometimes I need to do
other analysis using the source and target country.

I don't understand why you need to send the data to Excel and back to
Access. If you mean running calculations on the data when you say "data
manipulation", then yes, Excel is design for that. But why would you then
need to push the data back into Access?

Using Excel can avoid error better but I may need to create more columns in
order to create relationship between the 2 tables.

I don't understand what you mean by this.

Hope you would not mind to provide further enlightenment. Just wondering if
there is good source / book that I can refer for good database design for
Access?

Database Design for Mere Mortals (Hernandez) for issues re: database design.

Any of John Viescas' books on using Access for understanding how to make use
of Access' features/functions.

Good luck!

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/