Help with Database Design

[Mihail]

Hi all !
I know. The thread title sound strange but...

This is the very simplified scenario:

I have 4 tables:
- House (ID_House, Address)
- Rooms (ID_Room, TypeOfRoom) 'Bathroom, Living room ...
- Bulbs (ID_Bulb, Power)
- Lights(ID_Light , ID_House, ID_Room, ID_Bulb)

A house can have any number of different rooms (even zero)
In a room can be any numbers of bulbs (even zero).
The room table is not "close": in time I can add (or delete) more type of rooms (but I can't delete a room IF it has a bulb).


I need a report like a table:

Row headers: ID_House
Column headers: ID_Room
Intersection: ID_Bulb (if a bulb exist; blank if it is not a bulb in that room)

If in a room there are two or more bulbs I need two or more rows with the same ID_House and ID_Room

How you solve that ?
It is possible to define such query ?

What about if the Room table is "fixed" ? I think must be easier to design the report but still I don't know "how to".

Thank you !

[NeoPa]

You could probably use a Cross-Tab query, but not have multiple rows for multiple bulbs. Have a count instead.

Building reports is another matter. I'm not sure you can on a Cross-Tab query.

[Rabbit]

You could use a cross-tab and group by the bulb but not bring it into the select clause. And you could use a max on the bulb.

[Mihail]

Thank you for replies !

So the cross-tab query sound as a solution.
I'll learn about.

Thank you again.
If you have more ideas let me know, please.