I've got a list of column headers in a table, e.g.

A, B, C
1, 2, 3
4, 5, 6

etc.

In another table, I've got a list of field descriptions...

Field, Description
A, Apple
B, Boy
C, Cat,
etc,

How can I create a report which returns,..

Apple, Boy, Cat
1, 2, 3
4, 5, 6

On Mon, 22 Mar 2010 17:46:00 -0700 (PDT), Parag
<parag.bhatnagar (AT) gmail (DOT) com> wrote:

The report will have a field where you want "Apple" to appear. If you
simply generate a report based on tbl1 you would get "A".
So set its ControlSource property to:
=DLookup("Description", "tbl2", "Field='A'")

-Tom.
Microsoft Access MVP

On Mar 23, 5:31*pm, Tom van Stiphout <tom7744.no.s... (AT) cox (DOT) net> wrote:
Thanks for your help Tom. I know I initially said reporting but there
would also be a need to query across all fields, actually here are a
list of caveats:

(1) I need to be able *query* not just report across all data fields,
e.g. 'return all fields where Apple > 2'
(2) There more than 2000 column headers e.g. manually editing the
ControlSource property via DLookup isn't really an option.
(3) There are more fields than just 'Field' and 'description' all of
which we want to query across
(4) As apparent, there is no one primary key between any of the
tables.

The problem stems from a clinical database which extracts all data in
unique 'short-code' format and then gives us a table of what each
'short-code' format relates to.

Plan B so far - simply pre-process the data using a 'VLookup' in Excel
before dumping it into Access.

Thanks for your help so far.