Re: Report based on calculations of 2 records

Hi Kelvin,

It would help if I knew more about your data structure... can you tell me what your tables and main fields are? From what you wrote, I have figured this much out:

Your structure:

*CustomerInfo*
CustID, autonumber
Lastname, text
Firstname, text
CustNum, text (ie: Member123),
Address
Phone

*Transactions*
TranID, autonumber
CustID, long integer
ReceiptNo
TranDate, date
PayTo, date
PayFrom, date

you said, "If he comes again to pay on a different time for 11/01/2006 to 16/01/2006, then another transaction record is added."

My question is this: How do you know what period he is paying for? You are going to add a record in the transactions table for his payment ... Where does the PayTo and the PayFrom information come from? If it is on his statement, where is the information that generated the statement?

Warm Regards,
Crystal
Microsoft Access MVP 2006

remote programming and training
strive4peace2006 at yahoo.com
*
Have an awesome day ;)


Kelvin Leong wrote:
Hi Crystal,

Is there no other way to generate the report from my existing records? Creating this table will effect the rest of my records and I have more than 20000 of such transaction records to date.

Thanks

"strive4peace" wrote:


SOMEWHERE, you must create the pay periods that the customer will use...

perhaps a table like this:

*CustPeriods*
CustPerID, autonumber
CustID, long integer -- FK to customers
Date1, date
Date2, date
PerNum, integer --> calculated field*

where PerNum is number sequentially from 1 to whatever

* although I discourage the use of storing calculated fields, sometimes it makes sense from an efficiency point-of-view

When a customer makes a payment, you can choose the period that has been set up form them (store CustPerID)... that way, you have sequential numbers to link to (PerNum) and it is easy to tell if one is missing (just create a Numbers table with LineNum from 1 to whatever and do an outer-join with it)

But, you are still missing something critical -- what if a payment applies to more than one period??? How are you going to handle that?


Warm Regards,
Crystal
Microsoft Access MVP 2006

remote programming and training
strive4peace2006 at yahoo.com
*
Have an awesome day ;)


Kelvin Leong wrote:

Hi Crystal,

The time zones are not defined. It depends on how much the customer wants to pay. I have experimented creating a module that can be used to compare the previous record but I can't seem to make a report using the following reference:

http://support.microsoft.com/kb/210504/en-us

Do let me know if I am heading the right direction.

Thanks.

"strive4peace" wrote:



Hi Kelvin,

What I am really asking is if these "time zones" are defined somewhere... if so, you could have a table

*Periods*
PeriodID, autonumber
PayFrom, date
PayTo, date
ScheduleID, long integer
-- FK to Schedules if you have different dates for different schedules
LineNum, integer -- sequence number (within schedule if applicable) for period

*Payments*
PaymentID, autonumber
PayDate, date
PeriodID, long integer -- FK to Periods table
ReceiptNo
Amount, currency
etc

Then, you can store PeriodID (long integer) in your payments table and, with the LineNum field, you will easily be able to do an outer join to see what is missing.

By storing PeriodID_start and PeriodID_end (or null if it is ongoing) with the customer and their account, you will also be able to do simple calculations for Running Sums.

You will always have the information in the Periods table accessible by storing the PeriodID -- so you can show PayFrom and PayTo on reports.

You will also be able to log multiple payments for the same period.

Don't payments, however, always apply to the oldest balance first? And what if a payment is applied to parts of 2 different periods? Perhaps you need to approach your data structure from that perspective...


Warm Regards,
Crystal
Microsoft Access MVP 2006

remote programming and training
strive4peace2006 at yahoo.com
*
Have an awesome day ;)


Kelvin Leong wrote:


Hi Crystal,

The PayFrom and PayTo dates are of dd/MM/yyyy format. If a customer pays

from 01/01/2006 to 10/01/2006, then the PayFrom = 01/01/2006 and PayTo =

10/01/2006 and these values are keyed in to the Subform's respective fields. If he comes again to pay on a different time for 11/01/2006 to 16/01/2006, then another transaction record is added.

Because the running number for the transactions are not in sequence due to the fact that there are many customers' payment history is link to this Transaction.

The Form and Subform looks something similar to what is shown below:

Form
Name: abc123
ID: MEMBER213
Address: 1 First Street
Phone: 123456789

Subform
-------------------------------------------------------------
Receipt No | Date | PayFrom | PayTo
-------------------------------------------------------------
123 | 01/01/2006 | 01/01/2006 | 10/01/2006
145 | 14/01/2006 | 11/01/2006 | 16/01/2006

The Receipt No is the running number for the Transaction table and the Date is the date when the customer comes to pay.

The link between the Form and Subform is via the customer's ID, and is not shown in the Subform since it is hidden.

With this report, I can check whether the PayFrom and PayTo values are keyed in correctly. If not, PayFrom (for 145) - PayTo (for 123) will not equal to 1.

Thanks for your help.


"strive4peace" wrote:




Hi Kelvin,

Do you have standard PayFrom and PayTo dates? Or do they vary depending on the customer? How are these dates defined?

Warm Regards,
Crystal
Microsoft Access MVP 2006

remote programming and training
strive4peace2006 at yahoo.com
*
Have an awesome day ;)


Kelvin Leong wrote:



I have 2 tables, one is the CustomerInfo and the other their Transactions. I have a form which shows individual CustomerInfo from the CustomerInfo table, and a subForm which shows their payment history from the Transactions table in a data*** format.


From the Transactions table, there are 2 particular columns that is


important, namely PayFrom (date) and PayTo (date). Lets say that this customer has made 10 payments, records 1 to 10. I want to know whether (PayFrom (date) from [record 2]) equals to (PayFrom (date) of [Record 1]) + 1. SImilarly, I want to know whether (PayFrom (date) from [record 3]) equals to (PayFrom (date) of [Record 2]) +1, so on and so forth.

How am I able to generate a report to show that for a particular customers' payment history is entered correctly, that is the PayFrom (Date) of the latter record is 1 day after the PayTO (date) of the previous record?

.