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


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 */
) ;

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)
) ;

  part_number CHARACTER(10) PRIMARY KEY,
  category CHARACTER(100),
  subcategory CHARACTER(100),

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

Some sample data:

  categories (category) 
  ('Resistor') ;

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

  (part_number, category, subcategory, part)
  ('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:

     category = 'Resistor' 
     subcategory ;
  • As much as I try I cannot get my SQL interpreter to swallow anything else then this: SELECT tabSub.lngSubIndex, tabSub.txtSubType FROM tabSub INNER JOIN Amplifier ON tabSub.lngSubIndex = Amplifier.lngAmpSub WHERE ((tabSub.lngSubIndex)=(Amplifier.lngAmpSub)) AND ((tabSub.lngCategoryFROMtabCat)=(Amplifier.lngAmpCategory)); – Fred Cailloux Jan 28 '17 at 20:56
  • The example you proposed on SQLFiddle gave me some insight as to who to implement the logic but since I am using MS_ACCESS 2007 it doesn't seem to work as you expected. Also, in the last example when you refer to the word 'Resistor' in the WHERE line, we have to keep in mind that this variable must come from inside the Amplifier table column lngAmpCategory which is the column that contain the actual value selected by the user. – Fred Cailloux Jan 28 '17 at 21:10
  • The difficulty I have is to grab that value from that particular column.vs.field instead of plugging one single value into the SQL code, which, by the way, has worked fine when I tested it. Appreciate your help. – Fred Cailloux Jan 28 '17 at 21:10
  • @FredCailloux: you tagged your question with mysql, not with access. And you didn't specify your Amplifier table in your question (you talked about categories, sub[categories] and parts. It is difficult to answer a moving question. – joanolo Jan 28 '17 at 21:25
  • Check How do I ask a good question?. – joanolo Jan 28 '17 at 21:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.