Form to Output Table

[jzwp11]

Is there a monetary amount associated with each budget line item or budget line item description? I did not see that in your example DB; all I saw was a receipt total.



If multiple budget lines are associated with a receipt, that describes a one-to-many relationship. Furthermore, if a receipt's budget line can have multiple payments applied to it, that describes another one-to-many relationship. Now I assume that with all these payments, the same person may not take the payment so it does not make sense that only one employee would be associate with the receipt, but rather the employee should be associate with the receiving the payment. I would agree that the receipt could apply to only 1 payor. Have you had any instances where a receipt applied to many payors?

Can one payment be comprised of multiple payment types or only 1?

[dwright]

Is there a monetary amount associated with each budget line item or budget line item description? I did not see that in your example DB; all I saw was a receipt total.

The money will be tied back to each budget line. The original had a receipt total because I was basing it on one budget line. It would be nice to have a receipt total - on the receipt - but the money goes to the budget line.

Now I assume that with all these payments, the same person may not take the payment so it does not make sense that only one employee would be associate with the receipt, but rather the employee should be associate with the receiving the payment. I would agree that the receipt could apply to only 1 payor. Have you had any instances where a receipt applied to many payors?

If I understand what you're asking - per receipt it'll be one payor & one employee. If they have multiple payors it would be a different receipt.

Can one payment be comprised of multiple payment types or only 1?

My original response was 1 but I thought better of it because they have a few reports where they sort by payment type so therefore if someone comes in & pays part by check & part by cash that will need to be recorded.

[jzwp11]

The money will be tied back to each budget line. The original had a receipt total because I was basing it on one budget line. It would be nice to have a receipt total - on the receipt - but the money goes to the budget line.

People are obviously making payments, but how do they know how much to pay? Is there a "price" associated with a budget line. For example, how is the amount of a fine determined and then charged to the payor so that he/she knows how much to pay? Is it a variable amount or a fixed amount?

If I understand what you're asking - per receipt it'll be one payor & one employee.

I understand the 1 payor. Is the employee so named because they are responsible for that receipt no matter whether or not they collect a payment?

[dwright]

People are obviously making payments, but how do they know how much to pay? Is there a "price" associated with a budget line. For example, how is the amount of a fine determined and then charged to the payor so that he/she knows how much to pay? Is it a variable amount or a fixed amount?

They have not fixed a price to each thing. I believe the left that open because of price changing. I am assuming the employees know the prices & they make people aware of the prices before services such as shots, or water checks etc. Unfortunately it is covering the entire health department services from public health to environmental. At the least they did not ask me to fix a price to each budget line description. They left that to be filled in by the employee.

I understand the 1 payor. Is the employee so named because they are responsible for that receipt no matter whether or not they collect a payment?

Yes. The employee is response for that receipt. That is why they are not allowed to delete them. When it all comes down to it that money is accountable back to that person.

Did that answer both questions?

[jzwp11]

They have not fixed a price to each thing. I believe the left that open because of price changing. I am assuming the employees know the prices & they make people aware of the prices before services such as shots, or water checks etc. Unfortunately it is covering the entire health department services from public health to environmental. At the least they did not ask me to fix a price to each budget line description. They left that to be filled in by the employee.

If you only record the receipt total and not the price of each budget line, then you can only apply payments to the receipt as a whole. As long as that is all you need, then you would not have to have a price tied to each budget line item.

[dwright]

I think I misunderstood the question. I thought you were saying for each fine it's $25 therefore you'd have to have a table with the pricing in it. They expect to be able to report back the cost per budget line. A lot of their reporting is based on budget line & type of payment.

[jzwp11]

Perhaps I misspoke. If you want your users to assign monetary values to budget lines at their discretion, you would store that value when the budget line item is assigned to the receipt. You would not have a monetary value in the budget lines or budget line description table directly.

Based on our discussions to date, I have modified the table structure of your previously posted database as I interpret your application. The structure is not yet complete as we still have to incorporate the payments.

[dwright]

I couldn't get to access yesterday. I am now looking over what you attached (thank you by the way).
Let me see if I can understand the set up.

So will tblPeople accumulate every new payor?

Is the tblBudgetLine & tbleBudgetLineDescription the main budgetline tables & tblRole & tblPeople the main people tables, the ReceiptOutput is for the Receipt to filter into & tblReceiptBudgetLines & tblReceiptPeople are for the multiple budget lines on the receipts?

How does payment types filter back in if I have to report on them?

Am I close with the connection & how they hook up?

[jzwp11]

So will tblPeople accumulate every new payor? YES, you will have to enter each new person (payor or employee or any other person relative to your application) to tblPeople.

Is the tblBudgetLine & tbleBudgetLineDescription the main budgetline tables & tblRole & tblPeople the main people tables, the ReceiptOutput is for the Receipt to filter into & tblReceiptBudgetLines & tblReceiptPeople are for the multiple budget lines on the receipts

I'm not sure I understand what you are saying above, so I'll try to explain it. The tblRoles holds all possible roles a person can play in your application. At present there are only 2 roles: employee and payor.

Any person associated with your application must be entered in tblPeople with a prescribed role. A person can only have 1 role. If a person can play more than 1 role then the current table structure will need to be revised. For example, if an employee can also be a payor, then we would have to revise the table structure. You'll have to make the call since you are more familiar with your application.


