Rows from left to right

  • Thread starter Thread starter Jane
  • Start date Start date
J

Jane

Hi,

I am running many reports where the query runs for feilds
across a date range, ie


01/02 02/02 03/02 04/02 05/02
Feild 1: 1 3 5 6 9

I cannot find a way where the rows of the query will
display in this format.

Thanks

J
 
There are many aspects to your question.

1. Assuming a field named SaleDate, type this into the Field row of your
query:
TheMonth: Format([SaleDate], "mm\/yy")

2. Change to a Crosstab query (Crosstab on Query menu).
Access adds Crosstab and Total rows to the grid.
Under the field above, choose "Column Heading" for Crosstab.

3. Drag your "Feild" into the grid.
Choose "Row Heading" in the Crosstab row under this field.

4. Assuming you want to total Amount for the Feild for the month in each
column, drag the Amount column into the grid. At the Total row, choose Sum.
At the Crosstab row choose Value.

The crosstab query gives you the output you want, but the names of the
columns changes depending on the date range. That makes it difficult to use
the crosstab as the source for a report. You can set the Column Headings
property of the crosstab query so it has all possible values, and provide
columns for all of them on your report. Alternatively, you can dynamically
assign the ControlSource of the text boxes on your report in its Open event
by examining the Fields of the QueryDef.
 
Hi Allen

Thanks very much for this, it works perfectly.
-----Original Message-----
There are many aspects to your question.

1. Assuming a field named SaleDate, type this into the Field row of your
query:
TheMonth: Format([SaleDate], "mm\/yy")

2. Change to a Crosstab query (Crosstab on Query menu).
Access adds Crosstab and Total rows to the grid.
Under the field above, choose "Column Heading" for Crosstab.

3. Drag your "Feild" into the grid.
Choose "Row Heading" in the Crosstab row under this field.

4. Assuming you want to total Amount for the Feild for the month in each
column, drag the Amount column into the grid. At the Total row, choose Sum.
At the Crosstab row choose Value.

The crosstab query gives you the output you want, but the names of the
columns changes depending on the date range. That makes it difficult to use
the crosstab as the source for a report. You can set the Column Headings
property of the crosstab query so it has all possible values, and provide
columns for all of them on your report. Alternatively, you can dynamically
assign the ControlSource of the text boxes on your report in its Open event
by examining the Fields of the QueryDef.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.
Hi,

I am running many reports where the query runs for feilds
across a date range, ie


01/02 02/02 03/02 04/02 05/02
Feild 1: 1 3 5 6 9

I cannot find a way where the rows of the query will
display in this format.

Thanks

J
Click to expand...


.
Click to expand...
 
Back
Top