About to give up..... on coding a query

[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

 

[[MVP] S.Clark]

Why do the year's have to have their own table? Can you consolidate to just
one, then use a parameter to specify the year?


--
HTH,

Steve Clark, Access MVP
FMS, Inc.
Professional Solutions Group
http://www.FMSInc.com
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Is your Access database too slow?
Are you ready to upgrade to SQL Server?
Contact us for optimization and/or upsizing!
http://www.FMSInc.com/consulting
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

 

[Alp Bekisoglu]

Steve, you are an angel! a saviour!

I truely do not know why on earth have I got hooked up on using seperate
tables for each year!

I guess life, age, hurry and the stress of trying to complete before a
deadline had blinded my mind. I really wonder why such simple and
hassle-free solution never occured to me...

Maybe because I need to list those 8 rows for each year in a report as 4 x 4
which I did have a fight with earlier just to get it display as:
r1 r5
r2 r6
r3 r7
r4 r8
I'll try it tomorrow (its 3am now) and do hope I can figure it out again. If
I can't then I'll yell for help again.

I owe you one! A big one. If you ever end up in Malaysia, do let me know.

A million thanks!

Alp