2-step Inventory List Generator

[wwojtale]

I'm trying to create an inventory database that has two main types of items,
each with distinctly different sub-items. I want to select the general item
in one table field and and have my selection generate the appropriate list of
sub-items in the next table field.

I have set up 2 different tables with the required lists of sub-items.

I have tried to set up a query, but since I'm comparing two tables that
separately relate to the inventory table, the error message suggested that I
separately create the query and then enter into SLQ - unsuccessful.

I was able to get 1/2 of the "where" equation to function, but not reliably.
In otherwords - if I changed my inital input, the list didin't change with
it.

I have worked on this in various context over several years - this is the
closest I've come. Please help - don't want to do it with multible fields.

 

[rolaaus]

It sounds like what you're wanting/needing is doable (as most things are,
it's just a matter of how). However, I'm not tracking what you are
describing. Can you elaborate with something additional like table designs
or stripped down example data?

 

[wwojtale]

That's what I thought and have been researching all day...still working on it.
Ok..I know this is wrong, but hopefully you can follow my logic. These are
basically two different sub-queries that I need to run one after the other
within the context of a larger query in order to generate one of two lists
for a combo box.

SELECT [Bead Types].[Bead Shape]
FROM [Bead Types]
WHERE ((Inventory.[Product Category])="Bead")
ORDER BY [Bead Types].[Bead Shape];

SELECT [Finding Types].[Finding Shape]
FROM [Finding Types]]
WHERE ((Inventory.[Product Category])="Finding")
ORDER BY[Finding Types].[Finding Shape];

I also need to make sure that I'm pulling the current record value selected
for Inventory.[Product Category] (only two options - as above). The value
may change for each record entered. In doing only 1/2 of the above I did get
some success, but in dataview it seems to pop up a parameter window for the
value of Inventory.[Product Category] (not sure if this is normal).

Thanks.