Help with complex query (for me at least)

[GrindEspresso]

Hi, I have a query I'm trying to put together but I'm having difficulty. The results I'm trying to achieve are as follows:

 
Product, Type, Size, In Stock, At Factory, At Store
Slate, R, 1.7x91x190, 121, 44, 3
Slate, R, 1.7x89x210, 0, 1, 1
Marble, M, 2.2x100x100, 12,25,1

Size comes from three columns Thickness, Width and Length so the size above is concatenated together for display. The count fields (stock/factory/store) come from counting the rows for each thickness that has those widths and lengths.

I need to group the thickness/width/length count them and display them distinct. Any help would be much appreciated.

Thanks (sorry about the formatting)

[weekend00]

first, you'd better store the size in 3 seperate fields: width,length,height. then you can get what you need easily

[GrindEspresso]

If your referring to the table from which the data is coming, they are stored seperately. thickness, width, length.

[weekend00]

then you can use query to group by thickness,width,or length.

[GrindEspresso]

I have gotten this query so far but its not producing the results I want.

SELECT [Product], [Type], [thickness] & "x" & [Width] & "x" & [Length] AS [Size], [thickness], [width], [length], Count(thickness) AS [At Store],
FROM [Packing List]
GROUP BY [Product], [Type], [thickness], [width], [length], [Date]
HAVING ((([Date]) Between #8/20/2010# And #11/20/2010#));

I have tried this using SELECT DISTINCT and if I remove the count then I get one row for each thickness so how do I incorporate the count in the query and still only have one row for each thickness?

Any help appreciated

Regards

[weekend00]

you can't put thickness, width and length together, you need to write different queries for thickness, length and width.

for example, for thickness:
SELECT [Product], [Type], [thickness],Count(thickness) AS [At Store], FROM [Packing List] GROUP BY [Product], [Type], [thickness]
HAVING ((([Date]) Between #8/20/2010# And #11/20/2010#));