Table information going to two subtables

[ryan.fitzpatrick3]

I have a hierarchy of tables that start off with two selections, 1)
ingredients and 2) packaging, then it goes into a sub table, 1)
ingredients would have a) commodities, b) crop items, c) dairy, 2)
packaging would have a) Glass b) Plastic c) Corrugated, etc, then
those selections would break down into another sub table that goes
more specific. In total there are five tables for five layers. All the
ingredient and packaging information is in the same table for the same
layer. I have a main form that has two sub forms, 1 sub form for
ingredients and the other for packaging, but my problem is that I do
not know how to get just the ingredient information in the table to
the ingredient sub form and packaging information into the packaging
sub form. Can this be done? Thanks in advance.

Ryan

 

[George Nicholson]

Create and save a query that includes only the Ingredient info. Use that as
the Recordsource for your subform.

Repeat for Packaging.

 

[Evi]

Let me just check that I've got this right -(i'll make up the field names)

You should have a table for Ingredients
IngredientsID, Ingredients , other stuff to do with ingredients

you should have a table for packaging
PackagingID, Packaging

You should have another table that puts Ingredients and packaging together.
IngPackID
PackagingID
IngredientsID

So what is in your main form?

Evi

 

[ryan.fitzpatrick3]

Evi,

I have 5 tables, 1st being ingredients and packaging, upon clicking
the subtable (table 2) you can choose for ingredients commodities,
crops, dairy, etc and if you choose packaging labels, PET, resin etc,
and it goes down from there (table 3) to table 4 to table 5.

On my main form I have an ingredient sub form and a packaging subform,
both are mirrored in the same way, it has a combobox where you can
select the choices which would be the information in tables 4 and 5.
the combo box runs off a union query between table 4 and 5, and that
query is what the combo box populates with, but it pulls both
packaging and ingredient information. I only would like ingredients in
ingredient sub form and packaging in packaging sub form. Make sense?
What do I need to do? Thanks in advance.

Ryan

 

[Evi]

Argh! I must be really thick, Ryan but I still don't get it.
Your list of Ingredients means nothing to me as does your list of packaging
labels.
I can't even tell if your table structure is logical from your description.
I just don't know enough about your business. But perhaps we can communicate
in some common language

You say you have both packaging and ingredient items in the same table? Do
you mean that they are both in the same field and you have another field
which tells you if the item is an ingredient, a packaging or something else?
if Yes.
If you want 2 combos or subforms, one showing only the packaging and one
only the ingredients, then base each one on a different query which is
filtered to show only one category of items. I've not come across a combo
which comes from a union query before. How does it know which table to feed
its data into? Or is it only used to filter something?

In your description of your tables are you saying that you have a Major
Category, Sub Category, SubSubCategory structure?

What do you mean by 'both are mirrored in the same way'?

Do you mean that you want 1 combo to filter another so that if (for example)
you choose pets, your next combo will offer a list of dogs, cats, birds and
when you choose dogs from there you get poodle, labrador, spaniel in a third
combo?

It might (if I can get out of duh! mode) help to give us your table
structures (like the proposed structure I gave below) indicating the primary
and foreign key fields along with data samples which make sense to the
uninitiated.

Evi

 

[ryan.fitzpatrick3]

Evi,

It probably is my lack of distinct descriptions that is making this
difficult. I'll answer your questions. The first table is a main
table, table 2 is a subtable, table 3 is a subsubtable, table 4 is a
subsubsubtable, and table 5 is a subsubsubsubtable. These 5 tables are
shown on 5 listboxes, 1st listbox is table 1 (main table) then on the
selection of ingredient or packaging listbox 2 populates with the
subtable for ingredients or packaging.

your example is correct about how the tables filter into the subtables

......to filter another so that if (for example) you choose pets, your
next (table) will offer a list of dogs, cats, birds and when you
choose dogs from there you get poodle, Labrador, spaniel in a third
(table)?

table 1

auto# Class
1 Ingredients
2 Packaging

Currently the subtable (table 2) has both ingredients and packaging
items together, it would look like this

autonum Category Class
1 Commodities Ingredients
2 Crops Ingredients
3 Dairy Ingredients
4 Label Packaging
5 Resin Packaging
6 Corrugated Packaging
....
17

