Query to show letters sent by date in a crosstab format

[carl.barrett]

Hi,

I'm back with the same question as I still can't get it to display
my data the way I want it to.

The table lists information about a perpetrator involved with an anti
social behaviour order. The table contains personal information about
the individual as well as (here is where the letter dates comes in:)
e.g.

Surname:
Forename:
DOB:
Address1:
Address2:
Address3:
Address4:
PostCode:
Date Poor Behaviour Letter Sent: 01/04/05
Date First Warning Letter Sent: 10/04/05
Date Second Warning Letter Sent: 20/04/05
Date Consult SS Letter Sent: 28/04/05
etc....
etc....

The structure of the system that surrounds this table is:

tblCase - this has a 1:M relationship with:
tblIncident - this has a 1:M relationship with:
tblIncidentPerpetrator

I would like a total of all letters sent to all perpetrators having
months across the top, the letter type down the left and a count/total
of them within, an example is shown below.

Jan Feb Mar Apr May
etc..
Date Poor Behaviour Letter Sent 30 15 10 43 12
Date First Warning Letter Sent 12 7 3 12 2
Date Second Warning Letter Sent 2 1 0 1 0
Date Consult SS Letter Sent 1 0 0 0 0

I have tried using a crosstab query (actually several of them) but
cannot get it to calculate and display the data the way I want it to.

I have even created a table containg the 12 months and tried to link it
to a query that shows my results as months instead of dates using:
format([<<Field>>],"mmm")

I have ran out of ideas and not even sure if what I'm trying to do is
possible using a query or crosstab. I may have to create an individual
query and report for each letter and put them all as subreports into
one report so that they appear on 1 page as 1 report.

Any help would be appreciated.

Regards.

CJB

[David Lloyd]

Carl:

One approach is to create a crosstab query for each of your four dates and
then union the four queries together to get the final query results.

The crosstab for Poor Behavior might look like the following:

TRANSFORM Count(Test4.PoorBehav) AS CountOfPoorBehav
SELECT "PoorBehavior" AS Expr1
FROM Test4
GROUP BY "PoorBehavior"
PIVOT Format([poorbehav],"mmm") In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug"," Sep","Oct","Nov","Dec");

You could create similar queries for the other three dates, and then union
each of the four queries together like the following:

Select * FROM QueryPB
UNION
Select * FROM QueryFW
UNION
Select * FROM QuerySW
UNION
Select * FROM QueryCon;

--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or warranties.
<ca**********@newcastle.gov.uk> wrote in message
news:11*********************@g43g2000cwa.googlegro ups.com...
Hi,

I'm back with the same question as I still can't get it to display
my data the way I want it to.

The table lists information about a perpetrator involved with an anti
social behaviour order. The table contains personal information about
the individual as well as (here is where the letter dates comes in:)
e.g.

Surname:
Forename:
DOB:
Address1:
Address2:
Address3:
Address4:
PostCode:
Date Poor Behaviour Letter Sent: 01/04/05
Date First Warning Letter Sent: 10/04/05
Date Second Warning Letter Sent: 20/04/05
Date Consult SS Letter Sent: 28/04/05
etc....
etc....

The structure of the system that surrounds this table is:

tblCase - this has a 1:M relationship with:
tblIncident - this has a 1:M relationship with:
tblIncidentPerpetrator

I would like a total of all letters sent to all perpetrators having
months across the top, the letter type down the left and a count/total
of them within, an example is shown below.

Jan Feb Mar Apr May
etc..
Date Poor Behaviour Letter Sent 30 15 10 43 12
Date First Warning Letter Sent 12 7 3 12 2
Date Second Warning Letter Sent 2 1 0 1 0
Date Consult SS Letter Sent 1 0 0 0 0

I have tried using a crosstab query (actually several of them) but
cannot get it to calculate and display the data the way I want it to.

I have even created a table containg the 12 months and tried to link it
to a query that shows my results as months instead of dates using:
format([<<Field>>],"mmm")

I have ran out of ideas and not even sure if what I'm trying to do is
possible using a query or crosstab. I may have to create an individual
query and report for each letter and put them all as subreports into
one report so that they appear on 1 page as 1 report.

Any help would be appreciated.

Regards.

CJB

[carl.barrett]

Hi David,

Thanks for the help, it worked a treat.

I was originally trying to do this as a couple of queries but obviously
not possible as I need one for each letter.

Thanks again.

Cheers.

CJB

David Lloyd wrote:

Carl:

One approach is to create a crosstab query for each of your four dates and
then union the four queries together to get the final query results.

The crosstab for Poor Behavior might look like the following:

TRANSFORM Count(Test4.PoorBehav) AS CountOfPoorBehav
SELECT "PoorBehavior" AS Expr1
FROM Test4
GROUP BY "PoorBehavior"
PIVOT Format([poorbehav],"mmm") In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug"," Sep","Oct","Nov","Dec");

You could create similar queries for the other three dates, and then union
each of the four queries together like the following:

Select * FROM QueryPB
UNION
Select * FROM QueryFW
UNION
Select * FROM QuerySW
UNION
Select * FROM QueryCon;

--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or warranties.
<ca**********@newcastle.gov.uk> wrote in message
news:11*********************@g43g2000cwa.googlegro ups.com...
Hi,

I'm back with the same question as I still can't get it to display
my data the way I want it to.

The table lists information about a perpetrator involved with an anti
social behaviour order. The table contains personal information about
the individual as well as (here is where the letter dates comes in:)
e.g.

Surname:
Forename:
DOB:
Address1:
Address2:
Address3:
Address4:
PostCode:
Date Poor Behaviour Letter Sent: 01/04/05
Date First Warning Letter Sent: 10/04/05
Date Second Warning Letter Sent: 20/04/05
Date Consult SS Letter Sent: 28/04/05
etc....
etc....

The structure of the system that surrounds this table is:

tblCase - this has a 1:M relationship with:
tblIncident - this has a 1:M relationship with:
tblIncidentPerpetrator

I would like a total of all letters sent to all perpetrators having
months across the top, the letter type down the left and a count/total
of them within, an example is shown below.

Jan Feb Mar Apr May
etc..
Date Poor Behaviour Letter Sent 30 15 10 43 12
Date First Warning Letter Sent 12 7 3 12 2
Date Second Warning Letter Sent 2 1 0 1 0
Date Consult SS Letter Sent 1 0 0 0 0

I have tried using a crosstab query (actually several of them) but
cannot get it to calculate and display the data the way I want it to.

I have even created a table containg the 12 months and tried to link it
to a query that shows my results as months instead of dates using:
format([<<Field>>],"mmm")

I have ran out of ideas and not even sure if what I'm trying to do is
possible using a query or crosstab. I may have to create an individual
query and report for each letter and put them all as subreports into
one report so that they appear on 1 page as 1 report.

Any help would be appreciated.

Regards.

CJB