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):
<tbody>
</tbody>
Then i have a crosstab query that rearranges this data to look like this:
<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!
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 |
<tbody>
</tbody>
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 |
<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!