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!

*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.

Jobs

Returning one row for a one to many query

Returning one row for a one to many query

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





RE: Returning one row for a one to many query

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.

RE: Returning one row for a one to many query

(OP)
Yes my table is now normalised.

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

Isn't that correct? Thanks

RE: Returning one row for a one to many query

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

RE: Returning one row for a one to many query

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.

RE: Returning one row for a one to many query

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

RE: Returning one row for a one to many query

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

RE: Returning one row for a one to many query

But it IS in ONE table???

RE: Returning one row for a one to many query

(OP)
There are two tables. TblProperty and TblRooms.

RE: Returning one row for a one to many query

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

From TblProperty a, tblRooms b

Where a.PropID=b.RoomPropIDLink

RE: Returning one row for a one to many query

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

RE: Returning one row for a one to many query

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.

RE: Returning one row for a one to many query

(OP)
Thanks. Is concatenation the way to go?

Thanks.

RE: Returning one row for a one to many query

(OP)
Thank you, got there with your help.

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!

Resources

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