Loop query needed??

[gillianleec]

I have a table with the fields: ID, OrderID, Test0, Test1, Test2, Test2, ...Test40

I know this is not normalized but I don't have control over that part. What I need to do is build a report that is grouped by ID that displays each test (matched to another table by testname (Billrules) for the price of that test) in a separate line.

Example:

ID=090923890
CAB (this is the testname) $26.00
TEX $13.00

etc. for all the tests on a single record. Most records only have 3-4 test fields filled in. Here is a sample record: ID=090923890, OrderID=456456, Test0=CAB, Test1=TEX


I hope this is clear...
Gillian

 

[Blorf]

To do this, you are going to have a MONSTER query that links each test field to another copy of the cost table. If you really can't get the data changed, I will try to help, but tell those who prevent you from changing that the db structure is truely bad.

ChaZ

 

[gillianleec]

I would really appreciate the help. This is a purchased program and it has all the functionality that they need in other areas. It is written in dbVista in Poland.

Is there any way we can write a loop for this. The field names are test1, test2, ... Maybe a testand then a counter??

Gillian