You need a lot more context and some examples. I can't wrap my head around what you're trying to do.

Example a hotel. It has room numbers and it has furniture like different style of chairs. I would like to be able to enter a room number and have the database tell me what styles of chairs are in that room.
Thanks

Hey Richard, I find this to be an intriguing question that I would like to also learn a solution for. My first intuition was to treat this as a super simple one-to-many database where you have a single form of rooms, and a continuous subform listing the furniture in each room. However, this is not the case. An individual piece of furniture can only exist in one room, not many. So it seems she would like a list of furniture in each room in a way where the furniture can only exist in that one room. It's a one-to-one relationship but you'd want to view it as a list by room. We'd have to figure out how to move each piece of furniture from room to room without duplicating data or losing data. I would be curious to learn how this is done.

Quick thought - Could this work??

Styles meaning that there are numerous chairs of that style. Therefore, each chair for each style must be unique (SN, etc).
1. Make the main table for each individual furniture by SN. Table would include Style, SN, Color, etc.
2. Table2 has Room number and furniture in that room by type (bed, chair, refrigerator) and SN of each piece of furniture.
3.Data source would be "show me everything in table2 WHERE RoomNumber ="room looking for".

When/if you move a piece of furniture you would have to go to the Table2 and change its location.

Cascade "deletes" so when a furniture is removed/ destroyed from inventory it is taken out of all tables.

Kim If you just want to track the types of chairs in each room, a simple one-to-many relationship from rooms to chairs works fine. But if each chair has a serial number and you want to track the exact physical item, then you need a more detailed setup.

You would have a table for item types like Chair, Bed, or Desk. Then you'd have a table for the model or style of each item, such as Recliner or Folding Chair. Each actual item would be a separate record with its own serial number, linked to both its model and the room it is currently in. This way you can track which specific item is in which room, along with details like style, purchase date, and condition. It is still all one-to-many relationships, just layered: rooms have items, items link to models, and models belong to item types. This gives you a flexible structure without redundant data.

This would actually make a good TechHelp video if you'd like. I already cover something very similar to it in my Asset Management Seminar.

Richard & Tom
Thank you. I would like to see how master Rost would do it in a techhelp video.
Kim

I will add to the list.

Sorry like to jump in on this one.  I think that ser num per chair is not the correct way to go at this one .  this is just an input to spark ideas for example 1 type of chair wit a qty of 100.  Something like a stock inventory.  so if stock room AA has 50 type chairs, then 50 remain to be placed in supply room.  So lets step back and look at the table structure and look at Richards Invoice set up where product a for customer A stock remaining is reduced as you issue an item...  Now do this rather than customer do rooms place them in the location specified.  Build the report to meet your need individual rooms or continues ... Place the lego how you like.   Feed back welcomed what do you think?

V/r
Juan

I used Juan's method on items in my database that i did not care about information as serial numbers.  Only how many many I had, it saved back end a lot of space - and not having to type in hundreds of serial numbers and other data.  Just have supporting table like Richard mention above.

Right serial numbers only matter if you care about the individual items. Like when I used to sell hard drives or motherboards I would scan the barcodes on those items because I wouldn't want someone sending back a computer with a different hard drive in it. That unique hard drive with its own serial number was what I tracked in the database. But other things I didn't care about, like keyboards or mice.