3 Replies Latest reply on Dec 19, 2016 7:48 AM by Mahfooj Khan

    Custom SQL to union and Join 'Date/Time' to 'Date'

    Josh V

      I have the following 4 tables in Access.  Three of them are tables that need to be unioned (Table 1, Table 2, Table 3) with exact same column names

       

      Table 1

      Date/TimeAirportCountry
      1/1/2014 12:02:00 AMLGAUSA
      1/1/2014 12:30:00 AMYYZCanada
      1/1/2014 12:59:00 AMOGGUSA

       

      I was able to do that successfully do the union with the following query

       

      SELECT* FROM [Table 1]

      UNION ALL

      SELECT *  FROM [Table 2]

      UNION ALL

      SELECT *  FROM [Table 3]

       

      Now i need to do an inner join to the following table in the same database with date field.

      Table 4:

      DateRain
      1/1/201412
      1/2/201415
      1/3/201420

       

       

      I figured that I would need to convert the date/time to date so far all I came up was this:

       

      SELECT [u].[Date/Time] AS [Date/Time],

          [Table 4].[Date] AS [Date],

          [Table 4].[Temp] AS [Temp]

      FROM ( SELECT* FROM [Table 1]

      UNION ALL

      SELECT *  FROM [Table 2]

      UNION ALL

      SELECT *  FROM [Table 3]) AS [u]

          INNER JOIN [Table 4] ON (TO_CHAR([u].[Date/Time],'DD/MON/YYYY') = [Table 4].[Date])

       

      However I cant get it work . My final table should have  the following columns

      Date/TimeDateAirportCountryRain

       

      Your help will be greatly appreciated. Thanks!