Tables (1 Viewer)

Alvin neil

New member
Local time
Today, 16:44
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

..forever waiting... waiting for jellybean!
Local time
Today, 21:44
Joined
May 7, 2009
Messages
19,169
do it really means Consecutive (like there is no gap).
or At Least 6 month of contribution?
 

Alvin neil

New member
Local time
Today, 16:44
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

..forever waiting... waiting for jellybean!
Local time
Today, 21:44
Joined
May 7, 2009
Messages
19,169
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
Today, 13:44
Joined
Aug 6, 2017
Messages
1,899
Hi

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

MajP

You've got your good things, and you've got mine.
Local time
Today, 09:44
Joined
May 21, 2018
Messages
8,463
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)
 

mike60smart

Registered User.
Local time
Today, 13:44
Joined
Aug 6, 2017
Messages
1,899
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

Top Bottom