B
beaker1167
I have tables that keeps track of sales data by item#, quanity and price. I
have 5 departments and many subdepartments within those. (i.e. Clothing -
Men's, Womens, Boys, Girls and Infants). I record daily sales per
subdepartment, and there are no duplications on a daily basis. I can run a
long report showing subdepartment totals and then department totals with a
grand total at the end. This report is correct.
I created a crosstab query with the departments being the columns and weekly
sales being the rows. This data is incorrect due to duplicates during the
week. How can I fix this?
Example:
Monday Sales:
Clothing Sports Garden
400 * $4.00 200 * $10.00 600 * 2.00
200 * 10.00 100 * 7.50 90 * 15.00
The different rows in the above is the subdepartments
Wednesday Sales:
Clothing
300 * 4.00 100 * 7.50 400 * 2.00
200 * 10.00 80 * 7.50 85 * 15.00
With the above data my sales report would produce:
Mens $1,600 (400*4.00)
Womens $2,000 (200*10.00)
Clothing $3,600
Hockey $2,000 (200*10.00)
Hunting $ 750 (100*7.50)
Sporting $2,750
And so forth...
Now my weekly crosstab report would not include:
Wednesdays Clothing 200*10.00 or the Sporting 100*7.50 due to duplicates for
that week. If I add the field of items numbers to the query my results are
way off. I believe I would get the $6,800 for Clothing for the week but
repeated and added for each time an item# is used.
Please help.
have 5 departments and many subdepartments within those. (i.e. Clothing -
Men's, Womens, Boys, Girls and Infants). I record daily sales per
subdepartment, and there are no duplications on a daily basis. I can run a
long report showing subdepartment totals and then department totals with a
grand total at the end. This report is correct.
I created a crosstab query with the departments being the columns and weekly
sales being the rows. This data is incorrect due to duplicates during the
week. How can I fix this?
Example:
Monday Sales:
Clothing Sports Garden
400 * $4.00 200 * $10.00 600 * 2.00
200 * 10.00 100 * 7.50 90 * 15.00
The different rows in the above is the subdepartments
Wednesday Sales:
Clothing
300 * 4.00 100 * 7.50 400 * 2.00
200 * 10.00 80 * 7.50 85 * 15.00
With the above data my sales report would produce:
Mens $1,600 (400*4.00)
Womens $2,000 (200*10.00)
Clothing $3,600
Hockey $2,000 (200*10.00)
Hunting $ 750 (100*7.50)
Sporting $2,750
And so forth...
Now my weekly crosstab report would not include:
Wednesdays Clothing 200*10.00 or the Sporting 100*7.50 due to duplicates for
that week. If I add the field of items numbers to the query my results are
way off. I believe I would get the $6,800 for Clothing for the week but
repeated and added for each time an item# is used.
Please help.