Filter By Date In Different Fields

[Douglasrac]

Hi,

I have the following fields:



Name (text)

Date of Payment 1 (date)
Paid 1? (yes/no)

Date of Payment 2 (date)
Paid 2? (yes/no)

Date of Payment 3 (date)
Paid 3? (yes/no)

Date of Payment 4 (date)
Paid 4? (yes/no)

I want to create a query that will show me the names with "Date of Payment" of a week ago and that is not paid yet (so, not marked on the yes/no field).
So I open the query and I will see where date of payment is today until 7 days ago.
If the field Paid is marked, do not show.

The problem is that when I create this criteria for Date of Payment 1 and Paid 1, the program ignores Date of Payment 2. If Paid 1 is marked it doesn't show anything. But if Paid 1 is marked, the program should use the criteria for Date of Payment 2 and Paid 2, until everything is paid. So it's an OR criteria not AND.

How do I do this?

[jzwp11]

Having separate payment date fields (date of payment1, date of payment2 etc.) sequentially numbers is an example of repeating groups which indicates that your database is not normalized. For more on normalization, please check out this site.

Each payment should be a record in a table not a field. The table should look like this:

tblPayments
-pkPaymentID primary key, autonumber
-fkLoanID (foreign key to the instrument to which the payments are being applied like a loan or whatever)
-dtePayment (Payment date)
-PaymentNumber
-AmtPaid

The data in the table would look like this

pkPaymentID|fkLoanID|dtePayment|PaymentNumber|AmtP aid
1|9|1/1/11|1|$500
2|10|1/1/11|1|$200
3|9|2/1/11|2|$500
4|10|2/1/11|2|$200

The 9 and 10 in fkLoanID just refer to 2 different loans. Both loans have had 2 payments.

[Douglasrac]

That's true. I will normalize the data. Thank you!

[jzwp11]

You're welcome. Glad to help.

[Douglasrac]

Is it possible to create several records in one form?

Because if I normalize the data will be harder to insert payments.

Right now I put the first date and as I press tab the other fields are automatically filled with Date of Payment 1 + 1 Month. If if have to create on record for each payment, I will need to type each date, one by one, plus the PaymentNumber, plus the amount. Right now also the amount is automatically calculated. I put the number of payment and it divides by the total amount, and thats it.

[jzwp11]

When you create the main record (a loan or whatever you are dealing with), you can have Access create the appropriate number of related payment records automatically. Then when you receive a payment, all you need to do is enter the date it was received and the amount received. Technically you could pre-populate the amount also, but I don't know if that would be a good business practice since some people may not pay the specific amount.

In terms of forms, you would base a main form on the mail loan record and within that form you would have a subform that displays the payment records. This allows you to view everything in one place.

The table structure is the most important part of a successful database application, so I would worry most about that and leave the forms until later. If you are just starting out, I would recommend this site for some tutorials on building a database application.

[Douglasrac]

When you create the main record (a loan or whatever you are dealing with), you can have Access create the appropriate number of related payment records automatically. Then when you receive a payment, all you need to do is enter the date it was received and the amount received. Technically you could pre-populate the amount also, but I don't know if that would be a good business practice since some people may not pay the specific amount.

I would like to populate everything at the time I'm inserting the clients name, etc.
For me doesn't matter when the client pays. It's not a loan, and I'm not the one that will receive the money. I just need to know when he needs to pay, so I can go to a website and check if he really paid. If not I just have to tell the client.
So, I will insert total amount, number of parcels and first payment date, and access would calculate parcel amount and the others payment dates.

If each date have his own record, I don't know how to make a automatic calculation, because each one will have a form.

In terms of forms, you would base a main form on the mail loan record and within that form you would have a subform that displays the payment records. This allows you to view everything in one place.

To have everything in one place I plan to use a query. It's much simpler. The query will show me only the clients that have payment date from yesterday and 1 week ago. So I check once a week and just check a box (yes/no). Better than navigate in forms.

The table structure is the most important part of a successful database application, so I would worry most about that and leave the forms until later.

The table structure is the one you told me:

PaymentID: primary key
LoanID (foreign key from the previous form)
Date of Payment
Total Amount
Number of Parcels
Parcel Amount

