Table Structure help

[speedracer1971]

I need some help trying to setup tables on a new database. I want to setup a house analysis database where I can evaluate each room for it’s condition broken down by feature. I can’t seem to figure out a good way to tie all the rooms and evaluations back to that house’s address (primary key). I want the fields to work as shown below with the room evaluations tracked to each room and house address. If possible I would like to be able to enter the as many room names as needed for each address.

House Address

Bedroom 1______________Bedroom 2_______________Bathroom 1

Ceiling condition –Good____Ceiling condition-Fair____Ceiling condition- Good
Floor condition – Fair______Floor condition – Fair____Floor condition - Fair
Wall condition – Good_____ Wall condition – Bad____Wall condition - Good

Can someone please explain to me how I should structure the tables to work. As a novice Access programmer I am out of ideas. Please help me!!

 

[Robert88]

I suppose the House details in one table and the room details connected with an id to both, such as below;

[House Details]

fldHouseid
fldHouseNumber
fldAddress
fldSuburb
fldPostCode
.
.
. [more fields can be added for further details relating to the house]

[Room Details with connection via fldHouseid]

fldHouseid
fldRoomid
fldRoomType
fldFloorCondition
fldWallCondition
fldCeilingCondition
.
.
. [more fields can be added depending on what you want to record in each room]

The tables connect via a common fldHouseid, one (house) to many (rooms) relationship. If more details are required in each add appropriately, according to if it applies to House or Room.

Hope it helps

Robert88

 

[neileg]

Robert, you are creating a repeating group with FloorCondition, WallCondition, CeilingCondition.

tblRoomDetails
RoomID pk autonumber
HouseID
RoomTypeID
FeatureID
ConditionID

Then create tables that provide a lookup for FeatureID (ceiling, wall, floor, etc), RoomTypeID (kitchen, bathroom, etc) and for ConditionID (good, fair, poor, etc).

 

[Robert88]

Thanks neileg,

Normailsation, the key word here... A slight weak point but always willing to give it a go as I know there is always someone who will correct me, thank you.

Also if this is not all the info recorded, some more normalisation might be the go. At least it is a start.

Robert88

 

[neileg]

No probs, mate.

 

[Banaticus]

I would create a main table (primary number based on address field(s), nothing else in this table, unless you want to store other house specific information such as name of owner, etc. A Conditions table, with an address field, a room field and a condition field. A "Condition Type" table (no primary numbers/fields, just a list of conditions), a "Room" table (no primary numbers/fields, just a list of conditions).

The Conditions table would have the following fields:
[address, integral relationship, cascade updates/deletes from main table] [room, lookup wizard to Room table] [condition, lookup wizard to Condition Type table]

Thus, everything is not only in fields that can be searched on, etc., it is all in human readable format, nothing is represented by numbers. The lookup wizard will create a drop down list in each of those fields where a value can be chosen. If you're typing data into the fields, just type the first letter or two of the value and the wizard will automatically complete the item for you. Thus, in a room field you can type b [Tab] and the wizard will fill in the field with Bathroom as no other room starts with a "b". You would end with a table that looks like the following:
[123 Anywhere St] [Bathroom] [Fair]
[123 Anywhere St] [Living Room] [Poor]
[549 Bob Circle] [Den] [Fair]

Create a form with the address field from the main table then all the information from the Conditions table listed as a subform excepting the address (so that the address isn't listed a second time), the end result will look like:

ADDRESS
----room, condition
----room, condition
<Previous Address | Next Address>

My goal here was to have everything be in as human readable a form as possible while allowing all freedom for individual houses (perhaps the house has no living room and has 6 bedrooms?).

Edit: forgot to mention, create another [X Type] table and put it into the Conditions table with the lookup wizard.

 

[speedracer1971]

Thanks for all your suggestions. You are all definitely much more skilled than me at Access. I haven't had the time to try the suggestions yet but I think I can make it work with a little time.

Thanks again!!