Tables (1 Viewer)

Alvin neil

New member
Local time
Today, 09:34
Joined
Feb 17, 2020
Messages
6
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

error reading drive A:
Local time
Today, 14:34
Joined
May 7, 2009
Messages
9,345
do it really means Consecutive (like there is no gap).
or At Least 6 month of contribution?
 

Alvin neil

New member
Local time
Today, 09:34
Joined
Feb 17, 2020
Messages
6
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

error reading drive A:
Local time
Today, 14:34
Joined
May 7, 2009
Messages
9,345
you need two Queries to achieve number of monthly contributions:

Code:
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:
Code:
SELECT Query1.MemberID, Count(IIF(Trim(Query1.dte & "")="", Null, 1)) AS CountOfdte
FROM Query1
GROUP BY Query1.MemberID;
 

mike60smart

Registered User.
Local time
Yesterday, 23:34
Joined
Aug 6, 2017
Messages
514
Hi

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

mike60smart

Registered User.
Local time
Yesterday, 23:34
Joined
Aug 6, 2017
Messages
514
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
 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom