Full Version: Payment In Parcels
UtterAccess Forums > Microsoft® Access > Access Forms
Douglasrac
Hi,
I have a database where I control the commission to receive. I currently just have a check box that I mark if I received or not the commission. The problem is that now I start to receive commission in parcels. So I cannot check the checkbox because I only received partial commission. I also lose control on how much of each I already received.
So I want to create a system that allow me to deduct commission. So I thought about that:
Table
PaymentID: come from the previous table
CommissionID: just the ID for the new table
Value: the amount I receive related to that paymentID
Form
Commission: comes from payment table (calculated value)
Value: value I received
Total Amount Received: sum of all "Value" fields in the table that have same paymentID (how do I do that?) (calculated value)
Missing Value: Commission - Total Amount Received (calculated value)
Then this form will be like a sheet, where I can input as much parcels I need. Can be 1 or 2 or 3 until 7.
So, somebody have a better, easier, simpler solution?
And how can I make a sum of all values that have same CommissionID?
Thanks for the replies.
Jeff B.
It sounds like you are describing a situation in which there are Commissions-owed amounts, and Amount-paid-on-a-specific-commission.
That would seem to imply a one-to-many relationship, in which one Commission-owed could have multiple Amount-paid records.
One way to do this with forms is to use a main form/subform construction. The main form displays data about a specific Commission, while the subform displays data about any/all related Amount-paid records.
Is that what you're trying to do?
Douglasrac
Yes. That is exactly the case.
My problem now is actually another. I won't use a form to do this. I want to use a Query, because it would be much simpler.
Currently I use a query to check the checkboxes. Then I have a list of all clients and then just check the boxes of those I received. So now, I would just add a new field in the query that would be the amount received. When reaches zero (all paid) I check the checkbox.
The problem is that I don't know how the calculations will be done in a query. I put all my calculations formula in OnFocus of the properties of the field in the form. If there is no form, how can I make that field be a calculation?
Jeff B.
Again, I'm reading between the lines here, so this may not fit your situation ...
If you have one record with an amount for Commission, and, say, a dozen records with partial-amounts paid against that one Commission, one way to approach this would be to use a totals query to get the total amount paid (against that commission), then join the query that returns the (one) Commission & amount to the totals query that returns the total of amounts paid. Then you can calculate the difference between amount-owed and total-paid.
Is this still 'on-track'?
Douglasrac
Yes!
But I didn't know total queries would do that. I thought total queries would sum everything in one field, regardless what is related to.
Jeff B.
Take a look at Access HELP re: totals queries.
You can use selection criteria, and you can Group By... if you GroupBy the CommissionID, and Sum the amount, you'll have the total amount per CommissionID.
Douglasrac
And what about the calculations being done in the query?
Jeff B.
I can't tell if you've used Access HELP to check on queries, or total queries, or ...?
One way to do a calculation in a query is to create a new field, and do the calculation there -- something like:
NewField: Field1 - Field2
Douglasrac
Yes I checked the help for queries. Did not find anything that is Total Queries, only queries. Also did not find any new information but what I already know: queries are capable of making sums, max, min, etc.
build the basic structure of what I want, and now I don't know how can I input data in the query. If there are no records, the query just won't open.
Ocannot input data in forms, because will be a lot of work. I need a list full of records to input everything fast.
Also, as I said, the calculations are not done in the query. And if I want to make a field be a calculation I need a form. Even with a form, the calculation won't be updated if values are changed in the query. I need to open the form so the calculations can be done and updated.
So, if I cannot input new data (only edit data) in queries, my solution is to create a Form of various records.
The problem is the filter. I cannot filter a form as easily as I filter a query can I?
My query shows records where the checkbox is 0. If I mark it will be removed from the query. Is that possible in a Form?
Please tell me if I'm heading to the right direction. If I want to input data in several records I need a form or a query?
Jeff B.
I'm still not very clear on what you have, and what you're trying to do.
Have you been using Access long? Do you have experience developing applications? Which version of Access are you using?
More info, please...
Douglasrac
I have being using access for quite a long time now. I do not have experience on building applications and my programming knowledge (SQL) is almost none.
But I did build a large database on access with some features. I do have basic knowledge on how Access works.
thought I made myself clear. What I'm trying to do seems very simple. But as you probably notice, my mother language is not english, so maybe I'm not being clear enough.
If you want I can send you my database so you can take a look and better understand what I'm trying to do.
But basically is this:
Input data in several records at a time. So a form for each record is not recommendable. Would be better something like excel with a records list, and I just fill them all. Closer to that that I know is a query or a form of various records.
P.S.: My access is 2007.
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.