Access2000 - Where to Start - Newbie - Small issue with starting Database.

  • Thread starter Thread starter Johnny Dee
  • Start date Start date
J

Johnny Dee

Hi,
I am a fairly new user of Access and have a program I want to set up but
can't seem to get things started correctly.

I want to set up a database which calculates the Actual Running costs of a
Machine depending on what Make and Model are selected.

I have tables such as:
Brands, which includes the known Manufacturers of machines.
Models, which include the known Models of machines.
Parts, which contains ALL known parts and their details within.
Suppliers, which contains known suppliers from which Parts and Machines are
contained.

The thing I can't seem to get a grasp around is the following issues:

Some PARTS are used across Varied Machine Models and Also Sourced from
Multiple Suppliers.

I can get Individual Parts to be linked to Individual machines and
Suppliers, but is there a better way to start off the Database ?

Any advise or assistance as usual is appreciated.

Regards

John.
 
Hi,

You need to follow the rules of normalisation, resolving any many to many
relationships.
Then create extra foriegn keys were necessary.

Perhaps something like this.
Brands

BIDX

MIDX



Models

MIDX

PIDX



Parts

PIDX

SIDX



Supplies

SIDX


Suppliers, which contains known suppliers from which Parts and Machines are
contained.
Click to expand...

Maybe add PIDX to Brands ??



Then all you need to do is a join on the relevant tables.


--
Jules.
-------- MDBSecure 2004 - 30 Day Trial -------
Makes MS Access Databases more secure, based on SECFaq.
Imports existing tables, produces jet connection string.
http://www.mindwarp-consultancy-software.com/mdbsecure-free-trial-download.html
Or http://tinyurl.com/yupl5
 
Hi Jules, thanks for that, BUT I can't see how I can get this to work? I'm
beginning to think it's not possible?
I have three tables containing:
Brands:
BrandID, BrandName, ModelID

Models:
ModelID, BrandID

Parts:
PartNumber, Description, PriceRank, PartLife, BrandID, ModelID

I can't understand how to allow more than one PartNumber to be allocated or
assigned to more than the one ModelID. There only seems to be one collum
entry for ModelID in the Parts table ??

Any further assistance appreciated. Or could I send the Database to someone
to see what I am doing wrong ?

Kind Regards

John.
 
Hi Jules, thanks for that, BUT I can't see how I can get this to work? I'm
beginning to think it's not possible?
I have three tables containing:
Brands:
BrandID, BrandName, ModelID

Models:
ModelID, BrandID

Parts:
PartNumber, Description, PriceRank, PartLife, BrandID, ModelID

I can't understand how to allow more than one PartNumber to be allocated or
assigned to more than the one ModelID. There only seems to be one collum
entry for ModelID in the Parts table ??

Any further assistance appreciated. Or could I send the Database to someone
to see what I am doing wrong ?
Click to expand...

You need *ANOTHER TABLE* to model the many to many relationship. The
Parts table *should not have* either a BrandID or a ModelID field
(since as you correctly point out, this explicitly limits a Part to
being a single model).

Consider a table

ModelsUsed
ModelsUsedID Autonumber
PartNumber << link to Parts
ModelID << link to Models
BrandID << link to Models also

If you can have six different models all with the same partnumber,
you'ld have six records in this table.

Note that the Brands table SHOULD NOT contain the ModelID - otherwise
you're again limiting yourself to one and only one model for each
brand. The Models table is linked one-to-many to the Brands table;
you may want to add a ModelName or Description field to the Models
table so that you can have a human-meaningful identifier.
 
Back
Top