Count several lookup columns in a pivot table

[jannabe]

Hi,

I've created a form where a user has to enter a product and corresponding options and colours. He can enter 1 to 5 products with those additional options & colours. The product names and both options and colour lists are separate tables that i've inserted on the form as dropdownmenus with lookup fields.
So for instance:
Product1 - Option1 - Colour1
Product2 - Option2 - Colour2
...
Product5 - Option5 - Colour5
In total this makes 15 separate columns in my main table.

Now I would like to make a pivot table that shows for each product name the count of the chosen options and the chosen colours. I can't figure out how to do this since all three data are written accross five columns.

Thank you so much in advance for your help.

- a newbie -

[twinnyfo]

jannabe,

Please provide us the structure of your tables (all tables involved).

What you are asking for is definitely doable, but we need to understand your table structures, first.

[jannabe]

OK here are the table structures:

TProduct:
- ProductID (automatic)
- Productname (text)

TOption:
- OptionID (automatic)
- Product (lookup of TProduct.Produc tname en TProduct.Produc tID)
- Optionname (text)

TColour:
- ColourID (automatic)
- Colour (text)

TMain:
- OrderID (automatic)
- Product1 (lookup TProduct.Produc tname)
- Option1 (lookup TOption.Optionn ame)
- Colour1 (lookup TColour.Colour)
- Product2 (lookup TProduct.Produc tname)
- Option2 (lookup TOption.Optionn ame)
- Colour2 (lookup TColour.Colour)
- Product3 (lookup TProduct.Produc tname)
- Option3 (lookup TOption.Optionn ame)
- Colour3 (lookup TColour.Colour)
- Product4 (lookup TProduct.Produc tname)
- Option4 (lookup TOption.Optionn ame)
- Colour4 (lookup TColour.Colour)
- Product5 (lookup TProduct.Produc tname)
- Option5 (lookup TOption.Optionn ame)
- Colour5 (lookup TColour.Colour)

[twinnyfo]

What is tblMain used for? From a DB normalization standpoint, we would always recommend not using a "wide" table, but rather a tall one, in which each Order may have multiple products. According to your table, you are wasting resources when someone orders only one product, but you have even greater problems if someone orders more than five.

A proper way to do this would be to have an orders Table, which lists the OrderID and the CustomerID. Then, an OrderDetails Table that lists all the products ordered with that one order ID.

If you have something else going on that doesn't fit that description, please explain.

Either way, still doable, but this gets us moving in the right direction (DB structure, etc.) first, then we'll talk about operations.

[jannabe]

I've opted for this solution because there can be maximum 5 products ordered but it has to be clear which option and which colour is selected for each product.

So for instance my products are cars then I need to know for each car which option (eg. sports edition) and which colour (eg. blue) is selected.

[twinnyfo]

I keep trying to respond to this thread, but I am getting a network error.....

[twinnyfo]

jannabe,

At least I understand your structure, which helps. Of course, as a DB guy, my first response wil be, "But this would be so incredibly simple if your table was normalized..."

However, based on your current structure, the "concept" I will throw at you is to create five queries, one for each "set" of products, e.g.,

SELECT ProductX, OptionX, ColourX 
FROM TMain
WHERE ProductX Is Not Null;

Just change the "X" to the number of the Product Set.

Then, use a union query, which will produce your "tall" Table.

Use the results in an Aggregate Query (not a pivot table), and your results shoudl look like this:

[B][I]Product[/I][/B]  [B][I]Option[/I][/B]  [B][I]Colour[/I][/B]  [B][I]Count[/I][/B]
Car      Sports  Blue    1
Car      Sedan   Red     3
Widget   Deluxe  Silver  12
Widget   Custom  Green   312
Widget   Custom  Purple  75
etc.....

[twinnyfo]

BTW, been trying to provide sample of the UNION Query, but it keeps producing a network error (just the code for the Union Query).

[jannabe]

many many many thanks. At least now I know what to do :-)

[zmbd]

jannabe:
When you say: Product2 (lookup TProduct.Produc tname)
Do you mean that in the table, you are using a lookup field?

Are you publishing this database to a SharePoint Site?
Also what version of Access are you using?