Looping through data?

[SteveE]

I have a table with service codes W01 Service1 W02 Service 2 etc etc

When I receive orders in one of the fields OrderService will be required service codes in simple code format ie W01,W02,W03 etc

I need to populate 1 Text field against each ordr with all the service requirements ie Service1, Service6, Service5

I believe some form of instr function looping through the OrderService field is required but I am stumpped ! to add to my problem sometimes the same ServiceCode will be repeated but I dont want to repeat the descriptrion
to get ths I have a query

SELECT DISTINCT BulkDel.[ORDER NO], BulkDel.[DEL PLUS CODES] AS ServiceCode, IIf(InStr([Del Plus Codes],[Del]),[Desc],"") AS [SERVICES Desc]
FROM BulkDel, tblDellP
WHERE (((BulkDel.[ORDER NO])="024943190"));


ORDER NO ServiceCode SERVICES Desc
024943190 w01,w02,w20,w05,w06 Evening
024943190 w01,w02,w20,w05,w06 Marci
024943190 w01,w02,w20,w05,w06 Pre 12.00
024943190 w01,w02,w20,w05,w06 Pre-12.00
024943190 w01,w02,w20,w05,w06 Timed

I would like to have this in 1 field ie SERVICES Desc =Evening, Marci, Timed, Pre 12.00

Could any advise best way create a function to produce this?
thanks in advance

 

[jzwp22]

If an order can have many services associated with it, then that describes a one-to-many relationship. Additionally, if a service can apply to many orders that is another one-to-many relationship. When you have 2 one-to-many relationships between the same 2 tables, you need a junction table to capture both relationships

tblOrders
-pkOrderID primary key, autonumber
-OrderNo

tblServices (I assume that your current services table looks something like this)
-pkServiceID primary key, autonumber
-WO
-WODescription

tblOrderServices (the junction table)
-pkOrderServicesID primary key, autonumber
-fkOrderID foreign key to tblOrders
-fkServiceID foreign key to tblServices

 

[SteveE]

Thanks for the reply
1/ I have a table of available codes and descriptions the PK is Wcode
2/ The orders arrve in and 1 of the fields can have several WCodes the format is: W01,W03,W03 etc
When I import the order I wish to change the WCodes to a meaningfull Text Field of the Code Descriptions.
Using the instr function I can in my query capture each WCode meaning but this gives me another row of data for each Description i.e.
024943190 W01 Evening
024943190 W02 Marci
024943190 W20 Pre 12.00
024943190 W05 Pre-12.00
024943190 W06 Timed

What I need is show 1 row with the services description.
024943190 Evening, MarciPre, 12.00, Timed
hope this is a bit clearer.

 

[jzwp22]

The orders arrve in and 1 of the fields can have several WCodes the format is: W01,W03,W03 etc

How are you getting this data, via a spreadsheet?

When you import, you will need to migrate the data to the
tblOrderServices I suggested or something similar, then create a query that joins the table back to your code table to show the applicable description.

 

[SteveE]

Thanks again but I think you are missing my point, the data arrives in a text file which is imported into the order table, 1 of these fields contains a ServiesReq string of Wcodes which can have upto 10 Wcodes contained in it. I already have the table of available codes to look up against and I can pull the indivisual code descriptions from the ServicesReq string using instr() in my first part of this post. I need a way for looping through the codes contained in the ServiceReq string and putting the Descriptions into a single Field.
thanks again for your time though

 

[jzwp22]

The normalized way of handling it is to take the imported data and migrate it into the appropriate table structure (as records) and then from there it is just a simple query.

...putting the Descriptions into a single Field.

If you try to put all of the descriptions into a single field and depending on the # of characters involved in each description, you may quickly reach the 255 character limit for a field such that some of your descriptions will be truncated.

 

[SteveE]

I understand what you as saying but an order arrives and is processed we current output this to a carrier in a single line of data per order so if an order arrives in and requires w01 and w03 (theses are in the field as W01,W03 ) we manually convert thes to the meanings as the end carrier driver have no understanding of these codes so the order would be issued as Order 1234567 ServicesReq: Timed,Pre12.30

currently I have
Order 1234567 ServicesReq: Timed
Order 1234567 ServicesReq: Pre12.30

A query would need to lookup against a specific key ie W01 but the field is W01,W02 etc so I use the Instr to find specific keys in the string. The works 100% but will give me as many rows od data as services in this case 2 rows The descriptions are a max 10 chrs so limitations of 255 are no issue.

 

[jzwp22]

I would still recommend putting the data into a normalized structure. Once in the structure, you can use a custom function to put the descriptions together. Allen Browne has just such a function on his site.

 

[SteveE]

Ok thanks fyi. I have gone down your route, I have created a table with the ServiceReq codes as a PK with meaning in a Desc Field. As my data imports the field containing the service codes is appended to my table (only new codes are accepted due to the PK) I can see this table growing but not un-manageable as the service codes are manually applied by order entry or field sales people and opten we will have W01,W02 or W02,W01 etc . My new update query will then apply the Desc from the new table in a single field in the order row.
many thanks for the guide provided.
Steve

 

[jzwp22]

You're welcome. Good luck with your project.

 

[SteveE]

Re: Looping through data Updated?

Update for anyone interested in this:
I found that maintaining the lookup table with meanings of the codes was hard to do in the live system with 3-4000 orders a day so I pressed on and found my solution below, it might not be pretty but it works:

Private Sub DEL_PLUS_CODES_AfterUpdate()
On Error Resume Next
Dim WCodesDB As DAO.Database
Dim WCodesRS As DAO.Recordset
Dim DC As String
Dim XX As String
Dim Cost As Currency
Dim KCost As Currency
Cost = 0
KCost = 0
XX = ""
DC = ""
Set WCodesDB = CurrentDb ‘ Table containing WCodes WCode = PK
Set WCodesRS = WCodesDB.OpenRecordset("WCodesQ", dbOpenDynaset) ‘using query as source rs so I can use only the valid flag codes in the table
Do Until WCodesRS.EOF ‘ table has only 24 valid codes from possible 178 so this is a quick lookup
If InStr(Me.DEL_PLUS_CODES, WCodesRS![wCode]) Then
XX = WCodesRS![wCode] ’ adds the WCode to my DC string
DC = DC & " + " & XX & " " & WCodesRS![Desc] Combines all the WCodes to my DC string
Cost = Cost + WCodesRS![ServCost] ’ Captures the costs against each WCode
Me.SynCharge = Nz(Cost)
End If
WCodesRS.MoveNext
Loop
WCodesRS.close
Set WCodesRS = Nothing
Set WCodesDB = Nothing
End Sub

The results of this give me for this example:
W02,W03,W09,W31,W65 in Order Field (W03,W09, and W31 being the only valid WCodes)
= “ + W03 TIMED. + W09 PALL CONFIG + W31 PALLETISED “, in my ServicesReq Field

My only problem is that I currently run this code on a data sheet form, this code fires on focus to a specified field and also in an after update as in this sample. I cannot figure out yet how to run this against all the orders in the RecordSet looping within a loop I think, any help there would be appreciated.