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

[Mechixx]

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):
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]DataMonth[/TD]
[TD]ProductionUnit[/TD]
[TD]MTBF[/TD]
[TD]MTTR[/TD]
[/TR]
[TR]
[TD]Mar 19[/TD]
[TD]IMT 2294[/TD]
[TD]2193[/TD]
[TD]24[/TD]
[/TR]
[TR]
[TD]Mar 19[/TD]
[TD]IMT 2355[/TD]
[TD]3304[/TD]
[TD]21[/TD]
[/TR]
[TR]
[TD]Mar 19[/TD]
[TD]IMT 2357[/TD]
[TD]8844[/TD]
[TD]23[/TD]
[/TR]
[TR]
[TD]Apr 19[/TD]
[TD]IMT 2294[/TD]
[TD]3309[/TD]
[TD]16[/TD]
[/TR]
[TR]
[TD]Apr 19[/TD]
[TD]IMT 2355[/TD]
[TD]2942[/TD]
[TD]13[/TD]
[/TR]
</tbody>[/TABLE]


Then i have a crosstab query that rearranges this data to look like this:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]ProductionUnit[/TD]
[TD]Mar[/TD]
[TD]Apr[/TD]
[/TR]
[TR]
[TD]IMT 2294[/TD]
[TD]2193[/TD]
[TD]3309[/TD]
[/TR]
[TR]
[TD]IMT 2355[/TD]
[TD]3304[/TD]
[TD]2942[/TD]
[/TR]
[TR]
[TD]IMT 2357[/TD]
[TD]8844[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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!

 

[xenou]

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)

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)

SELECT QueryFoo.ProductionUnit, QueryFoo.Mar, QueryFoo.Apr INTO TableFoo
FROM QueryFoo;

QueryBaz (get the results)

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]

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