Recurring Charges code

[FEDERAL]

Does anyone have simple generic code for recurring billing so I don't have to reinvent the wheel?

[Rabbit]

Sorry, as a general rule of thumb, we do not give out wheels. However, if you're willing, we will help you solve any wheel making problems given your wheel's specifications.

[FEDERAL]

Thank you..

Here's what I need to do.

Add a set [PledgeAmount] generated every month (starting at no special day) from
[StartDate ] untll [EndDate] for a [MemberID]

simple?

[Rabbit]

So if let's say member id "Person A" has date range 1/1/2008 to 3/1/2003, you want to create 3 records with pledge amount in some pledge table?

How much VBA do you know?

[FEDERAL]

That's pretty much it. However, the pledge amount wouldn't be added to the Pledge Table until that month ( with some sort of monthly Update procedure that would update everyone with a pledge amount due that month)

It's been a while and my VBA is week

[Rabbit]

Are you able to set up a query that returns the records that should be appended for any given month?

[FEDERAL]

No, that would be the first think I could use help with

[Rabbit]

No, that would be the first think I could use help with

Okay, but before that I need to know how your tables are set up. i.e. what fields it has, their data type, and how the tables are related to each other.

[missinglinq]

Here's a template from Micro$oft for ACC2000 (and I assume can be run in later versions) that appears to do what you need to have done!

Charitable Contributions Database

Welcome to TheScripts!

Linq ;0)>

[FEDERAL]

Thanks Linq, but this doesn't give me recurring pledges

[FEDERAL]

Okay, Here goes...


Table: tblDonation
Columns
Name Type Size
DonationID Long Integer 4
DonationFor Text 50
DonationAmount Currency 8
DatePledged Date/Time 8
Date Received Date/Time 8
PayMethod Text 50
Check# Text 50
StatementSent Yes/No 1
Payed Yes/No 1
AmountPaid Currency 8
FamilyID Long Integer 4
DateDue Date/Time 8
Table Indexes
Name Number of Fields
AmountPaid 1
Fields: Ascending
DonationID 1
Fields: Ascending
FamilyID 1
Fields: Ascending
PrimaryKey 1
Fields: Ascending
__________________________________________________ ___________________________

Table: tblFamilyID
Columns
Name Type Size
keyID Long Integer 4
FamilyID Long Integer 4
HomeAddress1 Text 255
HomeAddress2 Text 255
HomeCity Text 255
HomeState Text 255
HomeZip Text 9
HomePhone1 Text 255
HomePhone2 Text 50
Relationships
tblFamilyIDtblMembers
tblFamilyID tblMembers
FamilyID FamilyID
Attributes: Not Enforced
RelationshipType: Indeterminate
Table Indexes
Name Number of Fields
FamilyID 1
Fields: Ascending
keyID 1
Fields: Ascending



__________________________________________________ __________________________________________________ ______________
Table: tblMembers
Columns
Name Type Size
MemberID Long Integer 4
FamilyID Long Integer 4
LastName Text 255
FirstName Text 255
DOB Date/Time 8
SpouseID Long Integer 4
BusAddress1 Text 255
BusAddress2 Text 255
BusCity Text 255
BusState Text 255
BusZip Text 9
BusPhone Text 255
Extention Text 255
Fax Text 255
CellPhone Text 50
Email Text 50
Website Text 50
Profession Text 255
Note Memo -
MaritalStatus Text 50
Age Text 50
Gender Text 50
CreationDate Date/Time 8
PotentialDonor Text 50
Company Text 50
Title Text 50
Assistant Text 50

Relationships
tblMemberstblActivityLog
tblMembers tblActivityLog
MemberID 1 MemberID
Attributes: Enforced, Cascade Updates, Cascade Deletes
RelationshipType: One-To-Many
tblFamilyIDtblMembers
tblFamilyID tblMembers
FamilyID FamilyID
Attributes: Not Enforced
RelationshipType: Indeterminate
tblMemberstblAddresses
tblMembers tblAddresses
FamilyID FamilyID
Attributes: Not Enforced
RelationshipType: Indeterminate
tblMemberstblChild
tblMembers tblChild
FamilyID FamilyID
Attributes: Not Enforced
RelationshipType: Indeterminate
tblMemberstblImportantDate
tblMembers tblImportantDate
FamilyID FamilyID
Attributes: Not Enforced
RelationshipType: Indeterminate

__________________________________________________ ______________

Table: tblPledgeRecords

Columns
Name Type Size
PledgeRecordID Long Integer 4
MemberID Long Integer 4
ProjectID Text 50
AmountContributed Currency 8
PaymentDate Date/Time 8
ReceiptSentDate Yes/No 1
Table Indexes
Name Number of Fields
MemberID 1
Fields: Ascending
PledgeRecordID 1
Fields: Ascending
PrimaryKey 1
Fields: Ascending
ProjectID 1
Fields: Ascending


The table"tblFamilyID" is used to join spouses so that they can be entered as individual members but still share common Family info. (ie. home address, children, donations etc.)

[FEDERAL]

To much info? Where did everyone go?!

[Rabbit]

To much info? Where did everyone go?!

Sorry, this fell through the cracks. I'll take a look right now.

