Turn one to many relationship into single records 2

[Moss100]

Hello, I have a property database.

In tbl_Property I have the unique property reference [PROP_ID] and the address [PROP_ADDRESS]

In a related table, tbl_Room, I have the rooms of the property. The fields here are [ROOM_ID], [PROP_ID_LINK}, [ROOM_NAME], [ROOM_DESC} etc..

I need to export the results in XML or (Rightmove BLM format)

So the first record may look like:

1234, 12 Baker Street, Lounge, Good size, Kitchen, Very Modern, Bedroom 1, Good Size

I can't really see how to begin???

Many thanks - regards Mark

 

[Moss100]

Hello,

Yes I tried:

Concatenate("SELECT ' Room Description: ' & roomdes FROM PropertySalesRooms WHERE Prop_link_id =" & [Prop_Id])

This works fine. But as MajP states, it is two or more fields I needs to concatenate.

This is what I need to acheive:

In my property database, The property address is stored in one table - say called tbl_Property
Each property has many rooms - These are stored in a table called say - tbl_Rooms

I need to end up with an output which shows something like (probably eventually in XML)...

1 Brill Avenue, Kitchen - Good Size, Lounge - Modern, Bedroom 1 - Well decorated, Bedroom 2 - Large size
2 Water Street, Pantry - Great storage, Lounge - L-Shaped, Bedroom 1 - Has Balcony, Bedroom 2 - Built in storage
etc.....

Thanks Mark

 

[MajP]

Here is a hardwired stripped down version of the code.
to use in query select concatenate([Prop_Link_ID]). This is untested.

Function Concatenate(PropID as long) As string
'   Created by Duane Hookom, 2003
'   Modified 6/21/2018  to hardwire    
' to use in query select concatenate([Prop_Link_ID])


    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    dim strSql as string
    Dim strConcat As String 'build return string
    
    strSql = "SELECT roomname, roomdes FROM PropertySalesRooms WHERE Prop_link_id = " & PropID
    Set db = CurrentDb
    Set rs = db.OpenRecordset(strSQL)

   
    do while not RS.eof
      strConcat = strConcat & rs!RoomName & " - " & rs!roomDes & ", "
      rs.MoveNext
    Loop
    if strConcat <> "" then
      strConcat = Left(strConcat, Len(strConcat) - 2)
    end if
    concatenate = strConcat
End Function

 

[Moss100]

MajP & Duane, I am so grateful for the help you have each given me here.

The code that MajP has provided is working

I have modified the code slightly to format the output a little. I think there is just one further tweak that I will need.

Each room will usually have: Name, Measurements and Description. The output required is as below:

Kitchen
measures: 12 x 5
This is a good size room with modern appliances...

The code is doing this, but I would like it to take into account if any element is missing - so it would not add in a carriage return etc. So in the above, if there was no measurement it would appear as:

Kitchen
This is a good size room with modern appliances...

If you could help me with this, I think that covers what I'm aiming for.

code so far:

Public Function Concatenate2(PropID As Long) As String
'   Created by Duane Hookom, 2003
'   Modified 6/21/2018  to hardwire
' to use in query select concatenate([Prop_Link_ID])


    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim strSql As String
    Dim strConcat As String 'build return string
    
    strSql = "SELECT roomname, roommeasure, roomdes FROM PropertySalesRooms WHERE Prop_link_id = " & PropID
    Set db = CurrentDb
    Set rs = db.OpenRecordset(strSql)

   
    Do While Not rs.EOF
      strConcat = strConcat & rs!RoomName & Chr(13) & Chr(10) & "measures:"& rs!roommeasure & Chr(13) & Chr(10) & rs!roomDes & Chr(13) & Chr(10) & Chr(13) & Chr(10)
      rs.MoveNext
    Loop
    If strConcat <> "" Then
      strConcat = Left(strConcat, Len(strConcat) - 2)
    End If
    Concatenate2 = strConcat
End Function

 

[MajP]

