Results 1 to 7 of 7
  1. #1
    Bob M is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Mar 2013
    Posts
    43

    query to sample data in a set time interval

    I have data which has the following fields:
    Date
    Time
    Variable
    --------
    Time goes down to milliseconds
    I wish to group all records where the time is in a 30 minute interval and output 1 record which averages the variable values.

    Is it possible to do this with a query ?

    (it is like the grouping available in excel when producing a pivot table)

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Date and Time are reserved words in Access.
    See http://allenbrowne.com/AppIssueBadWord.html for complete list.

    Readers will respond with options once they understand your situation. Please describe WHAT you are trying to achieve in plain English - give examples if necessary.
    Good luck.

  3. #3
    Bob M is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Mar 2013
    Posts
    43
    I have the following field in a .csv file
    20130201 00:00:00.007
    20130201 00:00:00.105
    20130201 00:00:00.206
    the first part is the date and the second part is time

    I wish to import the .csv file into Access 2007 and create new fields based on the time portion of the original imported field

    How do I a) import said file
    b) ensure that the time values are recognised by Access as 'time'

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Don't think Access will handle milliseconds. Is the data really on two lines?

    Access won't recognize the string as a date value. If you use the wizard will have to import as text then use string manipulation to rearrange the date parts then save to a date/time type field. The alternative is to open the csv file as an object in VBA and read one line at a time and manipulate each value into a date structure.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    Bob M is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Mar 2013
    Posts
    43
    Milliseconds - a problem OK. I have >12 million records per month -> and so I have a problem!
    No the data is in a single field but when I created the post the time part popped down a line
    I have tried importing as text, splitting the field into date and time fields and then formatting both as date and time i.e. "yyyymmdd" and "hh.nn.ss" but I keep getting errors

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    12 million records a month!!!!

    Access has a 2gb size limit.

    Access doesn't recognize the string as a date so applying a date format won't work. Have to use string manipulation functions to extract the date parts and reconstruct as a date value. Here is one way.

    x represents the string: DateSerial(Left(x,4), Mid(x,5,2), Mid(x,7,2))
    Last edited by June7; 03-30-2013 at 11:31 AM.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Bob,
    Further to June7's comments, can you tell us more about your application and data collection mechanism. There may be options for you,if we knew more about your set up. How many data points remain after each load of new data?
    The 2 gb may be a big issue. And getting your data into a format readily useable by Access may be another.
    There may be other storage options such as MySQL or SQLServer, but until we know more readers can't advise.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. combo box for date interval
    By hellox3 in forum Access
    Replies: 3
    Last Post: 01-15-2013, 02:07 AM
  2. Interval Reporting
    By mlopez in forum Queries
    Replies: 3
    Last Post: 07-16-2012, 05:48 PM
  3. Sample DB : Search Multiple Data Separated by Comma
    By spideynok in forum Sample Databases
    Replies: 3
    Last Post: 03-29-2012, 10:52 PM
  4. Interesting Query Problem (Sample Data Provided)
    By pinecrest515 in forum Queries
    Replies: 1
    Last Post: 02-07-2011, 03:27 PM
  5. Northwind sample database query
    By wasim_sono in forum Programming
    Replies: 5
    Last Post: 02-28-2006, 07:34 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums