Excel vs Access

[Dineth]

I am working at a government institution, agriculture sector.

We have field staff of ~150 doing different field ranges ~350. Some officers are assign to more than one field BUT no field is assign to more than one officer. (these fields belongs to different Districts, number of fields in each district is not the same)

we have four different programs namely new cultivation program (NPP), productivity improvement of existing lands (PIP), farmer capacity building (HRM) and post harvest handling (PHP).

Each program has its activities lets say NPP1, NPP2, PIP1, PIP2,PIP3 etc. for an example NPP1 is land inspection, NPP2 is donating planting materials. these activities are predefined and sequential. (planting materials can not be donate without land inspection)

Officers send their progress to progress monitoring unit monthly which includes progress of each program and each activity progress for that month.

My objective is to track, analyse, visualize officers progress.

These are the questions I have,
I am somewhat OK with Excel and NOT good with Access do you think I should use Access for this due to any special advantage only access can give me or can Excel handle this smoothly.

Any tricks and tips to consider before starting database?

[dominicb]

Goo evening Dineth

The need to use Access over Excel has in recent years dimniished somewhat now that Excel's miserly upper cell limit has been increased from about 66k. I have also switched an Access database recording production stats to Excel because what I'm using it for is quicker in Excel than Access, and, since the Excel file format was changed to .xlsx, the Excel file takes up about half the space of the Access file (130mb v 66mb).

That said, Access can't be beaten if you don't need to do many calculations with your data, want to set up a fancy data input screen using the forms functionality, and you want to set up relationships between your tables.

From what you've said, I think that Excel would handle this job very well. However, I would be tempted to set your data up in a tabular format, and use these to extract your numbers. Once the tables are set up, think about exporting them into Access (exporting between Excel > Access and back again is a breeze) and use this data to play with and see if using Access offers any benefits.

Access 13 offers loads of templates so set one of those up and pick it apart to see how relationships and stuff work. But I would start with Excel and learn Access at your own pace with a view to moving it if you think that this is necessitated.

HTH

DominicB

[CRIMEDOG]

how many people will be working with the files? How well can you trust that they won't delete you ranges / formulas / etc...

[Dineth]

how many people will be working with the files? How well can you trust that they won't delete you ranges / formulas / etc...

That would be only me.

[MarvinP]

Hi Dineth,

Because you have a set number of staff and fields and programs, Access seems appropriate. In Excel you will need to retype the staff, field, program, etc on each row to Pivot the data. Excel is easier to use and learn. More people know and use Excel than Access, ie more help with questions. Access is harder to learn. Pivots in Access can show text in the values while in Excel it can't.

I'd say to start collecting the data in Excel and Access can link to these Excel files and even convert or merge them (called append) into a huge table if/when needed.

[Dineth]

MarvinP-> thanks