how to sum a multi-value field in access query (1 Viewer)

bmhuettinger · Mar 20, 2018

Good afternoon! I have a field in a linked table that contains several alpha-codes for machine processes (this table is downloaded from our company system, not a table that I created or can modify). Each of these alpha-codes has a corresponding number of days for processing. I've used a nested replace() function to change all of the codes to numbers (of days),used the val() function to isolate the numbers, and now I'd like to add the numbers together for a "total number of days remaining". I've tried sum() and dsum() but i'm stuck.
Any ideas?

plog · Mar 20, 2018

SUM will add the values of multiple rows together, + will add the value of multiple columns together.

I can't really envision your data, can you provide a sample of it? Actually provide 2 sets of data:

A. starting data from your table. Include table and field names and enough data to cover every case.

B. expected results of A. Show me what data you expect to end up with after all is said and done when you process the data in A.

arnelgp · Mar 21, 2018

Sum on the expression you use to extract the number part, eg:
Sum(val(mid(field1, 2, 2)))

bmhuettinger · Mar 21, 2018

when I add the sum() to the expression I get an error message that my query doesn't include the specified expression "order" as part of the an aggregate function.
What is a good way for me to include the table? I have a field [RemainingProcess] and the data looks like this:

AE,FR,SS,QC,PCK
EF,TL,SLN,SS,QCF,PCK
AG,TL,SLN,SS, QCF

All of these alpha-codes have numeric values (number of days).

I have UDF [DaysRemaining] that uses the replace() to convert the alpha-codes to the corresponding number of days (and change the commas to spaces)

7 7 5 4
7 4 3 5 4
7 4 3 5 2

I'd like to sum the results (preferably in the same expression/field - [RemainingDays]) and come up with the following:

23
23
21

plog · Mar 21, 2018

You should create a custom function to do this. You would pass it the entire string, it would parse it, retrieve the values for each element then sum them. In a query, the field would look like this:

RemainingDays: get_RemainingDays([RemainingProcess])

Then, you build a function in a module using this outline:

Code:

Public Function get_RemainingDays(in_Processes)
    ' takes string of processes remaining (in_Processes) and returns number of days to complete them all

ret = 0    ' return value, by default 0

' below is code you write to process the data

' use the Split function (https://www.techonthenet.com/access/functions/string/split.php) to extract the elements of the string
' use a loop to cycle through every element
' inside that loop use a Dlookup to retrieve the corresponding values
' add the values retreived to the variable ret



get_RemainingDays = ret
  ' returns days remaining value that has been added together. 

End Function

isladogs · Mar 21, 2018

You've just found out for yourself one of the reasons why multivalue fields are generally disliked by experienced Access developers.
They may save a little time at the start but add significant time and effort later on.

Suggest you ask permission to replace them ASAP

arnelgp · Mar 21, 2018

Add the expression to your Grouping

bmhuettinger · Mar 21, 2018

ridders said:
You've just found out for yourself one of the reasons why multivalue fields are generally disliked by experienced Access developers.
They may save a little time at the start but add significant time and effort later on.

Suggest you ask permission to replace them ASAP

I wish. We're a large manufacturing facility and this data is downloaded straight from the system that operates mills and furnaces and machines of all kinds...I think the likelihood of getting anyone to reprogram the system to output "access friendly" data is akin to me figuring out how to solve this problem all by my lonesome. slim to none.

bmhuettinger · Mar 21, 2018

plog said:
You should create a custom function to do this. You would pass it the entire string, it would parse it, retrieve the values for each element then sum them. In a query, the field would look like this:

RemainingDays: get_RemainingDays([RemainingProcess])

Then, you build a function in a module using this outline:

Code:

Public Function get_RemainingDays(in_Processes) ' takes string of processes remaining (in_Processes) and returns number of days to complete them all ret = 0 ' return value, by default 0 ' below is code you write to process the data ' use the Split function (https://www.techonthenet.com/access/functions/string/split.php) to extract the elements of the string ' use a loop to cycle through every element ' inside that loop use a Dlookup to retrieve the corresponding values ' add the values retreived to the variable ret get_RemainingDays = ret ' returns days remaining value that has been added together. End Function

Thanks - I think I understand both the split function and the Public Function for the module, but I don't see how to connect the dlookup ("inside the loop") to assign value to the newly split alpha-codes. I'm assuming a table (which I have [ProcessRunTimes]) but I'm not sure what that would like in the code...

arnelgp · Mar 21, 2018

Perhaps you could use Eval() in your query to sum it

Expr: Eval(Replace(DaysRemaining(field1)," ", "+"))

bmhuettinger · Mar 21, 2018

arnelgp said:
Perhaps you could use Eval() in your query to sum it

Expr: Eval(Replace(DaysRemaining(field1)," ", "+"))

That did it!!!! Genius! I do have one other issue now, however, and let me know if this if I'm circumventing any forum rules, but because I've used so many nested replace() to accommodate 27 scenarios, my expression is "too complex". I think I can get a maximum of 18 total functions before i get the error. Do I have to split this into two (or three) UDFs?

arnelgp · Mar 22, 2018

You can by all means.

how to sum a multi-value field in access query (1 Viewer)

bmhuettinger

Registered User.

plog

Banishment Pending

arnelgp

..forever waiting... waiting for jellybean!

bmhuettinger

Registered User.

plog

Banishment Pending

isladogs

MVP / VIP

arnelgp

..forever waiting... waiting for jellybean!

bmhuettinger

Registered User.

bmhuettinger

Registered User.

arnelgp

..forever waiting... waiting for jellybean!

bmhuettinger

Registered User.

arnelgp

..forever waiting... waiting for jellybean!

Similar threads

Users who are viewing this thread