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...:)

Try GROUPing by the clothes.description:

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

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;