Need help organizing table data for a Combo Box

[tschuler]

I need help trying to figure out how to write a query to populate a combo box on a form. Here's the scenario:

I have a list of parts that are kept in supply or available for order. The table holds the list of parts. There are columns in the table for the 'Part Name' and 'Part Number' there are some other columns, but I don't think they are relevant for my question. So we have a 'tblParts':

Part_A 123
Part_B 124
Part_C 125
Part_D 126
Part_E 127
Part_F 128
Part_G 129
Part_H 130
Part_J 131
Part_K 132
Part_L 133
Part_M 134
Part_N 135
Part_P 136
Part_Q 137

My company makes a system that is made up of three racks. Those racks are in a table. So we have 'tblRacks':

Rack_1
Rack_2
Rack_3

These racks are constructed as an assembly of parts from the Parts List above (tblParts). Some of the parts may be used on more than one rack. Some of the parts are not part of the system (they are miscellaneous parts, we'll call them). I want the drop down list in the combo box to appear something like this:

System_1

Rack_1
Part_D
Part_E
Part_K

Rack_2
Part_A
Part_D
Part_G
Part_H
Part_J

Rack_3
Part_C
Part_F
Part_J
Part_K
Part_L

Misc. Widgets
Part_B
Part_M
Part_N

Misc. Other
Part_P
Part_Q

There are multiple quantities of System_1, and there is a table made up of the different serial numbers of System_1. So I have a 'tblSystem' with System Type, Serial Number, and Location:

System_1 #A Site_1
System_1 #B Site_2

Also, I need to take into consideration that there can be more than one system, so if there was a System_2, its list would be similar to System_1 and it would appear in the combo box drop down after the complete list of System_1, but before the Misc. Widgets.

Also, I will be creating some reports that will have the same format for displaying these parts.

Can anyone help me with this? I can make additional columns in the tables if needed. The important idea, however, is that when new parts are added or deleted (because Part_C could be replaced by a new and improved Part_R), it should not have to be added or deleted to several tables.

The actual tables are larger, I have shortened them down for example purposes.

Also, please keep in mind, that this is going to get more complicated, because I need to keep inventory on individual pieces. So I will eventually be expanding the database such that, say, Part_A has a quantity of 7 in our company and each will have an individual serial number. Of those 7 items of Part_A, I need to keep track of which serial number is in System_1#A, System_1#B, etc., and some of them could be in different supply rooms around the country (and I need to know which serial number is in which supply room).

 

[ProgramError]

part number - text
part Id - long integer
Quantity - long integer
location - text (lookup table rack_1,Rack_2,rack_3 ect)
System - integer (lookup table system1A, system1B)
supply rooms - text (lookup table)
Serial number - text

you may need to separate the serial number and tie it into the above table via an ID key


Ian Mayor (UK)
Program Error
Programmers do it one finger at a time!

 

[tschuler]

I don't understand what I am to do with the information in your reply. Could you please add some more detail? How is that supposed to take the data from my tables and make the list I want in the Combo Box look like the list in my original post?

I just started learning Access a few months ago, however, I am a fairly advanced VBA programmer, having done several complex Excel and Word documents with VBA programming.

Thanks.