Need help in Monthly Query Showing Months Name in Columns

[FA]

I would like to create a query that runs on monthly basis showing the
data in the the following format

January February March April May ........... Decemeber
Under each Month i want to have
Number of Systems as rows.

I am fairly new to the queries, please if someone can help. I have the
following query so far but i like to have a cross tab showing Months
name in the column and Number of Systems in the row.

SELECT Count(dbo_SYS.SYS_NME) AS NumberofSystems,
dbo_SYS.SYS_DATE,Year([SYS_DATE]) AS Year
FROM dbo_SYS
GROUP BY dbo_SYS.SYS_NME, dbo_SYS_INFO.SYS_DATE,Year([SYS_DATE])

Thanks alot

 

[Rick Brandt]

I would like to create a query that runs on monthly basis showing the
data in the the following format

January February March April May ........... Decemeber
Under each Month i want to have
Number of Systems as rows.

I am fairly new to the queries, please if someone can help. I have the
following query so far but i like to have a cross tab showing Months
name in the column and Number of Systems in the row.

SELECT Count(dbo_SYS.SYS_NME) AS NumberofSystems,
dbo_SYS.SYS_DATE,Year([SYS_DATE]) AS Year
FROM dbo_SYS
GROUP BY dbo_SYS.SYS_NME, dbo_SYS_INFO.SYS_DATE,Year([SYS_DATE])

Thanks alot

Open the new query wizard. One of the choices is "crosstab" query. That
kind of query does exactly what you are looking for.

 

[Allen Browne]

Use a crosstab query to get this kind of output.

1. Create a query, using this table.

2. Choose Crosstab on the query menu.
Access adds a couple more rows to the design grid.

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

4. In the next column in the Field row, enter:
Year([SYS_Date])
Accept Group By in the Total row.
Choose Row Heading in the Crosstab row.

5. In the next column in the Field row, enter:
Month([SYS_Date])
Accept Group By in the Total row.
Choose Column Heading in the Crosstab row.

6. Drag SYS_NME into the grid again.
Choose Count in the Total row.
Choose Value in the Crosstab row.

7. (Optional) In the Properties of the query, beside the Column Headings
property, enter the numbers 1, 2, 3, ... 12.

The query will output a row for each SYS_NME in each year.
The columns will be 1 to 12 (the month number).
The intersection point will be the count of the name for that month of that
year.

 

[FA]

I am encountering an error "Data type mismatch in the critaria
expression"
I want the column heading names as name of months, when i take out the
column names, it gives me the right result but column headings as 1,
2,,,. Can You please tell me what i am doing wrong?
Also is there any way i can add a YearToDate Number of System Column
heading As YTD having Sum of all the count of system in each month.

TRANSFORM Count(dbo_SYS.SYS_NME) AS CountOfSYS_NME
SELECT Year([SYS_Date]) AS Expr1
FROM dbo_SYS_INFO
GROUP BY Year([SYS_Date])
PIVOT Month([SYS_Date]) In
("January","February","March","April","May","June","July","August","September","October","November","Decemeber");

Thanks

 

[Allen Browne]

The mismatch is because Month() returns the month number, but you typed the
month names into the Column Headings property.

 

[FA]

Ok I think i will have to stick with the Month Number. Thanks Bunch for
everything. Also my YTD question is still unresolved, can you please
let me know how to i add a column YTD which give me total number or
system till today.

Thanks again

Moe

 

[Allen Browne]

You want to add a year-to-date column to the crosstab query?

This will involve typing an expression into a fresh colum in the Field row.
In the Total row, choose Sum
In the Crosstab row, choose Row Heading.
The expression YTD total will appear to the left of the columns.

The expression will be something like this:
YTD: Nz([1],0) + Nz([2],0) + ... + Nz([12],0)

That assumes that there are no future values, so the YTD is just the sum of
the columns. If you need this working for previous years, you will need to
use IIf() to calculate whether the value in the column is before or during
today's month, i..e Month(Date()).