newbe pulling infor from two sources

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I made a report to count the number of each type of transaction...I get the
type from taking each voucher and cutting off the first two letters from the
voucher number "Left$([VOUCHER],2)"...the report is created from a query. So
my report looks like:

Summary for CP = 50
Summary for MJ = 1400
Summary for PC = 10850

I have another table that explains what each two digit code is called Prefix
(CP = Copy Card, MJ = Stores Voucher)
How do I change my "Left$([VOUCHER],2)" to use the name instead.
And
In the Report "Record Source" which is "MyQuery", do I change it to reade
"MyQuery,Prefix" to make this work?
 
Looks as if you have committed a design error and are storing two pieces of
information in one field. This would be simple to solve if you split out the
VoucherType (one field) from the VoucherNumber (second field). Then it would be
a simple inner join in your query to get the expansion of the two-character code.

You can still do this with your underlying query. You have to modify the query
in the SQL text view though.

Add your Prefix table to the query.
Draw a join between Voucher and the Prefix abbreviation.
Add the expansion field to the fields to output
Now change the Query view to SQL

Find the area of the query that looks something like
YourTable INNER JOIN PrefixTable ON YourTable.Voucher = PrefixTable.Abbreviation

Change that to
YourTable INNER JOIN PrefixTable ON Left(YourTable.Voucher,2) = PrefixTable.Abbreviation

You CANNOT display this in the query by example view.

Another option would be to use the DLookup Function on your report. Set the
source for a control to something like

=DLookup("TransactionType","PrefixTable","Abbreviation=""" & LEFT(Voucher,2) & """")

Doing a DLookup repeatedly will be slow.
 
Back
Top