Ms-access – SQL query using an adjacent field value as a criterium

ms access

3 tables in a database. Table1 is tabCategory, Table2 is tabSub, Table3 is Parts.
Table3, Parts, is populated with 3 columns, multiple entries, thousands of them. Column1 is the part number, column2 is the Category and column3 is the Type from tabSub.
The user must fill column1 (PartNumber) according to the part number it wants to add in the DB.
He must also enter the Category (PartType) for his part, wich is limited to a list form Table1, tabCategory. He must also enter the SubCategory limited by a list from Table2, tabSub. Table2 is built with some 40 subcategories to which correspond only 10 different categories.
For example: part: DC23, Category:Capacitor, SubCategory:Mylar…if the part was a Transistor the Catgory would be a Semiconductor and the SubCategory could not be Mylar.
The Challenge: How can I make an SQL query that would deliver a list of all the available SubCategory using as a criteria the adjacent field Category chosen by the user?

Something like this (which does not work):
SELECT tabSub.Category, Amplifier.Category
FROM tabSub INNER JOIN Amplifier ON tabSub.Type = Amplifier.PartType
WHERE (((tabSub.Category)=[Amplifier].[Category]));
The goal being to limit the available user choices based on his entry in the Category field for that particular record. This Query result would be used to limit his list of choices.
Thanks for your suggestions. Cheers

Best Answer

You just need to make your tables have the proper Foreign Key Constraints

CREATE TABLE categories
(
  /* The category name is actually its natural primary key */
  category CHARACTER(100) PRIMARY KEY
) ;

CREATE TABLE subcategories
(
  /* All subcategories have a 'parent' category and a 'subcategory' itself */
  /* REFERENCES is one way to define a Foreign Key constraint */
  category CHARACTER(100) NOT NULL REFERENCES categories(category),
  subcategory CHARACTER(100) NOT NULL,

  /* The primary key is actually the 2 columns */
  PRIMARY KEY (category, subcategory)
) ;

CREATE TABLE parts
(
  part_number CHARACTER(10) PRIMARY KEY,
  category CHARACTER(100),
  subcategory CHARACTER(100),
  part CHARACTER VARYING (255),

  /* The pair (category, subcategory) must appear on the 'subcategories' table */
  /* This is actually a second way of defining a Foreign Key Constraint */
  CONSTRAINT 
      FOREIGN KEY              (category, subcategory) 
      REFERENCES subcategories (category, subcategory)
) ;

Some sample data:

INSERT INTO 
  categories (category) 
VALUES 
  ('Capacitor'), 
  ('Resistor') ;

INSERT INTO 
  subcategories (category, subcategory)
VALUES
  ('Capacitor', 'Mylar'),
  ('Capacitor', 'Ceramic'),
  ('Resistor', 'Carbon'),
  ('Resistor', 'Film'),
  ('Resistor', 'Mylar' /* if it ever made sense */) ;


INSERT INTO
  parts
  (part_number, category, subcategory, part)
VALUES
  ('DC23', 'Capacitor', 'Mylar',   'High voltage capacitor'),
  ('DC24', 'Capacitor', 'Ceramic', 'Higher voltage capacitor') ;

You can check it at SQLFiddle

The SQL that gets the list of all possible subcategories, given one category, is straightforward:

SELECT 
     subcategory 
FROM 
     subcategories 
WHERE 
     category = 'Resistor' 
ORDER BY
     subcategory ;
Related Question