[Rabbit]

Ok, that was a lot. Sorry, I didn't mean for you to post all your tables, I only needed the ones related to your problem, which is:

tblPledgeRecords
PledgeRecordID Long Integer 4
AmountContributed Currency 8
ReceiptSentDate Yes/No 1

tblDonation
DonationID Long Integer 4
DonationAmount Currency 8
DatePledged Date/Time 8

Ok, these look to be the important fields/tables. However, there were a few things I was confused by. There seems to be payment in both the Donations table and the Pledge table. Why is that? I was under the impression that Donation holds the information about each donation and the Pledge holds monthly payment information.

Here's what it should look like:

tblDonation
DonationID; Autonumber; PK
StartDate; Date
EndDate; Date
Amount; Currency

tblPledgeRecords
PledgeID; Autonumber; PK
DonationID; FK
RecordDate; Date

[FEDERAL]

thanks for coming back.

"tblPledgeRecords" doesn't exist any longer.(it was from an earlier version. I don't know why it showed up_)

[Rabbit]

tblDonation
DonationID; Autonumber; PK
StartDate; Date
EndDate; Date
Amount; Currency

tblMonthlyPayments
MonthlyPaymentID; Autonumber; PK
DonationID; FK
RecordDate; Date

Well, using those two tables I laid out, you can create a query that will let you know which Donations don't have a record in Monthly Payments. Then it's a simple matter of appending those into the table.

[FEDERAL]

OK, I got to that point and now have a query that shows by "FamilyID" in the "tblDonation" that has an amount >0 in "Amount"...

Now what?

[FEDERAL]

But shouldn't the pledge info (start date, end date, amount) be with the Member or Family table and then added to the donation table if the date =Month() and amount is >0 ?

[Rabbit]

But shouldn't the pledge info (start date, end date, amount) be with the Member or Family table and then added to the donation table if the date =Month() and amount is >0 ?

I don't see anything in your Member/Family tables that would denote that each family member makes individual donations.

From what I can tell, the donation agreement sets out how much per month, when it starts, and when it ends. And each agreement is tied to a family and each family has multiple members. I don't see a field in your member table showing that each member may have differing amounts.

[FEDERAL]

I meant to say...
If month() is between "StartDate" and "EndDate" then add record to
"tblDonation" where "DonationAmount" is "Amount" from "tblFamily"

[FEDERAL]

I did get it to work, Thanks
Now I just need to figure how to not allow the append to happen more than once a month. and to just use the month/year (no day) in the criteria

[Rabbit]

tblDonation
DonationID; Autonumber; PK
StartDate; Date
EndDate; Date
FamilyID; FK

tblMonthlyPayments
MonthlyPaymentID; Autonumber; PK
DonationID; FK
RecordDate; Date

tblFamily
FamilyID; Autonumber; PK
Amount; Currency

So your tables are something like that.

Well, the first thing is to find out which ones don't yet have a record for that month. And the first step to do that is to find out which ones do have a record.

Query1:
 
SELECT tblDonation.DonationID
FROM tblDonation INNER JOIN tblMonthlyPayments ON tblDonation.DonationID = tblMonthlyPayments.DonationID
WHERE Month(Date()) = Month(RecordDate) AND Year(Date()) = Year(RecordDate);
 

Then you find the ones that don't have a record.

Query2:
 
SELECT DISTINCT DonationID, Amount
FROM tblDonation INNER JOIN tblFamily ON tblDonation.FamilyID = tblFamily.FamilyID
WHERE DonationID NOT IN (SELECT * FROM Query1);
 

Then you add those records.

Query 3:
 
INSERT INTO tblMonthlyPayments
SELECT *
FROM Query2;
 

[FEDERAL]

Thanks but I couldn't make it work. Query3 gave me an error (couldn't find field Expr1 )

But I'm not sure I understand what this does anyway.

I Started by just using:

tblDonation
DonationID; Autonumber; PK
Monthly; Y/N
Recorded; Date
FamilyID; FK


tblFamily
FamilyID; Autonumber; PK
MonthlyPledge; Currency
StartPledge; Date
EndPledge; Date
Monthy; Y/N

Query1 - (Find all the FamilyID's with Monthly Pledges set up within a set Start Stop Date range)

SELECT tblFamilyID.FamilyID, tblFamilyID.MonthlyPledge, tblFamilyID.Monthly, tblFamilyID.StartPledge, tblFamilyID.EndPledge
FROM tblFamilyID
WHERE (((tblFamilyID.Monthly)=Yes) AND ((tblFamilyID.StartPledge)<=[Date]))
WITH OWNERACCESS OPTION;

Query2 - (Append record to tblDonation

INSERT INTO tblDonation ( FamilyID, DonationFor, DonationAmount, Monthly )
SELECT [qryMonthlyY/N].FamilyID, "Donation" AS [For], [qryMonthlyY/N].MonthlyPledge, [qryMonthlyY/N].Monthly
FROM [qryMonthlyY/N]
ORDER BY [qryMonthlyY/N].FamilyID
WITH OWNERACCESS OPTION;

[Rabbit]

Maybe you need to take a closer look at my queries, what you have is nothing like my example.