How do I format report from query results?

[Guest]

I have results of a query

Country Cat1 CountOfCat1
Italy Design 9
Italy Documentation 3
Italy Evidence 4
Italy Testing Exception 3


How can I take the results of the query to format the report to look like?

Country Design Documentation Evidence Test Except
Italy 9 3 4 3

Thanks,

Bob

 

[Allen Browne]

Use a crosstab query.

1. Create a query, using your existing query as in input "table."

2. Change it to a Crosstab query. (Crosstab on Query menu.)
Access adds Total and Crosstab rows to the query design grid.

3. Drag Country into the grid.
Accept Group By in the Total row under this field.
Choose Row Heading in the Crosstab row.

4. Drag Cat1 into the grid.
Accept Group By in the Total row.
Choose Column Heading in the Crosstab row.

5 Drag CountOfCat1 into the grid.
Choose Sum in the Total row.
Choose Value in the Crosstab row.

6. Open the Properties box (View menu.)
Looking at the properties of the query (not of a field), enter all the
possible Cat1 values as a delimited list, e.g.:
"Design"; "Documentation"; ...

7. Save the query.

8. Create the report based on this query.

Notes:
====
a) Step 6 is important where you use the query as a report. The columns that
end up in the report depend on which ones are actually used. If you don't
specify them, depending on the criteria, if the columns just disappear the
report won't work. Declaring the columns avoids this.

b) If you need to add criteria to the crosstab - such as
[Forms].[Form1].[TheDate] - you must declare them in the crosstab. Choose
Parameters on the Query menu, and enter the same names there.