untested

 Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim strSql As String
    Dim strConcat As String 'build return string
    dim strName as string
    dim strMeasure as string
    dim strDesc as string
    strSql = "SELECT roomname, roommeasure, roomdes FROM PropertySalesRooms WHERE Prop_link_id = " & PropID
    Set db = CurrentDb
    Set rs = db.OpenRecordset(strSql)

   
    Do While Not rs.EOF
      strName = ""
      strMeasure = ""
      strDesc = ""

      strName = nz(rs!roomName,"")
      'I will assume name cannot be empty
      strMeasure = Nz(rs!roommeasure,"")
      if strMeasure <> "" then strMeasure = vbcrlf & "measures: " & strMeasure
      strDesc = nz(rs!roomdes,"")
      If strDesc <> "" then strDesc = vbcrlf & Strdesc
      strConcat = strConcat & strName & strMeasure &  strDesc & vbcrlf & vbcrlf
      rs.MoveNext
    Loop
    If strConcat <> "" Then
      strConcat = Left(strConcat, Len(strConcat) - 2)
    End If
    Concatenate2 = strConcat
End Function

 

[dhookom]

You can also "play" with + vs &.
[pre]
Null & " measures" = " measures"
Null + " measures" = Null
[/pre]

I'm not sure if this is what you need but that's why I said "play".

strConcat = strConcat & rs!RoomName & Chr(13) + Chr(10) & "measures:" & rs!roommeasure + Chr(13) + Chr(10) & rs!roomDes & Chr(13) + Chr(10) & Chr(13) + Chr(10)

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[Moss100]

Thank you kindly for the code and efforts to date.

The code runs and does exactly what I want. I am calling it from within a query.

Unfortunately if I start to scroll through the records it causes Access to crash (a blue circle appears and does not disappear even after waiting 20 mins or so).
I have tried it on several computers. The only way to continue is to call us task manager and end Access.

Is there anything obvious that may cause this?

I'm surprised that what I'm trying to achieve appears difficult for access produce. Is it not fairly common requirement to want to display records in this way?

Thanks for your continued help - Mark

 

[Moss100]

Is the problem perhaps that the results are displayed in a query and Access somehow has a bug displaying on screen?

Would I be better having code to loop though, concatenate and put the results into a new temporary table? If so could someone help with this code?

Thanks Mark

 

[MajP]

This is a pretty resource expensive query. I would export your results into a table. Just turn your query into a make table query. I would store just the concatenated value and the PK.

 

[dhookom]

Do you close the recordset in the function?

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[Moss100]

Hello Duane,

I've tried all manner of things, but maybe it does need the recordset closing: I have been using:

Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim strSql As String
    Dim strConcat As String 'build return string
    dim strName as string
    dim strMeasure as string
    dim strDesc as string
    strSql = "SELECT roomname, roommeasure, roomdes FROM PropertySalesRooms WHERE Prop_link_id = " & PropID
    Set db = CurrentDb
    Set rs = db.OpenRecordset(strSql)

   
    Do While Not rs.EOF
      strName = ""
      strMeasure = ""
      strDesc = ""

      strName = nz(rs!roomName,"")
      'I will assume name cannot be empty
      strMeasure = Nz(rs!roommeasure,"")
      if strMeasure <> "" then strMeasure = vbcrlf & "measures: " & strMeasure
      strDesc = nz(rs!roomdes,"")
      If strDesc <> "" then strDesc = vbcrlf & Strdesc
      strConcat = strConcat & strName & strMeasure &  strDesc & vbcrlf & vbcrlf
      rs.MoveNext
    Loop
    If strConcat <> "" Then
      strConcat = Left(strConcat, Len(strConcat) - 2)
    End If
    Concatenate2 = strConcat
End Function

Do i need to add an rs = nothing ? If so would it be in the last line of the code? Thanks

 

[Moss100]

Hi MajP,

Thank you for your comment:
"This is a pretty resource expensive query. I would export your results into a table. Just turn your query into a make table query. I would store just the concatenated value and the PK."

I have tried the make table approach and this seems to work fine.

I have also read Duanes comment regarding closing the recordset - out of interest, i'll try this tomorrow to see if it helps. It does feel like Access is running out of resources so it will be interesting to see if this has an effect. If not it seems that the 'Make Table' approach will suffice. Many thanks Mark

 

[dhookom]

The original code I wrote includes:

      .Close
    End With
    Set rs = Nothing
[COLOR=#4E9A06]'====== uncomment next line for DAO ========[/color]    
    Set db = Nothing

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[Andrzejek]

All locally declared variables (db, rs, etc.) will be destroyed when Function is done running,
but it is a good idea to Close them and set to Nothing anyway.
That's just good programming practice, IMO.


---- Andy

There is a great need for a sarcasm font.

 

[dhookom]

You could also consider using global db and recordset objects.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[Moss100]

Thank you for all your help with this. Although the above worked in the end I turned to VBA to loop through the table and create a new table with the required data.

Many thanks Mark