Re: Report based on calculations of 2 records

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?


.