table 3 or subsubtable would look like, this table has all category
items together, ultimately both ingredient and packaging.

GroupItems Category
1 Oil Commodities
2 Flour Commodities
3 Fruit Crops
4 Nuts Crops
5 Milk Dairy
6 Resin LDPE Resin
7 Promo Label
....
40

table 4 and 5 would just filter in the same way 2 and 3 does, just a
more specific item. Make sense thus far.

The main form has an ingredientsubform, which has a combo box thats it
(to keep it simple). Table 5 has a lot of records in it since it's the
lowest filter you can go to, so every item in table 4 has multiple
items which go into table 5. table 5 also has like every other table
both ingredients and packaging, if you're not following in terms on
table items:

table 1 has 2 items
table 2 has 17 items
table 3 has 40 items
table 4 has 66 items
table 5 has 112 items

Please review table lists above, does this make sense so far? Let's
just worry about the ingredientsubform for now, I know I mentioned I
had 2 sub forms the other being for packaging. in table 5 there are
112 ingredients and packaging components, how do I get just the
ingredient components into the combo box in the ingredient sub form? I
know how to load the table/query into the combo box, but I don't know
how to separate the data whereas only ingredients goes into that combo
box? Do I put a numbering system in a new column in the tables where
if it's ingredients I put "1" and packaging I put "2" so in a new
query I can just bring in the "1" or ingredients? I know this works,
but the next tricky part is that in table 4 there are some items that
have hit the lowest breakdown they can go, if I only pull table 5 the
ones in table 4 won't get selected. This is why I did a union query
between table 4 and 5. I want it where if the item in 5 is pulled not
to pull the like item in table 4 because essentially that would be a
duplicate, make sense? But I want it to pull the item in table 4 if
it's not in table 5. With this, this doesn't separate ingredients and
packaging, do I make a query like how I mentioned earlier above for
each ingredients and packaging and do a union query off of those
tables like the SQL below? Then I can put this query into the combo
box.

SELECT [itemclass].item
FROM [itemclass]
WHERE id NOT IN (SELECT DISTINCT Group FROM itemsubclass)
UNION
SELECT [itemsubclass].item
FROM [itemsubclass ];

Thanks for helping me, I hope this explains it better.

Ryan

 

[ryan.fitzpatrick3]

Ignore the 1st paragraph about the list box, that is something else in
my database. Sorry that'll confuse you, there is 5 tables thats it and
a combo box sorry.

Evi,

It probably is my lack of distinct descriptions that is making this
difficult. I'll answer your questions. The first table is a main
table, table 2 is a subtable, table 3 is a subsubtable, table 4 is a
subsubsubtable, and table 5 is a subsubsubsubtable. These 5 tables are
shown on 5 listboxes, 1st listbox is table 1 (main table) then on the
selection of ingredient or packaging listbox 2 populates with the
subtable for ingredients or packaging.

your example is correct about how the tables filter into the subtables

.....to filter another so that if (for example) you choose pets, your
next (table) will offer a list of dogs, cats, birds and when you
choose dogs from there you get poodle, Labrador, spaniel in a third
(table)?

table 1

auto# Class
1 Ingredients
2 Packaging

Currently the subtable (table 2) has both ingredients and packaging
items together, it would look like this

autonum Category Class
1 Commodities Ingredients
2 Crops Ingredients
3 Dairy Ingredients
4 Label Packaging
5 Resin Packaging
6 Corrugated Packaging
...
17

table 3 or subsubtable would look like, this table has all category
items together, ultimately both ingredient and packaging.

GroupItems Category
1 Oil Commodities
2 Flour Commodities
3 Fruit Crops
4 Nuts Crops
5 Milk Dairy
6 Resin LDPE Resin
7 Promo Label
...
40

table 4 and 5 would just filter in the same way 2 and 3 does, just a
more specific item. Make sense thus far.

The main form has an ingredientsubform, which has a combo box thats it
(to keep it simple). Table 5 has a lot of records in it since it's the
lowest filter you can go to, so every item in table 4 has multiple
items which go into table 5. table 5 also has like every other table
both ingredients and packaging, if you're not following in terms on
table items:

table 1 has 2 items
table 2 has 17 items
table 3 has 40 items
table 4 has 66 items
table 5 has 112 items

