[AccessD] Cross posted as got brain freeze

Paul Hartland paul.hartland at googlemail.com
Thu Dec 13 05:12:25 CST 2018


That looks perfect, thank you so much, I will move it into sql soon

On Thu, 13 Dec 2018, 10:56 Stuart McLachlan <stuart at lexacorp.com.pg wrote:

> 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
> >
>
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>


More information about the AccessD mailing list