Query On Query issue

[colinxt]

The database is in the attachment.

Problem decription:

Determine waht percentage of off-season sales revenue each product category accounts for.
Off-season is 1/1/2005-3/1/2005 and 10/15/2005 - 12/31/2005.
The final query is named Check 12 and should list two fields: Product Category and a field named % of Off-Season Revenue.
There should be only one record per category.
Format the percentage field as percent with two decimal.

What I did is creating a query named offseasonrevenue to calculate the revenues meet the criteria and creating a query named wholesalerevenue to calculate all the revenue. For the final query, I used this equation to calculate the percentage: [offseasonrevenue]/[wholerevenue].
However, the result turned out strang. One category has many percentage listed. If I use total->sum to group them, there will appear percentages over 100%. But in the same datebase->Check 11, I did the same thing but turns out to be true.

So, how can I group the percentage like one record per category and not seeing the strang number? Why I did the same thing in the query Check 11 but with different results?

Thank you for your time.

 

[colinxt]

Nobody knows how？

 

[John Big Booty]

Try saving your DB back to '03 not everyone has '07

 

[Simon_MT]

You are grouping OffSeasonRevenvue by Prodcut Category but then "exploding" the values because there are many Products within each Product Category. I would normalise the Product Categories by creating a Table then use a Foreign Key from Products to Product Categories. Then run your query against this table not Products. There are other ways but this is more functional.

Simon

 

[colinxt]

You are grouping OffSeasonRevenvue by Prodcut Category but then "exploding" the values because there are many Products within each Product Category. I would normalise the Product Categories by creating a Table then use a Foreign Key from Products to Product Categories. Then run your query against this table not Products. There are other ways but this is more functional.

Simon

I guess your advise is available but according to the problem it need not a new table. Perhaps there is an another way. Thanks.

 

[Rabbie]

Is this a class assignment??

 

[Simon_MT]

Whoever is setting the question is using a non-normalised database and should be spanked.

Simon

 

[colinxt]

Is this a class assignment??

Not exactly. It is for the extra credit. And I have tried out three chances to get the full score. But I'm curious about the right answer.

 

[colinxt]

Whoever is setting the question is using a non-normalised database and should be spanked.

Simon

Haha, that's interesting cuz that is created by our teacher.
If I create a table like you said, what field would I need? And what do you mean of the "use the foreign key from product to product category"?

 

[Simon_MT]

Change your Product Catagory to ProductCategoryID or something similar using your our naming Conventions.

Create a Table for Product Categories

Fields:

Product Category ID
Product Category Description

Change the ProductCategoryID in Products to correspond the ID in the Table Product Categories. To get your desired results run the existing Queries against this Table and the number of Records will correspond and bingo you have a normalised database.

The point of normalisation is any repetitive descriptive information is stored in a Foreign Table so that is can be used over and over again. Should any changes be made these will be reflected through your database.

Simon