Results 1 to 12 of 12
  1. #1
    Join Date
    Jan 2003
    Posts
    9

    Unanswered: query to group date and give totals

    I have a query that includes a date field, I would like to group it into records for the current week. (Sunday - Saturday). So that each week is a seperate group.

    Secondly, there is a $ field. I would like to be able to have a running total of the charges. If this is better in a form, any ideas about that as well.

    Any help would be appreciated.

    Walter

  2. #2
    Join Date
    Aug 2002
    Location
    Charlotte NC
    Posts
    665

    Re: query to group date and give totals

    Originally posted by parteaux
    I have a query that includes a date field, I would like to group it into records for the current week. (Sunday - Saturday). So that each week is a seperate group.

    Secondly, there is a $ field. I would like to be able to have a running total of the charges. If this is better in a form, any ideas about that as well.

    Any help would be appreciated.

    Walter
    The only way that i know how to group weeks is to do a datediff function to dirive the week number. then the grouping will be easy. the running totals can be done in a query as well, though the use of a form would depend on your end users realy
    Jim

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    see Grouping output by week

    dunno how to do your running totals, though

    rudy

  4. #4
    Join Date
    Jan 2003
    Posts
    9

    Re: query to group date and give totals

    I thought datedif compared two weeks? How would you get it to give you a week number?

  5. #5
    Join Date
    Jan 2003
    Posts
    9
    The grouping link is great but that looks like it is for oracle and not microsoft access. How would you get that code to run behind a microsoft access form? Or in a query?

    Thanks.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    how would i get it to work in access? convert the oracle functions to access functions, i suppose

    give me your table layout (column names and datatypes) and tell me which ones to use and i'll give it a shot

    while i'm doing that, you'll want to create the integers table

    create table integers ( i integer );
    insert into integers(i) values(0);
    insert into integers(i) values(1);
    insert into integers(i) values(2);
    insert into integers(i) values(3);
    insert into integers(i) values(4);
    insert into integers(i) values(5);
    insert into integers(i) values(6);


    rudy

  7. #7
    Join Date
    Jan 2003
    Posts
    9
    Lets just make is simple. I can always add more field.
    Table with 1 field that is Date/Time.
    The table is called SterlingMessenger. The field is called Date.
    Thanks.





    Originally posted by r937
    how would i get it to work in access? convert the oracle functions to access functions, i suppose

    give me your table layout (column names and datatypes) and tell me which ones to use and i'll give it a shot

    while i'm doing that, you'll want to create the integers table

    create table integers ( i integer );
    insert into integers(i) values(0);
    insert into integers(i) values(1);
    insert into integers(i) values(2);
    insert into integers(i) values(3);
    insert into integers(i) values(4);
    insert into integers(i) values(5);
    insert into integers(i) values(6);


    rudy

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you don't really have a field called Date, do you? that's a reserved word, so i'm going to call it theDate

    okay, let's say you wanted a report from september 1 2002 until december 28 2002, grouped by week, summing the AmtPaid column

    (these dates were conveniently chosen to start on a sunday and end on a saturday)

    caution: not tested, and it's up to you to make sure the values 0 through 16 are in the integers table
    Code:
    select dateadd("d",7*i,  #2002-09-01#) as Day0
         , dateadd("d",7*i+6,#2002-09-01#) as Day6
         , sum(AmtPaid)                    as SumAmtPaid
      from integers
    left outer
      join SterlingMessenger
        on theDate 
           between dateadd("d",7*i,  #2002-09-01#) 
               and dateadd("d",7*i+6,#2002-09-01#)
     where theDate 
           between #2002-09-01#
               and #2002-12-28#
       and dateadd("d",7*i,  #2002-09-01#) <= #2002-12-28#
    group 
        by i
    let me know how it goes

    rudy

  9. #9
    Join Date
    Jan 2003
    Posts
    9
    I am sorry for being a complete moron. What is an integers table? How do you create it?

    Thank you for your time and assitance.

    WAlter

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i gave the syntax to create the integers table already

    create table integers ( i integer );
    insert into integers(i) values(0);
    insert into integers(i) values(1);
    insert into integers(i) values(2);
    insert into integers(i) values(3);
    insert into integers(i) values(4);
    ... up to ...
    insert into integers(i) values(16);

    you need at least 0 through 16 for that date range i gave

  11. #11
    Join Date
    Jan 2003
    Posts
    9
    What I am asking is this....

    In microsoft access you can create tables that have integers.
    I could call that table - IntegerTable
    Put a field with a data type Number. I could fill that with Byte, Integer, Long Integer.
    Then I could enter 1, 2, 3, 4, 5, 6, 7, etc.

    Is that what you are referring to? Or would you like me create a virtual one with code? (In which case I have no idea how this would be done)

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    > I could call that table - IntegerTable

    i call it integers, and the numeric field is called i

    just try running that code i gave, starting with create table integers...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •