Counting in several fields

[einsteinhelpme]

Suppose I have a table with some fields containing the codes (integer
numbers) of some products (brands) and another table

containing the products together with their codes, just like this:

ID PRODUCT NAME
----------------------
1 Adidas
2 Nike
3 Reebok
----------------------


IDF FIELD_1 FIELD_2 FIELD_3
-----------------------------------------------
1 1 2 0
2 1 3 2
3 3 1 0
4 2 0 0
5 3 2 1
-----------------------------------------------

I'd need a query that shows the products in columns and the count of
each brand in each field in rows, i.e.:

PRODUCT NAME FIELD_1 FIELD_2 FIELD_3
-------------------------------------------------------
Adidas 2 1 1
Nike 1 2 1
Reebok 2 1 0
-------------------------------------------------------

How can I do this?

Best regards,

Sergio.

 

[Duane Hookom]

Your base issue is an un-normalized table. You could create a union query to
normalize your second table and then link in another query to your first
table. Change the "another query" to a crosstab.

If you can't figure this out, come back with some actual table and field
names.

 

[einsteinhelpme]

Duane:

I can't figure out how the union query would solve my problem. Would
you be more specific?

Can you also tell me how the table should be designed for it to be
normalized? Suppose the first table is called PRODUCTS and the second
one is called CHOICE.

Thanks for your quick reply,

Sergio

Duane Hookom ha escrito:

 

[John Vinson]

Can you also tell me how the table should be designed for it to be
normalized? Suppose the first table is called PRODUCTS and the second
one is called CHOICE.

If you have three choices, your Field1, Field2, Field3, then they
should be in three ROWS, not three fields. "Fields are expensive,
records are cheap"!

John W. Vinson[MVP]

 

[Duane Hookom]

John is correct regarding fields vs records.

Your union query would be like:
==quniNormalChoice=======
SELECT ID, Field_1 as ID, "FIELD_1" as Colhead
FROM Choice
UNION ALL
SELECT ID, Field_2, "FIELD_2"
FROM Choice
UNION ALL
SELECT ID, Field_3, "FIELD_3"
FROM Choice;

Then create a crosstab based on quniNormalChoice that would use the Product
table.
TRANSFORM Val(Nz(Count([IDF]),0)) AS Expr1
SELECT [Product Name]
FROM Products INNER JOIN quniNormalChoice ON Products.ID =
quniNormalChoice.ID
GROUP BY [Product Name]
PIVOT quniNormalChoice.Colhead;

 

[einsteinhelpme]

I want to thank all of you for your advices. Before I could read your
last post I have worked out a solution. As you

previously said, table 2 (CHOICE), is ill-prepared for what I want to
do, so I had to create a union query to solve this.

This query would be:

SELECT Product_Name as PName, 1 as Order
FROM CHOICE, PRODUCTS
WHERE FIELD_1 = PRODUCTS.ID

UNION ALL

SELECT Product_Name as PName, 2 as Order
FROM CHOICE, PRODUCTS
WHERE FIELD_2 = PRODUCTS.ID


UNION ALL

SELECT Product_Name as PName, 3 as Order
FROM CHOICE, PRODUCTS
WHERE FIELD_3 = PRODUCTS.ID;

This query (lets name it ARRANGED_DATA) contains now the names of the
brands along with the order of appearance. (It's very

important the ALL keyword, which indicates that the query must include
duplicated values.)

Having done this, is easy to set up another query to obtain the final
results:

SELECT PName AS Products,
-sum(Order=1) AS Place_1,
-sum(Order=2) AS Place_2,
-sum(Order=3) AS Place_3
FROM ARRANGED_DATA
GROUP BY PName;

And that's all folks! Yes, I know it's very nasty the
-sum(BOOLEAN_EXPRESSION), but right now I can't figure out something

neater as a replacement for it (i.e. to count the YES occurrences in
the boolean expression). If you know something better,

I'd like to know about it.

I guess the solution you propose is better and I'll use it instead of
this one I listed here.

Thanks again for your time and concern.

Sergio


Duane Hookom ha escrito:

John is correct regarding fields vs records.

Your union query would be like:
==quniNormalChoice=======
SELECT ID, Field_1 as ID, "FIELD_1" as Colhead
FROM Choice
UNION ALL
SELECT ID, Field_2, "FIELD_2"
FROM Choice
UNION ALL
SELECT ID, Field_3, "FIELD_3"
FROM Choice;

Then create a crosstab based on quniNormalChoice that would use the Product
table.
TRANSFORM Val(Nz(Count([IDF]),0)) AS Expr1
SELECT [Product Name]
FROM Products INNER JOIN quniNormalChoice ON Products.ID =
quniNormalChoice.ID
GROUP BY [Product Name]
PIVOT quniNormalChoice.Colhead;

--
Duane Hookom
MS Access MVP

Duane:

I can't figure out how the union query would solve my problem. Would
you be more specific?

Can you also tell me how the table should be designed for it to be
normalized? Suppose the first table is called PRODUCTS and the second
one is called CHOICE.

Thanks for your quick reply,

Sergio

Duane Hookom ha escrito: