How to show temporary data in a query

[Rob_R]

First of all, I am not a database expert so maybe I have a question with a simple answer but I dont see it. To simplify the issue Ill give an example otherwise I have to explain electronic equipment which complicates the matter I think.

I have multiple tables, one for locations, one for cabinets, one for shelves and one for books. (all one to many, one location can have more cabinets, cabinets 1 to 4 shelves and the shelves can hold 8 similar sized books)
So far I can get the query's I need and the data I need, the issue is that I need to be able to reserve a place for a book, without adding it to the tables which hold the original information.

It could be I need to reserve a empty slot for a book, or when the book gets replaced I need the reservation and the existing book to be in the query, once the book gets replaced it goes from the reserved table to the tables I have now. To make matters worse, It might be we need to install a new cabinet, with one or more shelves, and one or more books.

I tried copying the tables with "_Reserved" added to the table name without the data and fill in the reservations in those tables and preform a union but could not get that to work.

Before I spend another 20 frustrating hours, I thought, maybe I am doing this all wrong, hence my visit to this site.

Any advice would be appreciated.
Regards,
Rob.

[ranman256]

it seems to me the book would never move. It is always in tBooks table, but the fields: LentTo, & Reserved would be filled with IDs from the tBorrower table

[Micron]

You should have at least 4 tables according to your pseudo system. If not, that could be part of the issue. When a shelf spot is reserved, do you know what book it is for? If yes, then add a status field to the shelf table that id's the book placement as permanent/static/whatever OR as Reserved. Don't make another table for this.

[ArviLaanemets]

As start you need tables, where you register locations, cabinets in those locations, shelves in those cabinets, and positions in those shelves:
tblLocations: LocationID, [LocationCode], [LocationName] (Codes and names are semioptional, i.e. you can use on, or another, or both - depends what you prefer/need. the difference is the code is a short string of certain structure, the name is longer and give a description of location. An example of row values - 1, "A01", "Room 01 in building A" - btw. this assumes you can have up to 99 different locations);
tblCabinets: CabinetID, LocationID, CabinetCode (Cabinet code can be something like e.g. "A01/01", i.e. it is cabinet number 1 in location "A01" - up to 99 cabinets in every location);
tblShelves: ShelfID, CabinetID, ShelfCode (ChelfCode is composed like "A01/01/1" for 1st shelf of cabinet "A01/01" - as the number of shelves can't be more than 4, it is defined with single character);
In case shelves in different cabinets can have different number of available positions for books, you definitely need a table where you define those positions too (like previous tables). In case the number of available positions on shelf is always 8, you can skip it - but in case you later need to get list of unused positions, this table is must anyway);
Then you need a table where all books are registered. And in this table, you must have a field where you can enter (i.e. select from combo in a form) PositionID (in case you created table tblPositions), or fields ShelfID and PositionNumber (in case tblPositions was omitted). Various codes you can use as visible list source in combos when registering cabinets, shelves, or where a book is stored.

You can get this schema more flexible when you leave locations out of position coding (removing LocationID from tblCabinets, and adding a separate table tblCabinetLocations; CabinetLocationID, CabinetID, LocationID). Then whenever there is a need, you can move any cabinet to any location, and register it's new position in tblCabinetLocations.

With such design, a relatively simple query will return e.g. a list of all free positions along with e.g. cabinet/shelf codes and position numbers, and cabinet locations.