Help on Report (1 Viewer)

[jrmondonedo]

(SOLVED) Help on Report

Again, I'm stumped and was left with twiddling my thumb because of the report I want to create :banghead:

The report is based on two related tables: (1) Members (Primary Key: Member ID); and, (2) Payments (Foreign Key: Member ID).

The contents of the report are the following:

1. Year (from Table: Payments)
2. Month (from Table: Payments)
3. Description (from Table: Payments)
4. Member ID (from Table: Members)
5. Amount (from Table: Payments)
6. OR # (from Table: Payments)
7. First Name (from Table: Members)
8. Family Name (from Table: Members)
9. Representative (from Table: Members)
10. Relationship (from Table: Members)

I would like to show the following on the report:

1. Total number of members to date (used: DCount("[MemberID]","[Members]") )
2. Total number of members paid for the month (used Count Function)
3. Payment Penetration Rate... and this is the portion where I'm losing a lot of hair from.

How do I compute the rate at by dividing the total number of members (from table: Members)/total number of members paid (from table: Payments)?

I tried =DCount("[MemberID]","[Members]")/([CountOfRemarks]) on expression builder but it's not working

Please don't hesitate to peruse the screenshots provided herein by this green apple.

Thanks and HELP!!!

 

[sensetech]

First, your report footer says "Total number of paid members who paid for the month" suggesting that the report is for a single month. The detail lines show several different months with at least one person paying in two of those months so any totals or percentages will be misleading.

Is the report supposed to be for a single month?

 

[jrmondonedo]

That's right; ideally, the report will be used to capture total cash payments received every single month or a specific period specified by the user.

However, the report details also show a breakdown of different months covered for lump sum payments or current payments intended to cover arrearages.

 

[sensetech]

So on January 26th I might be paying for December, January and February and it would appear as three separate records with the same payment date.

I think the first issue you have to address is whether the totals are supposed to be by payment date or effective month / year. If it's payment date then you could get weird results - if you had just one member and he paid for three months in one hit (as above) then you'd show a penetration rate of 300% for that payment date, wouldn't you?

 

[jrmondonedo]

Theoretically, you're correct and I see what you're trying to drive at Thanks for pointing it out.

What I wish to show on the report are the following:

1. Total payments received for the period specified by a user;
2. Total number of members who paid for the same period; and,
3. Rate (total # of members when the report was generated / total number of members who paid for the same period).

Would it be more advisable to count the OR Nos. instead of the Member IDs? If it is, it could present a complication since there are entries without a corresponding OR No. :banghead:

 

[jrmondonedo]

I think I almost nailed it by using the sql whatchamacallit code below

SELECT tmp.PaymentDate, tmp.MemberID, tmp.ORNumber, tmp.Description, Count(tmp.MemberID) AS NoOfPayees, Sum(Payments.Amount) AS TotalOfAmount
FROM (SELECT PaymentDate, MemberID, ORNumber, Description, Amount FROM Payments GROUP BY PaymentDate, MemberID, ORNumber, Description, Amount) AS tmp INNER JOIN Payments ON tmp.ORNumber = Payments.ORNumber
GROUP BY tmp.PaymentDate, tmp.MemberID, tmp.ORNumber, tmp.Description, tmp.ORNumber+IsNull
HAVING (((tmp.PaymentDate) Between [Type Beginning Date:] And [Type Ending Date:]))
ORDER BY tmp.PaymentDate, tmp.ORNumber;

However, the report is not showing payment records without a corresponding ORNumber (or those with null values on this particular column).

Please help on how to I could change the code above to include records with null values on the ORNumber column

 

[jrmondonedo]

(SOLVED) Help on Report

After a lot of experimenting, I'm back at last! By using left join on the relationship, the report is now showing records with null values on the ORNumber column

This is the code I used:

SELECT tmp.PaymentDate, tmp.MemberID, tmp.ORNumber, tmp.Description, Count(tmp.MemberID) AS NoOfPayees, Sum(Payments.Amount) AS TotalOfAmount
FROM (SELECT PaymentDate, MemberID, ORNumber, Description, Amount FROM Payments GROUP BY PaymentDate, MemberID, ORNumber, Description, Amount) AS tmp LEFT JOIN Payments ON tmp.ORNumber = Payments.ORNumber
GROUP BY tmp.PaymentDate, tmp.MemberID, tmp.ORNumber, tmp.Description
HAVING (((tmp.PaymentDate) Between [Type Beginning Date:] And [Type Ending Date:]))
ORDER BY tmp.PaymentDate, tmp.ORNumber;

By nailing this problem, which I inflicted upon myself by the way, I learned a lot from the experience and well-meaning members of this site for which I will always be grateful.

Thanks everybody