A specific query question

  • Thread starter Thread starter K. Georgiadis
  • Start date Start date
K

K. Georgiadis

I have a data base of automotive spare parts, each part
being used on several car makes, models, and model years.
In our jargon, a set of parts that is usable on, say,
Buick Regal 2003 is defined as a KIT. Accordingly, each
part would be usable in several kits, and there would be
many kits that could be pulled together.

1. How can I (efficiently) code each spare part so that I
can pull out the parts that constitute a specific kit?

2. If a part is usable on a 1998, 1999, 2000, 2001, etc.;
is there a syntax that allows me to put all these
definitions on a single cell, so that queries can sort
the data? Or am I looking on creating several Model year
columns for each part, with a yes/no checkbox?
 
K. Georgiadis said:
I have a data base of automotive spare parts, each part
being used on several car makes, models, and model years.
In our jargon, a set of parts that is usable on, say,
Buick Regal 2003 is defined as a KIT. Accordingly, each
part would be usable in several kits, and there would be
many kits that could be pulled together.

1. How can I (efficiently) code each spare part so that I
can pull out the parts that constitute a specific kit?

2. If a part is usable on a 1998, 1999, 2000, 2001, etc.;
is there a syntax that allows me to put all these
definitions on a single cell, so that queries can sort
the data? Or am I looking on creating several Model year
columns for each part, with a yes/no checkbox?
Click to expand...

You'll find any arrangement in which you have a separate column for each
year awkward and hard to maintain.

I'm not sure I understand the essential nature of a kit: whether a kit
simply consists of all parts that can be used on the same
make/model/year, or whether the parts in a kit are all related to some
specific function or subassembly. If the former case is true, I might
leave the "kit" out of the table design completely, and have just two
tables to store the information:

Table: Parts
Field: PartID (primary key)
Field: PartName
Field: PartManufacturer
(other fields specific to each part)

Table: PartsApplications
Field: PartID (compound primary key, fk Parts)
Field: MakerID (compound primary key, fk Makers)
Field: ModelID (compound primary key, fk Models)
Field: ModelYear (compound primary key)

Table PartsApplications has one record for each combination of a part
and a specific make/model/year it can be used on. To get all the parts
in a "kit", you just query table PartsApplications for all records for
all records for the given MakerID, ModelID, and ModelYear, joining the
results to table Parts to pick up the part name and any descriptive
information you may need.

On the other hand, if there's more to a kit than just make, model, and
year to which the parts in it can be applied, then you'd want to have a
different table structure that includes a table for Kits. It might look
like this:

Table: Parts
Field: PartID (primary key)
Field: PartName
Field: PartManufacturer
(other fields specific to each part)

Table: Kits
Field: KitID (primary key)
Field: KitName
Field: MakerID (fk Makers)
Field: ModelID (fk Models)
Field: ModelYear
(other fields specific to each kit)

Table: PartsKits
Field: PartID (compound primary key, fk Parts)
Field: KitID (compound primary key, fk Kits)

You'd have a record in Parts for each part, a record in Kits for each
kit, and a record in PartsKits for each valid combination of part and
kit. To query all the parts in a kit, you'd join Kits and PartsKits on
the KitID field, applying criteria to get just the kit you want, and
join the results to Parts to pick up any the part name and any other
descriptive information about the part.
 
Back
Top