One to many query problem

[collyrium]

Okay I am pulling my hair out because I can't wrap my head around this one, and I swear it's got to be so easy. Please help!

Here's the situation: 4 tables, each a successive 1-to-many relationship. like so:

table 1:
->Building
fields:
->BuildingID - AutoNumber
->BuildingName

table2:
->Floor
fields:
->FloorID - AutoNumber
->FloorName

table3:
->Room
fields:
->RoomID - AutoNumber
->RoomName

table4:
->Desk
fields:
->DeskID - AutoNumber
->DeskName

(A building has many floors, a floor has many rooms, a room has many desks)

Okay, here's my problem and the situation:
When adding a new Building to the database, the only information that is known is the names of the desks.
It's not until much later that I'm able to assign the desks to rooms and the rooms to floors.



So how do I get the query (and corresponding input form) to allow me to enter the desks that correspond to the building.


Please, please help? I'm going crazy!

 

[neileg]

Why not add a building ID to your desk table and a newbuilding flag to your building table. To manage this you can periodically run an update query that tests for the newbuilding flag. If the flag is true, leave the building ID in the desk table as it is. If it is false, derive the building ID from your relationships and update the entry in the desk table.

OR

Use a temporary table to hold desks in a new building and append these to the main desk table when you have the info do build all the links

There will be other ways, perhaps more elegant than these. Depends what your needs are, really.

 

[collyrium]

I still need help

Thanks for your reply Neil, but I'm still not able to make it work.

What I would like to happen (ideally) is that once I enter a new building a new floor (named something default like the name of the building) would automatically be generated, which would automatically generate a new room (again, named something default), which would then generate the first space for me to input a desk.

I would then be able to (I think) enter all of my rooms easily (maintaining relationship integrity). As the other specific data on the floors and rooms became available, I'd just be able to change their names.

How to I make all of those tables cascade new default entries?

Anybody?

 

[neileg]

With a form. You can have a form to create a new building that will also create default values as you wish. Say you enter a new building called New Tower. You can build code in the controls in a form to create a floor called New Tower Floor and New Tower Room.

Base the form on a query that brings in the fields from your tables. In the after update event of your building name, create some code that takes the building name and concatenates " Floor" and populates your floor field, and " Room" and populates your room field.