Compare two fields!

[finditsol]

hi everybody,

I know few about access, hope to receive more helps from you all!

I have two TABLES/FORMS named "08BCTC" and "09BCTC" with the same field as ID | QUATER | INCOME | TAX |

After ENTER data in field INCOME in form "09BCTC", I want to solve these problems

+ To compare the INCOME at current quater with previous quater (in the same table). If found no data of previous quater, alert "No data"

+ And the current quater with last year quater at the same period by comparing with data in TABLE "08BCTC". If found no data, alert "No data"

+ And in form "09BCT" showing its accumulative income up-to-date.



Here is the result I want

[llkhoutx]

You need to build a temporary table stacking data via multiple queries with each Qtr data going in separate columns. That way you can compare quarters on a single row on a form or report, e.g.

TemporaryTable
ID
Qtr
LastQtrIncome
CurrentQtrIncome
LastYearQtrIncome
Tax

Query1 - append CurrentQtr data to table
Query2 - append LastQtr data to table
Query3 - append LastYearQtr data to table
Query4 - sum the columns by grouping (Click the "sigma" icon and select "sum" on each column, except ID & Qtr colkumns, on the grouping row of the query. Performs calculations in form control recordsource as required.