Create a church database in Access

Writing good software is hard. Nowhere do you say what your background in software is, but if it is "none", then gracefully decline. Tell them some professionals in an Access forum told you it is beyond what can be expected of a beginner, and even some pros may have challenges with some finer points. Your time may be better invested in finding sponsors to fund the $500/yr membership fee.

Not sure where you are getting the $500/yr figure from. It would probably cost that and a little more to have a professional (like myself) develop a database for you. This is not a simple task. First it requires an understanding of relational database design.

Are you using the Outlook or Google calendar as a shared calendar?

And no Excel is not very suitable for the task of attendance.

To start off with, you need several tables set up in a normalized design.

Sometimes people who know little about software development have a terribly naive impression of the costs involved in creating an application and maintaining an application. I assume the reason the organization wants to replace the existing application is to save $500 a year in licensing costs for an existing commercial application. It'll take 8 to 10 years worth of licensing costs to cover the equivalent cost of creating a minimally usable replacement. That's assuming you hire someone experienced and competent. If you try to do it yourself, the challenge is going to be pretty tough.

Chances are high that a beginner with no knowledge of relational database design will take weeks or months to achieve a minimally usable result. If your time is worth $0 an hour to the church, your church "saves" money, of course. But then they have a minimally usable result to replace the one which works well, according to your assessment. This is what the old timers used to call "penny wise and pound foolish". Or in the US we'd probably say "penny wise and dollar foolish".

If you want to take this on as a donation to your congregation, that could be a fine thing to do. But the questions asked in this initial post suggest it'll be a long, frustrating uphill challenge for someone with no prior experience.

Still, should you decide to go ahead, the place to start is a study of Normalization, or the process of designing appropriate tables. Leave the details of color-coding events and linking calendars to a later stage.

Is there a way to set up relationships between individuals, who will each have their own line in the Members table?

For that functionality you might like to take a look at FamilyStructure.zip in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

This little demo file illustrates how an individual's parentage can be modelled by means of a table which models the many-to-many relationship type between two instances of a FamilyMembers table as below:

The demo illustrates how this can be represented in the user interface by means of forms and subforms.  As you'll see the implementation of even such a simple feature is not trivial.  The question you have to ask yourself is do you have the skills, or are you prepared to spend time gaining the skills which would enable you to develop a database with the level of functionality which is required in your case?

From your post my impression is that your relational database  skills are currently quite limited, and you would need to become much more comfortable with working in the development environment which Access provides.  If you do decide to proceed then you might, in the first instance, care to take a look at the DatabaseBasics demo in my same OneDrive folder.  Other demos in the same folder which might be of interest to you are Relationships; Boolean; and, most importantly, Normalization.  The last provides as simple as possible, but no more so, introduction to the process of normalization.  A good understanding of the first three Normal Forms at least is essential if a database is to be fit for purpose.

I should stress that none of my demos are intended to be tutorials or templates, and do demand some basic knowledge of the nuts and bolts of Access, including SQL and VBA.

@Scott: you probably did not google "Church360". Here is an example pricing: