Thanks Thanks:  0
Likes Likes:  0
Results 1 to 2 of 2

Thread: Calculated query column that returns true or false based on first 2 columns

  1. #1
    Board Regular
    Join Date
    Oct 2015
    Posts
    54
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Calculated query column that returns true or false based on first 2 columns

    Hello there!

    So I'm trying to create a query in Access that will have a calculated column that compares the first 2 columns and return True if the first column number is less than the 2nd column number. how would i go about doing this? tricky thing is, the first 2 column headings change based on the month (first 2 columns will be the last 2 months)

    to set the stage:
    i have a query that filters a table of data to only display last 2 months (example of what it looks like):
    DataMonth ProductionUnit MTBF MTTR
    Mar 19 IMT 2294 2193 24
    Mar 19 IMT 2355 3304 21
    Mar 19 IMT 2357 8844 23
    Apr 19 IMT 2294 3309 16
    Apr 19 IMT 2355 2942 13


    Then i have a crosstab query that rearranges this data to look like this:
    ProductionUnit Mar Apr
    IMT 2294 2193 3309
    IMT 2355 3304 2942
    IMT 2357 8844

    this is the SQL behind it:
    TRANSFORM Sum([qry_Tbl_MTBF_MTTR Query].MTBF) AS SumOfMTBF
    SELECT [qry_Tbl_MTBF_MTTR Query].ProductionUnit
    FROM [qry_Tbl_MTBF_MTTR Query]
    GROUP BY [qry_Tbl_MTBF_MTTR Query].ProductionUnit
    ORDER BY Format([DataMonth],"mmm") DESC
    PIVOT Format([DataMonth],"mmm");

    So from this i want to add a calculated column that compares the Mar and Apr column and if Mar is smaller than Apr then output True, if not then False. If either column doesn't have data i want to skip it or just leave it blank.
    Keep in mind that the 2 month columns will continue changing based on what month it is currently (as those columns will always be last 2 months)

    If not a calculated column, is there a way i can acheive this by another query to do the calculating?

    Any help on this would be greatly appreciate, thanks!

  2. #2
    MrExcel MVP
    Moderator
    xenou's Avatar
    Join Date
    Mar 2007
    Location
    Clev. OH, USA
    Posts
    16,301
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Calculated query column that returns true or false based on first 2 columns

    Hi,

    A low budget solution would be to turn your query into a make table query (or something of that sort - get the data into a table).

    Then you can add the columns you need with a further query:

    QueryFoo (your query)
    Code:
    TRANSFORM Sum([qry_Tbl_MTBF_MTTR Query].MTBF) AS SumOfMTBF
    SELECT [qry_Tbl_MTBF_MTTR Query].ProductionUnit
    FROM [qry_Tbl_MTBF_MTTR Query]
    GROUP BY [qry_Tbl_MTBF_MTTR Query].ProductionUnit
    ORDER BY Format([DataMonth],"mmm") DESC 
    PIVOT Format([DataMonth],"mmm");
    QueryBar (a make table query)
    Code:
    SELECT QueryFoo.ProductionUnit, QueryFoo.Mar, QueryFoo.Apr INTO TableFoo
    FROM QueryFoo;
    QueryBaz (get the results)
    Code:
    SELECT TableFoo.ProductionUnit, TableFoo.Mar, TableFoo.Apr, 
    IIF((TableFoo.Mar - TableFoo.Apr) is null, null, IIF((TableFoo.Mar < TableFoo.Apr), 'True', 'False')) as ChangeInValue
    FROM TableFoo;

    I can't give any good advice about the Mar - Apr problem (becoming Apr - May, then May - Jun, and so on). I would simply not use changing names for coiumn names. I would call Mar PreviousMonth and Apr CurrentMonth. Or I would be storing all the intermediate results in a data store that can be queried independently using parameters to pull data by any required range of periods or in comparison to other periods over time.

    Using: Office 2010/Win7 (work) Office 2010/Win7 (home)

    You are rich in proportion to the number of things you can let alone.
    -- Henry David Thoreau

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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