Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2012
    Posts
    2

    Unanswered: Time comparison query

    Hello-

    I have two tables, one with 13,000 records (Table A), another with 130 records (Table B). Both tables have times in them, but only some of the times actually match between the tables. I need to develop a query to find the closest match between the times in Table A to Table B. So for instance, if Table A has times of 08:01:00,08:01:10, and 08:01:20, etc... and Table B has a time of 08:01:01, I'd like to be able to identify the records in Table A that are within 5 seconds of the times in Table B. In this case, the closest time to 08:01:01 (from Table B) is 08:01:00 (from Table A). the query would only identify those records that in Table A that are within 5 seconds of the times in Table B. I don't know SQL very well, but, if someone provides the statements I can create the query. Thanks in advance.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    assuming your time values are stored in a datetime column, if not then why not?, then it should be a breeze
    use the datediff function

    it won't be very efficient and will probably hog the processor for a while
    I'd strongly recommend that you make certain the columns are indexed.

    the where clause would look something like
    Code:
    where ABS(datediff("s",ADateColumn,TheOtherDateColumn)) <= 5
    replace ADateColumn and TheOtherDateColumn with the names of the relevant columns
    you would need to check if that actually works, Im not to certain if datediff expects the lower value first or not.
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Oct 2012
    Posts
    2
    Thanks...got it to work, sort of. I built the Where clause you provided into an Expression ("Expr1: Abs(DateDiff("s",[Table B],[Table A]))<=5"). However, the results of the query only shows those records that have the same time between the two tables, not the times in Table A that are within 5 seconds of the times in Table B (identified by a "-1" in the Expr1 column). I was already able to get those results by joining the 2 times fields. I've tried tweaking the Join Properties as well, but that only changes the number of records, but doesn't give the results I need. Could it be the joins? I have tried just joining at Date, but that produces 380K results (way more that should be). Anyone have any thoughts? Thanks in advance.
    Attached Thumbnails Attached Thumbnails Untitled.jpg  

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I so rarely use the query designer I find it hard to diagnose what is going wrong

    however first off please correct your column names and don't use reserved words.. its going to bite you in the backside at some stage.
    https://www.google.co.uk/#hl=en&scli...w=1452&bih=913

    storing lat / long as separate degrees & minutes is limiting (especially if you need to do any spatial analysis of the results). far better to store as decimal degrees

    can we see the table design, IE the datatypes of the columns

    if you wish you could post the DB here
    take a backup copy
    compact & repair (see the tools) to remove any unused space in the DB
    then compress it as, say a zip,
    then add the file as an attachment
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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