New database

[YOUNAN]

am a beginner in access using access 2010, I am trying to build a database of our church, we are more than 500 families and total members are above 1330 persons.



<!-- accessforums, first post, 728x90, green style --> <ins class="adsbygoogle" style="width: 728px; height: 90px; display: inline-block;" data-ad-client="ca-pub-8768391963079401" data-ad-slot="7415104082"></ins>


My goal is to build a database in which I put the names, address, age and other information and also to count the fee that each family has to pay every year, and I have data from year 2014 and I want this database to continue counting in future.

The fee was 400 kr for adults and 200 kr for children and it changed from 1 jan, 2017 to 500 kr for adults only the children fee was not changed.
So I want to first to start with year 2014 and count how many adult and children members in each family ( only active ) and multiply by their fee and deduct the amount from their contribution that was paid in year 2014 and move the difference if minus or plus to year 2015 , and to continue doing the same in 2015 & 2016

2017 the fee for adult should change to 500. And this to be done to active members And that it can continue to do so at least ten years or if possible forever. But also to allow me to see if I check on a family the hole history of fees and contributions from day 1 that the family was registered.

and if a new family is registered the fees will be count from the registration year.

And also to get reports , such as the total members how many adults, how many children, new born , dead , moved , active, non-active or left the church.

I have started making a database and made some tables and a query, but now I am stuck, i want to attach what i have done so far but i can't attch anything. so if someone could guide me on how to attach a zip file. in FAQ it says that an admin can allow me to attach a file and after that i appreciate if someone could do whatever update on my databas and change in tables and add needed queries to help me achieve my goal above. also if my tables need to be joined together or to split it is ok to do that.

I can manage Forms and report by myself, I just need help in how my tables and my queries should look like so please feel free in doing whatever changes needed to be done.
I appreciate any help and thanks to all for your help and time.

Best regards

Younan

 

[ranman256]

You will want a tPersons table for every persons details, and a Household field.
tHousehold table will hold the home address, family name.

each family member will have the same tPersons.HouseHold ID.
you could assign a tPersons.Relation as father,mother ,son,aunt,etc.

a tDues table could have a charge for that household ,yearly, monthly,etc.
tPayments table to collect payments.

 

[Macropod]

Cross-posted at: http://www.accessforums.net/showthread.php?t=72165

Please read Mr Excel's policy on Cross-Posting in rule 13: http://www.mrexcel.com/forum/board-announcements/99490-forum-rules.html

 

[YOUNAN]

Dear Macropod

I tried to post here cause i got no help on the other site, so i would like to ask before i put another question if i can continue this post or not.

Thanks in advance.

Younan

 

[Macropod]

You should read the Rule I pointed you to.

 

[ranman256]

People who read MrExcel may not read Access forums.

 

[xenou]

if you have done something so far in regards to designing table structure you can post it here as text. A conventional table representation as text is the table name followed by column names and datatypes in parentheses, with the primary keys underlined or bolded.

For example:

Employee(EmployeeID number, FName text, MName text, LName text, DateHired date, IsActive yes/no)
Department(DepartmentID text, DepartmentName text)

these are somewhat loose datatypes but generally you want to indicate if datatypes are numeric, text, or date (with more or less detail as you desire, such as whether numbers are float or int, or the length allowed for text fields).

 

[YOUNAN]

Here the tables i have made already.

addresses table: family ID ( number ), Street (text ) , Street no. ( text ), Dept No. ( text ), Zip code (text) , city (text ), phone home (text ), mobile (text ), 2nd mobile (text ), email (text ) ( maybe to much detailed cause it will be easier to filter ).

Contributions table : payment ID (auto number ), family ID ( number ), payment date (date ), payment method ( drop down menu text), reciept no. (text ) , Amount (currency) .

family Members table : family ID (number) ,[ First name, middle name, last name ( all text )], gender (text) , birth date (date), relationship ( drop down menu text), occupation, education attainment, status ( drop down menu text), Marital status ( drop down menu text), Count (yes/no).

Contribution Fee table: under 18 ( calculated ) , over 18 till 2016 ( calculated ), over 18 from 2017 ( calculated ),

I also made a Contributions Query : payment ID, family ID, Payment date, payment method, Reciept number, amount.
when i run the query it aks for Family ID and after that start and end date ie 2014 to 2018 and then it lists all payments done by that family for the period i wrote.

any help will be appreciated.

Thanks in advance

Younan

 

[xenou]

In general that looks reasonable. Only I don't think I'd have any tables that are all calculated fields (if that is even possible). You can use queries for that purpose. It would be a matter of preference but I would not put any dropdowns in tables either. Tables are best for storing data, and you can use forms for viewing, editing, inserting, or deleting data (even forms that look exactly like the tables look - datasheet views).

 

[YOUNAN]

Thank you for your reply, as i said before i am ggod in Tables and forms, but i need help with my queries , if anyone could just show me how it would look like to do what i described in my first post here.

Thanks in advance.