none
How to select columns of different criteria from same table

    Question

  • Hi all,

    I've got a table with a lot of information and I want to return data that is of a user determined year in one column and data of another year in a different column so I can compare them. Unfortunately I'm having trouble doing this - any WHERE statements are applied on the whole query.

    For example, I want to compare the data from 2012 to the data from 2013. Is it possible to do this?

    Tuesday, July 09, 2013 3:07 PM

Answers

  • The easiest way to do what you are suggesting is to create 2 temp tables.  tmp1 would contain all the rows for 2012 and tmp2 would contain all the rows for 2013.  I give you some sample data that is contained in mainTable.  You copy records from 2012 to tmp1 and records from 2013 to tmp2. Now you could write a query like this:

    FirstName  LastName  datefld     items
      joe            smith       1/1/2012      A
      joe            smith       2/1/2012      B
      joe            smith       3/1/2012      C
      joe            smith       1/1/2013      D
      joe            smith       2/1/2013      E
      joe            smith       3/1/2013      F
      joe            smith       4/1/2013      G
      bill             jones      2/7/2012      A
      bill             jones      3/7/2012      B
      bill             jones      4/7/2012      C
      bill             jones      5/7/2012      D
      bill             jones      6/7/2012      E
      bill             jones      1/7/2013      A
      bill             jones      2/16/2013    B

    select t1.FirstName, t2.firstName, t1.LastName, t2.LastName, t1.datefld, t2.datefld, t1.Items, t2.Items, ... from tmp1 t1 left join tmp2 t2 on t1.firstName = t2.FirstName and t1.LastName = t2.Lastname


    Rich P



    • Edited by Rich P123 Tuesday, July 09, 2013 3:39 PM .....
    • Marked as answer by yogiebere Tuesday, July 09, 2013 9:00 PM
    Tuesday, July 09, 2013 3:37 PM

All replies

  • what kind of data do you want to compare or what is the final result?  Maybe you need to perform various queries in order to accomplish your task.  Please provide sample of the data.


    • Edited by La Mole Tuesday, July 09, 2013 3:29 PM More explanation
    Tuesday, July 09, 2013 3:28 PM
  • The easiest way to do what you are suggesting is to create 2 temp tables.  tmp1 would contain all the rows for 2012 and tmp2 would contain all the rows for 2013.  I give you some sample data that is contained in mainTable.  You copy records from 2012 to tmp1 and records from 2013 to tmp2. Now you could write a query like this:

    FirstName  LastName  datefld     items
      joe            smith       1/1/2012      A
      joe            smith       2/1/2012      B
      joe            smith       3/1/2012      C
      joe            smith       1/1/2013      D
      joe            smith       2/1/2013      E
      joe            smith       3/1/2013      F
      joe            smith       4/1/2013      G
      bill             jones      2/7/2012      A
      bill             jones      3/7/2012      B
      bill             jones      4/7/2012      C
      bill             jones      5/7/2012      D
      bill             jones      6/7/2012      E
      bill             jones      1/7/2013      A
      bill             jones      2/16/2013    B

    select t1.FirstName, t2.firstName, t1.LastName, t2.LastName, t1.datefld, t2.datefld, t1.Items, t2.Items, ... from tmp1 t1 left join tmp2 t2 on t1.firstName = t2.FirstName and t1.LastName = t2.Lastname


    Rich P



    • Edited by Rich P123 Tuesday, July 09, 2013 3:39 PM .....
    • Marked as answer by yogiebere Tuesday, July 09, 2013 9:00 PM
    Tuesday, July 09, 2013 3:37 PM
  • Rich,

    Thank you for you example.  I do agree with you and that's what i had in mind.  This is the easiest way.

    Tuesday, July 09, 2013 4:09 PM
  • Sounds great. Sorry for being such a beginner - how do I make temporary tables? Is it like a view?
    Tuesday, July 09, 2013 5:39 PM
  • tables are created by using a make-table query in Access.  Create the 2 temp tables  (One for 2012 and the other one for 2013) and then join them by first and last name (Left Join)

    Tuesday, July 09, 2013 6:30 PM
  • In my example, I purposely added different numbers of rows for 2012 and 2013 for each name so that you don't get a one to one correspondence -- like you have 3 2012's and 3 2013's for joe smith.  You have like 3 2012's and 4 2013's for example.  The left join will get all the rows from the left table and only the rows that match from the right table.  So for bill jones I think I had 5 rows for 2012 and 2 rows for 2013.  The left join would get all 5 rows from the 2012 table and the 2 rows from the 2013 table for bill jones.  The rest of the fields for the 2013 table would just be empty (null) fields in the query result. 

    To get the extra rows from the right table you can do either of two things (in Access Jet sql there are two ways -- doesn't work in Transact sql -- for sql server) .   The conventional way would be switch the 2013 table to the left side and 2012 on the right and use the left join.  But in Access Jet sql you can also do a right join to get the all the rows from the right table and the matching rows from the left table.


    Rich P



    • Edited by Rich P123 Tuesday, July 09, 2013 6:50 PM .....
    Tuesday, July 09, 2013 6:48 PM
  • Ok sounds good. Yeah that's a great way, then I don't have to use a WHERE to match them.

    As for creating the tables, is there a way to delete them? I imagine if the query is called several times it will throw an error when it tries to make a table of the same name.
    Tuesday, July 09, 2013 6:52 PM
  • Ok I tried the first Temp Table Creation:

    SELECT [tbl Project].Project, [tbl Months].Trips, Sum([tbl Months].Lane) AS SumOfLane, Sum([tbl Lanes].Pounds) AS SumOfPounds INTO Temp_Compare1 FROM [tbl Project] INNER JOIN ([tbl Lanes] INNER JOIN [tbl Months] ON [tbl Lanes].[Lane Name] = [tbl Months].Lane) ON [tbl Project].ID = [tbl Months].ID WHERE ((([tbl Months].[P Year])=[Forms]![frm_YearComparison_Report]![Combo25])) GROUP BY [tbl Project].Project, [tbl Months].Trips;

    But ran across this error:

    "A calculated column cannot be saved without a valid expression in the Expression property"
    Tuesday, July 09, 2013 7:21 PM
  • The best answer actually depends on what you mean by "compare".

    In query design view; one can add the same table (or query) multiple times ; and then you could create a join line between the 'Items' fields. 

    You could consider making a query for 2012 another for 2013, etc. 

    It's manual, it's a bit crude but it is simple and if the quantity of years is only a few should work fine.  This would allow you to put side by side annual values.

    Maybe helps a little...... 

    Tuesday, July 09, 2013 7:58 PM
  • To create a temp table and remove it on the fly do this:

    Dim tdf As TableDef
      
    For Each tdf In CurrentDb.TableDefs
       If tdf.Name = "tmp2012" Then
          DoCmd.RunSQL "Drop table tmp2012"
       End If
       If tdf.Name = "tmp2013" Then
          DoCmd.RunSQL "Drop table tmp2013"
       End If
    Next
      
    DoCmd.RunSQL "Create table tmp2012(FirstName text(50), LastName text(50), Datefld date, Items text(50))"
    DoCmd.RunSQL "Create table tmp2013(FirstName text(50), LastName text(50), Datefld date, Items text(50))"


    Rich P

    Tuesday, July 09, 2013 8:04 PM
  • Success! I got it to work (for now haha).

    Thanks for all the help guys, you're the best!

    Tuesday, July 09, 2013 9:00 PM