Help with a "Totals" Formula in a Database

[dgaletar]

Hello all, I have recently downloaded a database from a website, and it is working out VERY WELL!



Here is what I have:

In the database there is a Table named tblJob. The purpose of this Table is to be a base for work that is done.
- There are four (4) fields in this table:
(1) JobID (which is an autonumber)
(2) VehicleID (which is a number)
(3) JobDate (which is a Date/Time field)
(4) Total (which is set to currency)

My issue is this:
- In the "Format" field of the "Total" row, it has "Rp"#,##0;"(Rp"#,##0)". Can anyone tell me what that means? Because the calculations do not seem to be functioning properly. When I look at the "Table" view, the "Total" field displays "Rp154" on each row.

Meanwhile, there is another table which is named "tblJobItems". The purpose of this table is to track the items/parts used for the job.
- There are five (5) fields in this table:
(1) ItemID (which is an autonumber)
(1) JobID (which is a number)
(2) ItemName (which is Short Text)
(3) ItemCost (which is a Currency field)
(4) Quantity (which is set to Short Text)

What I would like to see is this, the field in Row #4 (Total) of the Table named tblJob should be the "Extended Total" of the "tblJobItems". Now, I was able to do this with a Query. I used this: Extended Cost: [ItemCost]*[Quantity] in the Query, but I don't know how to put the result into Row #4 (Total) of the Table named tblJob.

Can anybody point me in the right direction here???

Oh, and I should mention that before I started screwing around with this whole thing, I added the field (3) ItemCost (which is a Currency field). Before that the "Total" field only seemed to hold a value that you would type in, and not perform a calculation.

Thanks!!!

[John_G]

...how to put the result into Row #4 (Total) of the Table named tblJob.

My suggestion is "Don't".

Instead, create a query linking the two tables (it will be a summation ("totals") query), where one of the fields in the query is the sum of ([ItemCost]*[Quantity]). You would "group by" on the fields you use from tblJob.

Using calculated data in a table field is almost never a good idea, especially if the data is derived from another table (as it is here), because if the source data is changed, the calculated field usually isn't automatically updated.

The "Format" setting for the field affects only how the data is displayed, not the value in the table. If you want to see what the table values actually are, you can just delete the Format setting. If each row shows the same value for Total, it might be right, but I doubt it. Do you have the documentation as to how the "Total" field was populated?

[dgaletar]

Thanks John_G. First off, I have no documentation on this database. And, while I understand what you are saying, I'm having a little trouble with the execution.

So, I went to the "Create" tab and selected "Query Design". I named it "JobTotalsQry". I then added the two tables, tblJob & tblJobItems to the Query. I then copy and pasted what you wrote into the first column, [ItemCost]*[Quantity]. I also clicked on the "Totals" option from the top menu.

The problem is, I don't know what to do next. Do I use the results on my form? ... or report?

[dgaletar]

Also, I just figured out that there is one Form (titled "JobDetails") that has a SubForm (titled "tblJobItems"). The main Form ("JobDetails") is where the "Totals" field resides. Next to that text box is a button that reads "Get Total". When you click that I guess it populates the "Totals" box from the info on the SubForm, ("TblJobItems"). When I looked into that button, I found the folowing:

Option Compare Database

Private Sub Command131_Click()
On Error GoTo Err_Command131_Click
Dim JobID As Long
Dim record As Variant
Dim rs As Recordset
Dim db As Database
    
    Set db = CurrentDb
    Set rs = Me.RecordsetClone
    Set rs = db.OpenRecordset("InvoiceQry", dbOpenDynaset)
    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70


    If IsNull(Me.JobID) Then
    MsgBox "Please select job number"
Else
    DoCmd.OpenReport "Invoice", acViewPreview, , "[JobID] = " & Me.JobID & ""
End If


Exit_Command131_Click:
    Exit Sub


Err_Command131_Click:
    MsgBox Err.Description
    Resume Exit_Command131_Click
End Sub


Private Sub Command302_Click()
Me.JobDetailHead.Requery
Me.Total = [JobDetailHead].Form![txtTotal]
End Sub

I'm just trying to keep a running total in that field as the Job Items are added.

Does that help at all???

[dgaletar]

Here is a snap-shot of the report where the total is not working properly...

Totals Sample.doc

[John_G]

Ah, this might be an easy fix (for the report). Do you see what $153.99 actually is? It is the total of the unit prices, not the total of the extended prices. Open the report in design view, and see what the source of the "invoice total" control is. It's going to be the sum of some value, and that value is probably the unit price instead of the extended price.

Let us know what you find out.

[dgaletar]

Cool... I feel like we are making some progress!

The "Control Source" for the invoice total on the report is from the "InvoiceQry". And the invoice total in that query is from the "tblJob" table. And the invoice total in the "tblJob" table displays "Rp154" on each row, which is were I started this whole STUPID thing!

(I guess that I spoke too soon on the progress! )

[John_G]

One of the features of MS Access reports is the ability to show sub-totals and totals with very little effort. As a rule, totals should always be calculated in the report, and not be a part of the report source query. Yours is a simple report, with only one group and one total. Open the report in design view; the invoice total will be in the report footer. Change the control source of the control to
=Sum([quantity] * [unit price]) (replacing [quantity] and [unit price] with the names of your controls) and see how that works.

You don't need the total field in the tblJob or the InvoiceQry.

[dgaletar]

OUTSTANDING! Mr. John_G, you have done GREAT work here my friend!

So, if we were to next tackle the "Totals" field on the Form... would that be OK???

[John_G]

I managed to find and download that template (nice to have the link on the form).

Open the form JobDetailHead in design view. In the form footer, change the control source of txtTotal to =Sum([ItemCost]*[quantity]).

You will find that clicking the "Get Total" button on the Job Details form now gives you the right total for the invoice.

As an added enhancement, open the JobDetails form in design view. Change the control source of Total to : =JobDetailHead.Form.txttotal

Save the form, then try using it in form view. Watch what happens to the "Total" on the main form as you add or edit items in the subform. does it automatically update? It should. If that works, you don't need the "Get total" button anymore.


Now, this change means that the invoice total is no longer stored to the table tblJob, so you might find some other ripple effects (errors) that will have to be fixed. But that's OK - the invoice total shouldn't be in tblJob anyway.

[dgaletar]

All was good until I did the second change... the "added enhancement" if you would. The "Total" on the main form does NOT update automatically. When I do click on the "Get Total" button though, I get a Run Time Error that highlights the following line of code:

Private Sub Command302_Click()Me.JobDetailHead.Requery
Me.Total = [JobDetailHead].Form![txtTotal]
End Sub

[John_G]

Does the Total field on the main form show any value at all (other than 0)? On my version it works, but I made very few changes, other than the ones I mentioned.

That run-time error is not a problem - you can't update a control where the control source is an expression (starts with =).

Are you able to post a copy of your database - the fix should be something simple.

[dgaletar]

OK, here it is. BTW, are for hire??? We don't need many changes, but a few are a little above my pay grade!!!

[John_G]

Here is the patched up database.

I fixed the form/subform JobDetails/JobDetailHead so that the total automatically updates and the extended price on the subform works too.

I also put a behind-the-scenes patch (uses a hidden field on the JobDetails form) to update the Total field in tblJob, because so many other parts of the database use that field.

Let us know how it works.

[dgaletar]

BEAUTIFUL WORK! And, as I'm sure that you already know, it works GREAT! Thank you!!!