Combining category and subcategory

[Jill]

I have a table of several thousand products in several 100 categories. One
field is "category". Each product is listed twice, once with it's parent
category, and once with it's subcategory. Instead of:


product | category
product | subcategory

I need to change it to:

product | category | subcategory

and eliminate the duplicate (other than category) records.

Is there an easy way to do this, other than category-by-category? I'm using
Access 2007.

Thanks, Jill~

 

[PvdG42]

I have a table of several thousand products in several 100 categories. One
field is "category". Each product is listed twice, once with it's parent
category, and once with it's subcategory. Instead of:


product | category
product | subcategory

I need to change it to:

product | category | subcategory

and eliminate the duplicate (other than category) records.

Is there an easy way to do this, other than category-by-category? I'm
using Access 2007.

Thanks, Jill~

What is the relationship between Product, Category and Subcategory?
In the most common case, the situation would be that you have a set of
products, each uniquely identifiable (ProductID, perhaps). You also have
categories and subcategories of products as ways of identifying subgroups.
Category and Subcategory become fields in your Product table. You have 3
tables, Product, Category and Subcategory with one-to-many relationships
between Category and Product, and between Subcategory and Product.
These tables and relationships allow you to easily write queries to answer
questions such as:
Show all products in category A
What is the subcategory of product ID 10199?
etc.

 

[pietlinden]

What is the relationship between Product, Category and Subcategory?
In the most common case, the situation would be that you have a set of
products, each uniquely identifiable (ProductID, perhaps). You also have
categories and subcategories of products as ways of identifying subgroups..

I thought Subcategory was entirely dependent on Category. Then each
product belonged to a subcategory, which automatically implied a
Category.... like this:

Category---(1,M)----Subcategory---(1,M)----Product

 

[John Spencer]

The problem is how to determine which value in the "Category" field is the
category and which is the subCategory. How do you determine from the
information in the record which values are the category and which values are
the sub-category.

If the only assumption you can make is that the value that comes first
(alphabetically) is the Category and the value that comes second is the
sub-category then this would work. An unlikely solution.
SELECT A.Product, A.Category, B.Category
FROM [YourTable] as A INNER JOIN [YourTable] as B
ON A.Product = B.Product
WHERE A.Category < B.Category

If you have a list of which values are categories, then you could use that to
help you construct a query to give you the desired results.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[Jill]

The table that these are coming from isn't set up very well.

The primary key is an autonumber, which allows the product IDs to be listed
more than once. Category and subcategory are not differentiated, they are
all in the same field, just in different records. So we have

[productID] [category]

product1 - Furniture
product1 - Chairs

The only way I know what is a parent category and what is a subcategory is
because I have a list of them - there is no way to tell in the product
table.

Something tells me this is going to be a one-cat-or-sub-at-a-time thing,
huh?




I grouped the productIDs by count and made a new table "one-of-each".

What is the relationship between Product, Category and Subcategory?
In the most common case, the situation would be that you have a set of
products, each uniquely identifiable (ProductID, perhaps). You also have
categories and subcategories of products as ways of identifying subgroups.

I thought Subcategory was entirely dependent on Category. Then each
product belonged to a subcategory, which automatically implied a
Category.... like this:

Category---(1,M)----Subcategory---(1,M)----Product

 

[John Spencer]

PERHAPS you can use something like the following:

SELECT A.Product, A.Category, B.Category as SubCategory
FROM [YourTable] as A INNER JOIN [YourTable] as B
ON A.Product = B.Product
WHERE A.Category in (SELECT Category FROM ListOfCategories)
AND B.Category <> A.Category

An alternative
SELECT A.Product, A.Category, B.Category as SubCategory
FROM [YourTable] as A INNER JOIN [YourTable] as B
ON A.Product = B.Product
WHERE Exists (SELECT * FROM ListOfCategories WHERE Category = A.Category)
AND B.Category <> A.Category


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

The problem is how to determine which value in the "Category" field is
the category and which is the subCategory. How do you determine from
the information in the record which values are the category and which
values are the sub-category.

If the only assumption you can make is that the value that comes first
(alphabetically) is the Category and the value that comes second is the
sub-category then this would work. An unlikely solution.
SELECT A.Product, A.Category, B.Category
FROM [YourTable] as A INNER JOIN [YourTable] as B
ON A.Product = B.Product
WHERE A.Category < B.Category

If you have a list of which values are categories, then you could use
that to help you construct a query to give you the desired results.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

I have a table of several thousand products in several 100 categories.
One field is "category". Each product is listed twice, once with it's
parent category, and once with it's subcategory. Instead of:


product | category
product | subcategory

I need to change it to:

product | category | subcategory

and eliminate the duplicate (other than category) records.

Is there an easy way to do this, other than category-by-category? I'm
using Access 2007.

Thanks, Jill~