Complex Calculated fields in form

[JaneInMA]

I am building a form to track rejected product and doing fine until I got a request for a little more complexity that just takes it past my immediate knowledge.
I have the following fields- RejectType, Action (Added, Destroyed, Issued) and Number of Product.
I need to keep track of total number Added, # added per Reject Type, total Destroyed, # destroyed per Reject Type, total issued, # issued per Reject Type.
I presume I need to do this using VBA and recordsets?
Of course additional complexity is added as this is all to be tracked in a subform being limited by the linking fields.
Any thought how difficult this will be to set up?
FYI I am just above Access VBA novice level, ie recordsets still cause minor problems.

 

[RickSpr]

From what you say, I don't see where you need to use a recordset and code for this. A pair of totals queries should be able to give it to you. To make a totals query, just create an ordinary query containing the fields you want to add up (NumberOfProduct), along with the fields you want to group by (RejectType and Action in this case). Choose View>Totals from the menu to add the Totals line to the query grid. Then just choose Sum for the NumberOfProduct field.

This will give you a recordset with one row per combination of RejectType and Action, with the total of product in that category. You could easily display this in a subform, and not require any coding at all, except that each time a record is added on the main form you need to requery the subform to cause the totals to update.

To get the total added, destroyed, and issued, it seems to me you could add a footer in the subform that totals the category totals--quite easy, see Northwind for an example. Or, if you don't like that for some reason, you could use another totals query that just groups by Action, and another subform to display it.

Mind you, if your table is quite large, all this requerying would be costly and balky. But the number of Actions is small, and RejectType sounds like it wouldn't have a large number of possible values, so I'm hoping the query would execute quickly enough for you. If not, maybe you could just open the first totals query as a recordset in your form's Open event, save the totals, and from then on keep adjusting them as new records are added (only works for a single-user database, though). Rick Sprague

 

[MisterC]

That shouldn't be difficult at all. Generally speaking, you can just do some queries with the aggregate function COUNT and base your sub forms on those queries...

example query to return the count of an action given a RejectType's value:

SELECT Count(Table.Action) AS CountOfAction
FROM Table
GROUP BY Table.RejectType
HAVING (((Table.RejectType)=Whatever));

 

[JaneInMA]

Thanks for the hints, but the calculations are already in a subform. I have a main form which notes the reject product, lot and part number, then for each combination they then want to add the details I mentioned earlier. Can I link a field to a result from a query?. I tried this and it is giving me an error. I can see no problem if necessary creating multiple queries for each action/Reject Type but Access doesnt seem to want to display this. That is why I thought I may have to use VBA to calculate it but if I can circumvent it, I will happily. As far I can see no subforms are allowed in subforms. I am doing the calculations (or trying to in the subform footer)

 

[JaneInMA]

I am having a small amount of success using Dsum. Am I using a sledgehammer to crack a peanut?

 

[RickSpr]

I don't know. Running a query, or a bunch of queries, across the whole table every time you enter a record is really no different from having a bunch of DSums executing instead. They'd probably be executing the exact same query underneath, in fact. You might get away with doing fewer queries than DSums, if you calculated all three totals with one query. That would improve the efficiency.

The DSums are easy, though. Maybe the best thing is to start with them. If performance becomes an issue, write queries to get 3 totals for the price of one, and use recordsets to get the values. If even then performance is inadequate, modify things so that you only do the queries when your subform moves to a new record (the Current event), saving the totals in VBA variables in the Declarations section of the form module. After that, adjust the totals in code whenever another transaction is entered. Every time you change the totals variables, copy the new value to the form controls. That's the most efficient way, but it takes the most work.

I didn't explain that very thoroughly. If you need more detailed instructions for one of these approaches, just ask. Rick Sprague

 

[JaneInMA]

Thanks for the help, it is now working fine even if I have half a page of code to get them all the details they need.
Small detail, is there soem code I can add so that if the Dsum finds no relevant records it returns 0 not a null?
I was looking at Nz but I am a little unclear how to add this to the Dsum.
Thanks again- I am really getting the idea that with SQL and VBA Access can really do almost anything if you just do not give up.

 

[JaneInMA]

I have found a solution but if anyone has a more elegant solution please let me know.
I changed my variables to Variant type
Assigned the Dsum to the variant then used the Nz function.
Dim IntAQLAdd As Integer
Dim VarAQLAdd As Variant
VarAQLAdd = DSum("[NumberOfRejects]", "tblRejectTrackingEntries", "[RejectType] = 'AQL' AND [Action] = 'Added'&quot
IntAQLAdd = Nz(VarAQLAdd, 0)
I do this for all 9 variables and then add and subtract as necessary to create all the running totals requested.