how to get this output?

Haroldd · July 16th, 2003, 06:49 AM

hi,

i have 3 tables (clothes, employee and order) in an access db
they have the following attributes (they have more but these are all u need i thought)
clothes: [u]clothid</u>, description, price
employee: [u]employeeId</u>, location
Order: [u]orderid</u>, employeeid, clothid, totalAmount, totalPieces

now i wanna generate a report which is first sorted on location, within that it must be sorted on clothes.description

But when i have multiple orders for the same clothesid i'm getting also more rows for this piece of clothing in my report. All i want is the total per cloth.

I already tried some things with distinct and distinctrow...but somehow that stupid access ain't doing what i want it to do

i hope the text above made sense to anyone...:)

Jeff Mason · July 16th, 2003, 07:10 AM

Try GROUPing by the clothes.description:

Code:

SELECT e.location, c.description, SUM(o.totalAmount)
    FROM employee e
        INNER JOIN Order o ON e.employeeId=o.employeeId
        INNER JOIN clothes c ON o.clothid=c.clothid
GROUP BY e.location, c.description
ORDER BY e.location, c.description;

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

Haroldd · July 16th, 2003, 07:24 AM

this is the sql string which stands in access for my report. Sorry for the bad names but i had to translate them from dutch to english

SELECT DISTINCTROW clothes.clothesId, clothes.description, clothes.[price per article], Employee.EmployeeId, Employee.Location, First(order.orderId) AS [First from order_articleId], Sum(order.amount) AS [sum of amount], Sum(order.totalprice) AS [Sum of totalprice], First(order.employeeId) AS [first of order_EmployeeId]
FROM clothes INNER JOIN (Employee INNER JOIN order ON Employee.EmployeeId = order.EmployeeID) ON clothes.clothesid = order.clothesID GROUP BY Employee.Location, clothes.clothesID, clothes.description, clothes.[price per article], Employee.employeeID;