Hi everyone, I've searched for answers and read my books until I can't see strait, but I can't work this out.

I have a simple query of two tables. The two tables have fields allowing a relationship.

In the first table there is data I need to use to return results from the second table...Nothing unusal about that.

The second table is made up of a lot of columns and I need to return the data only held in the relevant column. I could simply include all of these columns in the query but the result would be a very wide query.

The column names in table two match the data in the field I'm trying to match from table 1. I guess what I'm looking for is to use the data from table one and use this to specify which field name (column) to return from the second table...I don't think I'm being very clear :confused:


The best way for me to explain this would be comparing it to a Hlookup in excel.

Can I include screen shots? Would that help? Is this the most unclear request for help in this forums history? :eek: :eek:

Are you using the visual query editor for this? Not sure why you don't just select the fields you want and drag the common columns from one table to the other, letting the tool do all the work. This should be a 3 second thing. Much easier to use than Excel functions.

I love your graphic!

Without seeing any additional information, this sounds a lot like the following query (Substitute for Table and Column Names as required):

SELECT <Table1Key>, <Table2Column1>, <Table2Column2>,... <Table2ColumnLast>
From Table1 INNER JOIN Table2 ON Table1Key=Table2Key

You would need to build the SQL dynamically after determining the value from table 1.

The real problem is your table design. Table 2 is short and wide when it should be long and narrow. Each column of table 2 should actually be a row with the identifying value. That way you can simply join tbl1 to tab2 on the value of the column with the field name. So if the value in tbl1.fldX = "Rent", you would join it to tbl2.fldX to return the "rent" amount. If tbl1.fldX = "Insurance", the join would return the tbl2 row where fldX = "Insurance" so you would see the Insurance amount.

Thanks for that but this is all a little beyond my knowledge level...

Is there no simple way of using the data result from TERM_DIST which luckily will exist as a field name within table KwikFitTyres

i.e.

If the data from TERM_DIST = 20000, the resulting data from column 20000 in KwikFitTyres is returned.

I've looked at using Dlookup but I think this might be wrong?

Sorry to be crap :(

http://www.benje.co.uk/ross.JPG