Adding columns in a table / fields in form

[Sklyn]

Hi and thanks for taking the time to help,

In my table of Financial Info relating to vehicles I am trying to calculate
the total monthly cost.
In my table I have columns "MonthlyCost" , "MonthlyGST", and
"TotalMonthlyCost"
I would like TotalMonthlyCost to be calculated from the other 2.
In my searching it seems I am better to not have this in the table at all,
but to calculate it in a query/form each time.
I do need it to show in a data entry form.
I will also need to be able to add all the TotalMonthlyCosts from many
vehicles for a GrandTotal Cost in a report perhaps.

What is the best way to go about this and how?
Please try keep it simple as i am fairly new to Access.

Thankyou muchly.

 

[KARL DEWEY]

Totals should not be stored in a table. Your table should collect cost,
type, and cost_date records and then use query, form, or report to calculated
totals.
The field used for date needs to be datatype DateTime.

 

[John W. Vinson]

Hi and thanks for taking the time to help,

In my table of Financial Info relating to vehicles I am trying to calculate
the total monthly cost.
In my table I have columns "MonthlyCost" , "MonthlyGST", and
"TotalMonthlyCost"
I would like TotalMonthlyCost to be calculated from the other 2.
In my searching it seems I am better to not have this in the table at all,
but to calculate it in a query/form each time.
Exactly.

I do need it to show in a data entry form.

Set the Control Source of the textbox for TotalMonthlyCost to

=[MonthlyCost] + [MonthlyGST]

or, if the GST might be NULL,

=[MontlyCost] + NZ([MonthlyGST])

The NZ() function will replace a NULL value with a zero.

I will also need to be able to add all the TotalMonthlyCosts from many
vehicles for a GrandTotal Cost in a report perhaps.

A Totals query or a call to DSum would probably be appropriate... for a more
detailed answer post a more detailed question (e.g. *which* of the many
vehicles? how do you choose them?)

 

[Sklyn]

Thanks heaps, so far so good

here's some more detail. I am keeping track of the vehicles not the payments
as such (for asset control not financial control, though finance will use
some info). Some vehicles are purchased (no monthly costs) some are leased
(has monthly cost) some are charged GST (pay tax on). There is not really a
payment date as such (as payment is always the same each month and is direct
debit), only Purchase date or lease commence date.
Vehicles belong to different Branches and Departments within the company so
I would like to be able to give a total monthly cost for all vehicles in the
branch "Remote Health", though there will still be data for vehicles which
have been disposed of and no longer cost but the dta will still be there. So
will need to filter for all "Current vehicles" in the CurrentStatus field,
then all "Remote Health" vehicles. and total the "Total monthly cost".
Also need to be able to Total for "All Other Branches" Current vehicles
I believe this should all be done on reports?
How much detail do you want of my Table structure? as the Branches, etc are
stored on a related table to the financial info. Should I combine this to one
table?
The main reason I made it a seperate table is to make it easier for creating
forms and to reduce table size, i "think" it would still be fairly normalised
if combined.

My table (_FinancialInfo) contains the following column Headings:

VehicleVIN# pk and 1-1 relationship with _VehicleDetails(VehicleVIN#) also pk
PurchaseOrLeaseType
Purchase/LeaseStartDate
MnthlyPaymentincStamp
MnthlyGSTAmount
TotalMnthlyPayment -- Which will be deleted
FundingBudget

along with a bunch of other details irrelevant to this.

My table (_VehicleDetails) contains the following column Headings:
VehicleVIN# pk 1-1
CurrentStatus
Branch
SubSectionDept
Program

and other details also.


Thanks again for you help.

Hi and thanks for taking the time to help,

In my table of Financial Info relating to vehicles I am trying to calculate
the total monthly cost.
In my table I have columns "MonthlyCost" , "MonthlyGST", and
"TotalMonthlyCost"
I would like TotalMonthlyCost to be calculated from the other 2.
In my searching it seems I am better to not have this in the table at all,
but to calculate it in a query/form each time.
Exactly.

I do need it to show in a data entry form.

Set the Control Source of the textbox for TotalMonthlyCost to

=[MonthlyCost] + [MonthlyGST]

or, if the GST might be NULL,

=[MontlyCost] + NZ([MonthlyGST])

The NZ() function will replace a NULL value with a zero.

I will also need to be able to add all the TotalMonthlyCosts from many
vehicles for a GrandTotal Cost in a report perhaps.

A Totals query or a call to DSum would probably be appropriate... for a more
detailed answer post a more detailed question (e.g. *which* of the many
vehicles? how do you choose them?)