Excel vs. Access for multi-page spreadsheet

[RiderJen]

Hello,
I'm seeking guidance on whether to move the data I'm currently managing from Excel to Access for easier maintenance and manipulation. I've inherited the membership roster - an Excel file - of a new volunteer organization of 45 trained members and about 100 un-trained, and growing. We get new sign-ups almost daily and have a goal of 200 trained members by end of 2018, and plans to grow each year.

The current roster includes 4 worksheets for different types of memberships. Data on each includes name, email, phone, home address, skills, training completed, and notes.

When an un-trained member becomes certified, I move that person from the Uncertified sheet to the Certified by deleting from one sheet and pasting the row into another sheet. This is tedious and prone to errors (did I insert the row, or copy over existing data?). Also, some members have other roles that are identified on other sheets.

I'm beginning to wonder if I should import the data to Access for easier manipulation and reporting -- or, if I just need to learn more advanced Excel skills. I use Microsoft Office 7 with the entire Windows 7 suite. I'm currently not using Access but have experience using it in a previous organization (I inherited it but didn't create it).

Ideally it should be sharable among at least 2 members so that if I'm unable to make updates then the other person can fill in. Currently our Excel spreadsheet is stored in Google Drive of our Gmail account.

Do these goals support the idea of moving to Access? I understand setting it up takes time, but once that is complete the routine updates and reporting features will pay off.

I hope I've been clear and would appreciate any guidance.

Thank you,
Jen

 

[alansidman]

Sounds as if this may be a good candidate for Access. Here are a couple of important links to help you build your db.

Fundamentals of Relational Database Design -- r937.com
Access Basics
DataPig Technologies Access Videos

 

[Fazza]

Sounds spot on, Jen. Database (Access ) scalability will be good, more robust/control, and better for multi-users. Better reports too. All the best, Fazza

 

[RiderJen]

Thanks to both of you for your replies. I will seek help from among the volunteers in our organization to see if anyone has experience and know-how to set it up so I can over.

The links are very helpful, alansidman - thank you.

Jen

 

[RiderJen]

I'm back with a new question:

BACKGROUND DETAILS: I will be the primary Access user, running it on my laptop at home. I would like to have 1 or 2 other members to help maintain it for brief periods when I'm away on vacation (add new members, update data, backup data). Each of us will access the database from our own home computer. A database backup file will be stored remotely for safe-keeping.

QUESTION: Is there a name for this type of Access sharing method, so I can look it up in a reference guide (or one of the helpful links alansidman shared above)?

Thank you,
Jen

 

[alansidman]

Sharing an Access DB is really a non event. So long as the Tables are stored on a common machine (server) or a shared network drive, then each of you should have rights to the db. You should split your db into a front end (forms and queries) and a backend (tables). https://support.office.com/en-us/ar...database-3015ad18-a3a1-4e9c-a7f3-51b1d73498cc

 

[RiderJen]

Sharing an Access DB is really a non event. So long as the Tables are stored on a common machine (server) or a shared network drive, then each of you should have rights to the db. You should split your db into a front end (forms and queries) and a backend (tables). https://support.office.com/en-us/ar...database-3015ad18-a3a1-4e9c-a7f3-51b1d73498cc

We won't be using a common machine or shared network drive. The database will be on my home computer which is accessible by no one else but me (and also my husband). The person who would serve as my backup will not have access to my computer via server or shared network drive.

I'll go to the Access forum to continue this discussion since that seems the proper place for it. Thank you for your help!

 

[RiderJen]

UPDATE: I consulted with some team members who urged me to take advantage of Excel's features (filters, pivot tables, etc) to manage the data the way I want to. I agree that I should at least give them a try, especially since we have no way to share a database between any of us (no common computer, no server).

I have lots of experience with basic Excel features, and some intermediate experience. I still have my workbooks (Excel 2007 Basics, and Excel 2007 Beyond the Basics) from a class I took about 10 years ago.

I'm a strong independent self-learner, so my next step is to find a good series of tutorials to take me down the path of mastering Excel. And, of course, turning to Mr. Excel for additional help.