Please review table lists above, does this make sense so far? Let's
just worry about the ingredientsubform for now, I know I mentioned I
had 2 sub forms the other being for packaging. in table 5 there are
112 ingredients and packaging components, how do I get just the
ingredient components into the combo box in the ingredient sub form? I
know how to load the table/query into the combo box, but I don't know
how to separate the data whereas only ingredients goes into that combo
box? Do I put a numbering system in a new column in the tables where
if it's ingredients I put "1" and packaging I put "2" so in a new
query I can just bring in the "1" or ingredients? I know this works,
but the next tricky part is that in table 4 there are some items that
have hit the lowest breakdown they can go, if I only pull table 5 the
ones in table 4 won't get selected. This is why I did a union query
between table 4 and 5. I want it where if the item in 5 is pulled not
to pull the like item in table 4 because essentially that would be a
duplicate, make sense? But I want it to pull the item in table 4 if
it's not in table 5. With this, this doesn't separate ingredients and
packaging, do I make a query like how I mentioned earlier above for
each ingredients and packaging and do a union query off of those
tables like the SQL below? Then I can put this query into the combo
box.

SELECT [itemclass].item
FROM [itemclass]
WHERE id NOT IN (SELECT DISTINCT Group FROM itemsubclass)
UNION
SELECT [itemsubclass].item
FROM [itemsubclass ];

Thanks for helping me, I hope this explains it better.

Ryan

Argh! I must be really thick, Ryan but I still don't get it.
Your list of Ingredients means nothing to me as does your list of packaging
labels.
I can't even tell if your table structure is logical from your description.
I just don't know enough about your business. But perhaps we can communicate
in some common language

You say you have both packaging and ingredient items in the same table? Do
you mean that they are both in the same field and you have another field
which tells you if the item is an ingredient, a packaging or something else?
if Yes.
If you want 2 combos or subforms, one showing only the packaging and one
only the ingredients, then base each one on a different query which is
filtered to show only one category of items. I've not come across a combo
which comes from a union query before. How does it know which table to feed
its data into? Or is it only used to filter something?

In your description of your tables are you saying that you have a Major
Category, Sub Category, SubSubCategory structure?

What do you mean by 'both are mirrored in the same way'?

 

[ryan.fitzpatrick3]

in this paragraph

The main form has an ingredientsubform, which has a combo box (in it)
thats it
(to keep it simple). Table 5 has a lot of records in it since it's the
lowest filter you can go to, so every item in table 4 has multiple
items which go into table 5. table 5 also (IS) like every other table
both ingredients and packaging (items).
table items:

it should be this.

Ignore the 1st paragraph about the list box, that is something else in
my database. Sorry that'll confuse you, there is 5 tables thats it and
a combo box sorry.

Evi,

It probably is my lack of distinct descriptions that is making this
difficult. I'll answer your questions. The first table is a main
table, table 2 is a subtable, table 3 is a subsubtable, table 4 is a
subsubsubtable, and table 5 is a subsubsubsubtable. These 5 tables are
shown on 5 listboxes, 1st listbox is table 1 (main table) then on the
selection of ingredient or packaging listbox 2 populates with the
subtable for ingredients or packaging.

your example is correct about how the tables filter into the subtables

.....to filter another so that if (for example) you choose pets, your
next (table) will offer a list of dogs, cats, birds and when you
choose dogs from there you get poodle, Labrador, spaniel in a third
(table)?

table 1

auto# Class
1 Ingredients
2 Packaging

Currently the subtable (table 2) has both ingredients and packaging
items together, it would look like this

autonum Category Class
1 Commodities Ingredients
2 Crops Ingredients
3 Dairy Ingredients
4 Label Packaging
5 Resin Packaging
6 Corrugated Packaging
...
17

table 3 or subsubtable would look like, this table has all category
items together, ultimately both ingredient and packaging.

GroupItems Category
1 Oil Commodities
2 Flour Commodities
3 Fruit Crops
4 Nuts Crops
5 Milk Dairy
6 Resin LDPE Resin
7 Promo Label
...
40

table 4 and 5 would just filter in the same way 2 and 3 does, just a
more specific item. Make sense thus far.

The main form has an ingredientsubform, which has a combo box thats it
(to keep it simple). Table 5 has a lot of records in it since it's the
lowest filter you can go to, so every item in table 4 has multiple
items which go into table 5. table 5 also has like every other table
both ingredients and packaging, if you're not following in terms on
table items:

