Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Query - Group by month & sort

Discussion in 'Information Technology' started by Jay, Jul 28, 2006.

  1. Jay

    Jay
    Expand Collapse
    Guest

    Hi,

    I have a table containing a date/time field. I have a summary query based
    on this table which was set up using the wizard. I stipulated to group by
    month when asked. (i.e. All records in April 2005)

    The query works fine. However when I try and sort on the month field it
    sorts based on the letter beginning the month i.e.

    April 2005
    February 2005
    June 2005
    May 2005

    I want to output the query results in a report, which I will need in
    ascending date order, not Date alpha order.

    I don't know why it does this as the original date field is definitely a
    date type & format etc.

    Any help would be greatly appreciated.

    -Jay-
     
  2. Loading...

    Similar Threads Forum Date
    Query about Jhatka Meat by Shooting in Head Sikh Sikhi Sikhism Aug 26, 2011
    Who is a sikh? A non sikh friend's query!! Sikh Sikhi Sikhism Apr 30, 2010
    General Query Hard Talk Sep 4, 2008
    Power of pauri's in Japji Sahib query Sikh Sikhi Sikhism Aug 17, 2006
    Sikhism a query Book Reviews & Editorials Aug 2, 2005

  3. KARL DEWEY

    KARL DEWEY
    Expand Collapse
    Guest

    Open the query in design view and insert a column to the left of your present
    Month-Year column. Use Format([YourDateField],"yyyymm") and uncheck the box
    for display. It will be sorted correctly.

    "Jay" wrote:

    > Hi,
    >
    > I have a table containing a date/time field. I have a summary query based
    > on this table which was set up using the wizard. I stipulated to group by
    > month when asked. (i.e. All records in April 2005)
    >
    > The query works fine. However when I try and sort on the month field it
    > sorts based on the letter beginning the month i.e.
    >
    > April 2005
    > February 2005
    > June 2005
    > May 2005
    >
    > I want to output the query results in a report, which I will need in
    > ascending date order, not Date alpha order.
    >
    > I don't know why it does this as the original date field is definitely a
    > date type & format etc.
    >
    > Any help would be greatly appreciated.
    >
    > -Jay-
    >
    >
     
  4. Jay

    Jay
    Expand Collapse
    Guest

    Thanks a lot for the response Karl. Will try it tomorrow.

    Why does it do it? I notice that the summary Qry wizard inserted a
    calculated field to do the grouping by field - is it this that somehow makes
    Access deal with the months as text? Seems a bit odd when months will hardly
    ever (if *at all*) need to be alpha sorted.

    Cheers,
    -Jay-


    On 30/6/06 22:51, in article
    70CBBD71-873B-4F25-8F4A-ED4872B10953@microsoft.com, "KARL DEWEY"
    <KARLDEWEY@discussions.microsoft.com> wrote:

    > Open the query in design view and insert a column to the left of your present
    > Month-Year column. Use Format([YourDateField],"yyyymm") and uncheck the box
    > for display. It will be sorted correctly.
    >
    > "Jay" wrote:
    >
    >> Hi,
    >>
    >> I have a table containing a date/time field. I have a summary query based
    >> on this table which was set up using the wizard. I stipulated to group by
    >> month when asked. (i.e. All records in April 2005)
    >>
    >> The query works fine. However when I try and sort on the month field it
    >> sorts based on the letter beginning the month i.e.
    >>
    >> April 2005
    >> February 2005
    >> June 2005
    >> May 2005
    >>
    >> I want to output the query results in a report, which I will need in
    >> ascending date order, not Date alpha order.
    >>
    >> I don't know why it does this as the original date field is definitely a
    >> date type & format etc.
    >>
    >> Any help would be greatly appreciated.
    >>
    >> -Jay-
    >>
    >>
     
  5. Jay

    Jay
    Expand Collapse
    Guest

    Oops, forgot to mention something. The original field is a full date (i.e.
    12/04/2006, 23/07/2005. Do I add a new column to the left of the present
    column? And just type the Format statement in the Field Row?

    Sorry to be a bit slow.

    Jay


    On 30/6/06 23:35, in article C0CB65AA.4332%zeugma@toucanspam.com, "Jay"
    <zeugma@toucanspam.com> wrote:

    > Thanks a lot for the response Karl. Will try it tomorrow.
    >
    > Why does it do it? I notice that the summary Qry wizard inserted a
    > calculated field to do the grouping by field - is it this that somehow makes
    > Access deal with the months as text? Seems a bit odd when months will hardly
    > ever (if *at all*) need to be alpha sorted.
    >
    > Cheers,
    > -Jay-
    >
    >
    > On 30/6/06 22:51, in article
    > 70CBBD71-873B-4F25-8F4A-ED4872B10953@microsoft.com, "KARL DEWEY"
    > <KARLDEWEY@discussions.microsoft.com> wrote:
    >
    >> Open the query in design view and insert a column to the left of your present
    >> Month-Year column. Use Format([YourDateField],"yyyymm") and uncheck the box
    >> for display. It will be sorted correctly.
    >>
    >> "Jay" wrote:
    >>
    >>> Hi,
    >>>
    >>> I have a table containing a date/time field. I have a summary query based
    >>> on this table which was set up using the wizard. I stipulated to group by
    >>> month when asked. (i.e. All records in April 2005)
    >>>
    >>> The query works fine. However when I try and sort on the month field it
    >>> sorts based on the letter beginning the month i.e.
    >>>
    >>> April 2005
    >>> February 2005
    >>> June 2005
    >>> May 2005
    >>>
    >>> I want to output the query results in a report, which I will need in
    >>> ascending date order, not Date alpha order.
    >>>
    >>> I don't know why it does this as the original date field is definitely a
    >>> date type & format etc.
    >>>
    >>> Any help would be greatly appreciated.
    >>>
    >>> -Jay-
    >>>
    >>>

    >
     
  6. Larry Linson

    Larry Linson
    Expand Collapse
    Guest

    You are adding a Calculated Field, so in the Query Builder, you'd press
    insert and then type "SomeName:" followed by the Format recommended by Karl.
    Be sure to click the Sort and choose Ascending in the Query Builder -- just
    adding the Field will not cause it to sort.

    Larry Linson
    Microsoft Access MVP


    "Jay" <zeugma@toucanspam.com> wrote in message
    news:C0CB67D1.433E%zeugma@toucanspam.com...
    > Oops, forgot to mention something. The original field is a full date
    > (i.e.
    > 12/04/2006, 23/07/2005. Do I add a new column to the left of the present
    > column? And just type the Format statement in the Field Row?
    >
    > Sorry to be a bit slow.
    >
    > Jay
    >
    >
    > On 30/6/06 23:35, in article C0CB65AA.4332%zeugma@toucanspam.com, "Jay"
    > <zeugma@toucanspam.com> wrote:
    >
    >> Thanks a lot for the response Karl. Will try it tomorrow.
    >>
    >> Why does it do it? I notice that the summary Qry wizard inserted a
    >> calculated field to do the grouping by field - is it this that somehow
    >> makes
    >> Access deal with the months as text? Seems a bit odd when months will
    >> hardly
    >> ever (if *at all*) need to be alpha sorted.
    >>
    >> Cheers,
    >> -Jay-
    >>
    >>
    >> On 30/6/06 22:51, in article
    >> 70CBBD71-873B-4F25-8F4A-ED4872B10953@microsoft.com, "KARL DEWEY"
    >> <KARLDEWEY@discussions.microsoft.com> wrote:
    >>
    >>> Open the query in design view and insert a column to the left of your
    >>> present
    >>> Month-Year column. Use Format([YourDateField],"yyyymm") and uncheck
    >>> the box
    >>> for display. It will be sorted correctly.
    >>>
    >>> "Jay" wrote:
    >>>
    >>>> Hi,
    >>>>
    >>>> I have a table containing a date/time field. I have a summary query
    >>>> based
    >>>> on this table which was set up using the wizard. I stipulated to group
    >>>> by
    >>>> month when asked. (i.e. All records in April 2005)
    >>>>
    >>>> The query works fine. However when I try and sort on the month field
    >>>> it
    >>>> sorts based on the letter beginning the month i.e.
    >>>>
    >>>> April 2005
    >>>> February 2005
    >>>> June 2005
    >>>> May 2005
    >>>>
    >>>> I want to output the query results in a report, which I will need in
    >>>> ascending date order, not Date alpha order.
    >>>>
    >>>> I don't know why it does this as the original date field is definitely
    >>>> a
    >>>> date type & format etc.
    >>>>
    >>>> Any help would be greatly appreciated.
    >>>>
    >>>> -Jay-
    >>>>
    >>>>

    >>

    >
     

Share This Page