Need help generating a report

[jsccorps]

Data is in the following structure:

Type Location Number
I AK 4
I LA 10
I MI 5
II AK 7
II LA 0
II MI 14

I want to generate the following report:

Location Number of Type I Number of Type II
AK 4 7
LA 10 0
MI 5 14

Will I need to generate a new query or is their a standard way of doing this?

Thanks

 

[Duane Hookom]

You should be able to build a crosstab query with "Number of Type " & [Type]
as the Column Heading, Location as the Row Heading, and First of [Number] as
the Value. You might want to set the Column Headings property to:
Column Headings: "Number of Type I";"Number of Type II"

Base your report on the crosstab query.

 

[Rob Parker]

There is a standard way - it's called a crosstab query.

You can generate one using the crosstab query wizard (which will also give
you a column for Total Number at each Location), or you can build it in the
query design grid. To do that for your data, first make a select query with
each of your fields, then change the query to a crosstab query (either via
the Query menu, the query type button on the design toolbar, or via the
right-click menu in the top section of the design grid).
When you change the query type, rows for Total and Crosstab will appear in
the grid. Select Group By for the Type and Location fields, and Sum for the
Number field in the Totals row; select Column Heading for the Type field,
Row Heading for the Location field, and Value for the Number field in the
Crosstab row. And that's it.

If you view the query in SQL view, you will have something like:
TRANSFORM Sum(YourTableName.Number) AS SumOfNumber
SELECT YourTableName.Location
FROM YourTableName
GROUP BY YourTableName.Location
PIVOT YourTableName.Type;

If you want the column headings as you show in your post, enter the
following expression in the Field cell for the Type field:
"Number of Type " & [Type]
Access will automatically prefix this with Expr 1: in the design grid, and
the last line of the SQL statement will change to:
PIVOT "Number of Type " & [Type];

HTH,

Rob