Crosstabs queries

Hello,
I'm a newbie and have just setup my first sql server based database. I
access it was quite easy creating queries and attaching them to reports.
Moving from access to Access data project, I'm finding it difficult to create
crosstab queries.
Can anyone point me in the right direction, bearing in mind that the
database created is in SQL server 2000 with Access 2000 (ADP) Client.
Thanks in advance.
I'm pretty sure there is but try these following groups as they should have the
exact syntax:
news:microsoft.public.sqlserver.server
news:microsoft.public.sqlserver.misc
news:comp.databases.ms-sqlserver
news:bit.databases.mssql-l
"Hans" wrote:

> Hello,
> I'm a newbie and have just setup my first sql server based database. I
> access it was quite easy creating queries and attaching them to reports.
> Moving from access to Access data project, I'm finding it difficult to create
> crosstab queries.
> Can anyone point me in the right direction, bearing in mind that the
> database created is in SQL server 2000 with Access 2000 (ADP) Client.
> Thanks in advance.
|||Its not as easy as creating cross tab queries in Access... You may want to
use sub queries and use those results from the sub queries and turn it into a
case statement to display in the main select quary..! Since you havent give
any idea on what you are trying to cross tab... (columns names, tables,
criteria) its hard to give you a syntex...
"Hans" wrote:

> Hello,
> I'm a newbie and have just setup my first sql server based database. I
> access it was quite easy creating queries and attaching them to reports.
> Moving from access to Access data project, I'm finding it difficult to create
> crosstab queries.
> Can anyone point me in the right direction, bearing in mind that the
> database created is in SQL server 2000 with Access 2000 (ADP) Client.
> Thanks in advance.
|||There are several xtab reoprts that needto be created. Example of one that
need to be prepared is.
table : tbl_patientepisodes
fields : EpisodeID - Unique(Index)
DateRefRecved - Date
DateRefAccepted - Date
DateRefNotAccepted - Date
rpt format:-
Title: Referals <from> to <To>
Apr May Jun Jul Aug Sep Oct Nov Dec Jan Feb Mar
No of referals reveived
No Of Referals Accepted
No Not Accepted
This reports will be based on a start and end date specified by the user.
hope this helps.
"SQLDBA" wrote:
[vbcol=seagreen]
> Its not as easy as creating cross tab queries in Access... You may want to
> use sub queries and use those results from the sub queries and turn it into a
> case statement to display in the main select quary..! Since you havent give
> any idea on what you are trying to cross tab... (columns names, tables,
> criteria) its hard to give you a syntex...
> "Hans" wrote:
|||PS sorry forgot to mention a graph need to be prepared with the reports as well
"SQLDBA" wrote:
[vbcol=seagreen]
> Its not as easy as creating cross tab queries in Access... You may want to
> use sub queries and use those results from the sub queries and turn it into a
> case statement to display in the main select quary..! Since you havent give
> any idea on what you are trying to cross tab... (columns names, tables,
> criteria) its hard to give you a syntex...
> "Hans" wrote:
|||"Hans" wrote:

> There are several xtab reoprts that needto be created. Example of one that
> need to be prepared is.
> table : tbl_patientepisodes
> fields : EpisodeID - Unique(Index)
> DateRefRecved - Date
> DateRefAccepted - Date
> DateRefNotAccepted - Date
Perhaps extracting data thusly:
-- Date Referred counts by month
SUM(CASE WHEN MONTH(DateRefRecvd) = 1 THEN 1 else 0 END) as JanRefRcvdCount,
SUM(CASE WHEN MONTH(DateRefRecvd) = 2 THEN 1 else 0 END) as FebRefRcvdCount,
..
..
..
SUM(CASE WHEN MONTH(DateRefRecvd) = 12 THEN 1 else 0 END) as DecRfRcvdCount,
-- Date Acepteded counts by month:
SUM(CASE WHEN MONTH(DateRefAccepted) = 1 THEN 1 else 0 END) as
JanRefAcceptedCount,
SUM(CASE WHEN MONTH(DateRefAccepted) = 2 THEN 1 else 0 END) as
FebRefAcceptedCount,
..
..
..
SUM(CASE WHEN MONTH(DateRefAccepted) = 12 THEN 1 else 0 END) as
DecRfAcceptedCount,