Data Entry Design Problem (1 Viewer)

[eildon]

I am not sure how to do this?

I have a table>Form that I input a payment. That one payment is on one bill but it represents many various properties. I.E Gas payment=1 bill but applies to 13 different properties (Group account that groups properties)
I want to be able to enter the payment on a payment form but then apply it to thirteen different properties assigning a propertyID and a PaymentID so I can bring it all together in a query but assigning a unique record for the individual payment so I can work out the exact cost that apartment expends on Gas,Electricity etc for the year or day.

For the entry of these payment is developing a subform on the main form the way to go? Has anyone done this type of functionality before.

Thanks

 

[boblarson]

Yep, a junction table with propertyID and PaymentID would be in order and using a subform to enter it.

 

[CEH]

For the entry of these payment is developing a subform on the main form the way to go? Has anyone done this type of functionality before.

Thanks

I think a lot of people have.... It's called "Cost Accounting"
I've done one similar to what your wanting.... So I'll expand on Bobs post....
I would have 3 tables, of course you might need more... First your Payment table with "PaymentID, date, amount.... so on.. Your second table.. Your "Property table" containing PropertyID, address...other info.... Then the table where you will join these two... PaymentDetail table. The payment detail table would contain PaymentID, PropertyID, Amount, Expense type (which really should be tied to yet another table) The PaymentDetail table would become your subform. Tied to the main form on "PaymentID"
Now after you enter the payment on the main form you can move to the subform and create as many allocations you need.
I would also add a "Amount Sum" field on the subform (on mine I hide it) and bring the figure onto the main form. This would give you a running total of the allocation against the payment. You'll have to add the code to cause an error message if the "Amount Sum" does not equal the "Payment Amount"

 

[eildon]

Cost accounting reply

Thanks Curtis. Thats exactly what I am doing. I am fairly new at the VB (yes another one) but I am getting my head into slowly. I tried to igure the running balance scenario but any code you may have would be helpful. I work out days between dates by linking the text box to a query with a variable named days in the field column like this - Days: DateDiff("d",[PayDetails]![PeriodStartDate],[PayDetails]![PeriodEndDate])

this seems to work but if there is a better way let me know.

Thanks for your reply and picking up on what I am doing.

Paul

 

[CEH]

As I said I use a hidden textbox in the subform. For example 'txtTotalDistr" control source =Sum([DistributionAmount]) The "DistributionAmount" would be your allocated amount textbox. Then on my mainform an unbound called "txtDistrBalance" with control source being =[frmDistributionsub].[Form]![txtTotalDistr] frmDistributionsub... being the subform. Then on the main form another unbound to show the balance.. control source "=([TransactionAmount]-[txtDistrBalance])" This MUST equal zero. Your "DateDiff" looks fine....but I don't follow the logic...Your transactions will have dates? Using a simple "Between [FirstDate] and [SecondDate] in the date field would filter your transactions.

 

[eildon]

Oh yeh. I didn't explain dates. All invoice payments are for periods i.e.from date to date. From there I work out daily costs. I then have a daily cost per unit and if we can indicate costs that are higher than average then we can check the apartments for water leakages, gas leakage or excessive use of utility. I intend to have a system that flags for costs that are increasing or spiking.

Anyway that is the idea. There is very little off the shelf software for this so hence me getting my head around customising it. Didn't realise I had to go back to school.

Thanks for help. I am sure I will be back on forum. PS At what level of help is the forum aimed at. Are you guys happy to help get us on track if we are not sure how to go about getting started on code?

 

[CEH]

I don't think this forum is aimed at any one level of help... MANY people here who are far above me in coding that you'll find will be glad to help. Just searching this forum you can find many answers. If you don't find it... many folks willing to help.

 

[boblarson]

eildon:

There are several different levels of knowledge here at the forums, from beginner to downright scary awesome. So, don't worry about it. I've seen several people go from the beginner stage to fairly advanced here. In fact I have done that as well. When I started here I was just starting out and over time I have gathered more knowledge so I feel more comfortable with things can answer at a higher level.

I don't consider myself in the upper range yet and have a lot to still learn. But, we just keep plugging away and getting further and further down the "experience" road.