Reports using look from 2nd table

  • Thread starter Thread starter David S. Calef
  • Start date Start date
D

David S. Calef

I need a point to creating a report that takes transactions in one table,
and looks up percents in a second table:

Table1: Account
Code
type
totalamount


Table2 Account
Code
Type
Percent1
Percent2
Percent3
Percent4

There are multiple transactions in table 1 that a report (or query) must
look up the 4 percents for that transactions Type, by Code and by
Account.... then I use the 4 percents to calculate the dollars splits I need
in the report. Each separate report will be for just one account that I
specify when I run the account.

If I could do this in a query, great.

If I need to do this in VBA....., where?

Or is an approach using Crystal easier.

I have done simple reports and queries for years. This I need a point to a
direction on how to accomplish.....

Thanks,

David
 
David,

In a query add table 1 and table 2 then join them by
[account] and
Code: 
. This should return the percentages
where the account and code match. With the percentages
available in your record source calculating your dollar
splits should be straight forward.

HTH,

Terry
 
Thanks Terry.

If I do a join on account, it gives me the table 2 percents.

If I do a join on account and code, it returns nothing.

I am going to need to do joins on account, type and code, but I can't seem
to make that work...

David


Terry said:
David,

In a query add table 1 and table 2 then join them by
[account] and
Code: 
. This should return the percentages
where the account and code match. With the percentages
available in your record source calculating your dollar
splits should be straight forward.

HTH,

Terry
[QUOTE]
-----Original Message-----
I need a point to creating a report that takes transactions in one table,
and looks up percents in a second table:

Table1:   Account
Code
type
totalamount


Table2   Account
Code
Type
Percent1
Percent2
Percent3
Percent4

There are multiple transactions in table 1 that a report (or query) must
look up the 4 percents for that transactions Type, by Code and by
Account.... then I use the 4 percents to calculate the dollars splits I need
in the report.  Each separate report will be for just one account that I
specify when I run the account.

If I could do this in a query, great.

If I need to do this in VBA....., where?

Or is an approach using Crystal easier.

I have done simple reports and queries for years.  This I need a point to a
direction on how to accomplish.....

Thanks,

David


.
[/QUOTE][/QUOTE]
Click to expand...
 
Back
Top