group and sort by month / site

I have the following tables/fields relevant to the query I’m after:

Screened.site
Recruitment.DateOfReferral

all I want to do is design a query and subsequent report which shows the 
number of referrals received in a given month/year and to group the outcome 
by site (to know how many were referred from a given site).

At the moment, I have this query:

SELECT Recruitment.DateOfReferral, Screened.Site, 
Count(Recruitment.DateOfReferral) AS CountOfDateOfReferral
FROM Screened INNER JOIN Recruitment ON Screened.StudyNumber = 
Recruitment.StudyNumber
GROUP BY Recruitment.DateOfReferral, Screened.Site
ORDER BY Recruitment.DateOfReferral, Screened.Site;

I have included the count function as I have found that there are some 
occasions when 2 referrals were received from the same site on the same day 
and this is overlooked when I don’t include the count function. However, I’m 
not interested in the actual day of referral – just the month. 

Ideally I’d like to get a report to show the following:

Month:	     Site:         Number of referrals received:
Jan 07       1		6
Jan 07        2                 4

On the report, I’ve formatted the date field so it only shows month and year 
which is what I want and I tried grouping by month using the grouping / 
sorting options but I can’t seem to get it to work. Can anyone help?!  

0
Utf
7/6/2007 11:28:02 AM
access.queries 6343 articles. 1 followers. Follow

8 Replies
866 Views

Similar Articles

[PageSpeed] 4

"Emelina Bumsquash" wrote:
>I have the following tables/fields relevant to the query I'm after:
>
> Screened.site
> Recruitment.DateOfReferral
>
> all I want to do is design a query and subsequent report which shows the
> number of referrals received in a given month/year and to group the 
> outcome
> by site (to know how many were referred from a given site).
>
> At the moment, I have this query:
>
> SELECT Recruitment.DateOfReferral, Screened.Site,
> Count(Recruitment.DateOfReferral) AS CountOfDateOfReferral
> FROM Screened INNER JOIN Recruitment ON Screened.StudyNumber =
> Recruitment.StudyNumber
> GROUP BY Recruitment.DateOfReferral, Screened.Site
> ORDER BY Recruitment.DateOfReferral, Screened.Site;
>
> I have included the count function as I have found that there are some
> occasions when 2 referrals were received from the same site on the same 
> day
> and this is overlooked when I don't include the count function. However, I'm
> not interested in the actual day of referral - just the month.
>
> Ideally I'd like to get a report to show the following:
>
> Month:      Site:         Number of referrals received:
> Jan 07       1 6
> Jan 07        2                 4
>
> On the report, I've formatted the date field so it only shows month and 
> year
> which is what I want and I tried grouping by month using the grouping /
> sorting options but I can't seem to get it to work. Can anyone help?!
>

Hi Emilina,

There are so many ways...

but, if we are not talking millions of records here,
I might just recast all dates to the first of the month
using DateSerial()

SELECT
DateSerial(Year(R.DateOfReferral),
                  Month(R.DateOfReferral),
                  1) AS Month_Year,
S.Site,
Count(R.DateOfReferral) AS CountOfDateOfReferral
FROM
Screened As S
INNER JOIN
Recruitment As R
ON
S.StudyNumber = R.StudyNumber
GROUP BY
DateSerial(Year(R.DateOfReferral),
                  Month(R.DateOfReferral),
                  1),
S.Site;

I did not include ORDER BY
since your report grouping will
override it anyway where you
would group by "Month_Year"
and sort by Month_Year, Site.

The report textbox bound to "Month_Year"
could then be formatted to "mmm yy"

That might be one way...

good luck,

gary 


0
Gary
7/6/2007 12:51:37 PM
thanks so much for your prompt response. however, i can't seem to make the 
query work. i'm guessing you were shorthanding some of the code so here's the 
SQL i've got based on your help:

SELECT
DateSerial (Year(Recruitment.DateOfReferral), 
Month(Recruitment.DateOfReferral), 1) AS Month_Year,Screened.Site,
Count(Recruitment.DateOfReferral) AS CountOfDateOfReferral
FROM Screened INNER JOIN Recruitment ON 
Screened.StudyNumber=Recruitment.StudyNumber
GROUP BY 
DateSerial(Year(Recruitment.DateOfReferral),Month(Recruitment.DateOfReferrall), 1), Screened.Site;

but i get the error message: 'you tried to execute a query that does not 
include the specified expression DateSerial 
(Year(Recruitment.DateOfReferral), Month(Recruitment.DateOfReferral), 1) as 
part of an aggregate function' 

any help greatly appreciated as always!


"Gary Walter" wrote:

