Relationships in Access?

Energize

Energize

Energize

Caporegime
Joined
12 Mar 2004
Posts
29,962
Location
England
Ok so I have a test database, where I have a table of computer components, I have component_id as the pk, and for each component, name, type, and price as attributes.

In the systems table, I have system_model as the pk, and then for the attributes, I have ,processor, ram etc.

What I want to be able to do is type in a component id, and have the database automatically fill in the name of the component.

So for something like this I would need a one to many relationship right? One system_id can include many component id's? But when I drag system id to component id, it always says "one to one relationship", how can I chang it to one to one?
 
I think you need to put Component_ID in the Systems table as a foreign key.

Then in the Relationship screen, link Component_ID from the Component table to the Component_ID in the Systems table.

Steve
 
In the systems table, processor, ram, etc all have the same type as the component_id?
Also when you are making the relationship you get the little box that has:
Code: 
   Table/Query           Table/Query
<name of table 1>     <name of table 2>

<name of field 1>     <name of field 2>
Which ever column has the systems table in, say it is the column for table 2, select the field (where it says name of field 2 in my diagram) and select the motherboard, or ram, etc. it should now be one-to-many
 
This is how it is laid out.

components.JPG
 

Ideally I would want it so that I could type a components id in, then have it bring up the name, price and type of it, and lay it out like this. To make it easy for me to design and build a pc for someone to the cost.

Type Name Price



Total Cost

It would also be nice to be able to make lists of systems for future reference like this.

System 1
Type Name Price

Total

System 2
Type Name Price

Total

System 3
Type Name Price

Total

Or would I have to use excel to do that?
 
Last edited:
Steve09 said:
I don't know what you mean so I'm out.
Type the Component_ID where?
Click to expand...

Well simply I want to create a database that will allow me to quickly spec up computers, it would be ideal because I wouldn't need to be connected to the internet and wouldn't have to spend any time working out prices because it would already be done by the program. I'm just trying to work out the best way to do it.
 
Right I can't really think of a nice way to do this.

If you continue how you have at the moment you could make each of the processor, motherboard, etc. rows in the systems table "Lookup wizards" (select this as the type of the field) which will then kind of do what you want, for each field it gives a drop down list and you could this contain the component names there and ignore the ids.

What you are doing should really be done using a form though. For it to behave nicely and exactly how you want you are starting to stray into vba so without knowing how competent you are at access I could be wasting my time to explain further :confused:

Back to the problem using tables as you are doing, for this to work best you would have to split processors into their own table, e.g. processors, motherboard into their own table, and so on.
Otherwise what is to stop you entering a motherboard in the graphics card column? You could address this by changing the SQL so that it select only ids that start with GFX for the graphics column but its not ideal.

Also price calculation is a fairly untidy looking bit of SQL, for each field in the systems table you would have to lookup the price for the id entered in the field then add them altogether.


I think you are best doing this in Excel,
have spreadsheets with your processor data on, one with motherboard data on etc.
Then have a system spreadsheet (this is all in the same .xls file by the way) which has something like:
GRAPHICS | Enter the code here | have a HLOOKUP/VLOOKUP to find the name of the component for which you entered the name | get price also using HLOOKUP/VLOOKUP

(| = a cell boundary)

Then at the bottom have a sum of the price column.
(There is actually no reason why you should split up the components into their own sheets other than to maintain your own sanity if there are lots of them!)

This does mean you have quite a rigid structure, what happens if someone wants UV lights, where do they go? But this problem exists with your database.

So neither are perfect but I think a spreadsheet would probably be a simpler solution :)
 
Energize said:
Ok so I have a test database, where I have a table of computer components, I have component_id as the pk, and for each component, name, type, and price as attributes.

In the systems table, I have system_model as the pk, and then for the attributes, I have ,processor, ram etc.

What I want to be able to do is type in a component id, and have the database automatically fill in the name of the component.

So for something like this I would need a one to many relationship right? One system_id can include many component id's? But when I drag system id to component id, it always says "one to one relationship", how can I chang it to one to one?
Click to expand...

Hi Energize, regarding your first post, is the following statement correct?

Each component can appear in many systems and each system consists of many components.

If the above statement is true then you have a many-to-many relationship and need another table in between the Component table and the System table for it to work correctly, such as SystemComponent for example. In this table you'd have systemModel and componentID as a composite primary key.

The above would solve the problem you mentioned in your first post, but I think you may have other problems to deal with in the not-too-distant future resulting from the actual design of the database itself.

If you just want to be able to spec a system then you would be better off doing it with a spreadsheet as posted/described above. If you want to store records of systems you've designed then you want a database to do that. You could have a database to store records and a separate spreadsheet for designing systems, which might be easier.

If you feel brave though, you could do it all in a single database ;)

I hope that makes sense :)
 
I remember what I really wanted to do now. I wanted to create a supertype called components and then have the different types of components eg cpu, as subtypes. Is there anyway to do this in access?

shoot1st I think you may be right, now I look at it I think it is a many to many relationship, doh!
 
Last edited:
The super type would be completely redundant I think? The reasons that it is exists in OOP are not applicable here.
You would just have a table for each kind of component. Without a table for each field in the systems table you aren't going to get a way from a many to many relationship. Unless you have something like a SystemComponent table which is going to mean you can still have a motherboard in a graphics card field etc.

Go with the spreadsheet :)
 
OOP? I was taught to use Supertypes in a database to reduce clutter in my oracle 10g database design qualification just a few months ago.
 
I think I'm going to try the database modelling program I have, it says it can automatically create a database that you model, so If I select access 2003, it should work.
 
Ok didn't know that sub types and super types were actual terms when it came to relationships :o
Clearly I better go to the database course next term :p
 
You must log in or register to reply here.
Back
Top Bottom