Is this the right way to go about this problem? (1 Viewer)

[IanWright]

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

 

[Pat Hartman]

Do not under any condition combine attributes in a single field. This will be a nightmare to work with. Each attribute deserves its own column no matter how many columns it takes. You should create combos from which attributes are selected to ensure consistant data entry. So for example, condition might have these values - New, Fair, Damaged. Color might have these values - Gray, Blue, Brown, Red (add more as necessary). Do not put these lookups in the table itself, put them in the form.

You need several tables:
tblPerson
PersonID
FirstName
LastName
Phone, etc.

tblBuilding
BuildingID
address, etc.

tblRoom
RoomID
BuildingID (foreign key to tblBuilding)
RoomNum
RoomDescription, etc.

tblPersonRoom - this table allows rooms to be shared.
RoomID
PersonID

tblFurnishings
FurnishingID
ItemName

tblInventory
RoomID
FurnishingID
ConditionID
ColorID
etc.

 

[IanWright]

Not sure if I was maybe a bit vague, when you say columns I think you might have misunderstood. I don't mean combining attributes as such, more referring to a common item by an ID#, so I have lots of ItemID links to say a green damaged chair, but I only have a single data entry row within a table saying what this ID represents. I've attached a screenshot of what I was working with, hopefully it'll illustrate what I mean a bit clearer.

I don't need the first 3 tables you mentioned as its limited to a single building department making things easier. Looking at the others though, I think it looks vaguley how I'm doing it the long winded way, except that you're using ID's as opposed to the text I'm type in..

Image1 is what I was trying to do, Image2 shows what I'm currently doing the easier way with lots of repition, which I think is similar to what your suggesting? But as you can see I don't use ID fields, I use textual data..

I'm trying to normalise the database as you mentioned, I think Image1 is normalised, but a little messed up in some way.. thanks for both the replies so far

 

[Zurvy]

IanWright,

Your Idea is possible, as I am busy more or less the same thing.

You can put the IDs in the table, based on what they select. You will have to write VB code for it.

If the attributes of the products are unique for each ID (which they are I assume), you can use "DlookUP" function. But be carefull with the syntax.

In VB, use Dlookup to find the ID based on the value they selec from the combobox etc, store it in a variable, and with a button (assuming you want to check the form) append it to table (action SQL)

Is that what you want?
Zurvy

 

[IanWright]

That sounds like the kind of thing yes, have no idea how to do any of that though, hopefully I should be able to find some documentation online. The problem however, is what if someone tries to edit a field? It'll still modify the table if a field is changed unless that Dlookup function can be done and then reset the field values to what they were before...

 

[Zurvy]

Hey again,

It is quite simple to solve.

You will need to make different buttons;

1. For new entry ==> For this button, you can use Dlookup function. The dlookup function crtieria will be based on the Values of the product attributes selected by the user. You can either use different controls (combobxes) or One. If you use one, you can use a query to conjucated all the values together. But you will have to conjugate the values in the same value when using Dlookup function. Try to understand Dlookupfunction first. It is tricky, so make sure you understand it. I don't have an example for you here, because the project I'm working at is at my work. If you stil have problem post a reply, I will see if I can help you tomorrow (here in Holland it is 10 PM)


==> When the dlookup is done, use a simple SQL statement. It will have be an INSTERT into statement. Select statement don;t work. At leat, with DoCmd.RunSQL ("Your sqlStatement")command. In order to do that, just creat an APPEND query, and copy the SQL statement in a variable and use the DoCmd function.


2. For changing an entry. When they which to change, you still use Dlookup. Now instead of usin append, use Update query (at leas I think, havn;t tried this before)


3. etc.

Is this clear?

 

[Pat Hartman]

green damaged chair

- that is THREE attributes - color, condition, type. You are making a serious mistake by combining them in a single field.

 

[IanWright]

Nono, sorry.. They're not combined, they are attributes in seperate fields.. see the screenshot http://www.access-programmers.co.uk/forums/attachment.php?attachmentid=10491

does that make more sense? And does that clarify what my problem is?

 

[ColinEssex]

If you use Pat's suggestion (and I suggest you do) and use ComboBoxes (based on LookUp tables) for your selections, you can store the ID in the table. Just set the ComboBox first (ID) column to 0 (zero) length, the description will be displayed on the form and the ID stored in the table.

This will be crucial if you need to change the description of an item, you don't need to update the whole table as the ID remains the same no matter what the description, so you'll always get the latest description in the reports.

As an extreme example, say someone insisted that you not use "Blue" but it must be "Sky blue with white flecks" what would you do? . . . . an update query - no need, just change the ComboBox lookup table description.

Col

 

[IanWright]

I don't think you guys understand my problem. I have lookup tables already like that with lists of avaliable colours etc. None of that stuff is the problem, the problem relates to the link between ItemID (yes this refers to a whole selection of attributes type, condition, colour, age, number of drawers etc) and displaying this information on a form.

However because the information on the form, is actually the information from the furniture table which when changed alters the furniture table, instead of altering the ID number which would correspond to the new set of attributes defined in the furniture table..

 

[NoFrills]

So by changing any field on the screen which is currently displaying a record from the Furniture table, you want a new record to be written with a new ItemID while not changing the old record?

Is this correct ?

 

[IanWright]

I want a new ItemID to be wrote, or replaced within the Audit table depending on if its an old entry edited, without the contents of the furniture table being modified. But the data is entered via typing in details similar to that which the furniture table would contain and not via typing in an ItemID number.

Just a reminder of the tables, they are:

RoomID, ItemID in the Audit table and
ItemID, various furniture attributes in the Furniture table.