Calculating Finances

[claddagh]

Hi Guys,

New to the forum, and its nice to see how active this place is, and upto date.

My question is, and probably simple to correct. ( Please be patient with me, i`m new to this, and my terminology might be incorrect, also please simple answers )
I`ve searched the forums, but as my teminology is wrong I cant find the results im after


Anyway, I have a table with many fields in it, but the parts i`m having problems with are as follows.
I have fields with
Price Inc Vat, Deposit Payment, Stage Payment, Balance Payment & Balance Due.,

The part im trying to get to work is the balance due which I want to automatically update, based in the data inputed into price inc vat deposit payment etc.

The calculation i`m after is as followes
Balance Due = ( Total Ammount Inc Vat - Depost Payment - Stage Payment - Balance Payment )
eg, Total Ammount Inc VAt = £2000
Depost paid is £1000
so far balance due would be £1000
Then Final Payment is made 2 weeks later £1000
So balance due would then show £0.00

Is this possible

Thanks in advance

If I have not made myself too clear, please excuse my crapness, and anyhelp Is much appreciated.
If you need any more info, please do not hesitate to contact me on
max.vernon@claddagh-group.com

Thanks in Advance

Max Vernon

[edit]

Just to let you know, I also need these details kept in the table for future reference so I dont think I can use a query to do it.
I`ve attached a copy of the database with some example data in it if this is needed for any assistance.

 

[oldaf294]

It is best not to save calculated data in the database. If one or more of the parameters changes the results will not be computed. Besides the fact that it just inflates the table size. create a query and choose the relevent fields. in a blank
"Field" place;
= ( [Total Ammount Inc Vat] - [Depost Payment]- [Stage Payment] - [Balance Payment] )
Do not forget to put the brackets around the field names if there are spaces in the field name...
jim
You can do the same thing on Forms or Reports. Just create an unbound textbox and use the above formula for the "Control Source"
jim

 

[claddagh]

Thanks

oldaf294,

Thanks for the prompt responce.

This seems to have worked in the query, so when I open the query I get the info i`m after.

How can I display this data in a form, when the rest of the data is on a table.
Do I need to set up some sort of relationshop with a 1-1 type ?
Or does it not matter as long as I have this field in the form ?

I hope I have made myself clear, if not and you need more info, you can contact me on
max.vernon@claddagh-group.com

Thanks in advance for the help.

p.s. sorry to sound dumb.
I have Alison Balter's Mastering Access 2000 Development on order, so when this comes, I hope to get upto speed very quickly.

Max

 

[Rich]

Payments belong in a separate table.
You have a one to many relationship between invoices and payments

 

[neileg]

How can I display this data in a form, when the rest of the data is on a table.
Do I need to set up some sort of relationshop with a 1-1 type ?
Or does it not matter as long as I have this field in the form ?

Include all the data you need on the form in the query. It is always preferable to base your form on a query, anyway.

 

[oldaf294]

claddagh,
Create a query selecting all relevent fields. Create a form, based on the query. On your form, from the tools menu, create an "unbound" textbox and enter your formula.
jim

 

[claddagh]

Advice Taken

Thanks for the responce.

I have taken the Finance Part of my main table, and entered this into its own table now.

Also I have created a "Master" Query with all the info That I need to use in my forms / reports etc.
This is all working a treat now.
Thanks.

 

[The_Doc_Man]

regarding what to use for a form, a valuable lesson is this:

If you can compute it in a query, you can show it on a form.

Why?

'cause you can base a form off a query. Heck, you can do ALMOST ANTHING off a query.

What limits does this use of a query place on forms?

Text-boxes derived from computed values usually cannot be updated.

Therefore, if it is a report or a display-only form, base it on a query and compute everything you can in the query.

If it is to be an updatable form, put only non-computed fields in the query and put formulas in the controlsource for things you want to compute. Then lock the computed textboxes so you cannot accidentally muck them. Also, perhaps you would want to set TabStop property to NO for any computed controls. That way you cannot tab to the control and accidentally update it.

 

[claddagh]

Soted

Thanks for all the suggestions guys.

I have now got this sorted.

Once again, this forum has saveed my arse !!

Many Thanks