Nested Grouping and sums

[NoFrills]

I did a search for Gouping and sums, but nothing really helped me.

What I am trying to figure out: I have a table where I am trying to use 4 fields in a query.

Vendor, items, Cost, Date1

- I am trying to Group by Vendor, Sub-Group by Items (Easy, I did this)
- Now I am trying to Filter this by date1 (Easy, I did this)

But I would like to sum the Cost for the Groups, but not list every repeated item. For Example:

Vendor A     Brush     100     1/1/2005
Vendor C     Book      50       1/2/2005
Vendor A     Brush     100     1/3/2005
Vendor B     Pencil     10       1/4/2005
Vendor A     Hat        50       1/5/2005
Vendor B     Pencil     10       1/6/2005
Vendor B     Pen       10       1/7/2005
Vendor B     Eraser    10       1/8/2005
Vendor A     Shoe       40      1/9/2005
Vendor D     House     1000    1/10/2005

Doing a search from 1/1/2005 - 1/9/2005 we get:

FORM OUTPUT:

Vendor A     Brush      200       2
             Hat       50        1
             Shoe      40        1
Vendor B     Pencil     20        2
             Pen          10      1
             Eraser     10        1
Vendor C     Book        50       1

 

[namliam]

In that exact layout? Not possible in a query, you would need to make it a report.

Regards

 

[NoFrills]

Is that impossible cause I am eliminating the repeated Vendors. Is the grouping possible. If the vendors need to be repeated, I would do that if I could.

 

[namliam]

Is not possible:

Vendor A Brush 200 2
Hat 50 1
Shoe 40 1
Vendor B Pencil 20 2
Pen 10 1
Eraser 10 1
Vendor C Book 50 1

Is possible

Vendor A Brush 200 2
Vendor A Hat 50 1
Vendor A Shoe 40 1
Vendor B Pencil 20 2
Vendor B Pen 10 1
Vendor B Eraser 10 1
Vendor C Book 50 1

Select Vendor, items, Sum(Cost), Count(Cost)
from yourtable
where datething
Group by Vendor, items

Or something like that....