Replacing Text Results With Number Values In a Query? (1 Viewer)

Good morning all,
I'm going to try and explain my issue as precisely and concisely as possible...

I have an imported table field that is a series of alpha-codes, concatenated with ",". Each code identifies a process step and corresponds with a certain number of days left in the entire process. I used a SplitField function in a query to separate the codes into individual processes, AND I have a (static) table that lists each possible process with it's corresponding number of (lead time) days.
Now, I would like to assign numeric data to the alpha-codes, based on the Lead Time table, and add them all together for a total "number of days remaining".
For example, The "RemainingProcess" field from the daily imported table for Sales Order 98765 = SS,AE,PL.
The query result is [1stProcess]=SS
[2ndProcess]=AE
[3rdProcess]=PL

The Lead Time Table
[SS]=2
[AE]=4
[PL]=1

The total number of days remaining for Sales Order 98765=7 (2+4+1)

I feel like the logic seems far more complicated than it should be, and that the answer is right in front of me but I'm at a loss.
I have to do this on a daily basis (import raw data, splitfield query, match processes with lead times, etc.) so I'm hoping for a relatively simplistic solution.

Thank you in advance.

Ultimately, this needs to be a simple aggregate query (https://support.office.com/en-us/ar...-a-query-430a669b-e7fd-4c4b-b154-8c8dbbe41c8a). However, I'm not convinced your data is structured properly to achieve that.

Can you post better data? Include actual table and field names along with enough sample data to give us a good idea of it. A spreadsheet or the database itself would be great, but if you like you can post data directly using this format:

TableNameHere
Field1Name, Field2Name, Field3Name, ...
David, 23, 1/4/2018, ...
Sally, 39, 2/7/2017, ...

The Lead Time table that contains the numeric values isn't completed yet as I'm still trying to figure how this all fits together but I was thinking of:
[SS],[AE], [AG],[PL]...[FR], etc. (processes)
1,4,5,1,2....3, etc (lead time in days)

The daily sales order information with respective remaining processes (the splitfield query) is attached as an Excel spreadsheet.

Thank you for replying,
Brandi

Show us the splitfield function and ill extend its functionality.

Also inckude the lead time table where those codes can be found.

You don't store values as field nor table names. So your lead time table should have lots of rows, not columns:

ProcessTimes
Process, DaysLeadTime
SS, 1
AE, 4
AG, 5
PL, 1
etc.

Also, that query isn't really helping you achieve what you want. You need to move the data to a properly structed table. The same principle applies with numbers as with values--don't store them in field names, store them as their own row of data.

So, using the first record in your attachment, you should store your data like so:

SalesOrders
SO_Num, Router, ProcessNumber, Process
91941, 234619, 1, SS
91941, 234619, 2, SLN
91941, 234619, 3, QCS
91941, 234619, 4, PKR

Get your data into that format and you can then run the query you want like so:

I really don't have any control how the daily data comes over - it's exported out of our system in that format - I'm not sure how to modify to your specs, easily on a daily basis.

Arnelgp,
This code is in a module

Public Function SplitFile(intField As Integer, strValue As String, strDelimiter As String) As String
Dim varSplit As Variant
On Error GoTo Err_SplitFile
varSplit = Split(strValue, strDelimiter, , vbTextCompare)
SplitFile = varSplit(intField - 1)

Err_SplitFile:
End Function


And then the query formula is:
1stProcess: splitfile(1,[remaining processes],",")
2ndProcess: splitfile(2,[remaining processes],",")
...and so on

plog is right.
your lead time table should
be structured vertically.

if you did that you can calculate the
total remaining hours.

add this to your query.
change the fieldname and table name
to the one you have.

leadtime = the leadtime in you leadtime table
leadcode = the field on your leadtime table
leadtimetable = the name of your leadtime table
importedtable = the imported table name


(select sum(t1.leadtime) from leadtimeTable as t1 where instr(importedtable.[remaining processes],t1.[leadcode])<>0) AS totalRemaingHrs

Edit: i recognise the code.

Thank you so much for your help. I created the LeadTime table based on Plog's format suggestion. I then made a table based using my SplitField qry (identical to the spreadsheet I originally posted). I created a new query using both tables and modified your code with my tables and field names. I'm getting a "syntax error in union query" and it's highlighting the "AS"

(select sum(t1.leadtime) from tbl_LeadTimeBySteps as t1 where instr(tbl_remainingprocessesLeadTime.[remaining processes],t1.[processcode]])<>0) AS totalRemaingHrs

I think i should probably be modifying "t1" to something and that somewhere I should be creating a "totalremaininghrs" field but I'm not sure where...

my apologies for not keeping up the pace...

Why do you have a union query?

that was the default query when i pasted your code into the SQL window?