Creating a Yearly Report

[cc143most]

I have a crosstab query that I need to turn into a yearly report with monthly
summaries. I want the layout to look like this:
Jan Feb Mar April May June July Aug Sept Oct
Nov Dec Total
Declined 1 0 5 8
Obtained 6 2 0 1

I cannot get the correct statement and have hunted all through the forum.
Basically, I need "If [Month]="Jan", [Declined] but I have no idea how to
word this correctly.

Thanks again to ALL of you who take the time to help us Newbies and Lost Ones!

 

[KARL DEWEY]

Try this, replacing the table and field names --
TRANSFORM Count([Jan 2009 Query].[Link #]) AS [CountOfLink #]
SELECT Left([Jan 2009 Query].[Consent Status], InStr([Jan 2009
Query].[Consent Status], " ")-1), Count([Jan 2009 Query].[Link #]) AS [Total
Of Link #]
FROM [Jan 2009 Query]
GROUP BY Left([Jan 2009 Query].[Consent Status], InStr([Jan 2009
Query].[Consent Status], " ")-1)
PIVOT Format([Jan 2009 Query].[YourDateField], "mmm")
In("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

 

[cc143most]

Forgive the question, but I am very new at this....

Is this a new crosstab, do I add it to my current crosstab, or do I put this
in my report?

I had combined two different crosstabs into one query so that I could access
the single query in my report (after learning that I couldn't use multiple
queries in my report).

Try this, replacing the table and field names --
TRANSFORM Count([Jan 2009 Query].[Link #]) AS [CountOfLink #]
SELECT Left([Jan 2009 Query].[Consent Status], InStr([Jan 2009
Query].[Consent Status], " ")-1), Count([Jan 2009 Query].[Link #]) AS [Total
Of Link #]
FROM [Jan 2009 Query]
GROUP BY Left([Jan 2009 Query].[Consent Status], InStr([Jan 2009
Query].[Consent Status], " ")-1)
PIVOT Format([Jan 2009 Query].[YourDateField], "mmm")
In("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

I have a crosstab query that I need to turn into a yearly report with monthly
summaries. I want the layout to look like this:
Jan Feb Mar April May June July Aug Sept Oct
Nov Dec Total
Declined 1 0 5 8
Obtained 6 2 0 1

I cannot get the correct statement and have hunted all through the forum.
Basically, I need "If [Month]="Jan", [Declined] but I have no idea how to
word this correctly.

Thanks again to ALL of you who take the time to help us Newbies and Lost Ones!

 

[cc143most]

Karl,

I tried this as a new query and got the following error:

Syntax error (missing operator) in query expression 'Left([Jan 2009
Query].[Consent Status], InStr([Jan 2009 Query].[Consent Status], " ")-1)'.

Thank you for your help! My boss is WELL aware that I could not come close
to meeting this challenge without those of you willing to help others in a
pinch!

Try this, replacing the table and field names --
TRANSFORM Count([Jan 2009 Query].[Link #]) AS [CountOfLink #]
SELECT Left([Jan 2009 Query].[Consent Status], InStr([Jan 2009
Query].[Consent Status], " ")-1), Count([Jan 2009 Query].[Link #]) AS [Total
Of Link #]
FROM [Jan 2009 Query]
GROUP BY Left([Jan 2009 Query].[Consent Status], InStr([Jan 2009
Query].[Consent Status], " ")-1)
PIVOT Format([Jan 2009 Query].[YourDateField], "mmm")
In("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

I have a crosstab query that I need to turn into a yearly report with monthly
summaries. I want the layout to look like this:
Jan Feb Mar April May June July Aug Sept Oct
Nov Dec Total
Declined 1 0 5 8
Obtained 6 2 0 1

I cannot get the correct statement and have hunted all through the forum.
Basically, I need "If [Month]="Jan", [Declined] but I have no idea how to
word this correctly.

Thanks again to ALL of you who take the time to help us Newbies and Lost Ones!

 

[KARL DEWEY]

Missing field name - Status_Type --
TRANSFORM Count([Jan 2009 Query].[Link #]) AS [CountOfLink #]
SELECT Left([Jan 2009 Query].[Consent Status], InStr([Jan 2009
Query].[Consent Status], " ")-1) AS Status_Type, Count([Jan 2009 Query].[Link
#]) AS [Total
Of Link #]
FROM [Jan 2009 Query]
GROUP BY Left([Jan 2009 Query].[Consent Status], InStr([Jan 2009
Query].[Consent Status], " ")-1)
PIVOT Format([Jan 2009 Query].[YourDateField], "mmm")
In("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

Karl,

I tried this as a new query and got the following error:

Syntax error (missing operator) in query expression 'Left([Jan 2009
Query].[Consent Status], InStr([Jan 2009 Query].[Consent Status], " ")-1)'.

Thank you for your help! My boss is WELL aware that I could not come close
to meeting this challenge without those of you willing to help others in a
pinch!

Try this, replacing the table and field names --
TRANSFORM Count([Jan 2009 Query].[Link #]) AS [CountOfLink #]
SELECT Left([Jan 2009 Query].[Consent Status], InStr([Jan 2009
Query].[Consent Status], " ")-1), Count([Jan 2009 Query].[Link #]) AS [Total
Of Link #]
FROM [Jan 2009 Query]
GROUP BY Left([Jan 2009 Query].[Consent Status], InStr([Jan 2009
Query].[Consent Status], " ")-1)
PIVOT Format([Jan 2009 Query].[YourDateField], "mmm")
In("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

I have a crosstab query that I need to turn into a yearly report with monthly
summaries. I want the layout to look like this:
Jan Feb Mar April May June July Aug Sept Oct
Nov Dec Total
Declined 1 0 5 8
Obtained 6 2 0 1

I cannot get the correct statement and have hunted all through the forum.
Basically, I need "If [Month]="Jan", [Declined] but I have no idea how to
word this correctly.

Thanks again to ALL of you who take the time to help us Newbies and Lost Ones!

 

[cc143most]

Karl,

I'm just not getting it! I got an error this time that there were improper
brackets around Total of Link #. I am simply copying and pasting your code
and changing my query name.

Let me back up and ask this. I have my table (two different pivot tables
combined) called 2009 Data. My columns are:
Month
Branch
Hospital
Total of Link #
Declined by Fam (multiple columns based on the second half of this reason)
Declined by Us (again, multiple columns)
Obtained by....(again, multiple columns)
Declined
RO
Obtained

I need all of this info on a report for the year. Jan - Dec are my columns
and the columns above are now my rows (with each hospital on a separate
page). Am I needing a new query of some sort or is there a way in my report
to say that I want the number of a particular reason for a particular month?

If you can tell me which way I need to go, I will spend all weekend reading,
studying, and playing with it. I've accomplished a great deal on this in a
month, but am very frustrated now. BTW, this raw data comes from another
outdated database that they tell me cannot do this kind of manipulation.
People are currently doing their reports by pen and calculator (manually
counting the numbers for each hosp/reason). They (programmers) are trying to
build a new database and we hope to use this as a "front".

Missing field name - Status_Type --
TRANSFORM Count([Jan 2009 Query].[Link #]) AS [CountOfLink #]
SELECT Left([Jan 2009 Query].[Consent Status], InStr([Jan 2009
Query].[Consent Status], " ")-1) AS Status_Type, Count([Jan 2009 Query].[Link
#]) AS [Total
Of Link #]
FROM [Jan 2009 Query]
GROUP BY Left([Jan 2009 Query].[Consent Status], InStr([Jan 2009
Query].[Consent Status], " ")-1)
PIVOT Format([Jan 2009 Query].[YourDateField], "mmm")
In("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

Karl,

I tried this as a new query and got the following error:

Syntax error (missing operator) in query expression 'Left([Jan 2009
Query].[Consent Status], InStr([Jan 2009 Query].[Consent Status], " ")-1)'.

Thank you for your help! My boss is WELL aware that I could not come close
to meeting this challenge without those of you willing to help others in a
pinch!

Try this, replacing the table and field names --
TRANSFORM Count([Jan 2009 Query].[Link #]) AS [CountOfLink #]
SELECT Left([Jan 2009 Query].[Consent Status], InStr([Jan 2009
Query].[Consent Status], " ")-1), Count([Jan 2009 Query].[Link #]) AS [Total
Of Link #]
FROM [Jan 2009 Query]
GROUP BY Left([Jan 2009 Query].[Consent Status], InStr([Jan 2009
Query].[Consent Status], " ")-1)
PIVOT Format([Jan 2009 Query].[YourDateField], "mmm")
In("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");



:

I have a crosstab query that I need to turn into a yearly report with monthly
summaries. I want the layout to look like this:
Jan Feb Mar April May June July Aug Sept Oct
Nov Dec Total
Declined 1 0 5 8
Obtained 6 2 0 1

I cannot get the correct statement and have hunted all through the forum.
Basically, I need "If [Month]="Jan", [Declined] but I have no idea how to
word this correctly.

Thanks again to ALL of you who take the time to help us Newbies and Lost Ones!

 

[KARL DEWEY]

I got an error this time that there were improper brackets around Total of
Link #. I am simply copying and pasting your code and changing my query name.
You notice that the copying and pasting adds hard returns where there should
not be. These must be edited out.
Example --
... AS Status_Type, Count([Jan 2009 Query].[Link
#]) AS [Total
Of Link #]
FROM [Jan 2009 Query]
-- edit to look like this --
... AS Status_Type, Count([Jan 2009 Query].[Link #]) AS [Total Of Link
#]
FROM [Jan 2009 Query]
I know nothing of Pivot tables.
My post query assumes that your table structure is this --
[Link #] - unknown
[Consent Status] - Text
[YourDateField] – DateTime datatype

Using multiple columns for status in my query will not work! You would need
to use a union query to pull the data together as I outlined above.