Report info from other table

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

NetworkTrade - 02 Aug 2006 12:30 GMT

Have Report based on Table1

Table1 is records of Devices and each record has 20 fields which are all
codes that effectively describe the Device of record.

So the Report page is one device/record - and it lists the 20 field codes in
txtCode1 thru txtCode20

But some readers need to see a short description to understand what the code
is. There is another table that is the Code/Description table (C-DTable). 2
column only but about 15,000 records with all the codes.

I would like to add unbound txtboxes or labels to the Report - with one next
to each of the existing txtCode boxes....and have appear the correct
description. Not sure how I set that up.

thnx

Signature

NTC

Reply to this Message

Duane Hookom - 02 Aug 2006 12:47 GMT

It sounds to me like you have "committed spreadsheet" by creating fields
rather than records. I could be wrong but you haven't provided much
information or sample records.

If you can't normalize your data, you should consider creating a union query
that would create 20 related records from the values in 20 fields. You could
then link these 20 records to the C-DTable and display the descriptions.

Signature

Duane Hookom
MS Access MVP

> Have Report based on Table1
>
[quoted text clipped - 17 lines]
>
> thnx

Reply to this Message

NetworkTrade - 04 Aug 2006 14:49 GMT

thanks - in this case I don't think there is a normalization problem. The
central Device Table has just one record per device as it should.

Each device is custom made with about 20 parameters - and there is a pool of
15,000 pre-defined parameter records in a C-D Table (Code-Description Table).

So a record of one device in the Device Table has generally 20 fields each
with a code. And the sum of 20 codes effectively describes that device for
the end user.

Its pretty efficient in this regard. In whipping up an easy Report from
this central Device Table it is of course only showing the codes for each
device record - as it should - but many humans also need the corresponding
description from the C-D Table (which should not be duplicated in the central
Device Table) as a visual aid.

Will try the Union as you suggest. I was thinking that label(s) along side
the codes in the report would/could be the way to go with them bound or
calling the appropriate description from the CD Table that corresponds to a
code...obviously am experimenting around with this.

As some of the descriptions are lengthy am a little worried on exceeding
record length max but not sure if that is an issue for a query based union as
long as it is not a single table record.

thanks again,

Signature

NTC

> It sounds to me like you have "committed spreadsheet" by creating fields
> rather than records. I could be wrong but you haven't provided much
[quoted text clipped - 25 lines]
> >
> > thnx

Reply to this Message

Duane Hookom - 04 Aug 2006 21:00 GMT

I would have table of parameter values with 20 records rather than 20
fields.This would allow linking to the code-description table.

Signature

Duane Hookom
MS Access MVP

> thanks - in this case I don't think there is a normalization problem. The
> central Device Table has just one record per device as it should.
[quoted text clipped - 64 lines]
>> >
>> > thnx

Reply to this Message