Joining a table where the records are stored within a column separated by a comma

[Number11]

So i need to work away to search for a postcode within a table, but the data is separated by a comma

Table fields

Region_Sales
Total_Orders
Short_Postcode_Areas

the table i have only holds the part postcode as TN25, as the order datasets have multip with the comma the query is not finding the TN25?

Any ideas please

[td]


[/td]​

 

[tvanstiphout]

select * from myTable
where InStr(Postcode_Areas, "TN25") > 0

As you are finding out, this is a gift that will keep on giving, until you normalize the database design and spin off the postcodes into their own child table.

 

[theDBguy]

Just curious, are you using a MVF (multi value field)?

 

[Number11]

Just curious, are you using a MVF (multi value field)?

its the dataset we have nothing i can do about it its years and years old LOL
and i need to have a table join as the exampel of gave was just that

 

[MajP]

Good question. Is that just a text field with commas or is it a MVF?
I assume it is not an MVF because you would be able to query it just like any other query.
If it is a simple text field you could easily write code (or simply have Chat do it) that would normalize your data by creating a child table. Then you can do this much easier.

tblPostCodes
--PostCode
--Region

 

[MajP]

So I asked Chat to do this.

Public Sub PopulatePostCodes()Dim db As DAO.Database
    Dim rsSource As DAO.Recordset
    Dim rsTarget As DAO.Recordset
    Dim vCodes As Variant
    Dim i As Long
    Dim strCode As String

    Set db=CurrentDb
    Set rsSource=db.OpenRecordset("tblData",dbOpenSnapshot)
    Set rsTarget=db.OpenRecordset("tblPostCodes",dbOpenDynaset)
    Do While Not rsSource.EOF
        ' Split the comma-separated postcodes
        If Nz(rsSource!PostCodes,"")<> "" Then
            vCodes=Split(rsSource!PostCodes,",")
            For i=LBound(vCodes)To UBound(vCodes)
                    strCode=Trim(vCodes(i))
                If strCode <> "" Then
                    rsTarget.AddNew
                    rsTarget!Region=rsSource!Region
                    rsTarget!PostCode=strCode
                    rsTarget.Update
                End If
            Next i
        End If
        rsSource.MoveNext
    Loop
    rsSource.Close
    rsTarget.Close
    Set rsSource=Nothing
    Set rsTarget=Nothing
    Set db=Nothing
    MsgBox "tblPostCodes populated successfully.",vbInformation
End Sub
``

Untested but looks correct.
My prompt

I have a table called tblData with columns Region and PostCodes. In the column PostCodes I have multiple post cost seperated with a comma. I created a new table tblPostCodes with fields Region and PostCode. Write a vba procedure to populate tblPostCodes by splitting the post codes in tblData.

 

[Number11]

So I asked Chat to do this.

Public Sub PopulatePostCodes()Dim db As DAO.Database
    Dim rsSource As DAO.Recordset
    Dim rsTarget As DAO.Recordset
    Dim vCodes As Variant
    Dim i As Long
    Dim strCode As String

    Set db=CurrentDb
    Set rsSource=db.OpenRecordset("tblData",dbOpenSnapshot)
    Set rsTarget=db.OpenRecordset("tblPostCodes",dbOpenDynaset)
    Do While Not rsSource.EOF
        ' Split the comma-separated postcodes
        If Nz(rsSource!PostCodes,"")<> "" Then
            vCodes=Split(rsSource!PostCodes,",")
            For i=LBound(vCodes)To UBound(vCodes)
                    strCode=Trim(vCodes(i))
                If strCode <> "" Then
                    rsTarget.AddNew
                    rsTarget!Region=rsSource!Region
                    rsTarget!PostCode=strCode
                    rsTarget.Update
                End If
            Next i
        End If
        rsSource.MoveNext
    Loop
    rsSource.Close
    rsTarget.Close
    Set rsSource=Nothing
    Set rsTarget=Nothing
    Set db=Nothing
    MsgBox "tblPostCodes populated successfully.",vbInformation
End Sub
``

Untested but looks correct.
My prompt

Ok so this works but its not then coping over the Total_Sales numbers which in my example the Total of 4821 is for all the postcodes in that Region

 

[RonPaii]

its the dataset we have nothing i can do about it its years and years old LOL
and i need to have a table join as the exampel of gave was just that

I also had a non-normalized field like yours giving me the same problems.

The fix is to add the new detail tables for the zip code keeping the original form controls but detached. In the on current event, fill that control. In the after update event, update your normalized table. The user will not see the change. You could also keep the original column in the table, updating it until you can cleanup the code base.

 

[MajP]

ok so this works but its not then coping over the Total_Sales numbers which in my example the Total of 4821 is for all the postcodes in that Region

There is no need to.
Main table has
Region
Total Sales

Child table has
Region
PostCode

You can link Main Table to child table using Region. Make Region the PK in the Main table and the foreign key in the child table.

 

[tvanstiphout]

Ok so this works but its not then coping over the Total_Sales numbers which in my example the Total of 4821 is for all the postcodes in that Region

Total_Sales should not be a field in a table, but a field in a Totals query, so it can be calculated on the fly, since sales change all the time, and regions may sometimes change as well.

As an example of how to do it correctly, take a look at the Northwind Dev Edition sample database, specifically the OrderTotal field on frmOrderList. Its value comes from qryOrderTotal, which calculates it on the fly from the order line items with an expression: OrderTotal: Sum([Quantity]*(1-[Discount])*[UnitPrice])

 

[MajP]

