New database

YOUNAN

Board Regular
Joined
Feb 10, 2015
Messages
101
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
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.
 
Upvote 0
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
 
Upvote 0
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).
 
Upvote 0
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
 
Upvote 0
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).
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,164
Messages
6,170,444
Members
452,326
Latest member
johnshaji

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top