Welcome to SPN

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

Sign Up Now!

grouping dates with a query

Discussion in 'Information Technology' started by Jay, Nov 9, 2005.

  1. Jay

    Jay
    Expand Collapse
    Guest

    I have a problem. I am trying to put data into date "buckets", but the
    grouping interval needs to be variable and inputted with a form. For
    example, I want to look at how many orders were placed every "x" days,
    where x is inputted on the form. The first date needs to be the first
    date out of the data. If my first record has a date of 12/1/04, and my
    form has the number 2 inputted on it, then the output of the query
    should be this:
    Date Sum of itm_proc
    12/1/04-12/3/04 3
    12/2/04-12/4/04 4
    12/3/04-12/5/04 2

    And so on. A pivot table would work, but I need to be able to adjust
    the grouping levels based on the input on the form.
     
  2. Loading...

    Similar Threads Forum Date
    Sikh News BREAKING: Young Sikh Shot Dead In Jalalabad, Afghanistan . - Sikh24 News & Updates Breaking News Oct 2, 2016
    Pacific Oil slicks spotted in search for missing Malaysia Airlines plane (Live updates) Breaking News Mar 8, 2014
    India Candidates flock to Dera Sacha Sauda Breaking News Jan 17, 2012
    India Seven SAD Candidates of SGPC Win Unopposed Breaking News Aug 27, 2011
    GurmatChanan Updates Gurbani Download Aug 27, 2011

  3. KARL DEWEY

    KARL DEWEY
    Expand Collapse
    Guest

    First you should not use a field named Date as that is an Access reserved word.

    Use a Totals query with this --
    Date Group: Format(Left(Partition([Date],0,999999,2),7),"mm/dd/yyyy") & " :
    " & Format(Right(Partition([Date],0,999999,2),5),"mm/dd/yyyy")

    "Jay" wrote:

    > I have a problem. I am trying to put data into date "buckets", but the
    > grouping interval needs to be variable and inputted with a form. For
    > example, I want to look at how many orders were placed every "x" days,
    > where x is inputted on the form. The first date needs to be the first
    > date out of the data. If my first record has a date of 12/1/04, and my
    > form has the number 2 inputted on it, then the output of the query
    > should be this:
    > Date Sum of itm_proc
    > 12/1/04-12/3/04 3
    > 12/2/04-12/4/04 4
    > 12/3/04-12/5/04 2
    >
    > And so on. A pivot table would work, but I need to be able to adjust
    > the grouping levels based on the input on the form.
    >
    >
     
  4. Jay

    Jay
    Expand Collapse
    Guest

    I am not really using a field named date, and I am pretty sure I have
    the format down, but I am not sure how to place the sum of items in the
    buckets based off of user entry. If you could give me any info on
    this, I would really appreciate it.
     
  5. KARL DEWEY

    KARL DEWEY
    Expand Collapse
    Guest

    I tried to used a form to feed the query and it did not work.

    I do not understand your comment -- sum of items in the buckets

    To use what I gave to you create a select query and change to Totals query
    by clicking on the Greek symbol for summation on the icon bar while in design
    view.
    Paste my post as your left-most column. I gave it to you to pull groups of
    two days. For different size groups replae the '2' in
    ....Partition([Date],0,999999,2),5)... with your number of days. Replace
    [Date] with your field name in brackets.

    Drag itm_proc from your table to the FIELD row of the design grid and change
    the TOTAL from Group By to Sum.


    "Jay" wrote:

    > I am not really using a field named date, and I am pretty sure I have
    > the format down, but I am not sure how to place the sum of items in the
    > buckets based off of user entry. If you could give me any info on
    > this, I would really appreciate it.
    >
    >
     
  6. Jay

    Jay
    Expand Collapse
    Guest

    Hey, it worked. Thanks a lot. This is going to save me a lot of time.
    I appreciate it.
     
  7. Jay

    Jay
    Expand Collapse
    Guest

    Wait, there is an issue with this. It groups in intervals of two days,
    which is great, but I need it to pull two day intervals for every day.
    Something like this:

    Date Group Sum
    1/1/2003 3 (=sum of 1/1/2003 and 1/2/2003)
    1/2/2003 4 (=sum of 1/2/2003 and 1/3/2003)
    1/3/2003 5 (=sum of 1/3/2003 and 1/4/2003)

    I still need the two day group, but I need it to pull for every day
     
  8. Jay

    Jay
    Expand Collapse
    Guest

    There is a problem with the query. This is what it shows:
    Date Group Sumofitm_proc
    1/3/2003 : 23
    1/5/2003 : 18
    1/7/2003 : 38
    and so on....

    The problem with this is that some of the totals are wrong, I manually
    counted the records that should have been in the grouping and it was
    off. Also, I need it to show groups like this, with the groups
    sequential:
    Date Group Sumofitm_proc
    1/3/2003 - 1/4/2003 23 (=sum of 1/3/2003 and 1/4/2003)
    1/4/2003 - 1/5/2003 15 (=sum of 1/4/2003 and 1/5/2003)
    1/5/2003 - 1/6/2003 18 (=sum of 1/5/2003 and 1/6/3003)

    If you know how to do this, that would really help.
     
  9. Jay

    Jay
    Expand Collapse
    Guest

    If you want to feed the query using a form, you replace the 2 in the
    date group field with [Forms![FormName]![textboxorcombobox]
     
  10. peregenem@jetemail.net

    peregenem@jetemail.net
    Expand Collapse
    Guest

    Jay wrote:
    > I am not sure how to place the sum of items in the
    > buckets based off of user entry.


    SQL was invented for data management. Report writers (I understand
    Access ships with one) were invented to do this kind of formatting.
    Don't get me wrong: this kind of exercise in pure SQL is a fun
    challenge. In solving it this way, though, you're supposed to be left
    with the impression that SQL is the wrong tool for the job.

    If you table looks like this

    CREATE TABLE Items (
    itm_proc INTEGER NOT NULL,
    itm_date DATETIME NOT NULL
    )
    ;

    and your 'sequence' table - the table of integers everyone has - looks
    like this

    CREATE TABLE [Sequence] (seq INTEGER)
    ;
    INSERT INTO [Sequence] VALUES (1)
    ;
    INSERT INTO [Sequence] VALUES (2)
    ;
    INSERT INTO [Sequence] VALUES (reasonably high number here)
    ;

    etc etc (or you may want to create the data in Excel and import it <g>)
    then your procedure (Parameter Query) could like something like this:

    CREATE PROCEDURE TestProc
    :)interval_days INTEGER = 3)
    AS
    SELECT R1.report_date, SUM(Items.itm_proc)
    FROM Items
    INNER JOIN (
    SELECT (
    SELECT MIN(itm_date)
    FROM Items
    ) - :interval_days + (S1.seq * :interval_days) AS report_date
    FROM [Sequence] AS S1
    WHERE (
    SELECT MIN(itm_date)
    FROM Items
    ) - :interval_days + (S1.seq * :interval_days) <= DATE()
    ) AS R1
    ON (Items.itm_date
    BETWEEN R1.report_date AND (R1.report_date + :interval_days))
    GROUP BY R1.report_date
     
  11. Jay

    Jay
    Expand Collapse
    Guest

    Thanks for the help, I have another post working on the same issues, so
    I will try them both out. I'll post my results on Saturday. Thanks
    again.
     

Share This Page