table 1 has 2 items
table 2 has 17 items
table 3 has 40 items
table 4 has 66 items
table 5 has 112 items

Please review table lists above, does this make sense so far? Let's
just worry about the ingredientsubform for now, I know I mentioned I
had 2 sub forms the other being for packaging. in table 5 there are
112 ingredients and packaging components, how do I get just the
ingredient components into the combo box in the ingredient sub form? I
know how to load the table/query into the combo box, but I don't know
how to separate the data whereas only ingredients goes into that combo
box? Do I put a numbering system in a new column in the tables where
if it's ingredients I put "1" and packaging I put "2" so in a new
query I can just bring in the "1" or ingredients? I know this works,
but the next tricky part is that in table 4 there are some items that
have hit the lowest breakdown they can go, if I only pull table 5 the
ones in table 4 won't get selected. This is why I did a union query
between table 4 and 5. I want it where if the item in 5 is pulled not
to pull the like item in table 4 because essentially that would be a
duplicate, make sense? But I want it to pull the item in table 4 if
it's not in table 5. With this, this doesn't separate ingredients and
packaging, do I make a query like how I mentioned earlier above for
each ingredients and packaging and do a union query off of those
tables like the SQL below? Then I can put this query into the combo
box.

SELECT [itemclass].item
FROM [itemclass]
WHERE id NOT IN (SELECT DISTINCT Group FROM itemsubclass)
UNION
SELECT [itemsubclass].item
FROM [itemsubclass ];

Thanks for helping me, I hope this explains it better.

 

[Evi]

Well explained Ryan. You're making sense of it. Just check if I've got this
right.
So the structure I would expect to see in your database (if each item only
fits in 1 subsubsubcategory and each subsubsubcategory only fits in one
subsubcategory etc)
is:

Tbl5 Items
ItemID (Autonumber Primary Key)
Item
SSSCatID (Foreign Key field - linked from the Primary Key of Tbl4)

Tbl4 SubSubSubCategory
SSSCatID (PK)
SSSCat
SSCatID (Foreign Key Field - Linked from the Primary Key of Tbl3)

Tbl3 SubSubCat
SSCatID (PK)
SSCat
SCatID (FK link from Tbl2)

Tbl2 subCat
SCatID (PK)
SCat(Commodities, Crops, Dairy, Label)
CatID( FK link from tbl1)

Tbl1Cat
CatID
Cat (ingredients, packaging)

That's the structure I would expect to see if I've understood you correctly.

You may perhaps have a 'dummy' record in each category (perhaps with the
name Unknown) so that if you can't fit an item or sub category into a larger
category immediately it goes into 'Unknown'
If that's all you need, then you can filter your suborm so that they only
show 1 class of Category by putting into a query ItemId Item SSSCatID from
table 5, SSCatID from Tbl4, SCatID from Tbl3, CatID from Tbl4. You can
filter the list by CatID so that it shows only 1 set of category items or
the other

Similarly, if you wanted to show SSSCategories rather than items then you
would start with all the fields from TblSSSCategory and then the Foreign Key
fields from the other tables.

You can even have one subform filter another one, so long as your table
structure is correct.


You say you sometimes have SSSCategories which contain no items and that's
why you have your Union query. One way to 'get around' that would be to have
an entry in TblItems with the same name as the SSSCategory so that the
SSSCategory becomes an Item. You could replace this by other values if you
later have real items to add to that category.

The problem with not doing this is that the Primary Key field of your Union
query is neither SSSCategoryID or ItemID so you can't use its value in a
meaningful way.


Or Do you need to match Items which come (eventually) under the Ingredients
category with items which (eventually) come under the packaging Category? If
yes, then you need a 6th table with

IngredPackID
IngredItemID (Joined from ItemID in Tbl5)
PackItemID (Also joined from ItemID in Tbl5)

I think you may be using Lookups in your tables which is why it was so
difficult to understand what was really happening in your table. You may
need to ditch these for ordinary text boxes so that you can see your
database's real structure and add your combo boxes to forms using the
wizard.



Evi

Evi,

