PDA

View Full Version : Relational Databases - Need assistance



01-03-2002, 02:48 PM
Hi there,

I'm using M$ Acess..,

Here's what I'm trying to do:

My Webpage calls Data from a database to list the names of components. The Database at present holds a complete list of all the components in all of the computers in one HUGE table... This is useless for a number of reasons. Firstly I have to enter the cost of each component on many places, and secondly it doesn't allow me to make much change to the layout of systems.

So, what I want is to have a table for each type of component (eg CPU, Memory, etc) and one which contains the component layout of all the PCs...

I have set this up, but I cannot understand the way in which the relationships work. I've read heaps of books on it but none explain things in a way I can understand.

I want to update the 'CPUs' table and have it automatically update every row in the 'Computers' table which has 'CPU' in it's 'Component' Column...

How do I do this?

I know how to crank open the relationships window, and I have a box for every table, but when I drag things around as per the instructions of the help file I can't figure out what it is ACTUALLY DOING...

Thanks in advance...

:)

Erin

01-03-2002, 03:41 PM
Erin. First off, I don't think this is the right place to go looking for advice about relationships.

Second, I'm not really sure about how Access works, haven't gone near it since version 2.0.

However, I do know a bit about database theory. You are looking for a query that looks up results from the different tables, aren't you? A PC being a list of component types, each being a query item, creating a cost.

Not sure that the CPUs need to be in one table with hard disks in another, but won't go there.

But you want to get a total cost where CPU=X and RAM=Y and DISK=Z and Monitor=A and graphics card=B etc. Better being almost a table of separate lookups, if you wanted to list each result.

It would be easy to do in Excel with named ranges for each table and the query using VLOOKUP commands.

But in Access? It's a form with multiple queries, with a sum at the bottom. Isn't it?

robo.

01-03-2002, 04:01 PM
Hi there,

I know about as much about the complex side of Excel as you do about the said side of Access... I'm trying to avoid hard coding anything into my website to speed up the process of updating names of components and their costs. I have one page at the moment that uses calls the word 'CPU', followed by 'AMD Athlon XP 1700+' with a colon printed in between. This loops until the 'ID=19' (which is the last component in the system). Trouble is, if I want to update the cost of the 'AMD Athlon XP1700+' I have to do it once for every system that uses it.

I could just have a table of every component I use, and hard code into the page the list of components, and have it call the price according to the database... But this is hard-coding again, and I want to avoid it if possible...

Can you explain what you mean about the VLOOKUP command?

Thanks

:)

Erin

01-03-2002, 05:02 PM
It gets a bit technical. I think you might to have to ---SHOCK HORROR --- read a book.

Sorry Erin, Data basing isn't easy even when you do know how. And I've forgotten how I manipulated DB2 files using Pascal.

COBOL didn't help either ...

01-03-2002, 05:19 PM
Hi Erin,

To get your ASP to go you really do need to understand your databases!

A good set of books are: 'essentials Access 2000' by Robert Ferrett, Sally Preston and John Preston.

I have the intermediate book and it's really handy to dip into to find a working example.

Anyhow.... your tables. Rule 1 - relational databases are designed to eliminate redundant data (ie you set up tables so you don't repeat things).

Start with a 'Components' table and a 'Type' table.

In the Components table create fields for everything THAT IS NOT REPEATED somewhere else.

eg.
ComponentID (Autonumber)

Part Number (Text)

Product Description (Text - unless it can vary in length to over 50 characters)

Price (Currency)

In the Type table create the following fields:

TypeID (Autoumber)

TypeName (Text)

Now go back to your Components Table and add a field called 'Type' in the field type box use 'Lookup'

Save these and enter some test data in the Type table - like CPU, motherboard etc, then enter some test data in the Components table using the drop down box for TypeID.

Have fun,
Hether

03-03-2002, 11:05 AM
Erin,
the trick with relational data-bases is to have separate tables for each type of data, but I don't think that you need a sepsrate table for each component type for hat you are trying to achieve. In your case, you probably only need a 'component' table listing all of the components individually, with their price, and a 'System' table, listing all the computer systems. This would make updating relatively easy. You then need to make a 'Components in System' table, where you have two columns - System ID and Component ID (from their respective tables. You must then make an entry for each component of each system. This is initially time consuming, but easy to update for each new system. I presume you want your customer to be able to select a system and view its components so you then need to create a query that extracts the component data for each system as required (or you could just filter the table). I have not done this with a web page, but it is certainly a basic skill with a pc based Access database. If you want a hand, mail me at ray.anderson@xtra.co.nz.

03-03-2002, 11:58 AM
Hi,

I have now made a query in access which gets one value for each type of component, and prints them in a query box thingy... Following Heather's instructions, I'm now trying to stick that SQL into my web page... I've put the SQL into the line:

Set OnParts = Connect.Execute('SQL in here')

And then, when I would usually call it with something like:

<%=Onparts('CPU')%>

That's not going to work, so what code do I replace this line with?

Thanks,

Erin