Returning one row for a one to many query 1

[Moss100]

Hello I have the following.

A property table. TblProperty.

It has fields

PropID
PropAddress

A related table is TblRooms

It has fields

RoomID
RoomPropIDLink
Room1
Room2
Room3

When I run a query, it shows up the Property Address field each time.

What I want is the property address, along with the rooms all as one record -
Just as if it were all in a single table.

Please could someone tell me how to do this.

Ultimately I want to be able to export each property along with its rooms as an individual record as a text file.

Many thanks mark.

 

[SkipVought]

So you still have not normalized your Rooms table as advised?

Don't really understand your question. Perhaps even an example of data in tables.

 

[Moss100]

Yes my table is now normalised.

One property (in property table) can have many rooms in Room table.

Isn't that correct? Thanks

 

[SkipVought]

Why would one RoomID refer to THREE rooms? That's not normalized!

 

[SkipVought]

so for a SINGLE room (RoomID) you might have as fields...
Name
Shape Rectangle, L, Pie ???
Length
Width
Height
Sq Ft
Nbr Windows
Nbr closets

Hard to do with your schema.

 

[SkipVought]

...And what's so significant about THREE in your mind?

 

[Moss100]

Sorry - it's been a long day - the above was incorrect.

My Room table has fields

RoomId
RoomPropIDLink
RoomName
RoomDescription

Sorry for the confusion.

So I'm trying to get a query showing the property and all the rooms, but as a single record, just as i would get if it was all in one table.

Many thanks.

 

[SkipVought]

But it IS in ONE table???

 

[Moss100]

There are two tables. TblProperty and TblRooms.

 

[SkipVought]

Select a.PropAddress, b.RoomName, b.RoomDescription

From TblProperty a, tblRooms b

Where a.PropID=b.RoomPropIDLink

 

[Moss100]

Thanks but that has just given me an address on each room


I.e

Property address. Room Name
Acorn road. Hall
Acorn road. Kitchen
Acorn road. Lounge.


What I want is.

Acorn road, Hall, Kitchen, Lounge

Thanks Mark.

 

[SkipVought]

Then you need to write VBA code. Don't think you can do that in a query as each property will have a varying number of rooms.

 

[Moss100]

Thanks. Is concatenation the way to go?

Thanks.

 

[PHV]

Have a look here:
faq701-4233

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Moss100]

Thank you, got there with your help.