It probably is my lack of distinct descriptions that is making this
difficult. I'll answer your questions. The first table is a main
table, table 2 is a subtable, table 3 is a subsubtable, table 4 is a
subsubsubtable, and table 5 is a subsubsubsubtable. These 5 tables are
shown on 5 listboxes, 1st listbox is table 1 (main table) then on the
selection of ingredient or packaging listbox 2 populates with the
subtable for ingredients or packaging.

your example is correct about how the tables filter into the subtables

.....to filter another so that if (for example) you choose pets, your
next (table) will offer a list of dogs, cats, birds and when you
choose dogs from there you get poodle, Labrador, spaniel in a third
(table)?

table 1

auto# Class
1 Ingredients
2 Packaging

Currently the subtable (table 2) has both ingredients and packaging
items together, it would look like this

autonum Category Class
1 Commodities Ingredients
2 Crops Ingredients
3 Dairy Ingredients
4 Label Packaging
5 Resin Packaging
6 Corrugated Packaging
...
17

table 3 or subsubtable would look like, this table has all category
items together, ultimately both ingredient and packaging.

GroupItems Category
1 Oil Commodities
2 Flour Commodities
3 Fruit Crops
4 Nuts Crops
5 Milk Dairy
6 Resin LDPE Resin
7 Promo Label
...
40

table 4 and 5 would just filter in the same way 2 and 3 does, just a
more specific item. Make sense thus far.

The main form has an ingredientsubform, which has a combo box thats it
(to keep it simple). Table 5 has a lot of records in it since it's the
lowest filter you can go to, so every item in table 4 has multiple
items which go into table 5. table 5 also has like every other table
both ingredients and packaging, if you're not following in terms on
table items:

table 1 has 2 items
table 2 has 17 items
table 3 has 40 items
table 4 has 66 items
table 5 has 112 items

Please review table lists above, does this make sense so far? Let's
just worry about the ingredientsubform for now, I know I mentioned I
had 2 sub forms the other being for packaging. in table 5 there are
112 ingredients and packaging components, how do I get just the
ingredient components into the combo box in the ingredient sub form? I
know how to load the table/query into the combo box, but I don't know
how to separate the data whereas only ingredients goes into that combo
box? Do I put a numbering system in a new column in the tables where
if it's ingredients I put "1" and packaging I put "2" so in a new
query I can just bring in the "1" or ingredients? I know this works,
but the next tricky part is that in table 4 there are some items that
have hit the lowest breakdown they can go, if I only pull table 5 the
ones in table 4 won't get selected. This is why I did a union query
between table 4 and 5. I want it where if the item in 5 is pulled not
to pull the like item in table 4 because essentially that would be a
duplicate, make sense? But I want it to pull the item in table 4 if
it's not in table 5. With this, this doesn't separate ingredients and
packaging, do I make a query like how I mentioned earlier above for
each ingredients and packaging and do a union query off of those
tables like the SQL below? Then I can put this query into the combo
box.

SELECT [itemclass].item
FROM [itemclass]
WHERE id NOT IN (SELECT DISTINCT Group FROM itemsubclass)
UNION
SELECT [itemsubclass].item
FROM [itemsubclass ];

Thanks for helping me, I hope this explains it better.

Ryan

Argh! I must be really thick, Ryan but I still don't get it.
Your list of Ingredients means nothing to me as does your list of packaging
labels.
I can't even tell if your table structure is logical from your description.
I just don't know enough about your business. But perhaps we can communicate
in some common language

You say you have both packaging and ingredient items in the same table? Do
you mean that they are both in the same field and you have another field
which tells you if the item is an ingredient, a packaging or something else?
if Yes.
If you want 2 combos or subforms, one showing only the packaging and one
only the ingredients, then base each one on a different query which is
filtered to show only one category of items. I've not come across a combo
which comes from a union query before. How does it know which table to feed
its data into? Or is it only used to filter something?

In your description of your tables are you saying that you have a Major
Category, Sub Category, SubSubCategory structure?

What do you mean by 'both are mirrored in the same way'?

Do you mean that you want 1 combo to filter another so that if (for example)
you choose pets, your next combo will offer a list of dogs, cats, birds and
when you choose dogs from there you get poodle, labrador, spaniel in a third
combo?

It might (if I can get out of duh! mode) help to give us your table
structures (like the proposed structure I gave below) indicating the primary
and foreign key fields along with data samples which make sense to the
uninitiated.

Evi