Need to update fields in multiple records with each SAVE of new record in group

[Chris033115]

I have a data entry form similar to an invoice entry screen with header and line items however my header details identify a set of items being tested (such as a serial/lot number of goods and their manufacture date) and the line items refer to individual samples from this group. Each sample is measured for specific criteria and various calculations are made. The difference with this scenario (vs an invoice) is that the sample details (line items) are saved as each is processed. If there 20 samples in the group, there are 20 separate saves performed.

When each sample is saved, I (re)calculate summary information (number, average weight, total weight) for those that "Pass", "Fail", and the aggregate "Total". One of these calculations provides Total Average Weight for the group.

MY PROBLEM IS that with each new sample, I have to loop through, recalculate, and update a field in all of the previously saved line items. I've tried doing this with VBA code but it only updates the first record (rather than ALL records that share its ReportID). I also tried doing an update query but it skips the first record in the group and all the other updates for the field in question are wrong.

I want to update the "PercentDiff" field for all samples belonging to the current ReportID. The equation for this is:

((LineItems.SampleWeight / Form.TotalAverageWeight) -1)*100

Note: TotalAverageWeight is updated to both the report header AND the active form prior to looping through the details to calculate each sample's new "PercentDiff".

Below is the code I'm using:

[Public Sub RecalcPercentDiff()
Dim RS As Recordset
Set db = CurrentDb


Set RS = db.OpenRecordset("SELECT * FROM LineDetails WHERE Filename = '" & Me.txtReportID & "'")
RS.MoveLast
With RS
.Edit
!PercentDiff = ((!SampleWeight / Me.txtTotalAverageWeight) - 1) * 100
!TOL = IIf(Abs(!PercentDiff) > 3, "Fail", "")
RS.Update
End With
RS.Close
Set RS = Nothing


End Sub]


I attached an Excel file showing manual calculations of what's expected as well as screenshots of the line item datasheet with what the program is providing, as well as the results of the QueryUpdate (no change in numbers from code calculation).

[CJ_London]

the point is that you should not store calculated fields in a table, you calculate 'on the fly' in a query, form or report. As you are experiencing, trying to maintain the field is a pain in the ***

[orange]

I agree with Ajax's answer. With database you do not store a field value that can be calculated as and when needed using a query. There can be exceptions for read only reporting etc where the data values are in the final "report". But Access is different than Excel or, said differently, Database is different than spreadsheet.

[CJ_London]

I can't work out how your percent difference is calculated - I would have thought the value in F1 should be null or 0 since there isn't a previous difference

Providing your sample numbers are without breaks and the order is relevant to determine the previous value then this query should do what you require

SELECT LineDetails.ReportID, LineDetails.SampleNo, LineDetails.Weight, (SELECT Avg(Weight) FROM LineDetails AS L WHERE ReportID=LineDetails.ReportID) AS CAvgWeight, [Weight]/[CAvgWeight] AS CWtAvgWeight, IIf(IsNull([WtAvgWeight]),Null,[CWtAvgWeight]/[WtAvgWeight]) AS PCChange
FROM LineDetails LEFT JOIN (SELECT ReportID, SampleNo+1 as PrevSample, (SELECT Avg(Weight) FROM LineDetails AS L WHERE ReportID=LineDetails.ReportID) AS AvgWeight, Weight/AvgWeight AS WtAvgWeight
FROM LineDetails)  AS P ON (LineDetails.ReportID = P.ReportID) AND (LineDetails.SampleNo = P.PrevSample)
ORDER BY LineDetails.SampleNo;

You'll need to modify the percentage calc since I can't see how you have derived it

[Chris033115]

When the first sample is SAVED the Percent Diff isn't calculated because it would be null or division by zero. However when the 20th sample is saved, there is a TOTAL AVERAGE WEIGHT for the group that can be used, hence the reason for looping through all members of this group and recalculating the (final) Percent Diff for each sample in comparison to their combined TotalAveWeight. At this point the line items hold each sample's "Weight" and the header record holds the value for the "TotalAveWeight". (1 to many relationship linked with the "ReportID".)

On the Excel spreadsheet:
Column C holds each sample/line item "Weight"
The "TotalAveWeight" based on all five samples is in cell D8

Plugged Equation (LineItem.Weight/Header.TotalAveWeight): (107.2992 / 107.08528) = 1.00199766
1.00199766 - 1 = 0.00199766
0.00199766 * 100 = .199766 (rounded above to 0.20)

I'm moving a database/application to Access... I've done this is different programming software and it worked fine. I'm not storing the "equation" in a field, I'm updating the value of a field based on a programmed calculation and 99% of the fields in this application are populated with the results of a calculation.

I think part of the problem is that the TotalAveWeight for the specific ReportID is not being found. And then I need to modify the instructions on looping through the records to include RS.MoveFirst and RS.MoveNext or something to that effect. I'm gonna work on the code and scrape the idea of using an Update Query.

[CJ_London]

so you just need to modify the code (highlighted) and don't need the previous record to get this

SELECT LineDetails.ReportID, LineDetails.SampleNo, LineDetails.Weight, (SELECT Avg(Weight) FROM LineDetails AS L WHERE ReportID=LineDetails.ReportID) AS CAvgWeight, [Weight]/[CAvgWeight] AS CWtAvgWeight, IIf(IsNull([WtAvgWeight]),Null,([CWtAvgWeight]/[WtAvgWeight]-1)*100) AS PCChange
FROM LineDetails

[Chris033115]

Ajax - I appreciated your thought and time. That's one complicated looking Select statement. I'll give it a try... does this replace the SELECT statement in my original code?

My second biggest problem was getting it to update ALL the line items that belong to a single ReportID. My original code updated the first matching record it found and didn't update the other samples' PercentDiff field. The SELECT ... WHERE (by ReportID) should create a recordset with multiple line items but it would only actually update 1 of them and exit.

[CJ_London]

You are missing the point - you should not be updating a calculated field - this is Access, not Excel. If you want to go that route, stick with Excel.

does this replace the SELECT statement in my original code?

No - it provides the complete solution per your post #1 - you don't need your code. Why don't you just copy and paste it into a query and run it.

[Chris033115]

This is the result using your Select statement on the same 5 records displayed in post #1.

[CJ_London]

sorry - left a bit of the old code in there by mistake

round(([CWtAvgWeight]-1)*100,2) AS PCChange

[Chris033115]

The Update Query still didn't work. I have figured out the proper code to loop through and update all the line items for the ReportID and that's what I'm gonna' use since I'm doing multiple other things with code with a single push of a button.
(linked to header record by ReportID, showing TotalAveWeight field. All line items of report now have "PercentDiff" reflective of the test group.

Thanks again Ajax for your persistence!

THIS IS WHAT WORKS...

Public Sub RecalcPercentDiff()

Dim RS As Recordset
Set db = CurrentDb
Dim WtAve As Integer
Dim intCounter As Integer


WtAve = Nz(DAvg("Weight", "LineDetails", "[ReportID]='" & Me.txtReportID & "'"), 0)


intCounter = 0


Set RS = db.OpenRecordset("SELECT * FROM LineDetails WHERE ReportID = '" & Me.txtReportID & "'")


With RS
    Do Until intCounter = TempVars!v_RecordsAccepted
        .Edit
        !PercentDiff = ((!Weight /WtAve) - 1) * 100
        .Update
        .MoveNext
        intCounter = intCounter + 1
    Loop
End With
RS.Close
Set RS = Nothing


End Sub

[CJ_London]

glad you got there in the end...