A
Alp Bekisoglu
The scenario is:
There is an amount, income
There is the year factor, ya
There are tables with the following structure:
fm, to, first, nxt, amount, ratio (all are numeric)
id - autonum
These tables are named iaw years, i.e. 2000, 2001, 2002.....
Each has 8 rows of data (in increments)
Required is:
According to the "ya" (provided via a form or lookup) the corresponding
table needs to be selected and used, i.e. if ya=2001 then the table named
2001 should be selected as part of the query source.
The "income" is then matched between the "fm" and "to" figures of this
table, as fm<=income<to, within the query (criterion) and the two
corresponding figures of "amount" and "ratio" values are to be returned by
this query.
For quite some time I have been desperately trying to get this done in VBA
(using A2002 in 2000 file format) by coding the SQL to no avail. The reason
I see my only way out being in the code is taht, the alternative way would
require the creation of all these tables (say for 10 years to come) and then
replace each when the exact figures are disclosed. This also brings along
the creation of all the corresponding static queries each based on one of
these tables.
Can someone, anyone, help me achieve this before I completely go crazy? I am
willing to send what ever further information might be needed. I just did
not want to bloat the NG.
Thanks,
Alp
There is an amount, income
There is the year factor, ya
There are tables with the following structure:
fm, to, first, nxt, amount, ratio (all are numeric)
id - autonum
These tables are named iaw years, i.e. 2000, 2001, 2002.....
Each has 8 rows of data (in increments)
Required is:
According to the "ya" (provided via a form or lookup) the corresponding
table needs to be selected and used, i.e. if ya=2001 then the table named
2001 should be selected as part of the query source.
The "income" is then matched between the "fm" and "to" figures of this
table, as fm<=income<to, within the query (criterion) and the two
corresponding figures of "amount" and "ratio" values are to be returned by
this query.
For quite some time I have been desperately trying to get this done in VBA
(using A2002 in 2000 file format) by coding the SQL to no avail. The reason
I see my only way out being in the code is taht, the alternative way would
require the creation of all these tables (say for 10 years to come) and then
replace each when the exact figures are disclosed. This also brings along
the creation of all the corresponding static queries each based on one of
these tables.
Can someone, anyone, help me achieve this before I completely go crazy? I am
willing to send what ever further information might be needed. I just did
not want to bloat the NG.
Thanks,
Alp