Results 1 to 8 of 8
  1. #1
    MrMarmite is offline Novice
    Windows 10 Office 365
    Join Date
    Apr 2020
    Posts
    8

    When 2 become 1

    Hi,



    I have two queries which I want to combine. They both have identical structures. There are many fields, however, what this boils down to, I think, is this simple example

    Q1:
    Field Value
    Type S
    Value E
    DateTime 25/07/2020 04:32:27

    Q2:
    Field Value
    Type R
    Value F
    DateTime 25/07/2020 04:32:27


    What I want to be able to do is create

    Field Value
    Type1 S
    Value1 E
    Type2 R
    Value2 F
    DateTime 25/07/2020 04:32:27


    This is based on the DateTimes being the same in the two records.

    Any suggestions are welcome

    Thanks

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    21,557
    Start a new query and add both those queries to the grid. Create a join on the date/time value (it will need to be EXACTLY the same). Add the desired fields from the two queries. You can alias the fields to identify which query each value is coming from.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    MrMarmite is offline Novice
    Windows 10 Office 365
    Join Date
    Apr 2020
    Posts
    8
    Thanks...I tried that and got nothing...and the dates are the same to the second.

    Quote Originally Posted by pbaldy View Post
    Start a new query and add both those queries to the grid. Create a join on the date/time value (it will need to be EXACTLY the same). Add the desired fields from the two queries. You can alias the fields to identify which query each value is coming from.

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    21,557
    Can you attach the db here?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    Ajax is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    8,723
    and the dates are the same to the second.
    dates are stored as double type numbers - the bit after dp representing time as number of seconds divided by the number of seconds in the day (86400). Doubles do not make good fields to join on as you can get very small variations in the actual value which visually is rounded to the nearest second.

    suggest try converting the date fields to text using the format function and joining on those instead

    Code:
    SELECT *
    FROM TblA, tblB
    WHERE format(tblA.DT,"dd/mm/yyyy hh:nn:ss")= format(tblB.DT,"dd/mm/yyyy hh:nn:ss")
    or introduce an element of allowance

    Code:
    SELECT *
    FROM TblA, tblB
    WHERE tblA.DT between dateadd('s',-1,tblB.DT) and dateadd('s',1,tblB.DT)

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    49,885
    Why are you needing to combine queries which were likely derived from same source data? Maybe these intermediate queries are not needed. Provide sample raw data. If you want to provide db for analysis, follow instructions at bottom of my post.
    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
    MrMarmite is offline Novice
    Windows 10 Office 365
    Join Date
    Apr 2020
    Posts
    8
    Quote Originally Posted by June7 View Post
    Why are you needing to combine queries which were likely derived from same source data? Maybe these intermediate queries are not needed. Provide sample raw data. If you want to provide db for analysis, follow instructions at bottom of my post.
    Many thanks. I would need to recreate a similar dB, as this one has a lot of personal details in it. They were both created from the same data. Essentially one of them is the sent values, and the other is the received values. Each transaction creates these two entries. The only common field is the time stamp, and even that can be a second different. It's very frustrating

  8. #8
    MrMarmite is offline Novice
    Windows 10 Office 365
    Join Date
    Apr 2020
    Posts
    8
    Many thanks. I was beginning to think that even though two dates look the same, they might not be stored as the same.

    Quote Originally Posted by Ajax View Post
    dates are stored as double type numbers - the bit after dp representing time as number of seconds divided by the number of seconds in the day (86400). Doubles do not make good fields to join on as you can get very small variations in the actual value which visually is rounded to the nearest second.

    suggest try converting the date fields to text using the format function and joining on those instead

    Code:
    SELECT *
    FROM TblA, tblB
    WHERE format(tblA.DT,"dd/mm/yyyy hh:nn:ss")= format(tblB.DT,"dd/mm/yyyy hh:nn:ss")
    or introduce an element of allowance

    Code:
    SELECT *
    FROM TblA, tblB
    WHERE tblA.DT between dateadd('s',-1,tblB.DT) and dateadd('s',1,tblB.DT)

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

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 - Senior Forums