Re: Report based on calculations of 2 records

Hi Crystal,

My structure:

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

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

My apologies for not letting you know that each customer has an agreement
period to fulfill which is governed by the AgreementStart and AgreementEnd.
The agreement period is considered fulfilled if the customer pays until the
AgreementEnd date.

And for each transactions, my user keys in the PayFrom and PayTo dates based
on how much the customer wants to pay, in terms of days. This form is a
subform and is shown as in a data*** view. Since there are many customers
coming in to pay at different times, the receipt number (transaction table's
running number) will not be sequential for any one customer.

We have a statement report that lists the customer and his payment based on
a query that links the two tables together, via the CustID.

Even if the customer fulfills the agreement, there is no control to stop the
users from issuing more receipts under his account. That is why we need to
check the data entry between records to make sure users do not make errors
keying in the PayFrom and PayTo fields.

Hope my reply helps you solve my problem.

Thanks for your patience.

"strive4peace" wrote:

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?


.

Loading