Results 1 to 7 of 7
  1. #1
    Join Date
    Aug 2006
    Posts
    18

    Question Unanswered: help to make query by time

    Good day,

    I have a table with many records each record has specific time. I would like to make query by time to see how many transaction has been done every 10 minutes.

    I made a query its show me only one period. how to see all others period.

    For example:

    Start Time--End Time -- Amount (Total of amount for the period)
    10:00:00 -- 10:10:00 -- 2000
    10:10:01 --- 10:20:00 -- 1500

    and for all other period :

    I have attached the file with the data table and query.

    any help is very appreciated..

    Thanks in Advance
    Attached Files Attached Files

  2. #2
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Hint -- This expression will convert your time 10 minutes intervals

    CDate(DatePart("h",[Time]) & ":" & Left(DatePart("n",[time]),1) & "0")

    Therefore, 10:16 will become 10:10 and 10:04 will become 10:00

    I notice your data has 255,000 records over a 16 minute period. You
    won't keep that pace up for long.
    Inspiration Through Fermentation

  3. #3
    Join Date
    Aug 2006
    Posts
    18
    Hi

    can you please till me where to but this expression. I put it in criteria. the query dosnt accept it. or if you dont mind could you please make in attached db file.

    note: I have many records during 4 hours, but I decreesed the data to get small size so I can attach it.

    thanks for assistant
    Last edited by sarab99; 08-29-06 at 13:13.

  4. #4
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    it is not a criterion.
    in the next available empty "Field" box in query design, type

    my10minLumps: CDate(DatePart("h",[Time]) & ":" & Left(DatePart("n",[time]),1) & "0")

    and run the query.

    izy
    currently using SS 2008R2

  5. #5
    Join Date
    Aug 2006
    Posts
    18
    Hi Expert the code give me error

    http://www.atawi.net/up/upload/atawi_6303329.jpg

    by the way this not what I am loking for .

    Is it possible to be like this
    http://www.atawi.net/up/upload/atawi_6000085.jpg

    Thank you for trying to assist me I appreciated.
    Last edited by sarab99; 08-29-06 at 20:43.

  6. #6
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    The error is probably caused by bad data. Are you sure everything in the field evaluates as strictly time 10:16:24 am and not 12/23/2005 10:16:24?

    Your example shows 15 minute intervals, and you asked for how to group on 10 minute intervals. 15 minutes intervals require a different type of solution (one I'm not sure I can help with).
    Inspiration Through Fermentation

  7. #7
    Join Date
    Aug 2006
    Posts
    18
    Thanks its ok now

Posting Permissions

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