Getting data in a report from a second table?

  • Thread starter Thread starter John O'Boyle
  • Start date Start date
J

John O'Boyle

I'm trying to create a simple report with report with one subtotal per
group, and (possibly) a grand total at the end of the report. I have
five fields in the table; date, payee, amount, memo and acct. The data
is ordered on "acct + date" and is grouped on "acct". The report has
an "acct" header and a "acct" footer in addition to the report header
and footers. I have a text-box in the "acct" footer, which is
"Control-sourced to "sum the amount". I have a second table which is
comprised of account numbers and the corresponding account name, indexed
on account number.

I'd like to be able to add a second text box in the "acct" footer which
would get it's data from the second table, using the acct number as the
indexer? I KNOW I didn't state that correctly. Anyway, I need some
guidance in the form of an expression that will reference the second
table using the acct number to get the account name.

If you've had patience enough to have read this far, and have some idea
how to help me I'd appreciate it.

Thanks.

JLOB
 
John said:
I'm trying to create a simple report with report with one subtotal per
group, and (possibly) a grand total at the end of the report. I have
five fields in the table; date, payee, amount, memo and acct. The data
is ordered on "acct + date" and is grouped on "acct". The report has
an "acct" header and a "acct" footer in addition to the report header
and footers. I have a text-box in the "acct" footer, which is
"Control-sourced to "sum the amount". I have a second table which is
comprised of account numbers and the corresponding account name, indexed
on account number.

I'd like to be able to add a second text box in the "acct" footer which
would get it's data from the second table, using the acct number as the
indexer? I KNOW I didn't state that correctly. Anyway, I need some
guidance in the form of an expression that will reference the second
table using the acct number to get the account name.
Click to expand...


Here's the general idea of doing it with an expression;

=DLookup("AcctName","Accounts","AcctNum=" & Me.AcctNum)

But, a more general approach is to use a query as the record
source of the report. Add both table to the query and make
sure there is a connecting line between the two "AcctNum
fields. This way you can include the account name in the
query's field list so it can be used directly in the report
the same as any other field.
 
Back
Top