Query problems

C

chris84a

Hi

I am creating a database for a very large delivery of furniture which all
has to be taken to different rooms of a building. Not every room has every
piece of furniture but there are over 50 types of furniture. I am trying to
create a list for each room of the pieces of furniture to be put in. The
problem I have is that the report created lists all 50 or so types of
furniture in each room where as I want just the furniture specific to Room
1(for example) to be listed under Room 1. I think I need write something in
the query telling it to hide that piece of furniture if there is no
corresponding value with it.

Any help appreciated

Chris
 
C

Chaim

Assuming that your database has (at least) three tables, one (Furniture)
holding the furniture information, another (Rooms) holding the room
information, and a third (Room Contents) that says which furniture is in
which room, the query would look something like:

select R.[room designator], F.[furniture descriptor]
from Rooms R inner join (
Furniture F inner join RoomContents RC
on F.[furniture descriptor] = RC.[furniture descriptor])
on R.[room designator] = RC.[room designator]
where R.[room designator] = [Which Room?]
order by R.[room designator], F.[furniture descriptor];

You can play with using a crosstab to get a tabular output with room
designators as the rows, and presence/non-presence of a furniture piece as
the columns.

Good Luck!
 
Top