> 
> Hi Emilina,
> 
> There are so many ways...
> 
> but, if we are not talking millions of records here,
> I might just recast all dates to the first of the month
> using DateSerial()
> 
> SELECT
> DateSerial(Year(R.DateOfReferral),
>                   Month(R.DateOfReferral),
>                   1) AS Month_Year,
> S.Site,
> Count(R.DateOfReferral) AS CountOfDateOfReferral
> FROM
> Screened As S
> INNER JOIN
> Recruitment As R
> ON
> S.StudyNumber = R.StudyNumber
> GROUP BY
> DateSerial(Year(R.DateOfReferral),
>                   Month(R.DateOfReferral),
>                   1),
> S.Site;
> 
> I did not include ORDER BY
> since your report grouping will
> override it anyway where you
> would group by "Month_Year"
> and sort by Month_Year, Site.
> 
> The report textbox bound to "Month_Year"
> could then be formatted to "mmm yy"
> 
> That might be one way...
> 
> good luck,
> 
> gary 
> 
> 
> 
0
Utf
7/6/2007 2:00:08 PM
Hi Emilina,

That does not make sense...
the expression does not need to be
"part of an aggregate function" because
is part of the "group."

What does the column look like in BDE?

From your SQL, it should look like:
(I won't type out all of function innards)

Field:   Month_Year: DateSerial(xxxx)
Table:
Total:  Group By
Sort:
Show:   <checked>
Criteria:
or:

What happens if you "toggle" the sigma icon
to temporarily "ungroup." Does the expression
return the correct results?

Is the SQL you provided a strict "copy-and-paste?"

It looks perfect to me...


"Emelina Bumsquash" wrote
> thanks so much for your prompt response. however, i can't seem to make the
> query work. i'm guessing you were shorthanding some of the code so here's 
> the
> SQL i've got based on your help:
>
> SELECT
> DateSerial (Year(Recruitment.DateOfReferral),
> Month(Recruitment.DateOfReferral), 1) AS Month_Year,Screened.Site,
> Count(Recruitment.DateOfReferral) AS CountOfDateOfReferral
> FROM Screened INNER JOIN Recruitment ON
> Screened.StudyNumber=Recruitment.StudyNumber
> GROUP BY
> DateSerial(Year(Recruitment.DateOfReferral),Month(Recruitment.DateOfReferrall), 
> 1), Screened.Site;
>
> but i get the error message: 'you tried to execute a query that does not
> include the specified expression DateSerial
> (Year(Recruitment.DateOfReferral), Month(Recruitment.DateOfReferral), 1) 
> as
> part of an aggregate function'
>
> any help greatly appreciated as always!
>
>
> "Gary Walter" wrote:
>
>>
>> Hi Emilina,
>>
>> There are so many ways...
>>
>> but, if we are not talking millions of records here,
>> I might just recast all dates to the first of the month
>> using DateSerial()
>>
>> SELECT
>> DateSerial(Year(R.DateOfReferral),
>>                   Month(R.DateOfReferral),
>>                   1) AS Month_Year,
>> S.Site,
>> Count(R.DateOfReferral) AS CountOfDateOfReferral
>> FROM
>> Screened As S
>> INNER JOIN
>> Recruitment As R
>> ON
>> S.StudyNumber = R.StudyNumber
>> GROUP BY
>> DateSerial(Year(R.DateOfReferral),
>>                   Month(R.DateOfReferral),
>>                   1),
>> S.Site;
>>
>> I did not include ORDER BY
>> since your report grouping will
>> override it anyway where you
>> would group by "Month_Year"
>> and sort by Month_Year, Site.
>>
>> The report textbox bound to "Month_Year"
>> could then be formatted to "mmm yy"
>>
>> That might be one way...
>>
>> good luck,
>>
>> gary
>>
>>
>> 


0
Gary
7/6/2007 2:24:30 PM
Only strange thing I can see is that
I would have expected Access to
automatically put brackets around table
and field names within Year/Month functions
within DateSerial function?

SELECT
DateSerial (Year([Recruitment].[DateOfReferral]),
Month([Recruitment].[DateOfReferral]), 1) AS Month_Year,Screened.Site,
Count(Recruitment.DateOfReferral) AS CountOfDateOfReferral
FROM Screened INNER JOIN Recruitment ON
Screened.StudyNumber=Recruitment.StudyNumber
GROUP BY
DateSerial(Year([Recruitment].[DateOfReferral]),
Month([Recruitment].[DateOfReferrall]), 1), Screened.Site;

"Emelina Bumsquash" wrote:
> thanks so much for your prompt response. however, i can't seem to make the
> query work. i'm guessing you were shorthanding some of the code so here's 
> the
> SQL i've got based on your help:
>
> SELECT
> DateSerial (Year(Recruitment.DateOfReferral),
> Month(Recruitment.DateOfReferral), 1) AS Month_Year,Screened.Site,
> Count(Recruitment.DateOfReferral) AS CountOfDateOfReferral
> FROM Screened INNER JOIN Recruitment ON
> Screened.StudyNumber=Recruitment.StudyNumber
> GROUP BY
> DateSerial(Year(Recruitment.DateOfReferral),Month(Recruitment.DateOfReferrall), 
> 1), Screened.Site;
>
> but i get the error message: 'you tried to execute a query that does not
> include the specified expression DateSerial
> (Year(Recruitment.DateOfReferral), Month(Recruitment.DateOfReferral), 1) 
> as
> part of an aggregate function'
>
> any help greatly appreciated as always!
>
>
> "Gary Walter" wrote:
>
>>
>> Hi Emilina,
>>
>> There are so many ways...
>>
>> but, if we are not talking millions of records here,
>> I might just recast all dates to the first of the month
>> using DateSerial()
>>
>> SELECT
>> DateSerial(Year(R.DateOfReferral),
>>                   Month(R.DateOfReferral),
>>                   1) AS Month_Year,
>> S.Site,
>> Count(R.DateOfReferral) AS CountOfDateOfReferral
>> FROM
>> Screened As S
>> INNER JOIN
>> Recruitment As R
>> ON
>> S.StudyNumber = R.StudyNumber
>> GROUP BY
>> DateSerial(Year(R.DateOfReferral),
>>                   Month(R.DateOfReferral),
>>                   1),
>> S.Site;
>>
>> I did not include ORDER BY
>> since your report grouping will
>> override it anyway where you
>> would group by "Month_Year"
>> and sort by Month_Year, Site.
>>
>> The report textbox bound to "Month_Year"
>> could then be formatted to "mmm yy"
>>
>> That might be one way...
>>
>> good luck,
>>
>> gary
>>
>>
>> 


0
Gary
7/6/2007 2:32:11 PM
yep, a strict copy and paste! i can't get into the BDE view as it won't let 
me get out of the SQL view because it keeps giving me that error message - 
i'll try to start from scratch using the BDE view perhaps?

"Gary Walter" wrote:

> Hi Emilina,
> 
> That does not make sense...
> the expression does not need to be
> "part of an aggregate function" because
> is part of the "group."
> 
> What does the column look like in BDE?
> 
> From your SQL, it should look like:
> (I won't type out all of function innards)
> 
> Field:   Month_Year: DateSerial(xxxx)
> Table:
> Total:  Group By
> Sort:
> Show:   <checked>
> Criteria:
> or:
> 
> What happens if you "toggle" the sigma icon
> to temporarily "ungroup." Does the expression
> return the correct results?
> 
> Is the SQL you provided a strict "copy-and-paste?"
> 
> It looks perfect to me...
> 
> 
> "Emelina Bumsquash" wrote
> > thanks so much for your prompt response. however, i can't seem to make the
> > query work. i'm guessing you were shorthanding some of the code so here's 
> > the
> > SQL i've got based on your help:
> >
> > SELECT
> > DateSerial (Year(Recruitment.DateOfReferral),
> > Month(Recruitment.DateOfReferral), 1) AS Month_Year,Screened.Site,
> > Count(Recruitment.DateOfReferral) AS CountOfDateOfReferral
> > FROM Screened INNER JOIN Recruitment ON
> > Screened.StudyNumber=Recruitment.StudyNumber
> > GROUP BY
> > DateSerial(Year(Recruitment.DateOfReferral),Month(Recruitment.DateOfReferrall), 
> > 1), Screened.Site;
> >
> > but i get the error message: 'you tried to execute a query that does not
> > include the specified expression DateSerial
> > (Year(Recruitment.DateOfReferral), Month(Recruitment.DateOfReferral), 1) 
> > as
> > part of an aggregate function'
> >
> > any help greatly appreciated as always!
> >
> >
> > "Gary Walter" wrote:
> >
> >>
> >> Hi Emilina,
> >>
> >> There are so many ways...
> >>
> >> but, if we are not talking millions of records here,
> >> I might just recast all dates to the first of the month
> >> using DateSerial()
> >>
> >> SELECT
> >> DateSerial(Year(R.DateOfReferral),
> >>                   Month(R.DateOfReferral),
> >>                   1) AS Month_Year,
> >> S.Site,
> >> Count(R.DateOfReferral) AS CountOfDateOfReferral
> >> FROM
> >> Screened As S
> >> INNER JOIN
> >> Recruitment As R
> >> ON
> >> S.StudyNumber = R.StudyNumber
> >> GROUP BY
> >> DateSerial(Year(R.DateOfReferral),
> >>                   Month(R.DateOfReferral),
> >>                   1),
> >> S.Site;
> >>
> >> I did not include ORDER BY
> >> since your report grouping will
> >> override it anyway where you
> >> would group by "Month_Year"
> >> and sort by Month_Year, Site.
> >>
> >> The report textbox bound to "Month_Year"
> >> could then be formatted to "mmm yy"
> >>
> >> That might be one way...
> >>
> >> good luck,
> >>
> >> gary
> >>
> >>
> >> 
> 
> 
> 
0
Utf
7/6/2007 2:36:05 PM
Also, if this is a "copy-and-paste,"
I don't understand why there is a
space after DateSerial in SELECT
clause, but not in GROUP BY clause?

"Gary Walter" wrote:
> Only strange thing I can see is that
> I would have expected Access to
> automatically put brackets around table
> and field names within Year/Month functions
> within DateSerial function?
>
> SELECT
> DateSerial (Year([Recruitment].[DateOfReferral]),
> Month([Recruitment].[DateOfReferral]), 1) AS Month_Year,Screened.Site,
> Count(Recruitment.DateOfReferral) AS CountOfDateOfReferral
> FROM Screened INNER JOIN Recruitment ON
> Screened.StudyNumber=Recruitment.StudyNumber
> GROUP BY
> DateSerial(Year([Recruitment].[DateOfReferral]),
> Month([Recruitment].[DateOfReferrall]), 1), Screened.Site;
>
> "Emelina Bumsquash" wrote:
>> thanks so much for your prompt response. however, i can't seem to make 
>> the
>> query work. i'm guessing you were shorthanding some of the code so here's 
>> the
>> SQL i've got based on your help:
>>
>> SELECT
>> DateSerial (Year(Recruitment.DateOfReferral),
>> Month(Recruitment.DateOfReferral), 1) AS Month_Year,Screened.Site,
>> Count(Recruitment.DateOfReferral) AS CountOfDateOfReferral
>> FROM Screened INNER JOIN Recruitment ON
>> Screened.StudyNumber=Recruitment.StudyNumber
>> GROUP BY
>> DateSerial(Year(Recruitment.DateOfReferral),Month(Recruitment.DateOfReferrall), 
>> 1), Screened.Site;
>>
>> but i get the error message: 'you tried to execute a query that does not
>> include the specified expression DateSerial
>> (Year(Recruitment.DateOfReferral), Month(Recruitment.DateOfReferral), 1) 
>> as
>> part of an aggregate function'
>>
>> any help greatly appreciated as always!
>>
>>
>> "Gary Walter" wrote:
>>
>>>
>>> Hi Emilina,
>>>
>>> There are so many ways...
>>>
>>> but, if we are not talking millions of records here,
>>> I might just recast all dates to the first of the month
>>> using DateSerial()
>>>
>>> SELECT
>>> DateSerial(Year(R.DateOfReferral),
>>>                   Month(R.DateOfReferral),
>>>                   1) AS Month_Year,
>>> S.Site,
>>> Count(R.DateOfReferral) AS CountOfDateOfReferral
>>> FROM
>>> Screened As S
>>> INNER JOIN
>>> Recruitment As R
>>> ON
>>> S.StudyNumber = R.StudyNumber
>>> GROUP BY
>>> DateSerial(Year(R.DateOfReferral),
>>>                   Month(R.DateOfReferral),
>>>                   1),
>>> S.Site;
>>>
>>> I did not include ORDER BY
>>> since your report grouping will
>>> override it anyway where you
>>> would group by "Month_Year"
>>> and sort by Month_Year, Site.
>>>
>>> The report textbox bound to "Month_Year"
>>> could then be formatted to "mmm yy"
>>>
>>> That might be one way...
>>>
>>> good luck,
>>>
>>> gary
>>>
>>>
>>>
>
> 


0
Gary
7/6/2007 2:39:20 PM
I just started the query from scratch and used the view you suggested and it 
worked! thank you so much. not sure what the problem was with the SQL view 
but it must've been something i did wrong - apologies for that. 

the resulting SQL i've got which works is:
SELECT 
DateSerial(Year(Recruitment.DateOfReferral),Month(Recruitment.DateOfReferral),1) 
AS Month_Year, Screened.Site, Count(Recruitment.DateOfReferral) AS 
CountOfDateOfReferral
FROM Screened INNER JOIN Recruitment ON 
Screened.StudyNumber=Recruitment.StudyNumber
GROUP BY 
DateSerial(Year(Recruitment.DateOfReferral),Month(Recruitment.DateOfReferral),1), Screened.Site, Screened.ResultsOfSLTScreenEligible
HAVING (((Screened.ResultsOfSLTScreenEligible)=1))
ORDER BY 
DateSerial(Year(Recruitment.DateOfReferral),Month(Recruitment.DateOfReferral),1), Screened.Site;

thanks so much for your help

"Gary Walter" wrote:

> Also, if this is a "copy-and-paste,"
> I don't understand why there is a
> space after DateSerial in SELECT
> clause, but not in GROUP BY clause?
> 
> "Gary Walter" wrote:
> > Only strange thing I can see is that
> > I would have expected Access to
> > automatically put brackets around table
> > and field names within Year/Month functions
> > within DateSerial function?
> >
> > SELECT
> > DateSerial (Year([Recruitment].[DateOfReferral]),
> > Month([Recruitment].[DateOfReferral]), 1) AS Month_Year,Screened.Site,
> > Count(Recruitment.DateOfReferral) AS CountOfDateOfReferral
> > FROM Screened INNER JOIN Recruitment ON
> > Screened.StudyNumber=Recruitment.StudyNumber
> > GROUP BY
> > DateSerial(Year([Recruitment].[DateOfReferral]),
> > Month([Recruitment].[DateOfReferrall]), 1), Screened.Site;
> >
> > "Emelina Bumsquash" wrote:
> >> thanks so much for your prompt response. however, i can't seem to make 
> >> the
> >> query work. i'm guessing you were shorthanding some of the code so here's 
> >> the
> >> SQL i've got based on your help:
> >>
> >> SELECT
> >> DateSerial (Year(Recruitment.DateOfReferral),
> >> Month(Recruitment.DateOfReferral), 1) AS Month_Year,Screened.Site,
> >> Count(Recruitment.DateOfReferral) AS CountOfDateOfReferral
> >> FROM Screened INNER JOIN Recruitment ON
> >> Screened.StudyNumber=Recruitment.StudyNumber
> >> GROUP BY
> >> DateSerial(Year(Recruitment.DateOfReferral),Month(Recruitment.DateOfReferrall), 
> >> 1), Screened.Site;
> >>
> >> but i get the error message: 'you tried to execute a query that does not
> >> include the specified expression DateSerial
> >> (Year(Recruitment.DateOfReferral), Month(Recruitment.DateOfReferral), 1) 
> >> as
> >> part of an aggregate function'
> >>
> >> any help greatly appreciated as always!
> >>
> >>
> >> "Gary Walter" wrote:
> >>
> >>>
> >>> Hi Emilina,
> >>>
> >>> There are so many ways...
> >>>
> >>> but, if we are not talking millions of records here,
> >>> I might just recast all dates to the first of the month
> >>> using DateSerial()
> >>>
> >>> SELECT
> >>> DateSerial(Year(R.DateOfReferral),
> >>>                   Month(R.DateOfReferral),
> >>>                   1) AS Month_Year,
> >>> S.Site,
> >>> Count(R.DateOfReferral) AS CountOfDateOfReferral
> >>> FROM
> >>> Screened As S
> >>> INNER JOIN
> >>> Recruitment As R
> >>> ON
> >>> S.StudyNumber = R.StudyNumber
> >>> GROUP BY
> >>> DateSerial(Year(R.DateOfReferral),
> >>>                   Month(R.DateOfReferral),
> >>>                   1),
> >>> S.Site;
> >>>
> >>> I did not include ORDER BY
> >>> since your report grouping will
> >>> override it anyway where you
> >>> would group by "Month_Year"
> >>> and sort by Month_Year, Site.
> >>>
> >>> The report textbox bound to "Month_Year"
> >>> could then be formatted to "mmm yy"
> >>>
> >>> That might be one way...
> >>>
> >>> good luck,
> >>>
> >>> gary
> >>>
> >>>
> >>>
> >
> > 
> 
> 
> 
0
Utf
7/6/2007 2:48:01 PM
okay then....
eliminate the space after DateSerial
in SELECT clause...
it thinks you are grouping by something else...

"Emelina wrote:
> yep, a strict copy and paste! i can't get into the BDE view as it won't 
> let
> me get out of the SQL view because it keeps giving me that error message -
> i'll try to start from scratch using the BDE view perhaps?
>
> "Gary Walter" wrote:
>
>> Hi Emilina,
>>
>> That does not make sense...
>> the expression does not need to be
>> "part of an aggregate function" because
>> is part of the "group."
>>
>> What does the column look like in BDE?
>>
>> From your SQL, it should look like:
>> (I won't type out all of function innards)
>>
>> Field:   Month_Year: DateSerial(xxxx)
>> Table:
>> Total:  Group By
>> Sort:
>> Show:   <checked>
>> Criteria:
>> or:
>>
>> What happens if you "toggle" the sigma icon
>> to temporarily "ungroup." Does the expression
>> return the correct results?
>>
>> Is the SQL you provided a strict "copy-and-paste?"
>>
>> It looks perfect to me...
>>
>>
>> "Emelina Bumsquash" wrote
>> > thanks so much for your prompt response. however, i can't seem to make 
>> > the
>> > query work. i'm guessing you were shorthanding some of the code so 
>> > here's
>> > the
>> > SQL i've got based on your help:
>> >
>> > SELECT
>> > DateSerial (Year(Recruitment.DateOfReferral),
>> > Month(Recruitment.DateOfReferral), 1) AS Month_Year,Screened.Site,
>> > Count(Recruitment.DateOfReferral) AS CountOfDateOfReferral
>> > FROM Screened INNER JOIN Recruitment ON
>> > Screened.StudyNumber=Recruitment.StudyNumber
>> > GROUP BY
>> > DateSerial(Year(Recruitment.DateOfReferral),Month(Recruitment.DateOfReferrall),
>> > 1), Screened.Site;
>> >
>> > but i get the error message: 'you tried to execute a query that does 
>> > not
>> > include the specified expression DateSerial
>> > (Year(Recruitment.DateOfReferral), Month(Recruitment.DateOfReferral), 
>> > 1)
>> > as
>> > part of an aggregate function'
>> >
>> > any help greatly appreciated as always!
>> >
>> >
>> > "Gary Walter" wrote:
>> >
>> >>
>> >> Hi Emilina,
>> >>
>> >> There are so many ways...
>> >>
>> >> but, if we are not talking millions of records here,
>> >> I might just recast all dates to the first of the month
>> >> using DateSerial()
>> >>
>> >> SELECT
>> >> DateSerial(Year(R.DateOfReferral),
>> >>                   Month(R.DateOfReferral),
>> >>                   1) AS Month_Year,
>> >> S.Site,
>> >> Count(R.DateOfReferral) AS CountOfDateOfReferral
>> >> FROM
>> >> Screened As S
>> >> INNER JOIN
>> >> Recruitment As R
>> >> ON
>> >> S.StudyNumber = R.StudyNumber
>> >> GROUP BY
>> >> DateSerial(Year(R.DateOfReferral),
>> >>                   Month(R.DateOfReferral),
>> >>                   1),
>> >> S.Site;
>> >>
>> >> I did not include ORDER BY
>> >> since your report grouping will
>> >> override it anyway where you
>> >> would group by "Month_Year"
>> >> and sort by Month_Year, Site.
>> >>
>> >> The report textbox bound to "Month_Year"
>> >> could then be formatted to "mmm yy"
>> >>
>> >> That might be one way...
>> >>
>> >> good luck,
>> >>
>> >> gary
>> >>
>> >>
>> >>
>>
>>
>> 


0
Gary
7/6/2007 2:50:40 PM
Reply:

Similar Artilces:

Ability to limit check amt or check amt per month per vendor
We had an issue where a typo caused a check to be posted to the incorrect vendor. We know the maximum check that could ever be posted to this vendor, and are in somewhat of a Sarbanes pickle because there is no built in way in Great Plains to put a control on this (per Support) ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreade...

crashing when sending to a group
I use Outlook Express to send and receive most email. Every time I try to send an email to a group I've set up, Outlook Express crashes. The email isn't sent, and th application crashes. In fact, even if I try to check the group's properties in address book, the same thing happens. I can't even delete the group, since it crashes as soon as I click on the 'Properties' icon. Any ideas out there? This is a group to support Outlook from the Office group of programs. Outlook Express is a part of Internet Explorer and is a quite different program despite its similar...

multiple site rollout management
I am looking to develop a Microsoft Project spreadsheet to manage 300 individual projects all with approx. 15 tasks. Is this possible or is this program more specifically to manage individual projects and not rollouts. I have done this in Excel, but the client would like in Projects if possible. Thanks Mark Yes, possible. No problem. -- Jan De Messemaeker Microsoft Project Most Valuable Professional +32 495 300 620 For availability check: http://users.online.be/prom-ade/Calendar.pdf "Mark" <Mark@discussions.microsoft.com> wrote in message news:A8193171...

Sorting an Array #3
I have an array of 2 columns: A & B. A contains a Product Code and B contains a Product Description which can sometimes be unique or sometimes the same description. For example: A1 B1 1 X8 ThingA 2 X8 ThingA 3 G9 WidgetA 4 S5 PartA 5 G9 WidgetB 6 X8 PartB 7 W1 StuffA 8 W1 StuffA 9 X8 ThingA I need to be able to create a list, by Product Code, listing only one occurrance per product description. Essentially, the same way a Filter displays items in a column. -- Regards... You could select column B. Then Data|filter|advanced fil...

CrossTab Group Totals
i have created a crosstab query that works great but I have a problem in that I need a grand total of the field called Total on the last line that totals the Total column. Any suggestions Make your crosstab from a union query with your table and a totals query that looks like this -- qryTable -- SELECT YourTable.Product, YourTable.ProdDate, YourTable.Score FROM YourTable UNION ALL SELECT "Grand_Total" AS Product, Null AS ProdDate, Sum(YourTable.Score) AS Total_Score FROM YourTable GROUP BY "Grand_Total", Null; TRANSFORM Sum(qryYourTable.[Score]) A...

Cannot move mailbox between routing groups
Hi Im in the process of upgrading from exchange 5.5 to 2003. I was previously able to move mailboxes between all my 2003 routing groups. I have installed a new server in a new routing group and removed one of my old 5.5 servers. Scince this work, I am unable to move mailboxes between 2003 routing groups. The wizard only gives me the opting to move the mailbox to the old 5.5 server in each routing group. As I said, I was able to see all servers in all routing groups before. Any ideas? Thanks Is Exchange 2003 SP1 installed on the new server? "John Scrooby" <John@espotti...

Grouping and Ungrouping Greyed out
Hi, My Uncle (who has been using Publisher for years) has called me to ask why he can't group or ungroup a picture and a text box in Publisher 2003. The options appear to be greyed out when he goes to use them. Also, when he uses the select objects button he can draw a box around the objects but the group icon does not appear at the bottom right of the select objects box. He's stuck without broadband right now which is why I am asking. Thanks for any help, Becki. Does the text box fill the entire page? Ask your Uncle if he has drawn a full border on his document? If yes, te...

One group can't send to distribution list that is locked down
I've got one group that cannot send to a distribution group even though the have the correct rights to send to that group. E2k3 sp2 with all the latest fixes and server2k3 sp1 with all fixes. This setup has been working fine for a year and just started bombing on me with NDR's stating You do not have permission to send to this recipient. No changes to group membership, scope or type have happened to either group in quite some time. ANyone seen this happen before? bouncing the system attendant got it fixed. "knightly" wrote: > I've got one group that cannot sen...

Site connector between Exchange 5.5 and Exchange 2003
Hi, Can I run a site connector between an Exchange 5.5 server and an Exchange 2003 server. Basically what we want is to have Site A running Exch 5.5 (NT4) and receiving all the mail from the internet and site B running Exch 2003 receiving all its mail from Site A. Is this environment possible? Many thanks, Al Sure, you will have to bootstrap it though. Its not the most clean of environments but it can certainly work. Check out: 300129 XADM: How to Establish Mail Flow and Directory Replication Between http://support.microsoft.com/?id=300129 -- Dan Winter, MCSE Microsoft Exchange Suppo...

sorting cells in a sub routine
I'm trying to Sort a block of cells as a step in a routine. The size of the block varies, but it will always be Sorted in Ascending order by the last column. The routine works by having Excel evaluate the CurrentRegion of the ActiveCell with the CurrentRegion cells surrounding it. If they match, the sort fires. All of this works fine except when the sheet is protected. As far as I can tell, CurrentRegion causes an error message when the sheet is protected. How else can I establish the area to be sorted? I'm currently doing it by finding the first and last Columns in the Region an...

Sort Question
Hello, I have a Combo Box as a Group By item in my Access 2007 report. The grouping is correct, however the sorting appers to take place based on the ID Column(0) of the Combo Box insted of actual text Column(1). How can I force the report to sort by the value and not the ID? Thank you, Silvio Your report's record source must contain the table and field with "the value" so you can use it to sort. This seems to be another reason why lookup fields in tables are a bad idea. -- Duane Hookom Microsoft Access MVP "Silvio" wrote: > Hello,...

Recovery Storage Groups with 2 Exchange Servers
Hi There, We currently have 2 exchange 2003 servers, mail1 (SP1) and mail2 (SP2). Now before we had mail2, we were backing up the information store on a monthly basis (mail1). When we brought mail2 up, we did a move mailbox and all users are now pointing to mail2. I now have a request to restore data from June 2005 - when mail1 was just around. I was able to create the RSG and restore the database and even mount it. But when I try to run an exmerge, I bomb out with the following errors - [21:27:50] Error! Cannot identify the user with the msExchMailboxGuid \01I\E5\00\91\D8\F5E\85\...

Pivot table grouping (URGENT!)
Hi All, I have a pivot table which has currency amounts in the column and a count of claims in the data area. I have grouped by 100, starting at 0 running to 600. The only thing I'm questioning is when it gets to 200 I think its including any spend in the group 100-200 then again in the group 200-300. Is there anyway I can get the grouping to run from 0-100, then 101-200, 201-300 etc? Please advise -- Adam ----------- Windows 98 + Office Pro 97 When you group by 100, the groups shouldn't overlap. If you start at 0, it should automatically group by 0-99, 100-199, etc. Adam w...

Keep Group Header Titles with Split Detail
How can I force the titles in a Group Header to repeat when the details of the report go over to a second or third page? -- Thanks As Always Rip Set the Repeat Section property of your group header section. -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Ripper" <Ripper@discussions.microsoft.com> wrote in message news:1F6A2545-741C-4DE3-9D55-BCA56EC2BF4F@microsoft.com... > How can I force the titles in a Group Header to repeat w...

Windows Logon Screen bitmap Via group policy
Windows Logon Screen bitmap Via group policy how to change logon screen (like when we hit Alt+Ctrl+Dele) that blue screen to some other wallpaper via group Policy -- shivaj ------------------------------------------------------------------------ shivaj's Profile: http://forums.techarena.in/members/151956.htm View this thread: http://forums.techarena.in/active-directory/1278515.htm http://forums.techarena.in I have never heard of a way to do this. This might be something a client NewsGroup might have an answer for. -- Paul Bergson MVP - Directory Services...

How to Move public folder within same site.
Hi , i have 2 exchange server ver 2003 sp2. One of server is old so i want to remove it. but i can access public folder through ESM only when i connect my old server.So pl help me how to move public folder from my old server to new server. what steps i follow . Waiting for your reply. Thanks In advance Sandeep Sawant. Hi If your servers are in the same forest/exchange org, you will be able to use the new "Move All Replicas" feature in Exchange System Manager on Exchange 2003 SP2. To run this feature, right click on your Public Folder Store in Exchange System Manager, and sel...

I think I messed up big time... v.Delete Administrative Groups
To make a long story short, we had an Exchange 2003 SP2 Clustered Server in an Administrative Group. We had a catastrophic failure of the hardware, and now we have an Administrative Group, that still thinks the server is there. This is the only server in the Administrative Group. As of right now, this entire Exchange 2003 environment is not in a production environment. If I select the server, the ESM will freeze for a second then just hop up to the Org name. Not allowing me to right click and delete. I am totally lost. I want to get delete this Administrative Group, but I have no idea h...

Why can't save SORTS?
An using MS Office 2002 Excel. Have various worksheets that I always sort a certain way... for instance, a list sorted by Column B, then Column A, then Column C...(with no header row). Problem is... everytime I go to sort, I have to RE-ENTER the B, A, C sort criteria. Is there a way to save it for a specific worksheet file? Have had some suggestions to write macros, but this takes time, and it seems like surely there is a way to retain the last sort entered. Lotus 123 saves your last sort criteria with the spreadsheet. Alternatively, does MS Office *2003* Excel correct this? Would appre...

sorting numbers and numbers that contain text in excel
A column contains both strictly numbers and also numbers that are followed by text (e.g., row 1: 1000, row 2: 1500; row 3: 1000a; row 4: 1500a) Identical numbers are related documents, with the text suffixes referring to addenda documents; thus, document 1000 has an addendum document 1000a; How can I sort the column so in the following order: row 1 (1000), row 3 (1000a), row 2 (1500), row 4 (1500a)? Thank you -- MZ =TEXT(A1,"0") will turn each into text, then sort by that helper column (and don't accept Excel's suggestion to treat text that looks like number...

Setting up twice monthly mortage payments
Has anyone an idea how to set up an accelerated payment mortgage (twice monthly payments) in Money? In microsoft.public.money, j. singer wrote: >Has anyone an idea how to set up an accelerated payment >mortgage (twice monthly payments) in Money? Accounts&Bills->AccountSetup .... Mortgage ..... Borrowing ........... Paid How Often:Twice A Month would seem to do it. >-----Original Message----- >Has anyone an idea how to set up an accelerated payment >mortgage (twice monthly payments) in Money? >. Simply set this up like any other bill payment. Identify the en...

How to group
Hi, I have a spreadsheet with the following columns: Item # 1 to 1000 Account Number alphanumeric 20 characters long Amount 1 Amount 2 Period 1 Period 2 Data is currently sorted by Amount 2 ( largest to smallest). There are multiple entries for the same account. What I want to do is now group the data so that All the same account numbers are grouped together along with all other fields but in this grouping I want the amounts to keep the current sort largest to smallest ( largest to smallest).If an account as three numbers I want them on the sheet in consecutive lines ...

Customer Relationship
CRM 3.0 If a user opens up the Account or Contact entity, the Relatinoships icon in the left menu is available. When clicking the Relationships icon, a view of existing Customer Relationships is displayed. Our implementation shows 4 columns (as I believe everyones' does): Party 1, Role 1, Role 2, Party 2. A while back, users had the ability to click on those column headers to sort the records how they wanted. But for some reason, the application today has no default sort order and does not allow the selection of a column header at all. The only thing one of our end users could...

Sorting Aplhanumeric Text as Numeric Values in Excel 2003
I was able find information on this for Excel 97, but could not find anything that looked at the subject in Excel 2003. Any help would be appreciated. ...

Select 'Sort By' column by simply typing heading letter
Hello, In the next version of MS Excel, please restore the ability to define Sort By columns by simply typing the Column heading letter as was the case in Word 2003. When Excel 2007 came out, I was astonished to discover that capability was no longer available. For example, suppose I want to sort a large spreadsheet by Column CDX. In both Excel 2003 and 2007, if I open the Sort box and click Sort By, the drop down menu, by default, lists Column A as the first option. In Excel 2003, I could simply click twice to insert the cursor, type backspace (to remove the A), then ...

Sorting & retaining cell format
hi i want to sort a range of A2:D20 by the headers in row 1. however some of the cells in column B have specific formats with different cell colours and a border that diagonally cross the cells in red, to make it looked crossed out. when i set up the sorts they work OK but the border formatting stays with the original cell location rather than moving with the correct text. does anyone have any ideas? Nick Hi Nick AFAIK the only workaround would be using conditional format based on your cell values. Fixed border formats will not be sorted. Frank Nick wrote: > hi > > i want...