Summing problem

[Thone]

Hi,
I have a database on my products. I try to sum the
total sale on each one but the summing result is very
strange. I query the the all order and use group by to
group each category, then i use sum on the each sale each
order. But the result is much higer than what it should
be. What I do wrong with it. Thank you.

 

[Jeff Boyce]

Thone

Without more information about the fields you have, what tables the fields
are in, and the SQL statement you are using, it could be tough to diagnose.
We don't need your entire database, just a few more ideas about this part of
it.

More info, please...

Jeff Boyce
<Access MVP>

 

[Thone]

Hi,
I have tables contain fields like this [OrderDate]:
- Order Date
- Order Item
- Cost per Item
- Amount Bought

then I use query to obtain the following info [Q]:
- Order Date
- Order Item
- Total Cost (Cost per Item * Amount)

then I try to see the total cost for each month
- Month (month(OrderDate)) - Group By
- Total Cost Each Month (SUM(Total Cost))

I design them using query's design view, and in total
field i have "group by" for Month and "sum" for total cost
each month.


The SQL is:
SELECT Month([Q]![OrderDate]) AS [Month], Sum(Q.[Total
Cost]) AS [SumOfTotal Cost]
FROM (OrderDate INNER JOIN Q ON OrderDate.OrderDate =
Q.OrderDate) INNER JOIN ((Category INNER JOIN Item ON
Category.CategoryID = Item.CategoryID) INNER JOIN [Order]
ON Item.ItemID = Order.ItemID) ON OrderDate.OrderDate =
Order.OrderDate
GROUP BY Month([Q]![OrderDate]);

Thank you.

 

[Jeff Boyce]

Thone

You have a query [Q] that generates TotalCost for each OrderDate/OrderItem
row. You can modify that query to also generate Month([OrderDate]) -- NOTE:
if you have more than one year's data, you may want to use a date range
criterion for OrderDate to limit to a single year.

You can create a query against [Q] that generates summary data (a total),
grouped by Month.

Creating the second query lets you take it in steps.

Perhaps another newsgroup reader can offer a "combined" SQL statement...

Good luck

Jeff Boyce
<Access MVP>