Re: Report based on calculations of 2 records

Hi Kelvin,

you said, "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."

Now you say you WANT them to type in the dates again so that you can make sure that you can compare back to the agreement date ...

So when people come in they just choose to pay between any dates... not something that is set? WHY have the users key it in again when it can be looked up? You are just inviting errors.

anyway, it is obvious you are not willing to entertain any ideas to make your database structure more solid, so here is the answer to what you asked:

If you are just wanting the date of the record with the MAX(old todays date) <= (real todays date), do this:

put 2 copies of the Transactions table as fieldlists for the query. Give the first one an alias of TranCurrent and the second one an alias of TranLast.

Link on CustID


Then, on the grid

field --> ReceiptNo
table --> TranCurrent

field --> TranDate
table --> TranCurrent

field --> PayTo
table --> TranCurrent

field --> PayFrom
table --> TranCurrent


field --> PrevDate: TranDate
table --> TranLast
criteria --> = dMAX(
"TranDate",
"Transactions",
"CustID=" & [CustID]
& " AND TranDate <#"
& [TranDate] & "#")

field --> PrevPayTo: PayTo
table --> TranLast

field --> PrevPayFrom: PayFrom
table --> TranLast

If you want to join the Customers table, use TranCurrent for the link

Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day ;)

remote programming and training
strive4peace2006 at yahoo.com

*

Kelvin Leong wrote:
Hi Crystal,

The system is made in such a way that the 2 main storage tables are as mentioned in my earlier replies. And the transaction table is structured in a way to include PayFrom and PayTo dates is to allow users to compare back to the Agreement Date.

The reason for such calculations is not only to check whether the records are keyed in correctly, it allows us to monitor his payment history or pattern. For example, how frequent he comes to pay and how much he is paying each time.

We have reports that monitors his overdues. We would also like to further automate the reports generation to include his payment patterns via calculation between his payment records.

I am afraid changing the structure is quite tedious and this system is being used on a daily basis.

I'm actually trying the following code (in a module) to help me with my solution (Reference from Microsoft):

Option Compare Database
Option Explicit

Function PrevRecVal(KeyName As String, KeyValue, FieldNameToGet As String)
'*************************************************************
' FUNCTION: PrevRecVal()
' PURPOSE: Retrieve a value from a field in the previous form
' record.
' PARAMETERS:
' F - The form from which to get the previous value.
' KeyName - The name of the form's unique key field.
' KeyValue - The current record's key value.
' FieldNameToGet - The name of the field in the previous
' record from which to retrieve the value.
' RETURNS: The value in the field FieldNameToGet from the
' previous form record.
' EXAMPLE:
' =PrevRecVal(Form,"ID",[ID],"OdometerReading")
'**************************************************************
Dim RS As DAO.Recordset
Dim db As DAO.Database
On Error GoTo Err_PrevRecVal

' The default value is zero.
PrevRecVal = 0
Set db = CurrentDb()
' Get the form recordset.
Set RS = db.OpenRecordset("Transaction", dbOpenDynaset)
' Find the current record.
Select Case RS.Fields(KeyName).Type
' Find using numeric data type key value?
Case DB_INTEGER, DB_LONG, DB_CURRENCY, DB_SINGLE, _
DB_DOUBLE, DB_BYTE
RS.FindFirst "[" & KeyName & "] = " & KeyValue
' Find using date data type key value?
Case DB_DATE
RS.FindFirst "[" & KeyName & "] = #" & KeyValue & "#"
' Find using text data type key value?
Case DB_TEXT
RS.FindFirst "[" & KeyName & "] = '" & KeyValue & "'"
Case Else
MsgBox "ERROR: Invalid key field data type!"
Exit Function
End Select

' Move to the previous record.
RS.MovePrevious

' Return the result.
PrevRecVal = RS(FieldNameToGet)
RS.Close
Bye_PrevRecVal:
Exit Function
Err_PrevRecVal:
Resume Bye_PrevRecVal
End Function

Function NextRecVal(KeyName As String, KeyValue, FieldNameToGet As String)
'*************************************************************
' FUNCTION: NextRecVal()
' PURPOSE: Retrieve a value from a field in the next form
' record.
'**************************************************************
Dim db As DAO.Database
Dim RS As DAO.Recordset
Set db = CurrentDb()
On Error GoTo Err_NextRecVal
' The default value is zero.
NextRecVal = 0

' Get the form recordset.
Set RS = db.OpenRecordset("Transaction", dbOpenDynaset)

' Find the current record.
Select Case RS.Fields(KeyName).Type
' Find using numeric data type key value?
Case DB_INTEGER, DB_LONG, DB_CURRENCY, DB_SINGLE, _
DB_DOUBLE, DB_BYTE
RS.FindFirst "[" & KeyName & "] = " & KeyValue
' Find using date data type key value?
Case DB_DATE
RS.FindFirst "[" & KeyName & "] = #" & KeyValue & "#"
' Find using text data type key value?
Case DB_TEXT
RS.FindFirst "[" & KeyName & "] = '" & KeyValue & "'"
Case Else
MsgBox "ERROR: Invalid key field data type!"
Exit Function
End Select

' Move to the next record.
RS.MoveNext

' Return the result.
NextRecVal = RS(FieldNameToGet)
RS.Close
Bye_NextRecVal:
Exit Function
Err_NextRecVal:
Resume Bye_NextRecVal
End Function

I am able to create a report but for the first record for each customer, points to a different record as illustrated below:

Name: Customer A
CustID: MEM123456
-------------------------------------------------------------------------------------
Receipt No TodaysDate PayFrom PayTo Previous TodaysDate
-------------------------------------------------------------------------------------
0010001 01/01/2006 01/01/2006 10/01/2006 26/12/2005
0010203 15/01/2006 11/01/2006 14/01/2006 01/01/2006
0010621 20/01/2006 15/01/2006 26/01/2006 15/01/2006
===================================================

Name: Customer B
CustID: MEM123532
-------------------------------------------------------------------------------------
Receipt No TodaysDate PayFrom PayTo Previous TodaysDate
-------------------------------------------------------------------------------------
0010101 03/01/2006 01/01/2006 09/01/2006 09/11/2005
0010233 16/01/2006 10/01/2006 12/01/2006 03/01/2006
0010521 18/01/2006 13/01/2006 15/01/2006 16/01/2006
==================================================

Is there a way to make "Previous TodaysDate" = "TodaysDate" for first record for each segment of the customer's statement as the above report lists all of the customers?

Thanks.

"strive4peace" <"strive4peace2006 at yaho" wrote:


Hi Kelvin,

you said, "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."

When you issue a statement to a customer, that record needs to be recorded so the customer can pay it. I realize you are not wanting to store calculated fields, but once a statement is issued, that field becomes a piece of data.

you should already have the payment due records in your system. when the customer pays, it should go against one (or more) of those records.

the point I am trying to get to is that you should not be keying in the dates when a payment is received 00 they should be already known.


Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day ;)

remote programming and training
strive4peace2006 at yahoo.com

*

Kelvin Leong wrote:

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.

.