VBA code for field/record selection

[qwert1]

I have 2 tables.

table 1 looks like:
Test1 Test2 Test3
0.6 0.1 0.5
0.4 0.4 0.3
0.2 0.8 0.5

table 2 looks like:
Test LowLimit HighLimit
Test1 0.3 0.5
Test2 0.3 0.6
Test3 0.2 0.6

I want to generate a table like following (if tbl1 record value is between high/low limit of tbl2, it's good, otherwise bad)
Test1 Test2 Test3
Bad Bad Good
Good Good Good
Bad Bad Good

how can i achieve this?

thanks a billion....

[emsik1001]

Hi

First Quick solution:

Your 'database' looks like spreadsheet approach so just export to excel and then it should be very easy to do and import to db.

Second option would be to redesign the tables to become a proper database
check 'normalization' on wikipedia. (The design will depend on the 'big picture' of your database)

Hope it helps a little bit.

Regards
Emil

[qwert1]

Hi Emil,

Thanks for your reply.

What I am trying to do is only one step of a long process, and will be repeated for multiple tables & many times on daily basis. In short, Excel approach will not work for me.

My real problem is that I don’t know how to:
Select a specific field with VB code.
Select a specific record after a field been selected from a table with VB code.
Find AbsolutePosition of a record from a certain field,

I googled internet but did not get any useful info.

Can you point me to the direction that I can learn how to manipulate data records using VB?

Thanks!!

[NeoPa]

Basic DAO recordset loop using two recordsets may help you there, but I would first look at Normalisation and Table structures.

Your Table1 needs to be Transposed. The fields should be records. It can probably be done using VBA but really, as there is no identifier in your records in Table1, it is undefined, and therefore useless, information.

[qwert1]

NeoPa,

Thanks for your help.

In order to focus on the issue, I just made up a small tbl of data that represents my problem. The real db tbl contains more info as exampled here and are better structured.

Thanks again...

[NeoPa]

I would think the layout you showed must be at least representative though (surely?). If so, then you still have the same problem and need to look at it. If not, then we probably need something that reflects the actual situation.

BTW I applaud the concept of knocking together a test-bed. Just be careful that it's a close enough representation so you're not getting irrelevant results.