×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Turn one to many relationship into single records
2

Turn one to many relationship into single records

Turn one to many relationship into single records

(OP)
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

RE: Turn one to many relationship into single records

So you start with the data like this?

PROP_ID  PROP_ADDRESS     ROOM_NAME  ROOM_DESC
1234     12 Baker Street  Lounge     Good size
1234     12 Baker Street  Kitchen    Very Modern
1234     12 Baker Street  Bedroom    Good Size
 


---- Andy

There is a great need for a sarcasm font.

RE: Turn one to many relationship into single records

(OP)
I could run a query to get the above, but what I actually have to start is:

A PROPERTY table & a ROOM table linked as one to many

So I guess I have:


CODE -->

PROPERTY TABLE
ID     Address
1234   12 Baker Street 



CODE

ROOMS TABLE
ID LINK    ROOM      DESCRIPTION
1234       Lounge    Good Size
1234       Kitchen   Very Modern
1234       Bedroom   Good Size 



Many thanks Mark

RE: Turn one to many relationship into single records

If using the posted code you probably want to first make another query first to get the room and description into one column

qryRooms
select [ID Link], [Room] & ": " & {description] as RoomDescription FROM Rooms

RE: Turn one to many relationship into single records

(OP)
Hello - thank for the help.

I have nearly got there but.....

I think because my results are over 255 characters long, the text in the output ends in garbage...

ie. "marble hearth and decorative✇ᥠŏ魢杺￿￿泛来嘿杦"

http://allenbrowne.com/bug-16.html

Im not sure how to address this. Any ideas?

Thanks Mark

RE: Turn one to many relationship into single records

Moss100,
Is there are reason you aren't using the code in the FAQs suggested by MajP? Did it not work or did you have an issue implementing?

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

RE: Turn one to many relationship into single records

(OP)
Hello Duane,

I hope you are well.

Thank you for taking an interest in my latest dilemma :)

I have a query based on my property table picking the properties (20 or so) which I require.

Then in the design of the query I have put:

CODE -->

Expr2: Concatenate("Room","PropertySalesRooms","Element_Property_ID_Link = " & [Prop_ID]) 

This seems to produce a result, but also an error and also shows the "garbage" charaters at the end.

With regard to the above...
PropertySalesRooms is a query which shows all room details
Room is the field in the query containing the room details
Element_Property_ID_Link is the link to the property

Im sure it is user error....

Thanks mark

RE: Turn one to many relationship into single records

(OP)
Opps - confusing two functions:

CODE -->

Exp3: Concatenate("SELECT Room FROM PropertySalesRooms WHERE Element_Property_ID_Link =" & [Prop_Id]) 

Works, but yields the same garbage charaters at the end.

RE: Turn one to many relationship into single records

If both functions return garbage at the end then I expect it's a data issue. What is the source of the data? Are you importing it from a different system or is it keyed into an Access table?

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

RE: Turn one to many relationship into single records

(OP)
The data source is just two access tables

I have a first query which simply gets all the room names from a table. I have then as suggested concatenated RoomName, RoomDescription and RoomMeasurement into a single field called Room

The second query selects the Properties from a differenet table and this is the query I integrate the concatenation function in.

Im wondering wether this is the cause: http://allenbrowne.com/bug-16.html

If all what I have done seems OK, I will try a with a fresh database this evening and report back. Thank you Mark

RE: Turn one to many relationship into single records

Hi Mark,
It looks like this could be the issue. Are you suggesting the Room field might contain more than 255 characters?

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

RE: Turn one to many relationship into single records

(OP)
OK, good news - or sort of.

So when I run the Concatenate function directly relating to a single field in the query PropertySalesRooms all is fine.

However when I run the function which relates to a concatenated field in the query PropertySalesRooms I get garbage.

I have set up a new database to test this.

The concatenated field in the query PropertySalesRooms is simply as below, so not sure why it causes problems?

CODE -->

Room: "Description: " & [Element_Description] 

Your continued help much apprecaited

Regards Mark

RE: Turn one to many relationship into single records

(OP)
So after some testing I have discovered the bug/problem.

The concatenation function works fine when a single field is chosen. Even if this field is long text and has way more than 255 characters, the function runs fine.

The function is also happy if it is concatenating a concatenated field from the query SO LONG as the underlying concatenated field is under 255 characters.

