some help please

[Guest]

hey all,

I have an un-normalized table named Products which can have several
categories associated with it. Four to be exact.

So I have in this table four different fields that store the same type of
information. i know, iknow...a no, no. But that's what I inherited. But
anyway.

so, here's an example
CategoryID's are:
1=Electronics
2=Household
3=Bath
4=Automotive


Product 1 stores 4,1,null,null
Product 2 stores 1,4,null,null
Product 3 stores 1,2,null,null

What i need to know how to do is: out of a subset of Products i need know
what the percentage of each type is out of that subset. Can someone please
show me some ideas on how to solve this in query?

 

[Tom Ellison]

Dear Rod:

You can make the table appear as though you had the data normalized. You
can work from this. It will never be as fast and efficient as having it
normalized, but it will at least allow you to query things with some
efficiency of code, if not performance.

This will produce the appearance of the dependent table that you should have
had:

SELECT Product, 1 AS Seq, CategoryA AS Category
FROM YourTable
WHERE CategoryA IS NOT NULL
UNION ALL
SELECT Product, 2 AS Seq, CategoryB AS Category
FROM YourTable
WHERE CategoryB IS NOT NULL
UNION ALL
SELECT Product, 3 AS Seq, CategoryC AS Category
FROM YourTable
WHERE CategoryC IS NOT NULL
UNION ALL
SELECT Product, 4 AS Seq, CategoryD AS Category
FROM YourTable
WHERE CategoryD IS NOT NULL

This query will appear like what the missing table should. You can query
this efficiently. Some day, you can use this to produce the data for the
missing table as well.

In the above, you need to correct the column names and the table name for
what you actually have.

Tom Ellison

 

[Guest]

Tom,

That's very cool thank you, so now I have 28 different kinds of categories
what's the best way to get percentages for each of the categories that exist?

 

[Tom Ellison]

Dear Rod:

You should be able to create a cross-tab query based on the query I
provided. Give it a try, OK, and let me know how it goes.

Tom Ellison

 

[Guest]

ok i might need some practice on the cross-tab query but this is what i did
so far: i create that first query you showed me and save it (so that's what a
union query). then i created a new query that used the union query and it
worked.

does a cross-tab query let you do it in one query?

rodchar

 

[Guest]

Here's the result i needed and got with a regular query based of the union
query.

Category
1 41%
2 20%
3 9%
4 10%
5 1%
....


Could i have done this with a cross-tab query?

 

[Tom Ellison]

Dear Rod:

The query I provided puts the data into the appearance of being properly
organized. This is called normalization.

Once the data has a normalized appearance, it is not difficult to work with
it.

A cross-tab query requires properly normalized data. So, no, you couldn't
do this from non-normalized data using a crosstab.

Tom Ellison