Multiple Products in "Suite" group

A

Andy

Hi;

The boss wants to "Cluster" products in a group or "Suite" meaning "Blouse,
Dress, Hat" = Woman's Outfit.

Have an Order/Sales dbase.

Main Form = Customer data, (Name, Address, Phone)
sfrmProductsBought = Category, (Auto, Clothing, Furniture), Sub-Category,
(Blouse, Dress, Hat), ProductName, (Blue Shirt, Green Shirt, Red Shirt)

The Category, Sub-Category and ProductName are "Drill Down" Combo Boxes.
For the "Drill Down" followed Microsoft's help: Seperate tbls for each
Combo Box with the tbl Primary Keys Data Type set as text. Then created a
main frm for Category, an sfrm for Sub-Category and an sfrm in
sfrmSub-Category for the individual Products. All of this works correctly.

When the ProductName is selected in the sfrmProductsBought the "After
Update" Event uses Me!s to enter the Description, Price Etc.
Example:
Me![txtProductDescription] = Me![cbxProductNameID].Column(1)
All of this works correctly.

The boss wants to "Cluster" products in a group or "Suite" meaning "Blouse,
Dress, Hat" = Woman's Outfit. Have seen this work in an accounting program.

At first simply added a "Grouped Products" Category to tblCategory that
won't work in this instance.

Then tried this:
Created tbls: GroupedCategory and GroupedProducts. (One To Many
Relationship)

