Formula needed to display month by month data.

[Guest]

I am attempting to build a report that displays data about the month by month
count of deliveries at 12 different job sites. The report lists each site
vertically with the months of the year running horizontally.

So it looks something like this:

Jan Feb Mar Apr May Jun Jul Aug …etc.

Site 1

Site 2

Site 3

I have the underlying query set up to it groups by both site and month so
the query results look something like this.

JobSite Month DeliveryCount

Site 1 09 1
Site 2 01 1
Site 2 02 4
Site 2 04 3
Site 3 01 2
Site 3 02 1

… (hopefully you get the idea).
Some sites only get one or two deliveries a year and other sites get
multiple deliveries each month.

So here’s the question: What kind of expression do I use in the text box
under the individual months in order to display just the number of deliveries
that month for that site?

My own very lame attempt looked something like this for January:
If [Month]=01 then =[DeliveryCount], else = 0

This wasn’t worth a hoot. So if someone could help me figure out the correct
formula I’d me quite grateful.

Many thanks,
David

 

[Duane Hookom]

Change your query to a crosstab that has JobSite as the Row Heading,
Left(MonthName([Month]),3) as the Column Heading, and Count of deliveries as
the value. You can also set the Column Headings property to:
Column Headings: "Jan","Feb","Mar",..."Dec"

 

[Guest]

I recommend the following:

Add a field to the report query for Month.
Sort the report by site, date, month (I assume the delivery detail is one
per line)
Add a footer per month, do not add a header for date.
Add a detail line showing possibly an ID, but make the detail lines Visible
property = False
On the month footer list site name (hide duplicates = True), month value and
count of detail ID

You will get the following:

Jobsite Month Delivery Count
Site 1 09 1
Site 2 01 1
02 4
04 3
Site 3 01 2
02 1

If you want to compare year, you can add another column for Year and do the
same basic idea with possibly a years total.

Hope this helps.

 

[Guest]

Hi Duane,

Thanks so much. I followed your suggestion and got exactly what I needed.
I've not used crosstab queries until now and I see how valuable they can be.

I just have one issue with the results: the resulting cross tab table only
lists those job sites at which deliveries occurred throughout the year. I
have 12 sites and I know that that two of them got no deliveries the entire
year. I'm having trouble finding a way to include these programs with no data.

Any thoughts?

Many thanks,
David

 

[Duane Hookom]

You can enter all possible column headings into the Column Headings property
of the crosstab query.

--
Duane Hookom
MS Access MVP

Hi Duane,

Thanks so much. I followed your suggestion and got exactly what I needed.
I've not used crosstab queries until now and I see how valuable they can
be.

I just have one issue with the results: the resulting cross tab table only
lists those job sites at which deliveries occurred throughout the year. I
have 12 sites and I know that that two of them got no deliveries the
entire
year. I'm having trouble finding a way to include these programs with no
data.

Any thoughts?

Many thanks,
David

Change your query to a crosstab that has JobSite as the Row Heading,
Left(MonthName([Month]),3) as the Column Heading, and Count of deliveries
as
the value. You can also set the Column Headings property to:
Column Headings: "Jan","Feb","Mar",..."Dec"