473,543 Members | 2,297 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Query to show letters sent by date in a crosstab format

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:
tblIncidentPerp etrator

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

Nov 13 '05 #1
2 2241
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.Poo rBehav) AS CountOfPoorBeha v
SELECT "PoorBehavi or" AS Expr1
FROM Test4
GROUP BY "PoorBehavi or"
PIVOT Format([poorbehav],"mmm") In
("Jan","Feb","M ar","Apr","May" ,"Jun","Jul","A ug","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**********@n ewcastle.gov.uk > wrote in message
news:11******** *************@g 43g2000cwa.goog legroups.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:
tblIncidentPerp etrator

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
Nov 13 '05 #2
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.Poo rBehav) AS CountOfPoorBeha v
SELECT "PoorBehavi or" AS Expr1
FROM Test4
GROUP BY "PoorBehavi or"
PIVOT Format([poorbehav],"mmm") In
("Jan","Feb","M ar","Apr","May" ,"Jun","Jul","A ug","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**********@n ewcastle.gov.uk > wrote in message
news:11******** *************@g 43g2000cwa.goog legroups.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:
tblIncidentPerp etrator

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


Nov 13 '05 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

8
7553
by: Donna Sabol | last post by:
First, I should start by saying I am creating a database to be used by some very impatient, non-computer literate people. It needs to be seameless in it's operation from their point of view. I want them to do as little as possible when they run their reports. I have a crosstab query that displays usage of items for each month. It looks...
1
2287
by: Steve | last post by:
I have looked through the newsgroup for an answer to this but haven't been able to find anything resembling my situation. What I want to do is relatively simple, I think. I have a crosstab query that is bound to a report I am creating. I would like the column headings to be the name of the correspoding field name. For example, I have...
4
8097
by: Judy | last post by:
I'm using Access 2003 and was wondering if it is possible to have a paramater selection within a crosstab query so that I wouldn't need to build a new table. I have a select query that I'm using to build the crosstab query from. In the select query I'm prompting for a specific quarter but when I go to run the crosstab it doesn't seem to...
6
2124
by: carl.barrett | last post by:
Hi, I have a table that Stores individual records about a person and the dates that a number of letters were sent to them, e.g. Date First Letter Sent: 01/04/05 Date Second Letter Sent: 10/04/05 Date Third Letter Sent: 20/04/05 And so on ... (there are 12 letters in total) I would like to create a query/(ideally a report) that would...
2
5946
by: Claus Haslauer | last post by:
Hi, I want to create a crosstab query that looks like this Date | Elevation 1 | Elevation 2 | ... ______________________________________________________________________ Date 1 | xx.y | xx.y Date 2 | xx.y | xx.y xx.y are measurements taken at different dates at different elevations. xx.y and elevation happen to be in the same format...
14
3472
by: Tina | last post by:
My employer tracks productivity/performance of clinicians (how much they bill) each week, its averages for the month, and the 6 months. These averages are compared to their expected productivity. However, the expectation changes - it may be 60% for a while, then change to 50%. Initially, I was averaging the expectation, along with the...
5
3504
by: Wired Hosting News | last post by:
I tried to be breif and give a scenario so as not to be overlooked because it was soooo long. Let me give you real world. I am a manufacturer of goods and produce 11 items that are distributed to 1800 stores of a national home improvement chain store. Every week I electronicaly receive an excel spreadsheet "inventory report" with 19,800...
2
13985
by: sixdeuce62 | last post by:
Hello, I am trying to create a query that will prompt me to enter the parameter value if beginning date and ending date. I have created everything I need in the query, but I have to manually go into SQL view to change the date range. This is what I have so far: TRANSFORM Count(.ID) AS CountOfID SELECT ., Count(.ID) AS FROM
8
5898
by: Paul H | last post by:
I want to base a form on a crosstab query. The query shows statistics for a user defined period. The column headings will look something like this: ClientID Month01 Month02 Month03 etc.. So if the user selects 3 months in the criteria form, there will be 3 (month) column headings, if he selects 6 months, the will be 6 headings.
0
7336
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
7582
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
0
7726
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
1
7329
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
3384
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
0
3385
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
1809
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
1
948
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
0
626
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.