The tblGroupedCategory contains the name of the Group, (Men's Outfit,
Woman's Outfit)
tblGroupedProducts contains the same fields as "sfrmProductsBought"

The sfrmGroupedProducts uses the same Drill Down method and it all works
correctly.

Added a Combo Box to sfrmProductsBought that displays the GroupCategory
Names. Its Row Source Type is "Table/Query" and its Row Source is
tblGroupedCategory.

Tried using a Row Source of tblGroupedCategory and tblGroupedProducts but
that shows the same Grouped Category multiple times, (One to Many).
Set the qry Properties to "Unique Records" and it still shows Grouped
Category multiple times.

Have searched and searched for an example of how to copy the data from the
Grouped Products Tables to the sfrmProductsBought and haven't found anything
that is even close.

Would someone be so kind to point me in the correction direction.

Thank You for taking the time to read this post.

Andy
 
A

Andy

Thank You for Your replies.

Wasn't sure how to reply to both answers and continue the tread for Your
responses so copied/pasted both below.

For Dorian Yes do need to group ANY hat, blouse, dress into a group.

For Klatuu: Not sure what is meant by "Both sides of the join are the same
table".

Explaining further: (Note to both this explanation is detailed not stuffed
with excess info. Asking a question not writting a Novel.)

There are three seperate groups of related tables. All of the tbls are
record sources for forms and sub-forms.

First there is Products. The Products tables consist of:
tblProductCategory
tblProductSubCategory (related to tblProductCategory Many to One) Many
SubCategories In One Category.
tblProductInfo (related to tblProductSubCategory Many to One) Many Products
In One SubCategory.

Category SubCategory ProductInfo (Name,Price,Description,Qty, Etc)
Clothing Men's Hat
Men's Shirt
Men's Shoes
Woman's Blouse
Woman's Dress
Woman's Skirt
The Category is the Main Form, SubCategory is a SubForm in the Main Form and
ProductInfo is a SubForm in the SubCategory sfrm.

Next is the Order Form.
It contains two tables:
tblCustomer
tblProductsBought. (related to tblCustomer Many to One) Many ProductsBought
In One Customer.
The tblProductsBought contain the same fields as the tblSubCategory listed
above.

The tblCustomer is the Main Form the tblProductsBought is the SubForm.
The ProductsBought sfrm contains three Combo Boxes cboCategory
cboSubCategory cboProductInfo.
The user opens the cboCategory and selects a Category "Clothing". The Focus
is then moved to the cboSubCategory that then only displays SubCategories in
this Category (IE: Mens or Womans)
For this example: Womans
When the user selects a SubCategory the Focus is moved to cboProductInfo
that only displays products under the SubCategory "Woman's"

When the user selects a product all of the information for that product is
then inserted for the remainder of the fields in the form. (A "Drill Down")
Me![txtProductName] = Me![cbxProdNameID].Column(1)
Me![txtProductDescription] = Me![cbxProdNameID].Column(2)

Third is the "KIT" Inventory. (referred to in the first post as a Suite)
The KIT Inventory Form uses only two tbls.
tblGroupName
tblItemsInGroup (related to tblGroupName Many to One) Many ItemsInGroup In
One GroupName.
The tblItemsInGroup contain the same fields as the tblProductsBought and
tblSubCategory listed above.
It uses the same "Drill Down" as the sfrmProductsBought.

Upto this point all works correctly.

To bring the KIT Inventory into the sfrmrPoductsBought added a ComboBox in
frmCustomer, its Row Source is tblGroupName.
The box does show the Group Name correctly.

What is needed after the user selects the Group Name all of the data about
the products that the Group includes must be Copied/Inserted into the
frmProductsBought.

Normally I would use:
Me![txtProductName] = Me![cboProductName].Column(1)
Me![txtProductDescription] = Me![cboProductName].Column(2)

This doesn't work for this instance: There is only one Column (GroupName)
When I created a qry for the Row Source containing both the tblGroupName and
tblItemsInGroup the Combo Box list shows the GroupName Multiple times.
Tried Unique Record and the cbo still shows the GroupName multiple times.

Thank You for taking the time to read this explanation.

Would You be so kind to offer a little more assistance?

Andy


From Dorian:
You need to me more specific about the requirement. Do you want to group ANY
hat, blouse, dress ( in Sub-Category table) or a SPECIFIC hat, blouse, dress
(in ProductName table). In either case, you need a NEW table linked to
either
Sub-Category or ProductName. You did not say how you are going to use this
grouping and that is very important.

From Klatuu:
This is a common inventory problem. It is known as "kit" inventory.
What you need is an additional table that is like a join table, except both
sides of the join are the same table. This table will contain a record for
each kit ProductID and and ProductID in the Kit. For example:

Kit Item Qty
Woman's Outfit Blouse 1
Woman's Outfit Dress 1
Woman's Outfit Hat 2

And in your current inventory table, you will want a flag to indicate this
item is a kit.

Note the Qty field. This is usefull if a kit can contain more than 1 of the
items.


Andy said:
Hi;

The boss wants to "Cluster" products in a group or "Suite" meaning
"Blouse, Dress, Hat" = Woman's Outfit.

Have an Order/Sales dbase.

Main Form = Customer data, (Name, Address, Phone)
sfrmProductsBought = Category, (Auto, Clothing, Furniture), Sub-Category,
(Blouse, Dress, Hat), ProductName, (Blue Shirt, Green Shirt, Red Shirt)

The Category, Sub-Category and ProductName are "Drill Down" Combo Boxes.
For the "Drill Down" followed Microsoft's help: Seperate tbls for each
Combo Box with the tbl Primary Keys Data Type set as text. Then created a
main frm for Category, an sfrm for Sub-Category and an sfrm in
sfrmSub-Category for the individual Products. All of this works
correctly.

When the ProductName is selected in the sfrmProductsBought the "After
Update" Event uses Me!s to enter the Description, Price Etc.
Example:
Me![txtProductDescription] = Me![cbxProductNameID].Column(1)
All of this works correctly.

The boss wants to "Cluster" products in a group or "Suite" meaning
"Blouse, Dress, Hat" = Woman's Outfit. Have seen this work in an
accounting program.

At first simply added a "Grouped Products" Category to tblCategory that
won't work in this instance.

Then tried this:
Created tbls: GroupedCategory and GroupedProducts. (One To Many
Relationship)

The tblGroupedCategory contains the name of the Group, (Men's Outfit,
Woman's Outfit)
tblGroupedProducts contains the same fields as "sfrmProductsBought"

The sfrmGroupedProducts uses the same Drill Down method and it all works
correctly.

Added a Combo Box to sfrmProductsBought that displays the GroupCategory
Names. Its Row Source Type is "Table/Query" and its Row Source is
tblGroupedCategory.

Tried using a Row Source of tblGroupedCategory and tblGroupedProducts but
that shows the same Grouped Category multiple times, (One to Many).
Set the qry Properties to "Unique Records" and it still shows Grouped
Category multiple times.

Have searched and searched for an example of how to copy the data from the
Grouped Products Tables to the sfrmProductsBought and haven't found
anything that is even close.

Would someone be so kind to point me in the correction direction.

Thank You for taking the time to read this post.

Andy
Click to expand...
 
A

Andy

Klatuu;

Thank You again for Your reply.

I'm lost.

Searched Microsoft KB for a "Join Table". Nothing obvious to me.
Usually say "Getting Nowhere Fast". With changing the KB search criteria
"Got Nowhere Slow."

And more puzzling is Your sentence "A join table is used to resolve many to
many relationships."

The tables are One-To-Many.

Thank You for taking the time to read this.

Andy

Klatuu said:
A join table is used to resolve many to many relationships. Usually, each
column in a join table comes from a different table. In this case, they
would both come from the inventory table.

Andy said:
Thank You for Your replies.

Wasn't sure how to reply to both answers and continue the tread for Your
responses so copied/pasted both below.

For Dorian Yes do need to group ANY hat, blouse, dress into a group.

For Klatuu: Not sure what is meant by "Both sides of the join are the
same
table".

Explaining further: (Note to both this explanation is detailed not
stuffed
with excess info. Asking a question not writting a Novel.)

There are three seperate groups of related tables. All of the tbls are
record sources for forms and sub-forms.

First there is Products. The Products tables consist of:
tblProductCategory
tblProductSubCategory (related to tblProductCategory Many to One) Many
SubCategories In One Category.
tblProductInfo (related to tblProductSubCategory Many to One) Many
Products
In One SubCategory.

Category SubCategory ProductInfo (Name,Price,Description,Qty, Etc)
Clothing Men's Hat
Men's Shirt
Men's Shoes
Woman's Blouse
Woman's Dress
Woman's Skirt
The Category is the Main Form, SubCategory is a SubForm in the Main Form
and
ProductInfo is a SubForm in the SubCategory sfrm.

Next is the Order Form.
It contains two tables:
tblCustomer
tblProductsBought. (related to tblCustomer Many to One) Many
ProductsBought
In One Customer.
The tblProductsBought contain the same fields as the tblSubCategory
listed
above.

The tblCustomer is the Main Form the tblProductsBought is the SubForm.
The ProductsBought sfrm contains three Combo Boxes cboCategory
cboSubCategory cboProductInfo.
The user opens the cboCategory and selects a Category "Clothing". The
Focus
is then moved to the cboSubCategory that then only displays SubCategories
in
this Category (IE: Mens or Womans)
For this example: Womans
When the user selects a SubCategory the Focus is moved to cboProductInfo
that only displays products under the SubCategory "Woman's"

When the user selects a product all of the information for that product
is
then inserted for the remainder of the fields in the form. (A "Drill
Down")
Me![txtProductName] = Me![cbxProdNameID].Column(1)
Me![txtProductDescription] = Me![cbxProdNameID].Column(2)

Third is the "KIT" Inventory. (referred to in the first post as a Suite)
The KIT Inventory Form uses only two tbls.
tblGroupName
tblItemsInGroup (related to tblGroupName Many to One) Many ItemsInGroup
In
One GroupName.
The tblItemsInGroup contain the same fields as the tblProductsBought and
tblSubCategory listed above.
It uses the same "Drill Down" as the sfrmProductsBought.

Upto this point all works correctly.

To bring the KIT Inventory into the sfrmrPoductsBought added a ComboBox
in
frmCustomer, its Row Source is tblGroupName.
The box does show the Group Name correctly.

What is needed after the user selects the Group Name all of the data
about
the products that the Group includes must be Copied/Inserted into the
frmProductsBought.

Normally I would use:
Me![txtProductName] = Me![cboProductName].Column(1)
Me![txtProductDescription] = Me![cboProductName].Column(2)

This doesn't work for this instance: There is only one Column
(GroupName)
When I created a qry for the Row Source containing both the tblGroupName
and
tblItemsInGroup the Combo Box list shows the GroupName Multiple times.
Tried Unique Record and the cbo still shows the GroupName multiple times.

Thank You for taking the time to read this explanation.

Would You be so kind to offer a little more assistance?

Andy


From Dorian:
You need to me more specific about the requirement. Do you want to group
ANY
hat, blouse, dress ( in Sub-Category table) or a SPECIFIC hat, blouse,
dress
(in ProductName table). In either case, you need a NEW table linked to
either
Sub-Category or ProductName. You did not say how you are going to use
this
grouping and that is very important.

From Klatuu:
This is a common inventory problem. It is known as "kit" inventory.
What you need is an additional table that is like a join table, except
both
sides of the join are the same table. This table will contain a record
for
each kit ProductID and and ProductID in the Kit. For example:

Kit Item Qty
Woman's Outfit Blouse 1
Woman's Outfit Dress 1
Woman's Outfit Hat 2

And in your current inventory table, you will want a flag to indicate
this
item is a kit.

Note the Qty field. This is usefull if a kit can contain more than 1 of
the
items.


Andy said:
Hi;

The boss wants to "Cluster" products in a group or "Suite" meaning
"Blouse, Dress, Hat" = Woman's Outfit.

Have an Order/Sales dbase.

Main Form = Customer data, (Name, Address, Phone)
sfrmProductsBought = Category, (Auto, Clothing, Furniture),
Sub-Category,
(Blouse, Dress, Hat), ProductName, (Blue Shirt, Green Shirt, Red Shirt)

The Category, Sub-Category and ProductName are "Drill Down" Combo
Boxes.
For the "Drill Down" followed Microsoft's help: Seperate tbls for each
Combo Box with the tbl Primary Keys Data Type set as text. Then
created a
main frm for Category, an sfrm for Sub-Category and an sfrm in
sfrmSub-Category for the individual Products. All of this works
correctly.

When the ProductName is selected in the sfrmProductsBought the "After
Update" Event uses Me!s to enter the Description, Price Etc.
Example:
Me![txtProductDescription] = Me![cbxProductNameID].Column(1)
All of this works correctly.

The boss wants to "Cluster" products in a group or "Suite" meaning
"Blouse, Dress, Hat" = Woman's Outfit. Have seen this work in an
accounting program.

At first simply added a "Grouped Products" Category to tblCategory that
won't work in this instance.

Then tried this:
Created tbls: GroupedCategory and GroupedProducts. (One To Many
Relationship)

The tblGroupedCategory contains the name of the Group, (Men's Outfit,
Woman's Outfit)
tblGroupedProducts contains the same fields as "sfrmProductsBought"

The sfrmGroupedProducts uses the same Drill Down method and it all
works
correctly.

Added a Combo Box to sfrmProductsBought that displays the GroupCategory
Names. Its Row Source Type is "Table/Query" and its Row Source is
tblGroupedCategory.

Tried using a Row Source of tblGroupedCategory and tblGroupedProducts
but
that shows the same Grouped Category multiple times, (One to Many).
Set the qry Properties to "Unique Records" and it still shows Grouped
Category multiple times.

Have searched and searched for an example of how to copy the data from
the
Grouped Products Tables to the sfrmProductsBought and haven't found
anything that is even close.

Would someone be so kind to point me in the correction direction.

Thank You for taking the time to read this post.

Andy
Click to expand...
Click to expand...
Click to expand...
 
A

Andy

Klatuu;

Again Thank You for Your reply.

Your example "Kit Item Qty".

Are all those fields in one table?

In the example I included below Your field name "Kit" is in the
"tblGroupName" as "GroupName"
The fields "Item and Qty" are in the "tblItemsInGroup".

tblGroupName has the Primary key as GroupID (AutoNumber) and GroupName
tblItemsInGroup has the Primary key as ItemsInGroupID (AutoNumber) and
GroupID (Number) and ItemName, ItemDescription, ItemPrice, Etc)

The join is between tblGroupName GroupID (AutoNumber) AND tblItemsInGroup
GroupID (Number)

The Combo Box on the Main Form would display the GroupName only once and
when the user selected a group name all of the data for all of the products
in the group would be added to the sfrmProductsBought.

Similar to this:
Private Sub cboGroupName_AfterUpdate()
Me![txtProductName] = Me![cboGroupName].Column(2)
Me![txtProductDescription] = Me![cboGroupName].Column(3)
Me![txtProductDescription] = Me![cboGroupName].Column(4)

When I tried the RowSourceType as Table/Query and the RowSource using both
tables the GroupName was displayed multiple times.
With the tblGroupName only as the RowSource there isn't anything to populate
the fields in the sfrm.

Am I understanding You correctly?

Thank You for Your time.

Andy



Klatuu said:
Your tables may be a one to many; however, the logical relationship
between
the product codes in this instance is many to many. What this table does
is
give you a way to determine which products are included in a kit. If you
go
back to my original example:

Kit Item Qty
Woman's Outfit Blouse 1
Woman's Outfit Dress 1
Woman's Outfit Hat 2

Notice that all the fields in Kit have "Woman's Outfit". Then each Item
identifies the products include in the kit. Qty tells you how many of the
item are required to complete the kit. Maybe expanding on this example
will
help clear it up:

Kit Item Qty
Woman's Outfit Blouse 1
Woman's Outfit Dress 1
Woman's Outfit Hat 2
Men's Outfit Suit 1
Men's Outfit Shirt 1
Men's Outfit Tie 1
Men's Outfit Hat 1
Place Setting Dinner Plate 1
Place Setting Bread Plate 1
Place Setting Soup Bowl 1
Place Setting Cup 1
Place Setting Saucer 1

Each of the Kit's will also be an Item in the Inventory table. As I
stated
previously, you also need a flag in the Inventory table to tell you the
item
is a kit.

Andy said:
Klatuu;

Thank You again for Your reply.

I'm lost.

Searched Microsoft KB for a "Join Table". Nothing obvious to me.
Usually say "Getting Nowhere Fast". With changing the KB search criteria
"Got Nowhere Slow."

And more puzzling is Your sentence "A join table is used to resolve many
to
many relationships."

The tables are One-To-Many.

Thank You for taking the time to read this.

Andy

Klatuu said:
A join table is used to resolve many to many relationships. Usually,
each
column in a join table comes from a different table. In this case,
they
would both come from the inventory table.

:

Thank You for Your replies.

Wasn't sure how to reply to both answers and continue the tread for
Your
responses so copied/pasted both below.

For Dorian Yes do need to group ANY hat, blouse, dress into a group.

For Klatuu: Not sure what is meant by "Both sides of the join are the
same
table".

Explaining further: (Note to both this explanation is detailed not
stuffed
with excess info. Asking a question not writting a Novel.)

There are three seperate groups of related tables. All of the tbls
are
record sources for forms and sub-forms.

First there is Products. The Products tables consist of:
tblProductCategory
tblProductSubCategory (related to tblProductCategory Many to One)
Many
SubCategories In One Category.
tblProductInfo (related to tblProductSubCategory Many to One) Many
Products
In One SubCategory.

Category SubCategory ProductInfo (Name,Price,Description,Qty,
Etc)
Clothing Men's Hat
Men's Shirt
Men's Shoes
Woman's Blouse
Woman's Dress
Woman's Skirt
The Category is the Main Form, SubCategory is a SubForm in the Main
Form
and
ProductInfo is a SubForm in the SubCategory sfrm.

Next is the Order Form.
It contains two tables:
tblCustomer
tblProductsBought. (related to tblCustomer Many to One) Many
ProductsBought
In One Customer.
The tblProductsBought contain the same fields as the tblSubCategory
listed
above.

The tblCustomer is the Main Form the tblProductsBought is the SubForm.
The ProductsBought sfrm contains three Combo Boxes cboCategory
cboSubCategory cboProductInfo.
The user opens the cboCategory and selects a Category "Clothing". The
Focus
is then moved to the cboSubCategory that then only displays
SubCategories
in
this Category (IE: Mens or Womans)
For this example: Womans
When the user selects a SubCategory the Focus is moved to
cboProductInfo
that only displays products under the SubCategory "Woman's"

When the user selects a product all of the information for that
product
is
then inserted for the remainder of the fields in the form. (A "Drill
Down")
Me![txtProductName] = Me![cbxProdNameID].Column(1)
Me![txtProductDescription] = Me![cbxProdNameID].Column(2)

Third is the "KIT" Inventory. (referred to in the first post as a
Suite)
The KIT Inventory Form uses only two tbls.
tblGroupName
tblItemsInGroup (related to tblGroupName Many to One) Many
ItemsInGroup
In
One GroupName.
The tblItemsInGroup contain the same fields as the tblProductsBought
and
tblSubCategory listed above.
It uses the same "Drill Down" as the sfrmProductsBought.

Upto this point all works correctly.

To bring the KIT Inventory into the sfrmrPoductsBought added a
ComboBox
in
frmCustomer, its Row Source is tblGroupName.
The box does show the Group Name correctly.

What is needed after the user selects the Group Name all of the data
about
the products that the Group includes must be Copied/Inserted into the
frmProductsBought.

Normally I would use:
Me![txtProductName] = Me![cboProductName].Column(1)
Me![txtProductDescription] = Me![cboProductName].Column(2)

This doesn't work for this instance: There is only one Column
(GroupName)
When I created a qry for the Row Source containing both the
tblGroupName
and
tblItemsInGroup the Combo Box list shows the GroupName Multiple times.
Tried Unique Record and the cbo still shows the GroupName multiple
times.

Thank You for taking the time to read this explanation.

Would You be so kind to offer a little more assistance?

Andy


From Dorian:
You need to me more specific about the requirement. Do you want to
group
ANY
hat, blouse, dress ( in Sub-Category table) or a SPECIFIC hat, blouse,
dress
(in ProductName table). In either case, you need a NEW table linked to
either
Sub-Category or ProductName. You did not say how you are going to use
this
grouping and that is very important.

From Klatuu:
This is a common inventory problem. It is known as "kit" inventory.
What you need is an additional table that is like a join table, except
both
sides of the join are the same table. This table will contain a
record
for
each kit ProductID and and ProductID in the Kit. For example:

Kit Item Qty
Woman's Outfit Blouse 1
Woman's Outfit Dress 1
Woman's Outfit Hat 2

And in your current inventory table, you will want a flag to indicate
this
item is a kit.

Note the Qty field. This is usefull if a kit can contain more than 1
of
the
items.


Hi;

The boss wants to "Cluster" products in a group or "Suite" meaning
"Blouse, Dress, Hat" = Woman's Outfit.

Have an Order/Sales dbase.

Main Form = Customer data, (Name, Address, Phone)
sfrmProductsBought = Category, (Auto, Clothing, Furniture),
Sub-Category,
(Blouse, Dress, Hat), ProductName, (Blue Shirt, Green Shirt, Red
Shirt)

The Category, Sub-Category and ProductName are "Drill Down" Combo
Boxes.
For the "Drill Down" followed Microsoft's help: Seperate tbls for
each
Combo Box with the tbl Primary Keys Data Type set as text. Then
created a
main frm for Category, an sfrm for Sub-Category and an sfrm in
sfrmSub-Category for the individual Products. All of this works
correctly.

When the ProductName is selected in the sfrmProductsBought the
"After
Update" Event uses Me!s to enter the Description, Price Etc.
Example:
Me![txtProductDescription] = Me![cbxProductNameID].Column(1)
All of this works correctly.

The boss wants to "Cluster" products in a group or "Suite" meaning
"Blouse, Dress, Hat" = Woman's Outfit. Have seen this work in an
accounting program.

At first simply added a "Grouped Products" Category to tblCategory
that
won't work in this instance.

Then tried this:
Created tbls: GroupedCategory and GroupedProducts. (One To Many
Relationship)

The tblGroupedCategory contains the name of the Group, (Men's
Outfit,
Woman's Outfit)
tblGroupedProducts contains the same fields as "sfrmProductsBought"

The sfrmGroupedProducts uses the same Drill Down method and it all
works
correctly.

Added a Combo Box to sfrmProductsBought that displays the
GroupCategory
Names. Its Row Source Type is "Table/Query" and its Row Source is
tblGroupedCategory.

Tried using a Row Source of tblGroupedCategory and
tblGroupedProducts
but
that shows the same Grouped Category multiple times, (One to Many).
Set the qry Properties to "Unique Records" and it still shows
Grouped
Category multiple times.

Have searched and searched for an example of how to copy the data
from
the
Grouped Products Tables to the sfrmProductsBought and haven't found
anything that is even close.

Would someone be so kind to point me in the correction direction.

Thank You for taking the time to read this post.

Andy
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

All the fields in my example are in one table. Perhaps it would have been
more clear had I incuded the key of the items to use to link to the items in
the Inventory table.

The Group Name table would not be a bad idea, but, again, the group is also
an inventory item and should be in the inventory table.
If you are taking orders, you would be selecting from the inventory table.
To take an order for a "woman's outfit", you would look it up in the
inventory just like any other item. It would be your responsibility,
programmatically, to determine whether a selected item is a kit item, and
handle it however you need for your application.

Andy said:
Klatuu;

Again Thank You for Your reply.

Your example "Kit Item Qty".

Are all those fields in one table?

In the example I included below Your field name "Kit" is in the
"tblGroupName" as "GroupName"
The fields "Item and Qty" are in the "tblItemsInGroup".

tblGroupName has the Primary key as GroupID (AutoNumber) and GroupName
tblItemsInGroup has the Primary key as ItemsInGroupID (AutoNumber) and
GroupID (Number) and ItemName, ItemDescription, ItemPrice, Etc)

The join is between tblGroupName GroupID (AutoNumber) AND tblItemsInGroup
GroupID (Number)

The Combo Box on the Main Form would display the GroupName only once and
when the user selected a group name all of the data for all of the products
in the group would be added to the sfrmProductsBought.

Similar to this:
Private Sub cboGroupName_AfterUpdate()
Me![txtProductName] = Me![cboGroupName].Column(2)
Me![txtProductDescription] = Me![cboGroupName].Column(3)
Me![txtProductDescription] = Me![cboGroupName].Column(4)

When I tried the RowSourceType as Table/Query and the RowSource using both
tables the GroupName was displayed multiple times.
With the tblGroupName only as the RowSource there isn't anything to populate
the fields in the sfrm.

Am I understanding You correctly?

Thank You for Your time.

Andy



Klatuu said:
Your tables may be a one to many; however, the logical relationship
between
the product codes in this instance is many to many. What this table does
is
give you a way to determine which products are included in a kit. If you
go
back to my original example:

Kit Item Qty
Woman's Outfit Blouse 1
Woman's Outfit Dress 1
Woman's Outfit Hat 2

Notice that all the fields in Kit have "Woman's Outfit". Then each Item
identifies the products include in the kit. Qty tells you how many of the
item are required to complete the kit. Maybe expanding on this example
will
help clear it up:

Kit Item Qty
Woman's Outfit Blouse 1
Woman's Outfit Dress 1
Woman's Outfit Hat 2
Men's Outfit Suit 1
Men's Outfit Shirt 1
Men's Outfit Tie 1
Men's Outfit Hat 1
Place Setting Dinner Plate 1
Place Setting Bread Plate 1
Place Setting Soup Bowl 1
Place Setting Cup 1
Place Setting Saucer 1

Each of the Kit's will also be an Item in the Inventory table. As I
stated
previously, you also need a flag in the Inventory table to tell you the
item
is a kit.

Andy said:
Klatuu;

Thank You again for Your reply.

I'm lost.

Searched Microsoft KB for a "Join Table". Nothing obvious to me.
Usually say "Getting Nowhere Fast". With changing the KB search criteria
"Got Nowhere Slow."

And more puzzling is Your sentence "A join table is used to resolve many
to
many relationships."

The tables are One-To-Many.

Thank You for taking the time to read this.

Andy

A join table is used to resolve many to many relationships. Usually,
each
column in a join table comes from a different table. In this case,
they
would both come from the inventory table.

:

Thank You for Your replies.

Wasn't sure how to reply to both answers and continue the tread for
Your
responses so copied/pasted both below.

For Dorian Yes do need to group ANY hat, blouse, dress into a group.

For Klatuu: Not sure what is meant by "Both sides of the join are the
same
table".

Explaining further: (Note to both this explanation is detailed not
stuffed
with excess info. Asking a question not writting a Novel.)

There are three seperate groups of related tables. All of the tbls
are
record sources for forms and sub-forms.

First there is Products. The Products tables consist of:
tblProductCategory
tblProductSubCategory (related to tblProductCategory Many to One)
Many
SubCategories In One Category.
tblProductInfo (related to tblProductSubCategory Many to One) Many
Products
In One SubCategory.

Category SubCategory ProductInfo (Name,Price,Description,Qty,
Etc)
Clothing Men's Hat
Men's Shirt
Men's Shoes
Woman's Blouse
Woman's Dress
Woman's Skirt
The Category is the Main Form, SubCategory is a SubForm in the Main
Form
and
ProductInfo is a SubForm in the SubCategory sfrm.

Next is the Order Form.
It contains two tables:
tblCustomer
tblProductsBought. (related to tblCustomer Many to One) Many
ProductsBought
In One Customer.
The tblProductsBought contain the same fields as the tblSubCategory
listed
above.

The tblCustomer is the Main Form the tblProductsBought is the SubForm.
The ProductsBought sfrm contains three Combo Boxes cboCategory
cboSubCategory cboProductInfo.
The user opens the cboCategory and selects a Category "Clothing". The
Focus
is then moved to the cboSubCategory that then only displays
SubCategories
in
this Category (IE: Mens or Womans)
For this example: Womans
When the user selects a SubCategory the Focus is moved to
cboProductInfo
that only displays products under the SubCategory "Woman's"

When the user selects a product all of the information for that
product
is
then inserted for the remainder of the fields in the form. (A "Drill
Down")
Me![txtProductName] = Me![cbxProdNameID].Column(1)
Me![txtProductDescription] = Me![cbxProdNameID].Column(2)

Third is the "KIT" Inventory. (referred to in the first post as a
Suite)
The KIT Inventory Form uses only two tbls.
tblGroupName
tblItemsInGroup (related to tblGroupName Many to One) Many
ItemsInGroup
In
One GroupName.
The tblItemsInGroup contain the same fields as the tblProductsBought
and
tblSubCategory listed above.
It uses the same "Drill Down" as the sfrmProductsBought.

Upto this point all works correctly.

To bring the KIT Inventory into the sfrmrPoductsBought added a
ComboBox
in
frmCustomer, its Row Source is tblGroupName.
The box does show the Group Name correctly.

What is needed after the user selects the Group Name all of the data
about
the products that the Group includes must be Copied/Inserted into the
frmProductsBought.

Normally I would use:
Me![txtProductName] = Me![cboProductName].Column(1)
Me![txtProductDescription] = Me![cboProductName].Column(2)

This doesn't work for this instance: There is only one Column
(GroupName)
When I created a qry for the Row Source containing both the
tblGroupName
and
tblItemsInGroup the Combo Box list shows the GroupName Multiple times.
Tried Unique Record and the cbo still shows the GroupName multiple
times.

Thank You for taking the time to read this explanation.

Would You be so kind to offer a little more assistance?

Andy


From Dorian:
You need to me more specific about the requirement. Do you want to
group
ANY
hat, blouse, dress ( in Sub-Category table) or a SPECIFIC hat, blouse,
dress
(in ProductName table). In either case, you need a NEW table linked to
either
Sub-Category or ProductName. You did not say how you are going to use
this
grouping and that is very important.

From Klatuu:
This is a common inventory problem. It is known as "kit" inventory.
What you need is an additional table that is like a join table, except
both
sides of the join are the same table. This table will contain a
record
for
each kit ProductID and and ProductID in the Kit. For example:

Kit Item Qty
Woman's Outfit Blouse 1
Woman's Outfit Dress 1
Woman's Outfit Hat 2

And in your current inventory table, you will want a flag to indicate
this
item is a kit.

Note the Qty field. This is usefull if a kit can contain more than 1
of
the
items.


Hi;

The boss wants to "Cluster" products in a group or "Suite" meaning
"Blouse, Dress, Hat" = Woman's Outfit.

Have an Order/Sales dbase.

Main Form = Customer data, (Name, Address, Phone)
sfrmProductsBought = Category, (Auto, Clothing, Furniture),
Sub-Category,
(Blouse, Dress, Hat), ProductName, (Blue Shirt, Green Shirt, Red
Shirt)

The Category, Sub-Category and ProductName are "Drill Down" Combo
Boxes.
For the "Drill Down" followed Microsoft's help: Seperate tbls for
each
Combo Box with the tbl Primary Keys Data Type set as text. Then
created a
main frm for Category, an sfrm for Sub-Category and an sfrm in
sfrmSub-Category for the individual Products. All of this works
correctly.

When the ProductName is selected in the sfrmProductsBought the
"After
Update" Event uses Me!s to enter the Description, Price Etc.
Example:
Click to expand...
Click to expand...
Click to expand...
 
A

Andy

Klatuu;

Thank You for helping to educate me.

I tried putting the "GroupName" in the Inventory table. No Go in this
instance.

The Inventory consists of three tbls tblProductCategory,
tblProductSubCategory and tblProductsCarried
with a MainFormProductCategory an sfrmProductSubCategory and an sfrm within
sfrmProductSubCategory that records individual Products.

That enables the "Drill Down" on the sfrmProductsBought contained in
MainFormCustomers.

When the "GroupName" is added to tblProductCategory the user can't select
more than one SubCategory. That is why the "tblGroupName" and
"tblItemsInGroup" were created.

The MainFormGroupName allows the user to give the Group a specific name then
using the same "Drill Down" the user selects ProductCategory, SubCategory
and individual Products. The After Update Event then populates all of the
info pertaining to the product.

So Tables and Related Tables, Forms and Sub-Forms are not the hassle.

The task is to have the ComboBox (cboGroupName), on the MainFormCustomers
populate all of the fields in sfrmProductsBought using some sort of ME!
while only displaying the GroupName once.

Did try an "Append" qry with the criteria set as cboGroupName. It did put
the information in the proper places but did not include the CustomerID
number.

In closing as I searched for an answer to this I found an answer that You
had sent to another person about "Kit". In that answer You had used the
abbreviation "SKU". The person said that once they learned what "SKU" was
the rest was easy.

Klatuu; What is SKU?

Thank You.

Andy





Klatuu said:
All the fields in my example are in one table. Perhaps it would have been
more clear had I incuded the key of the items to use to link to the items
in
the Inventory table.

The Group Name table would not be a bad idea, but, again, the group is
also
an inventory item and should be in the inventory table.
If you are taking orders, you would be selecting from the inventory table.
To take an order for a "woman's outfit", you would look it up in the
inventory just like any other item. It would be your responsibility,
programmatically, to determine whether a selected item is a kit item, and
handle it however you need for your application.

Andy said:
Klatuu;

Again Thank You for Your reply.

Your example "Kit Item Qty".

Are all those fields in one table?

In the example I included below Your field name "Kit" is in the
"tblGroupName" as "GroupName"
The fields "Item and Qty" are in the "tblItemsInGroup".

tblGroupName has the Primary key as GroupID (AutoNumber) and GroupName
tblItemsInGroup has the Primary key as ItemsInGroupID (AutoNumber) and
GroupID (Number) and ItemName, ItemDescription, ItemPrice, Etc)

The join is between tblGroupName GroupID (AutoNumber) AND tblItemsInGroup
GroupID (Number)

The Combo Box on the Main Form would display the GroupName only once and
when the user selected a group name all of the data for all of the
products
in the group would be added to the sfrmProductsBought.

Similar to this:
Private Sub cboGroupName_AfterUpdate()
Me![txtProductName] = Me![cboGroupName].Column(2)
Me![txtProductDescription] = Me![cboGroupName].Column(3)
Me![txtProductDescription] = Me![cboGroupName].Column(4)

When I tried the RowSourceType as Table/Query and the RowSource using
both
tables the GroupName was displayed multiple times.
With the tblGroupName only as the RowSource there isn't anything to
populate
the fields in the sfrm.

Am I understanding You correctly?

Thank You for Your time.

Andy



Klatuu said:
Your tables may be a one to many; however, the logical relationship
between
the product codes in this instance is many to many. What this table
does
is
give you a way to determine which products are included in a kit. If
you
go
back to my original example:

Kit Item Qty
Woman's Outfit Blouse 1
Woman's Outfit Dress 1
Woman's Outfit Hat 2

Notice that all the fields in Kit have "Woman's Outfit". Then each
Item
identifies the products include in the kit. Qty tells you how many of
the
item are required to complete the kit. Maybe expanding on this example
will
help clear it up:

Kit Item Qty
Woman's Outfit Blouse 1
Woman's Outfit Dress 1
Woman's Outfit Hat 2
Men's Outfit Suit 1
Men's Outfit Shirt 1
Men's Outfit Tie 1
Men's Outfit Hat 1
Place Setting Dinner Plate 1
Place Setting Bread Plate 1
Place Setting Soup Bowl 1
Place Setting Cup 1
Place Setting Saucer 1

Each of the Kit's will also be an Item in the Inventory table. As I
stated
previously, you also need a flag in the Inventory table to tell you the
item
is a kit.

:

Klatuu;

Thank You again for Your reply.

I'm lost.

Searched Microsoft KB for a "Join Table". Nothing obvious to me.
Usually say "Getting Nowhere Fast". With changing the KB search
criteria
"Got Nowhere Slow."

And more puzzling is Your sentence "A join table is used to resolve
many
to
many relationships."

The tables are One-To-Many.

Thank You for taking the time to read this.

Andy

A join table is used to resolve many to many relationships. Usually,
each
column in a join table comes from a different table. In this case,
they
would both come from the inventory table.

:

Thank You for Your replies.

Wasn't sure how to reply to both answers and continue the tread for
Your
responses so copied/pasted both below.

For Dorian Yes do need to group ANY hat, blouse, dress into a
group.

For Klatuu: Not sure what is meant by "Both sides of the join are
the
same
table".

Explaining further: (Note to both this explanation is detailed not
stuffed
with excess info. Asking a question not writting a Novel.)

There are three seperate groups of related tables. All of the tbls
are
record sources for forms and sub-forms.

First there is Products. The Products tables consist of:
tblProductCategory
tblProductSubCategory (related to tblProductCategory Many to One)
Many
SubCategories In One Category.
tblProductInfo (related to tblProductSubCategory Many to One) Many
Products
In One SubCategory.

Category SubCategory ProductInfo (Name,Price,Description,Qty,
Etc)
Clothing Men's Hat
Men's Shirt
Men's Shoes
Woman's Blouse
Woman's Dress
Woman's Skirt
The Category is the Main Form, SubCategory is a SubForm in the Main
Form
and
ProductInfo is a SubForm in the SubCategory sfrm.

Next is the Order Form.
It contains two tables:
tblCustomer
tblProductsBought. (related to tblCustomer Many to One) Many
ProductsBought
In One Customer.
The tblProductsBought contain the same fields as the tblSubCategory
listed
above.

The tblCustomer is the Main Form the tblProductsBought is the
SubForm.
The ProductsBought sfrm contains three Combo Boxes cboCategory
cboSubCategory cboProductInfo.
The user opens the cboCategory and selects a Category "Clothing".
The
Focus
is then moved to the cboSubCategory that then only displays
SubCategories
in
this Category (IE: Mens or Womans)
For this example: Womans
When the user selects a SubCategory the Focus is moved to
cboProductInfo
that only displays products under the SubCategory "Woman's"

When the user selects a product all of the information for that
product
is
then inserted for the remainder of the fields in the form. (A
"Drill
Down")
Me![txtProductName] = Me![cbxProdNameID].Column(1)
Me![txtProductDescription] = Me![cbxProdNameID].Column(2)

Third is the "KIT" Inventory. (referred to in the first post as a
Suite)
The KIT Inventory Form uses only two tbls.
tblGroupName
tblItemsInGroup (related to tblGroupName Many to One) Many
ItemsInGroup
In
One GroupName.
The tblItemsInGroup contain the same fields as the
tblProductsBought
and
tblSubCategory listed above.
It uses the same "Drill Down" as the sfrmProductsBought.

Upto this point all works correctly.

To bring the KIT Inventory into the sfrmrPoductsBought added a
ComboBox
in
frmCustomer, its Row Source is tblGroupName.
The box does show the Group Name correctly.

What is needed after the user selects the Group Name all of the
data
about
the products that the Group includes must be Copied/Inserted into
the
frmProductsBought.

Normally I would use:
Me![txtProductName] = Me![cboProductName].Column(1)
Me![txtProductDescription] = Me![cboProductName].Column(2)

This doesn't work for this instance: There is only one Column
(GroupName)
When I created a qry for the Row Source containing both the
tblGroupName
and
tblItemsInGroup the Combo Box list shows the GroupName Multiple
times.
Tried Unique Record and the cbo still shows the GroupName multiple
times.

Thank You for taking the time to read this explanation.

Would You be so kind to offer a little more assistance?

Andy


From Dorian:
You need to me more specific about the requirement. Do you want to
group
ANY
hat, blouse, dress ( in Sub-Category table) or a SPECIFIC hat,
blouse,
dress
(in ProductName table). In either case, you need a NEW table linked
to
either
Sub-Category or ProductName. You did not say how you are going to
use
this
grouping and that is very important.

From Klatuu:
This is a common inventory problem. It is known as "kit"
inventory.
What you need is an additional table that is like a join table,
except
both
sides of the join are the same table. This table will contain a
record
for
each kit ProductID and and ProductID in the Kit. For example:

Kit Item Qty
Woman's Outfit Blouse 1
Woman's Outfit Dress 1
Woman's Outfit Hat 2

And in your current inventory table, you will want a flag to
indicate
this
item is a kit.

Note the Qty field. This is usefull if a kit can contain more than
1
of
the
items.


Hi;

The boss wants to "Cluster" products in a group or "Suite"
meaning
"Blouse, Dress, Hat" = Woman's Outfit.

Have an Order/Sales dbase.

Main Form = Customer data, (Name, Address, Phone)
sfrmProductsBought = Category, (Auto, Clothing, Furniture),
Sub-Category,
(Blouse, Dress, Hat), ProductName, (Blue Shirt, Green Shirt, Red
Shirt)

The Category, Sub-Category and ProductName are "Drill Down" Combo
Boxes.
For the "Drill Down" followed Microsoft's help: Seperate tbls
for
each
Combo Box with the tbl Primary Keys Data Type set as text. Then
created a
main frm for Category, an sfrm for Sub-Category and an sfrm in
sfrmSub-Category for the individual Products. All of this works
correctly.

When the ProductName is selected in the sfrmProductsBought the
"After
Update" Event uses Me!s to enter the Description, Price Etc.
Example:
Click to expand...
Click to expand...
Click to expand...
 
A

Andy

Klatuu;

Thank You.

Thinking that I should have worded the subject differently.

As You have read and agreed the tbls and frms are correct.

Think the the subject of this post should be something along the lines of
"Single Category w/Mutilple Products Inserted from Combo Box without Drill
Down".

Have to give it some thought on how to word it for accuracy.

Thank You for Your kindness.

Happy Holidays.

Andy

Klatuu said:
Stock Keeping Unit

Okay, given the structure of your inventory tables, I think that the Kit
inventory item should be at the tblProductsCarried level. It would need
to
be assigned to the Category and sub Category levels as well.

Andy said:
Klatuu;

Thank You for helping to educate me.

I tried putting the "GroupName" in the Inventory table. No Go in this
instance.

The Inventory consists of three tbls tblProductCategory,
tblProductSubCategory and tblProductsCarried
with a MainFormProductCategory an sfrmProductSubCategory and an sfrm
within
sfrmProductSubCategory that records individual Products.

That enables the "Drill Down" on the sfrmProductsBought contained in
MainFormCustomers.

When the "GroupName" is added to tblProductCategory the user can't select
more than one SubCategory. That is why the "tblGroupName" and
"tblItemsInGroup" were created.

The MainFormGroupName allows the user to give the Group a specific name
then
using the same "Drill Down" the user selects ProductCategory, SubCategory
and individual Products. The After Update Event then populates all of
the
info pertaining to the product.

So Tables and Related Tables, Forms and Sub-Forms are not the hassle.

The task is to have the ComboBox (cboGroupName), on the MainFormCustomers
populate all of the fields in sfrmProductsBought using some sort of ME!
while only displaying the GroupName once.

Did try an "Append" qry with the criteria set as cboGroupName. It did
put
the information in the proper places but did not include the CustomerID
number.

In closing as I searched for an answer to this I found an answer that You
had sent to another person about "Kit". In that answer You had used the
abbreviation "SKU". The person said that once they learned what "SKU"
was
the rest was easy.

Klatuu; What is SKU?

Thank You.

Andy





Klatuu said:
All the fields in my example are in one table. Perhaps it would have
been
more clear had I incuded the key of the items to use to link to the
items
in
the Inventory table.

The Group Name table would not be a bad idea, but, again, the group is
also
an inventory item and should be in the inventory table.
If you are taking orders, you would be selecting from the inventory
table.
To take an order for a "woman's outfit", you would look it up in the
inventory just like any other item. It would be your responsibility,
programmatically, to determine whether a selected item is a kit item,
and
handle it however you need for your application.

:

Klatuu;

Again Thank You for Your reply.

Your example "Kit Item Qty".

Are all those fields in one table?

In the example I included below Your field name "Kit" is in the
"tblGroupName" as "GroupName"
The fields "Item and Qty" are in the "tblItemsInGroup".

tblGroupName has the Primary key as GroupID (AutoNumber) and GroupName
tblItemsInGroup has the Primary key as ItemsInGroupID (AutoNumber) and
GroupID (Number) and ItemName, ItemDescription, ItemPrice, Etc)

The join is between tblGroupName GroupID (AutoNumber) AND
tblItemsInGroup
GroupID (Number)

The Combo Box on the Main Form would display the GroupName only once
and
when the user selected a group name all of the data for all of the
products
in the group would be added to the sfrmProductsBought.

Similar to this:
Private Sub cboGroupName_AfterUpdate()
Me![txtProductName] = Me![cboGroupName].Column(2)
Me![txtProductDescription] = Me![cboGroupName].Column(3)
Me![txtProductDescription] = Me![cboGroupName].Column(4)

When I tried the RowSourceType as Table/Query and the RowSource using
both
tables the GroupName was displayed multiple times.
With the tblGroupName only as the RowSource there isn't anything to
populate
the fields in the sfrm.

Am I understanding You correctly?

Thank You for Your time.

Andy



Your tables may be a one to many; however, the logical relationship
between
the product codes in this instance is many to many. What this table
does
is
give you a way to determine which products are included in a kit.
If
you
go
back to my original example:

Kit Item Qty
Woman's Outfit Blouse 1
Woman's Outfit Dress 1
Woman's Outfit Hat 2

Notice that all the fields in Kit have "Woman's Outfit". Then each
Item
identifies the products include in the kit. Qty tells you how many
of
the
item are required to complete the kit. Maybe expanding on this
example
will
help clear it up:

Kit Item Qty
Woman's Outfit Blouse 1
Woman's Outfit Dress 1
Woman's Outfit Hat 2
Men's Outfit Suit 1
Men's Outfit Shirt 1
Men's Outfit Tie 1
Men's Outfit Hat 1
Place Setting Dinner Plate 1
Place Setting Bread Plate 1
Place Setting Soup Bowl 1
Place Setting Cup 1
Place Setting Saucer 1

Each of the Kit's will also be an Item in the Inventory table. As I
stated
previously, you also need a flag in the Inventory table to tell you
the
item
is a kit.

:

Klatuu;

Thank You again for Your reply.

I'm lost.

Searched Microsoft KB for a "Join Table". Nothing obvious to me.
Usually say "Getting Nowhere Fast". With changing the KB search
criteria
"Got Nowhere Slow."

And more puzzling is Your sentence "A join table is used to resolve
many
to
many relationships."

The tables are One-To-Many.

Thank You for taking the time to read this.

Andy

A join table is used to resolve many to many relationships.
Usually,
each
column in a join table comes from a different table. In this
case,
they
would both come from the inventory table.

:

Thank You for Your replies.

Wasn't sure how to reply to both answers and continue the tread
for
Your
responses so copied/pasted both below.

For Dorian Yes do need to group ANY hat, blouse, dress into a
group.

For Klatuu: Not sure what is meant by "Both sides of the join
are
the
same
table".

Explaining further: (Note to both this explanation is detailed
not
stuffed
with excess info. Asking a question not writting a Novel.)

There are three seperate groups of related tables. All of the
tbls
are
record sources for forms and sub-forms.

First there is Products. The Products tables consist of:
tblProductCategory
tblProductSubCategory (related to tblProductCategory Many to
One)
Many
SubCategories In One Category.
tblProductInfo (related to tblProductSubCategory Many to One)
Many
Products
In One SubCategory.

Category SubCategory ProductInfo
(Name,Price,Description,Qty,
Etc)
Clothing Men's Hat
Men's Shirt
Men's Shoes
Woman's Blouse
Woman's Dress
Woman's Skirt
The Category is the Main Form, SubCategory is a SubForm in the
Main
Form
and
ProductInfo is a SubForm in the SubCategory sfrm.

Next is the Order Form.
It contains two tables:
tblCustomer
tblProductsBought. (related to tblCustomer Many to One) Many
ProductsBought
In One Customer.
The tblProductsBought contain the same fields as the
tblSubCategory
listed
above.

The tblCustomer is the Main Form the tblProductsBought is the
SubForm.
The ProductsBought sfrm contains three Combo Boxes cboCategory
cboSubCategory cboProductInfo.
The user opens the cboCategory and selects a Category
"Clothing".
The
Focus
is then moved to the cboSubCategory that then only displays
SubCategories
in
this Category (IE: Mens or Womans)
For this example: Womans
When the user selects a SubCategory the Focus is moved to
cboProductInfo
that only displays products under the SubCategory "Woman's"

When the user selects a product all of the information for that
product
is
then inserted for the remainder of the fields in the form. (A
"Drill
Down")
Me![txtProductName] = Me![cbxProdNameID].Column(1)
Me![txtProductDescription] = Me![cbxProdNameID].Column(2)

Third is the "KIT" Inventory. (referred to in the first post as
a
Suite)
The KIT Inventory Form uses only two tbls.
tblGroupName
tblItemsInGroup (related to tblGroupName Many to One) Many
ItemsInGroup
In
One GroupName.
The tblItemsInGroup contain the same fields as the
tblProductsBought
and
tblSubCategory listed above.
It uses the same "Drill Down" as the sfrmProductsBought.

Upto this point all works correctly.

To bring the KIT Inventory into the sfrmrPoductsBought added a
ComboBox
in
frmCustomer, its Row Source is tblGroupName.
The box does show the Group Name correctly.

What is needed after the user selects the Group Name all of the
data
about
the products that the Group includes must be Copied/Inserted
into
the
frmProductsBought.

Normally I would use:
Click to expand...
Click to expand...
Click to expand...
 
A

Andy

Klatuu;

Decided to use an Append Qry triggered by the AfterUpdateEvent for the
"cboPackagedItems".

Spent over 3 hours trial and error.

The Append worked instantly and correctly. What was missing was the
"CustomerTblID" wasn't appending so the qry was creating "Orphan" records.

Added a field "CustomerTblID: [Forms]![frmCustomer]![CustomerTblID] and
Appended it to "CustomerTblID"

That works.

Now working on code to Refresh the Sfrm. Currently the user has to cycle
the Record to see the Append results. If Me.Dirty Then Me.Refresh doesn't
work perhaps I need to add the Sfrm name to the refresh. Not sure Yet.

Thank You for Help.

Live Long and Prosper.

Andy


Klatuu said:
Merry Christmas to you.
Hope you getting it working like you want to.
This type of inventory is a little complex.

I did an inventory system for a fine china retail distributor several
years
ago where they sold individual pieces and sets. In addition to having to
keep track of what goes in to a set, it had to be able to do things like
if a
person ordered a plate and there were no individual plates in stock, look
for
a set that has the plate, take the individual pieces from the set and put
them in inventory as single pieces and remove the set from inventory and
sell
the plate. And, if a person ordered a set that was not in stock, look
through the inventory to see if the individual pieces that make up a set
were
in stock, then take the individual pieces out of stock and add the set to
stock to sell it.

That was an interesting application.

Andy said:
Klatuu;

Thank You.

Thinking that I should have worded the subject differently.

As You have read and agreed the tbls and frms are correct.

Think the the subject of this post should be something along the lines of
"Single Category w/Mutilple Products Inserted from Combo Box without
Drill
Down".

Have to give it some thought on how to word it for accuracy.

Thank You for Your kindness.

Happy Holidays.

Andy

Klatuu said:
Stock Keeping Unit

Okay, given the structure of your inventory tables, I think that the
Kit
inventory item should be at the tblProductsCarried level. It would
need
to
be assigned to the Category and sub Category levels as well.

:

Klatuu;

Thank You for helping to educate me.

I tried putting the "GroupName" in the Inventory table. No Go in this
instance.

The Inventory consists of three tbls tblProductCategory,
tblProductSubCategory and tblProductsCarried
with a MainFormProductCategory an sfrmProductSubCategory and an sfrm
within
sfrmProductSubCategory that records individual Products.

That enables the "Drill Down" on the sfrmProductsBought contained in
MainFormCustomers.

When the "GroupName" is added to tblProductCategory the user can't
select
more than one SubCategory. That is why the "tblGroupName" and
"tblItemsInGroup" were created.

The MainFormGroupName allows the user to give the Group a specific
name
then
using the same "Drill Down" the user selects ProductCategory,
SubCategory
and individual Products. The After Update Event then populates all of
the
info pertaining to the product.

So Tables and Related Tables, Forms and Sub-Forms are not the hassle.

The task is to have the ComboBox (cboGroupName), on the
MainFormCustomers
populate all of the fields in sfrmProductsBought using some sort of
ME!
while only displaying the GroupName once.

Did try an "Append" qry with the criteria set as cboGroupName. It did
put
the information in the proper places but did not include the
CustomerID
number.

In closing as I searched for an answer to this I found an answer that
You
had sent to another person about "Kit". In that answer You had used
the
abbreviation "SKU". The person said that once they learned what "SKU"
was
the rest was easy.

Klatuu; What is SKU?

Thank You.

Andy





All the fields in my example are in one table. Perhaps it would have
been
more clear had I incuded the key of the items to use to link to the
items
in
the Inventory table.

The Group Name table would not be a bad idea, but, again, the group
is
also
an inventory item and should be in the inventory table.
If you are taking orders, you would be selecting from the inventory
table.
To take an order for a "woman's outfit", you would look it up in the
inventory just like any other item. It would be your
responsibility,
programmatically, to determine whether a selected item is a kit
item,
and
handle it however you need for your application.

:

Klatuu;

Again Thank You for Your reply.

Your example "Kit Item Qty".

Are all those fields in one table?

In the example I included below Your field name "Kit" is in the
"tblGroupName" as "GroupName"
The fields "Item and Qty" are in the "tblItemsInGroup".

tblGroupName has the Primary key as GroupID (AutoNumber) and
GroupName
tblItemsInGroup has the Primary key as ItemsInGroupID (AutoNumber)
and
GroupID (Number) and ItemName, ItemDescription, ItemPrice, Etc)

The join is between tblGroupName GroupID (AutoNumber) AND
tblItemsInGroup
GroupID (Number)

The Combo Box on the Main Form would display the GroupName only
once
and
when the user selected a group name all of the data for all of the
products
in the group would be added to the sfrmProductsBought.

Similar to this:
Private Sub cboGroupName_AfterUpdate()
Me![txtProductName] = Me![cboGroupName].Column(2)
Me![txtProductDescription] = Me![cboGroupName].Column(3)
Me![txtProductDescription] = Me![cboGroupName].Column(4)

When I tried the RowSourceType as Table/Query and the RowSource
using
both
tables the GroupName was displayed multiple times.
With the tblGroupName only as the RowSource there isn't anything to
populate
the fields in the sfrm.

Am I understanding You correctly?

Thank You for Your time.

Andy



Your tables may be a one to many; however, the logical
relationship
between
the product codes in this instance is many to many. What this
table
does
is
give you a way to determine which products are included in a kit.
If
you
go
back to my original example:

Kit Item Qty
Woman's Outfit Blouse 1
Woman's Outfit Dress 1
Woman's Outfit Hat 2

Notice that all the fields in Kit have "Woman's Outfit". Then
each
Item
identifies the products include in the kit. Qty tells you how
many
of
the
item are required to complete the kit. Maybe expanding on this
example
will
help clear it up:

Kit Item Qty
Woman's Outfit Blouse 1
Woman's Outfit Dress 1
Woman's Outfit Hat 2
Men's Outfit Suit 1
Men's Outfit Shirt 1
Men's Outfit Tie 1
Men's Outfit Hat 1
Place Setting Dinner Plate 1
Place Setting Bread Plate 1
Place Setting Soup Bowl 1
Place Setting Cup 1
Place Setting Saucer 1

Each of the Kit's will also be an Item in the Inventory table.
As I
stated
previously, you also need a flag in the Inventory table to tell
you
the
item
is a kit.

:

Klatuu;

Thank You again for Your reply.

I'm lost.

Searched Microsoft KB for a "Join Table". Nothing obvious to
me.
Usually say "Getting Nowhere Fast". With changing the KB search
criteria
"Got Nowhere Slow."

And more puzzling is Your sentence "A join table is used to
resolve
many
to
many relationships."

The tables are One-To-Many.

Thank You for taking the time to read this.

Andy

A join table is used to resolve many to many relationships.
Usually,
each
column in a join table comes from a different table. In this
case,
they
would both come from the inventory table.

:

Thank You for Your replies.

Wasn't sure how to reply to both answers and continue the
tread
for
Your
responses so copied/pasted both below.

For Dorian Yes do need to group ANY hat, blouse, dress into a
group.

For Klatuu: Not sure what is meant by "Both sides of the join
are
the
same
table".

Explaining further: (Note to both this explanation is
detailed
not
stuffed
with excess info. Asking a question not writting a Novel.)

There are three seperate groups of related tables. All of
the
tbls
are
record sources for forms and sub-forms.

First there is Products. The Products tables consist of:
tblProductCategory
tblProductSubCategory (related to tblProductCategory Many to
One)
Many
SubCategories In One Category.
tblProductInfo (related to tblProductSubCategory Many to One)
Many
Products
In One SubCategory.

Category SubCategory ProductInfo
(Name,Price,Description,Qty,
Etc)
Clothing Men's Hat
Men's Shirt
Men's Shoes
Woman's Blouse
Woman's Dress
Woman's Skirt
The Category is the Main Form, SubCategory is a SubForm in
the
Main
Form
and
ProductInfo is a SubForm in the SubCategory sfrm.

Next is the Order Form.
It contains two tables:
tblCustomer
tblProductsBought. (related to tblCustomer Many to One) Many
ProductsBought
In One Customer.
The tblProductsBought contain the same fields as the
tblSubCategory
listed
Click to expand...
Click to expand...
Click to expand...
 
A

Andy

Klatuu;

Through the years that I have been checking the News Groups noticed that You
have helped a lot of people. Wanted You to have this answer re: Multiple
Products in Suite Group.

Know it isn't the only answer also know it may not be the best answer
however IT DOES WORK.

Started by creating 3 tbls: tblProductCategory, tblProductSubCategory,
tblProducts.
Related tblProductCategory to tblProductSubCategory
and then tblProductSubCategory to tblProducts.

Created frmProductCategory w/sfrmProductSubCategory. In
sfrmProductSubCategory added sfrmProducts.
Limited the Category and SubCategory to company requirements.

This forces the user to work within the parameter of Category and
SubCategory, yet it allows the users to enter any Product Name, Product
Description Etc.

Next created tblCustomer, (frmCustomer) and tblProductsBought,
(sfrmProductsBought). The tblProductsBought contains the same fields as in
tblProducts.

Used 3 Combo Boxes in sfrmProductsBought using Microsoft's KB "Drill Down
Method" to pull the data from the tblProductCategory,
tblProductSubCategory, tblProducts.

Next created tblPackagedItemsCategory, (frmPackagedItemsCategory) and
tblPackagedItemsProducts, (sfrmPackagedItemsProducts) and the
tblPackagedItemsProducts contains the same fields as in tblProducts.

Created 3 Combo Boxes in sfrmPackagedItemsProducts and used the same "Drill
Down Method" to pull the data from the tblProductCategory,
tblProductSubCategory, tblProducts.

This allows the user to name the Package yet forces them to pull the
Products from the tblProducts.

Added a Combo Box to frmCustomers (cboPackagedItems). Its RowSource is
tblPackagedItemsCategory. And an AppendQry w/Criteria
frmCustomers!cboPackagedItems.

Because the PackagedItems does not have a relationship to the tblCustomers
added the field "frmCustomerID" to the AppendQryField Name
(frmCustomers!CustomerID) and appended that to the
tblProductsBought!CustomerID.

Finally in the cboPackagedItems AfterUpdateEvent added this code:
Private Sub cboPackagedItems_AfterUpdate()

Dim Msg, Style, Title, Help, Ctxt, Response, MyString

DoCmd.SetWarnings False
DoCmd.OpenQuery "qryAppendPackagedItems", acNormal, acEdit ',
strCriteria
DoCmd.SetWarnings True
Me![sfrmProductsBought].Requery ' Needed to Display the appended items
w/o having the user cycle the records.
Me.sfrmProductsBought.SetFocus ' This line and the next moves the Focus
to allow the user to enter the Quantity Purchased
DoCmd.GoToControl "txtProductNumberOfUnitsBought"
' Then added a message box to remind the user to enter the Quantity
Purchased.
Msg = MsgBox("You have selected a ''Packaged Item''." & vbCrLf & _
"Be sure to enter the number of Units Bought for Each Line Item" &
vbCrLf & _
"The Focus has been moved to the Units Bought Control.", vbOKOnly,
"Yahoo It Works.")

End Sub

Klatuu; It may not be the sweetest and it may not be the cleanest but IT
WORKS and it allows the user plenty of flexibility. They can select a
Packaged Item or Individual Items and/or Both.

There are some items not listed above such as Packaged Items Category
Description and had to set the txtCustomerId to Visible = No but thats it.

Enjoy the Holidays.

Andy.




Andy said:
Hi;

The boss wants to "Cluster" products in a group or "Suite" meaning
"Blouse, Dress, Hat" = Woman's Outfit.

Have an Order/Sales dbase.

Main Form = Customer data, (Name, Address, Phone)
sfrmProductsBought = Category, (Auto, Clothing, Furniture), Sub-Category,
(Blouse, Dress, Hat), ProductName, (Blue Shirt, Green Shirt, Red Shirt)

The Category, Sub-Category and ProductName are "Drill Down" Combo Boxes.
For the "Drill Down" followed Microsoft's help: Seperate tbls for each
Combo Box with the tbl Primary Keys Data Type set as text. Then created a
main frm for Category, an sfrm for Sub-Category and an sfrm in
sfrmSub-Category for the individual Products. All of this works
correctly.

When the ProductName is selected in the sfrmProductsBought the "After
Update" Event uses Me!s to enter the Description, Price Etc.
Example:
Me![txtProductDescription] = Me![cbxProductNameID].Column(1)
All of this works correctly.

The boss wants to "Cluster" products in a group or "Suite" meaning
"Blouse, Dress, Hat" = Woman's Outfit. Have seen this work in an
accounting program.

At first simply added a "Grouped Products" Category to tblCategory that
won't work in this instance.

Then tried this:
Created tbls: GroupedCategory and GroupedProducts. (One To Many
Relationship)

The tblGroupedCategory contains the name of the Group, (Men's Outfit,
Woman's Outfit)
tblGroupedProducts contains the same fields as "sfrmProductsBought"

The sfrmGroupedProducts uses the same Drill Down method and it all works
correctly.

Added a Combo Box to sfrmProductsBought that displays the GroupCategory
Names. Its Row Source Type is "Table/Query" and its Row Source is
tblGroupedCategory.

Tried using a Row Source of tblGroupedCategory and tblGroupedProducts but
that shows the same Grouped Category multiple times, (One to Many).
Set the qry Properties to "Unique Records" and it still shows Grouped
Category multiple times.

Have searched and searched for an example of how to copy the data from the
Grouped Products Tables to the sfrmProductsBought and haven't found
anything that is even close.

Would someone be so kind to point me in the correction direction.

Thank You for taking the time to read this post.

Andy
Click to expand...
 
Top