Need Query Help ~ Please

[cvansickle]

I am a true beginner in the Access world. I enjoy teaching myself how to do different thing.. However, I have learned to lean on you Access Gurus when I get stuck.. So bare with me if this is an easy solution or even an impossible one..

What I have, is a sales floor that takes a large payment for service and breaks it into 4 or 5 monthly smaller payments.

My task is to create a form. In the form the sales manager can enter a date in a field and then I need the query to match that date with any of the 5 "payment date" fields. Then return a multiple list of all customers that are scheduled to pay that day so that he can mark if the payment was made or not for commison.

Thanks in advance. (Let me know if I need to explain better)



Chad

[jzwp11]

If you have 5 date fields in your table that represent when a payment is to be made, then your table is not structured properly. You have what are called repeating groups which violates normalization rules. For more on normalization, please check out this site.

You have another issue as well. I assume that the scheduled payment date is based on an initial date of some sort and a term say 5 months. If that is the case, the scheduled payment dates can actually be calculated, so you only need to store the initial date and the term in the database since in general, calculated values are not stored. You will, however, have to store the actual payment dates. Since a customer can have many payments, that describes a one-to-many relationship which is handled with 2 tables similar to the following:

tblCustomers
-pkCustomerID primary key, autonumber
-txtFName
-txtLName

tblCustomerPayments
-pkCustPayID primary key, autonumber
-fkCustomerID foreign key to tblCustomers
-dtePaid (date paid)
-CurrAmtPaid (amount paid)

I would guess that your application is a little more complex than that given that a customer would return on multiple occassions and buy things (we hope). So really, a customer can have many sales orders (or whatever you want to call them) and you would break that sales order into multiple payments. The structure for that would look more like this:

tblCustomers
-pkCustomerID primary key, autonumber
-txtFName
-txtLName

tblCustomerSales
-pkCustSaleID primary key, autonumber
-fkCustomerID foreign key relating to tblCustomers
-dteSale (date of the sale--the initial date I referred to earlier)
-term (number of months in which the sales amount must be paid in full)
-currSale the amount of the sale

tblCustomerSalePayments
-pkCustSalePayID primary key, autonumber
-fkCustSaleID foreign key to tblCustomerSales
-dtePaid
-AmtPaid

Since it looks like you are tracking commissions, are you also tracking who made the sale? If so, I would suggest having 1 table to hold all people whether they are customers or sales people

[cvansickle]

So let me explain a little better.. I have a product that sales for $4000 dollars.. But it is broken up into 4 monthly payments of $1000. So the sales person will set up the payment dates for each 4 payments (payment may be on different days based on weekends and such) then we have a person that runs the charges on set day.. Sales Manger want to go into a form and enter a date and see everyone that is scheduled to pay that day and "check off" those people that had a payment go through. But it needs to look at all 4 dates to make sure we capture all payments.

Example

Customer 1 - May 1st; June 1st; July 1st; August 1st
Customer 2 - June 1st; July 1st; August 1st, Sept 1st

So when Sales Managger enter July 1st, he will see customer 1 owes 3rd payment and Customer 2 owes 2nd payment.

Hope that makes sense...

[jzwp11]

What do you do if someone only pays $750 of the $1000 monthly payment?

[jzwp11]

If you want to keep it as simple as you describe by recording the actual scheduled pay dates that you determine rather than calculate those dates (BTW if set up correctly, Access can take into account holidays and weekends), then your structure would look like this:

tblCustomers
-pkCustomerID primary key, autonumber
-txtFName
-txtLName

tblCustomerScheduledPayments
-pkCustPayID primary key, autonumber
-fkCustomerID foreign key to tblCustomers
-dteScheduled
-longSequenceNumber (to hold 1, 2, 3 etc for payment so that you can say that it is the 2nd or 3rd payment as you describe)
-logPaid (a yes/no field that your supervisor will check when the payment is made)

In terms of having your supervisor enter a particular date, I would recommend using an unbound form with a textbox (for the date) and a command button that executes a query that returns all payments with the date entered. The query would join the two tables above and have its criteria as dteScheduled=forms!formname!textboxname