By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,652 Members | 1,842 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,652 IT Pros & Developers. It's quick & easy.

Query/Report to show how many letters sent per month

P: n/a
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 display
the information from all the records in this table in this format:
Jan Feb Mar Apr May etc..
Date First Letter Sent 30 15 10 43 12
Date Second Letter Sent 12 7 3 12 2
Date Third Letter Sent 2 1 0 1 0
etc..

Any help would be hugely appreciated.

Kind Regards.

CJB

Nov 13 '05 #1
Share this Question
Share on Google+
6 Replies


P: n/a

Use the Crosstab Query Wizard.

ca**********@newcastle.gov.uk wrote:
I would like to create a query/(ideally a report) that would display
the information from all the records in this table in this format:
Jan Feb Mar Apr May etc..
Date First Letter Sent 30 15 10 43 12
Date Second Letter Sent 12 7 3 12 2
Date Third Letter Sent 2 1 0 1 0
etc..


Nov 13 '05 #2

P: n/a
Hi,

I have tried using a crosstab but can't seem to get the right fields to
return as I can only choose a maximum of 3 row headings and there are a
dozen date fileds.

There is one option for a column heading and again there are 12 date
fields.

Once agan, any more direction would be appreciated.

Regards.

CJB

Nov 13 '05 #3

P: n/a
What do your tables look like? It sounds as though you've got a denormalized
table with 12 date fields in it, labelled Letter1, Letter2, ... Letter12

Try using a UNION query to normalize that table, and then run the crosstab
wizard on that query:

SELECT Field1, Field2, "Date First Letter Sent" As WhichLetter, Letter1 As
LetterDate
FROM MyTable
UNION
SELECT Field1, Field2, "Date Second Letter Sent" As WhichLetter, Letter2 As
LetterDate
FROM MyTable
....
UNION
SELECT Field1, Field2, "Date Twelfth Letter Sent" As WhichLetter, Letter12
As LetterDate
FROM MyTable

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

<ca**********@newcastle.gov.uk> wrote in message
news:11**********************@g44g2000cwa.googlegr oups.com...
Hi,

I have tried using a crosstab but can't seem to get the right fields to
return as I can only choose a maximum of 3 row headings and there are a
dozen date fileds.

There is one option for a column heading and again there are 12 date
fields.

Once agan, any more direction would be appreciated.

Regards.

CJB

Nov 13 '05 #4

P: n/a
The _wizard_ will only allow 3 "Row headings", but once the wizard has
completed it's work, feel free to modify the query to add in as many as
you need (Remembering the 255 column limit for _all_ the columns,
including the 12 date fields).

ca**********@newcastle.gov.uk wrote:
I have tried using a crosstab but can't seem to get the right fields to
return as I can only choose a maximum of 3 row headings and there are a
dozen date fileds.
There is one option for a column heading and again there are 12 date
fields.
Once agan, any more direction would be appreciated.


Nov 13 '05 #5

P: n/a
Hi,

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:)

Date Poor Behaviour Letter Sent
Date First Warning Letter Sent
Date Second Warning Letter Sent
Date Consult SS Letter Sent
etc....
etc....

Not all the date fields are populated as an issue may be resolved early
on, but I would like a total of all letters sent by all perpetrators
displayed by having months across the top, the letter type down the
left and a count/total of them within.

I hope this makes more sense.

Regards.

CJB

Nov 13 '05 #6

P: n/a
Hi,

Thanks for the response. When I include all the letters as row
headings I can still only select one as a column heading. I need the
total number of all letters sent for all people with Jan - Dec across
the top.

I appreciate what your saying but I can't seem to get what I want.

Any more direction would be appreciated.

Regards.

CJB

Nov 13 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.