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

 

[Andrzejek]

So you start with the data like this?
[pre]
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
[/pre]


---- Andy

There is a great need for a sarcasm font.

 

[Moss100]

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:

PROPERTY TABLE
ID     Address
1234   12 Baker Street

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

Many thanks Mark

 

[MajP]

How to concatenate multiple child records into a single value by dhookom
faq701-4233

 

[MajP]

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

 

[Moss100]

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

 

[dhookom]

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

 

[Moss100]

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:

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

 

[Moss100]

Opps - confusing two functions:

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

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

 

[dhookom]

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

 

[Moss100]

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

 

[dhookom]

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

 

[Moss100]

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?

Room: "Description: " & [Element_Description]

Your continued help much apprecaited

Regards Mark

 

[Moss100]

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

 

[MajP]

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.

 

[Moss100]

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

 

[MajP]

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

 

[dhookom]

Did you try:

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

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

 

[MajP]

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

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

 

[dhookom]

thanks MajP.

Mark,
What is the SQL of PropertySalesRooms?


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