Total_Sales should not be a field in a table, but a field in a Totals query, so it can be calculated on the fly, since sales change all the time, and regions may sometimes change as well.

Unfortunately the data does not come to the OP in that format. The OP only gets the totals.

its the dataset we have nothing i can do about it its years and years old LOL

 

[MadPiet]

So i need to work away to search for a postcode within a table, but the data is separated by a comma

Table fields

Region_Sales
Total_Orders
Short_Postcode_Areas

View attachment 123574

the table i have only holds the part postcode as TN25, as the order datasets have multip with the comma the query is not finding the TN25?

Any ideas please

[td]


[/td]​

(Not much to add here...)
But since there's no way to tell from your data which orders came from which Postcode Areas, you can't really split the Total_Orders over the individual postcodes. I think the best you can do is join on the INSTR() like MajP posted.
If you're collecting new data, then if you can, split the string so you get (PostCode, Amount)... well, if that's possible.

 

[isladogs]

I also have a table of UK postcode districts that was supplied with 2 fields with multiple values separated by commas.
The data came from the UK Government Office for National Statistics (ONS)
If you look at the nearby districts field, not all have the same postcode area as they are adjacent to districts in a different area.
The Sectors field shows all the sector for a given postcode district

There is a similar fields of districts in the PostcodeAreas table

I use those fields purely for display purposes so have never needed to split them.

However it might be possible to do so fairly easily by converting them to MVFs with a comma separator (not tested)

 

[MadPiet]

You could split them with VBA in Access or export to Excel and use PowerQuery and just be sure to specify to split to new ROWS and not new columns. Then just import the fixed table.
I opened a new Excel file and did the transform... took like maybe a whole minute of some pointing and clicking. =)

let
    Source = Excel.Workbook(File.Contents("C:\Users\piete\OneDrive\Documents\Postcodes to Split.xlsx"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Sheet1_Sheet,{{"Column1", type text}, {"Column2", type text}}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Postcode District", type text}, {"Sectors", type text}}),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Changed Type1", {{"Sectors", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Sectors"),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Sectors", type text}}),
    #"Trimmed Text" = Table.TransformColumns(#"Changed Type2",{{"Sectors", Text.Trim, type text}})
in
    #"Trimmed Text"

(and then you'd just import that... )

 

[The_Doc_Man]

A Co-Pilot search suggests that a "true" JOIN cannot be formed with that comma-delimited string since you don't know which sub-element of the complex string would match. You CAN build a JOIN using LEFT, RIGHT, or MID - all of which would require fixed parameters. But a more complex expression, perhaps involving INSTR(), would either make the query non-updateable or would trigger a "JOIN operation not supported" error (or words to that effect.) You could have the effect of a JOIN by having a WHERE clause that involved an INSTR() operation on that comma-separated string, but even if there were indexes, any function involving the complex string would negate their effectiveness.

Also, there is the matter of the order of operation of SQL on its components. The JOIN clause is processed early in the execution of the SQL query whereas the WHERE clause processing occurs much later. This can be a serious disadvantage in terms of execution speed, since a JOIN reduces the size of the intermediate result set earlier, leaving fewer records for later phases to process. Your WHERE clauses would have a lot more to do in your case because you can't make a proper JOIN early to reduce later work requirements.

My question to you is, given that you get the data set you get, is it possible for you to back off one step and work on a COPY of the data? I.e. before processing whatever else you do, you could try to deconvolute the field with multiple codes into a normalized parent/child situation? After which you could then quite easily normalize the copy and allow indexes to become useful again.

If you are essentially doing analysis on a data set, you probably don't want things changing out from under you during that analysis anyway. Thinking along these lines, does working from a snapshot fit in with your situation? Because you can do as you bloody well please with a snapshot, including that extra processing step to normalize that comma-separated field. Plus, of course, the cost of a clean-up phase to get rid of the extra copy when you are done with it.

I don't know if this would be too much work for you because I don't know the details of your data set or your analysis. But if you have an extensive, multi-step analysis to perform, it is POSSIBLE that the efficiency benefits of stronger normalization would outweigh the cost of the preparation and cleanup steps added by that normalization.

 

[CJ_London]

Bit confused - the thread title is 'Joining a table', the body about searching for a postcode. I see plenty of suggestions for searching. If it is about joining you can use a non standard join as suggested by Doc.

Assuming it is about joining, your can use Like in the join e.g.

SELECT *
FROM tblOrders INNER JOIN tblPostcodes ON tblPostcodes.postcode_area Like '*' & tblOrders.postcode & '*'

or more focused if there can be say a TN2 and a TN23

SELECT *
FROM tblOrders INNER JOIN tblPostcodes ON "," & tblPostcodes.postcode_area & "," Like '*,' & tblOrders.postcode & ',*'

This has to be done in sql, the query gui cannot display non standard joins.

Using like negates the use of indexes, however in this situation, I suspect the volume of records will be low (perhaps 1000 or so) so performance should not be an issue

 

[The_Doc_Man]

CJ's point is valid. The size of the data set figures into the efficiency of the process. For smaller data sets, it might not matter. When your processing starts to take tens of minutes to complete, you are heading to the fringe area where a pre-normalization step becomes more useful.

 

[MadPiet]

Number11,
But what are you going to DO with the data once it's separated? That's the part that's missing. If the number you get is a total for those postal codes, then maybe there's not much you can do - well, insofar as there's no way to spread the count over each postal code. You could use AVERAGE, but without knowing what kinds of questions you're going to ask of the data, it's hard to help.