Tables

[Alvin neil]

Hi Alvin here, am new to access but I happen to have agreed to help someone develop a database for their youth group. So in this data there's a table called shares, members contribute every month and the date and amount contributed is recorded every time someone gives cash ok. So my first trouble is how do I fit all those dates in one table and associate each of them with amount paid on that day. So a member is eligible for a loan if he/she contributes for 6 consecutive months, please help me create an expression that calculates for how many consecutive months has a member contributed. Please

 

[arnelgp]

do it really means Consecutive (like there is no gap).
or At Least 6 month of contribution?

 

[Alvin neil]

do it really means Consecutive (like there is no gap).
or At Least 6 month of contribution?

Am really not sure if the should be or shouldn't be gaps but am pretty sure it's atleast 6 months of contributions

 

[arnelgp]

you need two Queries to achieve number of monthly contributions:

SELECT tblShare.MemberID, Format(tblShare.DateField,"mmyyyy") AS dte
FROM Table1
GROUP BY TtblShare.MemberID, Format(tblShare.DateField,"mmyyyy")

save the above as Query1.
then use Query1 to make a Total Query to show how many monthly contributions each member have made:

SELECT Query1.MemberID, Count(IIF(Trim(Query1.dte & "")="", Null, 1)) AS CountOfdte
FROM Query1
GROUP BY Query1.MemberID;

 

[mike60smart]

Hi

You would need at least 2 tables - 1 for the Members details and another for the Payments made

 

[Alvin neil]

Hi

You would need at least 2 tables - 1 for the Members details and another for the Payments made

But how will I know who made which payment

 

[MajP]

But how will I know who made which payment

You probably need to read up on database normalization and joins. This example is very close (just swap orders for payments and you will have the idea)

SQL Joins - The Ultimate Guide - Essential SQL

SQL joins are used to combine columns from separate tables. Learn why joins are important, and how to use various join types.

www.essentialsql.com

 

[mike60smart]

Your Members table would have a Primary Key named MemberID - Autonumber
The related table MembersPayments would have its own Primary Key - Autonumber nameed MembersPaymentID
Also in this table would be the Foreign Key named MemberID - thisa would be a Number DataType>
You would then set Referential Integrity between the two tables on MemberID

Then for Data Input you would have a Main Form based on Members and a Subform based on MembersPayments