If the function is run with a concatenated field, which itself is over 255 characters it outputs garbage.


For example: If we feed the following field into the concatenation function it will work as follows:

Room: "Description: " & [Element_Description] (if under 255 characters the function works)

Room: "Description: " & [Element_Description] (if over 255 characters the function fails)

Room: [Element_Description] (any length seems to work)


I'm not sure if there is any work around. Perhaps there is a different approach I can take?

Many thanks Mark

RE: Turn one to many relationship into single records

I have not tried it, but it sounds like the problem is opening a recordset on a concatenated field and not writing to a field.

So if I was using Duane's code I would modify one line
.Fields(0) & pstrDelim
to
.Fields(0) & " Room Description: " & .Fields(1) & pstrDelim

Then when I use it, my sql string would contain both fields.

RE: Turn one to many relationship into single records

(OP)
Hi,

I have altered the functions code as you suggested and tried:
.Fields(0) & " Room Description: " & pstrDelim

my code in the query is:
Exp3: Concatenate("SELECT roomdes FROM PropertySalesRooms WHERE Prop_link_id =" & [Prop_Id])

This works - a step forward :)


I'm not sure how I add the second field though - so when I modify the function as:
.Fields(0) & " Room Description: " & .Fields(1) & pstrDelim

What do i put in my query: I have tried:
Exp3: Concatenate("SELECT roomname & roomdes FROM PropertySalesRooms WHERE Prop_link_id =" & [Prop_Id])
This errors?

Many thanks Mark

RE: Turn one to many relationship into single records

CODE -->

("SELECT roomname, roomdes FROM PropertySalesRooms WHERE Prop_link_id =" & [Prop_Id]) 
No the trick (which I hope works) was not to concatenate at first. Instead your query contains two fields. The code then concatenates both the room name field and room description field.

.Fields(0) & " Room Description: " & .Fields(1) & pstrDelim

field(0) is the first field and Field(1) is the second field. So Each pass through you should get

Living Room Room Description: Large
Probably a better readable concatenation would be like
.Fields(0) & "(" & .Fields(1) & ")" & pstrDelim
So you get
Living Room (Large), Dining Room (Cozy)

RE: Turn one to many relationship into single records

Did you try:

CODE --> VBA

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

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

RE: Turn one to many relationship into single records

Duane,
Remember the OP wants to concatenate the child records of two fields not one field.

CODE -->

ROOMS TABLE
ID LINK    ROOM      DESCRIPTION
1234       Lounge    Good Size
1234       Kitchen   Very Modern
1234       Bedroom   Good Size 


OP wants
Lounge Good Size, Kitchen Very Modern,....

If you concatenate prior and the fields go over 255 the recordset returns garbage. So the option (and not sure if it will work) is to concatenate the two fields in code. What you post I think only gives
Room Description: Good Size, Room Description: Modern

RE: Turn one to many relationship into single records

thanks MajP.

Mark,
What is the SQL of PropertySalesRooms?

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

RE: Turn one to many relationship into single records

(OP)
Hello,

Yes I tried:

CODE -->

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







RE: Turn one to many relationship into single records

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

CODE -->

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 

RE: Turn one to many relationship into single records

(OP)
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:

CODE -->

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 

RE: Turn one to many relationship into single records

untested

CODE -->

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 

RE: Turn one to many relationship into single records

You can also "play" with + vs &.
  Null & " measures" = " measures"
  Null + " measures" = Null
 

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

CODE --> vba

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

RE: Turn one to many relationship into single records

(OP)
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

RE: Turn one to many relationship into single records

(OP)
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

RE: Turn one to many relationship into single records

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.

RE: Turn one to many relationship into single records

Do you close the recordset in the function?

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

RE: Turn one to many relationship into single records

(OP)
Hello Duane,

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

CODE -->

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

RE: Turn one to many relationship into single records

(OP)

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

RE: Turn one to many relationship into single records

The original code I wrote includes:

CODE --> vba

.Close
    End With
    Set rs = Nothing
'====== uncomment next line for DAO ========    
    Set db = Nothing 

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

RE: Turn one to many relationship into single records

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.

RE: Turn one to many relationship into single records

You could also consider using global db and recordset objects.

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

RE: Turn one to many relationship into single records

(OP)
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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close