Re: Query by Month field for the Year

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

TL - 13 Mar 2008 20:01 GMT

I would like to set-up a query that will extract the date for January based
on the Date field being between 1/1/2008 and 1/31/2008. This should be a
continuous process through December. The year is associated with two amounts
that I want to be the total of the 2 fields added together for the entire
month.

My ending results should be:

Month Amount
Jan (1/1/2008 to 1/31/2008) (Amount_1 + Amount_2)
Feb
Mar
Apri
May
Jun

How do I build a query to group my months by rows? I thought it would be
useful to implement a CrossTab, but no sure if I could based on the query
requirements.
Please help me!

Thanks,
TL

Reply to this Message

John Spencer - 13 Mar 2008 20:27 GMT

SELECT Format(DateField, "mmm") as MonthName
, Sum(Nz(Amount_1,0) + Nz(Amount_2,0)) as Amount
FROM YourTable
WHERE DateField Between #1/1/2008# and #12/31/2008#
GROUP BY Month(DateField), Format(DateField, "mmm")
ORDER BY Month(DateField)

Signature

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
.

>I would like to set-up a query that will extract the date for January based
> on the Date field being between 1/1/2008 and 1/31/2008. This should be a
[quoted text clipped - 20 lines]
> Thanks,
> TL

Reply to this Message

TL - 13 Mar 2008 20:56 GMT

John,

I tried to apply your code in the query but it was unsucessful. Are you
using a Crosstab query. Please see my SQL below from a Crosstab query. When I
plugged your query in and pointed to my field names it requireds entry. I
would like to just display the data by month with totalling up the two fields
for the month. Can is this possible?

Query used in Crosstab produces desired out come but I can not figure out
how the numbers are being totalled:

TRANSFORM Sum(qryALLDisbursments.Additional_Award) AS SumOfAdditional_Award
SELECT qryALLDisbursments.Amount_Requested,
Sum(qryALLDisbursments.Additional_Award) AS [Total Of Additional_Award]
FROM qryALLDisbursments
GROUP BY qryALLDisbursments.Amount_Requested
PIVOT Format([Date_Processed],"mmm") In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

> SELECT Format(DateField, "mmm") as MonthName
> , Sum(Nz(Amount_1,0) + Nz(Amount_2,0)) as Amount
[quoted text clipped - 27 lines]
> > Thanks,
> > TL

Reply to this Message

John Spencer - 13 Mar 2008 22:05 GMT

Which two fields are you trying to total? IF Additional Award and
Amount_requested are the fields and the fields are never null (blank)
then I would expect you to use

TRANSFORM SUM(Additional_Award + Amount_Requested) as TotalAmount
SELECT "A" as RowHeader
Sum(Additional_Award) AS [Total Of Additional_Award]
,SUM (Amount_Requested) as TotalRequest
FROM qryALLDisbursments
GROUP BY "A"
PIVOT Format([Date_Processed],"mmm") In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

> John,
>
[quoted text clipped - 46 lines]
>>> Thanks,
>>> TL

Reply to this Message

TL - 14 Mar 2008 01:20 GMT

John,

It appears to work! But, I am having a difficult time understanding so I
will just have to practice more to get a better understanding of how Crosstab
queries work and how to create them to produce desired data.

Thanks,
TL

> Which two fields are you trying to total? IF Additional Award and
> Amount_requested are the fields and the fields are never null (blank)
[quoted text clipped - 66 lines]
> >>> Thanks,
> >>> TL

Reply to this Message

TL - 13 Mar 2008 20:31 GMT

In addition, I used a CrossTab just playing around with it and I got some
pretty descent results expect I think they are backwards. My objective is to
take the sum of two fields and place the total in the Month field. Please
take a look:

TRANSFORM Sum(qryALLDisbursments.Additional_Award) AS SumOfAdditional_Award
SELECT qryALLDisbursments.Amount_Requested,
Sum(qryALLDisbursments.Additional_Award) AS [Total Of Additional_Award]
FROM qryALLDisbursments
GROUP BY qryALLDisbursments.Amount_Requested
PIVOT Format([Date_Processed],"mmm") In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

> I would like to set-up a query that will extract the date for January based
> on the Date field being between 1/1/2008 and 1/31/2008. This should be a
[quoted text clipped - 19 lines]
> Thanks,
> TL

Reply to this Message