Hi there, I'm trying to create a database for a furniture audit of a university department. I just want to explain how I was trying it, and for anyone to tell me a better way of doing it, as I'm really struggling later on in the design to create a user interface for it.
The information I need to store is the Room Number, Occupant of the room and the furniture items within it. The furniture can be one of 10 different items (Chair, Desk, Table, Notice Board, Filing Cabinet etc..) and each furniture item has fields relating to it, some of which are shared (Quantity, Condition, Size, Colour, Number of Drawers).
The only way I can get what I want working at the minute, is to have a unique entry for every single furniture item, which is probably several thousand. There are however only about 70 different possible combinations that can occur, say a "Damaged Blue Chair" and a "New 3 Draw Filing Cabinet" are a couple of examples.
The way I was thinking of implementing it, was to have a table listing each of these combinations and assigning each one an ItemID as shown below:
ItemID Type Size Condition Draws Colour Age Fixed
19 Chair New 0 Black No
20 Chair Fair 0 Black No
21 Chair Damaged 0 Black No
22 Chair New 0 Blue No
23 Chair Fair 0 Blue No
24 Chair Damaged 0 Blue No
25 Chair New 0 Brown No
26 Chair Fair 0 Brown No
27 Chair Damaged 0 Brown No
Then I would have a table containing Room Number and Occupant, and a 3rd table containing Room Number, Quantity and ItemID. This way I thought would save on quite a lot of memory as I'm only repeating 1 field of numbers rather than 7 of varying values. The problem with this method, is later on when I get to the data entry side of things.
I will be using forms for the data entry, 1 main, and a subform containing the furniture. However the subform would need to display the textual information, and not the ItemID, which means a link between it and the table would be required. This however means that users can't edit the values already within the table, or add new items, because these changes occur directly to the table of Items. Instead I would have to query the values typed in to find the related ItemID if a new field is added, or a previous one edited then write this ItemID to the Table linking the rooms and furniture, before refreshing a form. This is what I've been trying to do with no success so I'm wondering if maybe my whole database structure is wrong.
Thanks for any assistance
Ian
The information I need to store is the Room Number, Occupant of the room and the furniture items within it. The furniture can be one of 10 different items (Chair, Desk, Table, Notice Board, Filing Cabinet etc..) and each furniture item has fields relating to it, some of which are shared (Quantity, Condition, Size, Colour, Number of Drawers).
The only way I can get what I want working at the minute, is to have a unique entry for every single furniture item, which is probably several thousand. There are however only about 70 different possible combinations that can occur, say a "Damaged Blue Chair" and a "New 3 Draw Filing Cabinet" are a couple of examples.
The way I was thinking of implementing it, was to have a table listing each of these combinations and assigning each one an ItemID as shown below:
ItemID Type Size Condition Draws Colour Age Fixed
19 Chair New 0 Black No
20 Chair Fair 0 Black No
21 Chair Damaged 0 Black No
22 Chair New 0 Blue No
23 Chair Fair 0 Blue No
24 Chair Damaged 0 Blue No
25 Chair New 0 Brown No
26 Chair Fair 0 Brown No
27 Chair Damaged 0 Brown No
Then I would have a table containing Room Number and Occupant, and a 3rd table containing Room Number, Quantity and ItemID. This way I thought would save on quite a lot of memory as I'm only repeating 1 field of numbers rather than 7 of varying values. The problem with this method, is later on when I get to the data entry side of things.
I will be using forms for the data entry, 1 main, and a subform containing the furniture. However the subform would need to display the textual information, and not the ItemID, which means a link between it and the table would be required. This however means that users can't edit the values already within the table, or add new items, because these changes occur directly to the table of Items. Instead I would have to query the values typed in to find the related ItemID if a new field is added, or a previous one edited then write this ItemID to the Table linking the rooms and furniture, before refreshing a form. This is what I've been trying to do with no success so I'm wondering if maybe my whole database structure is wrong.
Thanks for any assistance
Ian