Each tblBudgetLineDescription is a subset of an item in tblBudgetLine. In other words, tblBudgetLineDescription provides the specific, detailed item.

A receipt can consiste of many charges/fees/fines. Each of those charges/fees/fines are attributable to a tblBudgetLineDescription item, so we have to associate all of the applicable budget line description items to the receipt with their associated monetary value.

tblReceiptPeople holds all of the people applicable to each receipt. At present only two people are associated: your employee and the payor.



Hopefully that explains the structure a little better.

As to payments, we have not gotten to that point yet.

[dwright]

I should probably start my reply with the payors/people. Anyone from the public can come in for something & need to be a payor. They will not be preset people. Secondly it is realistically possibly that an employee could have some thing done such as a water test & need to be the payor also. This person (payor) will not need to be sorted on, will need to be reported on & there should only be 1 payor per receipt & 1 employee per receipt.
The budget lines & descriptions will rarely change, the employees will rarely change but the payors they can be any one of the residents of our county.

As far as the budget lines I believe I understand the way they are set up so far. I should tell you that the first Budget Line & Budget Line Description can be combined if it makes it easier because as we have figured out they are one-one. When I originally set out to do this task I thought we had 3 separate fields because of the way they presented them. But I soon realized that the Budget Line & Budget Line Description go together. The extended Description can have the same Budget Line & Description. Does that make sense?

[jzwp11]

As far as the budget lines I believe I understand the way they are set up so far. I should tell you that the first Budget Line & Budget Line Description can be combined if it makes it easier because as we have figured out they are one-one. When I originally set out to do this task I thought we had 3 separate fields because of the way they presented them. But I soon realized that the Budget Line & Budget Line Description go together. The extended Description can have the same Budget Line & Description. Does that make sense?

I'm not sure I quite understand what you are saying, but from the data you showed, some budget lines (primary) had multiple related "subbudgetline" (secondary or detail) items. In thinking of this further, a better way of handling it would be to put all budget line items (whether they are the primary or secondary) into 1 table and then relate the primary to secondary (if it has one or not) in another table. If that makes more sense, then I can restructure the tables accordingly and move the records around. Please let me know.

Secondly it is realistically possibly that an employee could have some thing done such as a water test & need to be the payor also. This person (payor) will not need to be sorted on, will need to be reported on & there should only be 1 payor per receipt & 1 employee per receipt.

Based on the above, we need to restructure the tables. What this entails is to create a new table that associates people with each of the roles they may play (tblPeopleRoles). In the relationship window, we remove the join between tblPeople and tblRoles. Once we remove the relationship, we can delete the fkRoleID field from tblPeople. We can now establish new relationships between tblPeople to tblPeopleRoles and tblRoles to tblPeopleRoles. With respect to tblReceiptPeople, we can no longer associate directly to tblPeople since a person can have many roles and we need to know which role they are play with respect to the receipt. So we have to remove the join between tblReceiptPeople and tblPeople. Then in tblReceiptPeople we will delete the fkPeopleID field and add a new field fkPeopleRoleID. Then we can establish the join between tblReceiptPeople and tblPeopleRoles. I've done that in the attached DB.

[dwright]

I am looking at your current set up on people to see if I understand it. So if someone new comes in from the public & they need a receipt do they have to first enter them in as a new person, then enter the receipt or will the receipt fill that table?

When I put the bogus data into the tables perhaps I screwed it up. Let me see if I can somehow explain the budget lines.
Each budget line consists of something like this - A.1234 5678 0000 (some don't have that many numbers. Each one of those budget lines has only one description such as 3-5 Preschool, or Comm Sanitation & Food, etc. Each combination of budget line and description (A.1234 5678 0000 3-5 Preschool, MAY have multiple extended descriptions such as Preschool - Services, Misc Other, Refund.

[jzwp11]

I am looking at your current set up on people to see if I understand it. So if someone new comes in from the public & they need a receipt do they have to first enter them in as a new person, then enter the receipt or will the receipt fill that table?

The person would have to be entered first and then the receipt created and the person tied to it. There are ways to enter the new person at the time the receipt is created but that is a form related function. We are not at that point yet.

Each budget line consists of something like this - A.1234 5678 0000 (some don't have that many numbers. Each one of those budget lines has only one description such as 3-5 Preschool, or Comm Sanitation & Food, etc. Each combination of budget line and description (A.1234 5678 0000 3-5 Preschool, MAY have multiple extended descriptions such as Preschool - Services, Misc Other, Refund.

As long as a budget line has at least 1 extended description, then the current structure should work. I did not see any budget lines that did not have at least 1 extended description. Do you have any that were not in the previous DB you provided?

[dwright]

You know how when you are using a GPS & you go in the wrong direction & it says "recalculating route"? I always expect it to say - enough figure it out yourself. Every time we go through this I expect you to get tired of me.

Ok, so the people set up is ok & a form function for enter which we'll worry about later.

Budget Lines. I put in bogus information which probably screwed stuff up because I didn't want to publish the true budget information. Not sure if it matters. The actual one has 24 budget lines with descriptions & each budgetline/description has at least 1 & up to 12 extended descriptions.

[jzwp11]

So far so good then. Now payments....

Without reference to the database, can you describe in detail the payment process you currently use?