Lookup table in a report

[Robert D. Young]

Is there a way to look up a value at report time? For example, imagine two
tables:

1. People (custid, real name)
2. Phone Calls (custid1, custid2)

I would like to take a report that currently says

"custid1 called custid2"

and change it to

"Mr. Black called Ms. Jones"

Do I have to do this in a query, or can I set the data source somehow in the
report itself? If it's a query, how do you have both "real names" appear in
the results? I suppose I'll have to use a SQL statement since I can't image
how it would be done QBE style...

Thanks,

- Robert

 

[Marshall Barton]

Is there a way to look up a value at report time? For example, imagine two
tables:

1. People (custid, real name)
2. Phone Calls (custid1, custid2)

I would like to take a report that currently says

"custid1 called custid2"

and change it to

"Mr. Black called Ms. Jones"

Do I have to do this in a query, or can I set the data source somehow in the
report itself? If it's a query, how do you have both "real names" appear in
the results? I suppose I'll have to use a SQL statement since I can't image
how it would be done QBE style...

In the QBE window, add the PhoneCalls table and add the
People table twice. Drag the custid1 field in the
PhoneCalls table to the custid field in the People table.
Also drag the custid2 field in the PhoneCalls table to the
custid field in the People1 table.

You can then add the RealName field in the People table to
the columns in the query and add an alias name, e.g.
CallFrom: RealName
People

Do the same from People1:
CallTo: RealName
People1

Now the report can have text boxes bound to the CalFrom and
CallTo fields to display the names.

 

[Robert D. Young]

Thanks - I never woulda thought of putting the same table in there twice...


- Robert