Understanding SQL Querys

[jacobbiljo]

I am working on a parts database where selecting a main category such as Mechanical or Electrical from one combo box that looks at its own list of choices decides which items will be selectable in the next box such as fasteners and castings for mechanical or connectors and relays when electrical was selected. The second database is set up in columns with each column being a different initial category.
How would i state the SQL query that would be used in the row source of the second combo box?
The brute force way would be to use individual statements saying
SELECT column1 FROM table2 WHERE MainCategory.id = 1;
SELECT column21 FROM table2 WHERE MainCategory.id = 2;
...

I know my syntax is probably off but is there a better way so that the SQL statement doesnt turn into 9+ lines?



Thanks

[RuralGuy]

Are you talking about Cascading ComboBoxes?

[jacobbiljo]

yes i am and thanks thats a great link.
Sorry im really new at this.
The problem is that system works great to look up data already entered but i am trying to look up seperate tables with this information in it that will then be applied to information as it is added to the database table listing all the parts

[RuralGuy]

Care to state that another way. It is confusing to me as stated. I'm not sure what you are trying to accomplish.

[jacobbiljo]

okay sorry about that i'll lay my project out step by step.
I am creating a parts database. behind the database as it is set up now are 3 tables:
-the first contains a long list for each part as it is added in with different columns for each bit of informatiopn about that part.
-the second is a simple list of the 9 main categories such as fasteners, castings, electrical components. so this is simply a 2x9 matrix one colomn being the ID number and the second the category. Once the database is set up, this info wont change
-the third table is a list of all the sub categorys to those 9 main catgories. This has 9 columns each representing a main category from the previous table. In the case of electrical parts this column will have the sub categories such as connectors and terminal strips. As more parts are added this page will get more and more filled in

I am trying to direct a second combo box to display a different column of information depending on which main category is selected in the first combo box.

[RuralGuy]

It sounds to me as if the third table is not normalized. I'm not certain just yet but it looks like the 3rd table should just have two fields: SubCategory and MainCategory.

[jacobbiljo]

Ya your probably right. That would make it just like the example provided. I have been trying to use this other more convoluted way so that i could use the excel sheet provided to me of the categories we will be starting out with and so that as changes are made i could simply insert a new table 3.
Making the database this way will require me manually entering in all the categories because i have yet to be impressed with access's table modification abilitys such as deleting groups of columns or cutting and pasting groups of cells.

Thanks for the help

[jacobbiljo]

The simple list approach works well but one problem doesnt make is work very well. when sub categories are added such as terminal blocks under the electrical main category they need to be saved with a number as well. This number will be used to generate the part number. Having a seperate table for the sub categories works because it relates these categories to that number which might not be chronological. So now i am back to the same question.
How can i select a column from a table to be displayed in a combo box based on a selection in another combo box looking at a different table?

[RuralGuy]

Ya your probably right. That would make it just like the example provided. I have been trying to use this other more convoluted way so that i could use the excel sheet provided to me of the categories we will be starting out with and so that as changes are made i could simply insert a new table 3.
Making the database this way will require me manually entering in all the categories because i have yet to be impressed with access's table modification abilitys such as deleting groups of columns or cutting and pasting groups of cells.

Thanks for the help

I doubt you will ever be impressed with those abilities of Access since they are functions of a SpreadSheet and not an RDBMS such as Access. I believe normalizing your ever changing spreadsheet when you get it would probably permanently solve your issue but I could be wrong.