[AccessD] Cross posted as got brain freeze

[Stuart McLachlan]

I think this is what you are after.  I'll leave the conversion of the Debug.Print to XML to you :)

tblSections is structured exactly as per your sample data.

One query:

SELECT Section, Start_Point As Bndry
FROM tblSections
UNION SELECT  Section, END_Point As Bndry
FROM tblSections
ORDER BY Section, Bndry;

Two functions in a module:

Option Compare Database
Option Explicit

Function BuildList() As Long
Dim rs As DAO.Recordset
Dim startpoint As Long
Dim endpoint As Long
Set rs = CurrentDb.OpenRecordset("qryBndrys")
startpoint = rs!bndry
Do
    rs.MoveNext
    If rs.EOF Then Exit Do
    endpoint = rs!bndry
Debug.Print rs!section, startpoint, endpoint, Getcodes(rs!section, startpoint, endpoint)
    startpoint = endpoint
Loop
rs.Close
End Function

Function Getcodes(section As String, startpoint As Long, endpoint As Long) As String
Dim rs As DAO.Recordset
Dim strSQL As String
Dim strT As String
strSQL = "Select Defect_Code from tblSections where Start_point <= " & startpoint & _
          " and end_point >= " & endpoint & " and Section = '" & section & "' order by 
Defect_Code"
Set rs = CurrentDb.OpenRecordset(strSQL)
While Not rs.EOF
    strT = strT & "," & rs!Defect_Code
    rs.MoveNext
Wend
Getcodes = Mid$(strT, 2)
End Function

Output of BuildList:

buildlist
SecA           0             10           RUT
SecA           10            20           POTHOLE,RUT
SecA           20            50           RUT
SecA           50            60           FAT,RUT
SecA           60            70           FAT,POTHOLE,RUT
SecA           70            100          FAT,RUT
SecA           100           140          FAT





On 13 Dec 2018 at 8:05, Paul Hartland via AccessD wrote:

> Could be limitless potential codes such as RUT & POTHOLE.
> 
> In the first table, a single code will never overlap itself, i.e in
> your question 2, there would be a single entry for pothole of start
> point 10 and end point 60.
> 
> 
> 
> 
> On Thu, 13 Dec 2018, 07:20 Stuart McLachlan <stuart at lexacorp.com.pg
> wrote:
> 
> > Oh yes, also:
> >
> > 1. how many potential codes
> > 2. Can you have location/code overlaps i.e. 10, 40 POTHOLE and 
> > 20,60 POTHOLE
> >
> > On 13 Dec 2018 at 7:09, Paul Hartland via AccessD wrote:
> >
> > > To all,
> > >
> > > Hopefully I will start seeing some messages come through soon, I
> > > have a problem where I just know the answer should be pretty
> > > simple but seem to have brain freeze.  I have the following table
> > > (sorry for any wrapping etc)
> > >
> > > SECTION START_POINT END_POINT DEFECT_CODE
> > > SecA         0.00                 100.00 RUT
> > > SecA         50.00         140.00 FAT
> > > SecA         10.00          20.00 POTHOLE
> > > SecA         60.00          70.00 POTHOLE
> > >
> > > and I need to produce something like below
> > >
> > > SECTION START_POINT END_POINT DEFECT_CODES_XML
> > > SecA         0.00                  10.00
> > > <DefectCodes>RUT</DefectCodes> SecA         10.00          20.00
> > > <DefectCodes>RUT,POTHOLE</DefectCodes> SecA         20.00 50.00
> > > <DefectCodes>RUT</DefectCodes> SecA         50.00          60.00
> > > <DefectCodes>RUT,FAT</DefectCodes> SecA         60.00         
> > > 70.00 <DefectCodes>RUT,POTHOLE,FAT</DefectCodes> SecA        
> > > 70.00 100.00 <DefectCodes>RUT,FAT</DefectCodes> SecA        100.00
> > > 140.00 <DefectCodes>FAT</DefectCodes>
> > >
> > > As you can see the start and end points can cross over etc, I
> > > think the main problem I am having is creating the start & end
> > > points from the initial four rows, any help greatly appreciated
> > > before I succumb to starting the Christmas spirit (whisky) earlier
> > > than I have anticipated.......
> > >
> > > Many many many thanks and Christmas wishes to everyone for the
> > > help.
> > >
> > > --
> > > Paul Hartland
> > > paul.hartland at googlemail.com
> > > --
> > > AccessD mailing list
> > > AccessD at databaseadvisors.com
> > > http://databaseadvisors.com/mailman/listinfo/accessd
> > > Website: http://www.databaseadvisors.com
> > >
> >
> >
> > --
> > AccessD mailing list
> > AccessD at databaseadvisors.com
> > http://databaseadvisors.com/mailman/listinfo/accessd
> > Website: http://www.databaseadvisors.com
> >
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>