Help with Home Inventory Database school project.

[suprdad25]

I was given an assignment to create a MS Access home inventory database that has a minimum of 5 tables.

I have 7 tables (Appliances,Vehicles,Movies,Furniture,Games,Electr onics, and Rooms) The rooms table has a one-to-many relationship with the six other tables.
Each table has a primary key and every table except the room table has a foreign key that links it to the parent table (room)

The problem I am having is that when I try to run a query to find all assets in a particular room I either get groups of records that repeat over and over or I get nothing at all. I am very new to Access so please be kind.

Thank you in advance for any help you can give.

[brynne]

Hi there,

Usually the primary key in a table is used as the foreign key in another linked table. No need to create a separate identifier for that purpose, e.g. if your primary key in the Appliance table was ApplicanceID of type 'autonumber', you would link it to the Room table by creating a field called ApplianceID with type 'number'. This is then the foreign key for Appliance in Room.

If you have specified the primary and foreign keys correctly, it may be that there is something wrong with your query.

B

[suprdad25]

I have the primary key in the parent table as Room_name. ( I know for a larger database this should be set as autonumber or ID) For my database each room name is unique and non-updateable. The foreign keys in the child tables are PARENTTABLE$parenttableprimarykey. For example I have a parent table (ROOM) with a PK (ROOM_NAME) it has a one-to-many relationship with a child table (ELECTRONICS) electronics has its own PK and it has a foreign key (ROOM$room_name). I'm pretty sure I did this correctly, I could be confused on how to query the info I am looking for.

Could someone post an example of what the SQL should look like?
or would someone be willing to look at the database and point me in the right direction?
Thanks again for all of your help.

[MMcCarthy]

I have the primary key in the parent table as Room_name. ( I know for a larger database this should be set as autonumber or ID) For my database each room name is unique and non-updateable. The foreign keys in the child tables are PARENTTABLE$parenttableprimarykey. For example I have a parent table (ROOM) with a PK (ROOM_NAME) it has a one-to-many relationship with a child table (ELECTRONICS) electronics has its own PK and it has a foreign key (ROOM$room_name). I'm pretty sure I did this correctly, I could be confused on how to query the info I am looking for.

Could someone post an example of what the SQL should look like?
or would someone be willing to look at the database and point me in the right direction?
Thanks again for all of your help.

Have a look at this tutorial and see if your table structures satisfy the rules.

Normalisation and Table structures

Mary

[suprdad25]

I believe the database is normalized to 3NF.

One question: Can a table have a one-to-many relationship with 6 other tables.
I have a ROOM table that has a one-to-many relationship with my ELECTRONICS, APPLIANCES, VEHICLES, MOVIES, FURNITURE, and GAMES tables.

I set it up this way because each room has many electronics, each room has many appliances and so on.


Thank you.

[MMcCarthy]

That sounds fine.

What queries are you trying to set up? What information do you want to retrieve from the data?

Mary

[suprdad25]

I want to query what electronics,movies, games, furniture are in the family room.
This would enable me to report to the ins. co. what items were in that room.

[MMcCarthy]

Try this ... (I had to make up the column names)

SELECT RoomName, ElectronicName, ApplicanceName, Vehicle, MovieName, FurnitureDesc, GameName
FROM ((((((Room INNER JOIN Electronics
ON Room.RoomName = Electronics.RoomName)
INNER JOIN Appliances
ON Room.RoomName = Appliances.RoomName)
INNER JOIN Vehicles
ON Room.RoomName = Vehicles.RoomName)
INNER JOIN Movies
ON Room.RoomName = Movies.RoomName)
INNER JOIN Furniture
ON Room.RoomName = Furniture.RoomName)
INNER JOIN Games
 ON Room.RoomName = Games.RoomName)
 

Mary

[NeoPa]

This is a 'Less Obvious' one.
Logically, you want all the 'Assets' in an asset table with potentially different properties associated with each type. That is, of course, if you want them on separate records rather than all together on one line. If the latter, then Mary's solution is about right (You will probably want to change all the INNER JOINs to LEFT JOINs though) for you.
You can show them on separate records with your current structure though, it just means 'bending' things a little. You will need to create a UNION query merging the data from all your Asset tables and then use the output from this as a recordsource in your main query. A LEFT JOIN from [Rooms] should do the trick from there.

[NeoPa]

If you could provide MetaData for your tables I could knock something up for you (The general SQL at least).
Here is an example of how to post table MetaData :
Table Name=tblStudent

Field; Type; IndexInfo
StudentID; Autonumber; PK
Family; String; FK
Name; String
University; String; FK
Mark; Numeric
LastAttendance; Date/Time

[NeoPa]

Sorry, I'm a bit slow today.
I just noticed that this is a school project/assignment and we have special rules for those.
Please see FAQ - Posting Homework or Coursework Questions and Answers
I can see that you have tried this so, as far as I'm concerned, you are in compliance with this. I merely mention this, as I will now not be able to post an answer ready made for you, but I am still happy to lead you in the right direction in a similar way to the way a tutor would.
BTW Please see earlier posts as they are also relevant.

[suprdad25]

My database has a few more tables but all are one-to-many from ROOM. All
have PK; ID. All have FK ROOM$id. All data types are the same.

What I want to do is run a query that would tell me what furniture, appliances, and electronics are in the family room.

I have asked my instructor for assistance and have been told that I should be able to get the query result I am looking for, but, he also was unable to get the result I am looking for. He is "refreshing his memory on SQL" and will get back to me. Below are my furniture, appliances, electronics, and room tables with their PK, FK, and other attributes.

Table Name = FURNITURE
ID; PK
ROOM$id; FK
make
description
style
cover

Table Name = APPLIANCES
ID; PK
ROOM$id; FK
make
model
description

Table Name = ELECTRONICS
ID; PK
ROOM$id; FK
make
model
serial_number
description

Table Name = ROOM
ID; PK
Room

Thank you for the assistance.

[NeoPa]

We seem to have got a little bit out of step here.
There are a number of comments (going back to post #9) that you don't seem to have read or replied to.
Without a response to these points I have no way of knowing which way you would like to continue.
Also, when I requested the MetaData I provided a format to copy which included the data types for each field. The format of yours is different (Unhelpful but not a show-stopper), but more to the point, doesn't provide the type information requested (Again, without which, progress is difficult).
I'm sure this is down to inexperience rather than anything else.