What I need is a direction. Should I do it as subforms? (doesn't seem the right path). Or maybe I will need to add a code into a button, that when pressed will calculate and populate the fields of the new form.

That's why seemed much simpler the other way. I need a simple task, and with normalized data, seems harder.

[jzwp11]

If you are not going to indicate anything about each individual payment in your database, then that is different, but if you intend to mark an individual payment as "paid" after you check the website you were referring to, then you will need to break out each payment as a record in a related table.

If a client can have many of these payment scenarios, then you need a table to hold the client info and then relate your payment table to the client


tblPayment
PaymentID: primary key
ClientID (foreign key to tblClient)
DateofFirstPayment
TotalAmount
NumberofParcels
ParcelAmount

(Note: it is best not to have spaces or special characters in your table or field names)

I'm not sure what your fields mean, but is the TotalAmount= NumberofParcels * ParcelAmount ?
If my guess is correct, Access can calculate the ParcelAmount and you would not need the field in the table.

Now if you intend not to record anything about an individual payment, then Access can calculate the due dates of subsequent payments given the information in the table above. Are all payments on a monthly basis? If so, we would not need a frequency field; if the frequency of the payments can vary, then we would need a field to hold the payment frequency (weekly, bimonthly, monthly, quarterly etc.)

[Douglasrac]

I'm not sure what your fields mean, but is the TotalAmount= NumberofParcels * ParcelAmount ?
If my guess is correct, Access can calculate the ParcelAmount and you would not need the field in the table.

Your guess is correct.

Now if you intend not to record anything about an individual payment, then Access can calculate the due dates of subsequent payments given the information in the table above. Are all payments on a monthly basis? If so, we would not need a frequency field; if the frequency of the payments can vary, then we would need a field to hold the payment frequency (weekly, bimonthly, monthly, quarterly etc.)

All payments are montly.

The only thing I will indicate in the database is if it's paid or not.

[jzwp11]

The only thing I will indicate in the database is if it's paid or not.

Based on this statement, then you will need the related table.

Assuming that you have a table to hold the client info, your table structure will look something like this:

tblClients
-pkClientID primary key, autonumber
-txtFName
-txtLName
-txtAddress

tblPayment
-PaymentID: primary key
-ClientID (foreign key to tblClient)
-DateofFirstPayment
-TotalAmount
-NumberofParcels

tblPaymentDetails
-pkPayDetailID primary key, autonumber
-fkPaymentID foreign key to tblPayment
-dtePayment
-logPaid (yes/no field; yes if paid, no if not paid)


I've attached a sample database with a few forms. Behind the button on frmPayment is code that creates the corresponding number of payment detail records based on the total amount and the number of parcels starting with the date of first payment that is entered.

[Douglasrac]

Based on this statement, then you will need the related table.

Assuming that you have a table to hold the client info, your table structure will look something like this:

tblClients
-pkClientID primary key, autonumber
-txtFName
-txtLName
-txtAddress

tblPayment
-PaymentID: primary key
-ClientID (foreign key to tblClient)
-DateofFirstPayment
-TotalAmount
-NumberofParcels

tblPaymentDetails
-pkPayDetailID primary key, autonumber
-fkPaymentID foreign key to tblPayment
-dtePayment
-logPaid (yes/no field; yes if paid, no if not paid)


I've attached a sample database with a few forms. Behind the button on frmPayment is code that creates the corresponding number of payment detail records based on the total amount and the number of parcels starting with the date of first payment that is entered.

Thank you so much for your help!

Now I have an idea where to go. But I want you to see the situation I have now, so you can see if this is really the best option.
I will attach my database so you can see.

It's in another language. Would be a lot of effort to translate, but will be easy for you too see.

On Forms go to Clientes (clients)
You see a record.
Click on the Umbrella icon.
Click on the Coins icon.
Add a new record.
Search for "Registro de pagamentos" (payment records)
You see a field to insert date.
Insert a new date.
Press TAB.

As you can see the field is automatically filled. And at the same time you can edit the field. This is important, because usually the first payment date is different than the others. And the others are always plus one month. Then I just press TAB until it reaches the number of parcels I want.

In your database, even that I see it's easy to modify the dates, still not as easy as this way.
Any possibility to adapt my way with the normalized data?

[jzwp11]

I don't think that there is a way to do it exactly like you have it since you are going from control to control in the form rather than record to record as in the normalized structure. However, there probably is a way around it. You can specify the first payment date and then you can have a control that would identify the date any subsequent payments would be made. For example let's say that the first payment is due on 1/1/2011, but all subsequent payments are due on the 15th of the month. The code can be easily modified to show payment dates of 1/1/2011, 2/15/2011, 3/15/2011 etc. Another alternative is to run an update query to amend all of the dates for a particular client.

[Douglasrac]

Thank you once again.

I will see what I can do.

[jzwp11]

You're welcome. Just let us know if you need additional assistance.