If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Need help generating a report
Data is in the following structu
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 |
#2
|
|||
|
|||
Need help generating a report
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. -- Duane Hookom Microsoft Access MVP "jsccorps" wrote: Data is in the following structu 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 |
#3
|
|||
|
|||
Need help generating a report
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 "jsccorps" wrote in message ... Data is in the following structu 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 |
Thread Tools | |
Display Modes | |
|
|