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

Mechixx

Board Regular
Joined
Oct 15, 2015
Messages
59
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):
DataMonthProductionUnitMTBFMTTR
Mar 19IMT 2294219324
Mar 19IMT 2355330421
Mar 19IMT 2357884423
Apr 19IMT 2294330916
Apr 19IMT 2355294213

<tbody>
</tbody>


Then i have a crosstab query that rearranges this data to look like this:
ProductionUnitMarApr
IMT 229421933309
IMT 235533042942
IMT 23578844

<tbody>
</tbody>

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!
 

Some videos you may like

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

xenou

MrExcel MVP, Moderator
Joined
Mar 2, 2007
Messages
16,627
Office Version
2013
Platform
Windows
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.
 

Mechixx

Board Regular
Joined
Oct 15, 2015
Messages
59
Thanks for your reply!

That makes sense what you've outlined here, i might possibly be able to figure out something with this now.
Agreed about the column name changing, might have to do as you say and store data which would hold the months as a PreviousMonth and CurrentMonth. The only reason those column names are changing is because they are an actual date (04/01/19) and formatted to text value, and i wasn't sure how else i could "unpivot" my data to look like it does in my second table.

Thanks again @xenou
 

Watch MrExcel Video

Forum statistics

Threads
1,095,615
Messages
5,445,484
Members
405,334
Latest member
Marcfar

This Week's Hot Topics

Top