query question

[David]

Hello All

I have a table that has the following data in it.

Production Date Size Break Type Location
2/1/07 11oz Other Pre-Filler
2/1/07 11oz Impact Capper
2/1/07 24oz Impact Worm Gear
2/1/07 11oz Thin Glass Rinser

What I want to do is create a report that list by date and grouped by Break
Type. So the report would look like this

Date Break Type Quanity
2/1/07 Impact 2
Other 1
Thin Glass 1

And another report that would group by location for example

Date Location Quanity
2/1/07 Pre-Filler 1
Capper 1
Worm Gear 1
Rinser 1

Can someone start me off in the right direction to setup a query to do this?


TIA
David

 

[Michel Walsh]

You can use the Report capabilities for it, based on the plain table (no
query), as long at all the columns you want are in one table.

The first report, GROUP on the production date, and Break Type, and use
COUNT(*) for quantity. Again, use the report group.



Sure, you can, INSTEAD, use a query:

SELECT productionDate, breakType, COUNT(*) As quantity
FROM tableName
GROUP BY productionDate, breakType


as source of your report.



For the second report, change breakType to Location.





Hoping it may help,
Vanderghast, Access MVP

 

[John Spencer]

Start a new query
-- Add your table
-- Add Production Date, Break Type, BreakType (again)
-- Select View: Total from the Menubar
-- Change GROUP BY to Count under the second Break Type
-- Save the query.

For Location, repeat the above, but use Location instead of Break Type

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[David]

Thank you so much Michael and John for the help