hide repeat values in a join

[Purnima Sharma]

I have created a join between two tables: tableA and tableB. Both the tables
are identical in structure. Table a lists the data for January and table B
lists the data for the whole year (previous). The tables are joined on loan#
and Order#. There is a possibility that TableB has multiple records for the
loan and Order number listed in TableA as shown below:

TableA:
Loan # Order# Amount(A) Fee type Invoice date
525263 N2500 $250.00 Escrow 1/15/06

TableB:
Loan # Order# Amount(B) Fee type Invoice date
525263 N2500 $250.00 Escrow 1/15/06
525263 N2500 $500.00 Title 2/15/05
525263 N2500 $50.00 Refinance 8/20/05

Result of join on Order # and Loan#:

Loan # Order# Amount(A) Amolunt(B) Fee type Invoice
date
525263 N2500 $250.00 $250.00 Escrow
1/15/06
525263 N2500 $250.00 $500.00 Title
2/15/06
525263 N2500 $250.00 $50.00 Refinance
8/20/05

My question is: I want amount(A) to be shown one time only, not three time
as it shows for matching records in tableB. It should show amount in the
first row only and blank in subsequent rows. One option would be to use Sum
on amount in tableB so that there can be only one matching row. I don't want
to use that option because I want to see fee type also. I have tried distinct
function, unique values but nothing gives the results I want. Can someone
help in this regard? thanks.
Purnima

 

[K Dales]

A question for you: do you want the amount from January (Amount A) to show on
a line of its own, or is it necessary that it be on the same line as Amount
B? If you just want to see the individual transactions listed line by line a
Union Query would make more sense; you need to go to the SQL Specific options
on the Query menu and choose Union, then the SQL would look something like
this:
SELECT TableA.[Loan#], TableA.[Order#], TableA.[Amount], TableA.[Fee Type],
TableA.[Invoice Date] FROM TableA UNION (SELECT TableB.[Loan#],
TableB.[Order#], TableB.[Amount], TableB.[Fee Type], TableB.[Invoice Date]
FROM TableB)
This combines the two tables as if they were one, if that is what you need.

If you truly need the amount to show with the first line with the
information from Table B: are you doing that because it is the same invoice
date and/or fee type? If so you could make a calculated field in the query
with an expression like this:
=Iif(([TableA].[Fee Type]=[TableB].Fee Type]) And ([TableA].[Invoice
Date]=[TableB].InvoiceDate]),[TableA].[Amount],Null)
This expression checks to see if the fee type and invoice dates are the same
and if so it will show Amount(A); otherwise it shows nothing.

Finally, if you need it on the first line with the B values regardless of
whether anything else matches or not, I can't think of a way to do it with
only a single query. You could build a more complex set of queries that
pieces this together. Or you could feed the query to a report and have the
report suppress printing AmountA unless it is on the first record.

 

[Purnima Sharma]

Hi, thank you very much for your help. I tried to use a union query but it
doesn't give the right result. It gives too many rows in the resulting query.
I think your second option of using a calculated field should work. I have
not tried it yet. Thank you very much.
Purnima Sharma

A question for you: do you want the amount from January (Amount A) to show on
a line of its own, or is it necessary that it be on the same line as Amount
B? If you just want to see the individual transactions listed line by line a
Union Query would make more sense; you need to go to the SQL Specific options
on the Query menu and choose Union, then the SQL would look something like
this:
SELECT TableA.[Loan#], TableA.[Order#], TableA.[Amount], TableA.[Fee Type],
TableA.[Invoice Date] FROM TableA UNION (SELECT TableB.[Loan#],
TableB.[Order#], TableB.[Amount], TableB.[Fee Type], TableB.[Invoice Date]
FROM TableB)
This combines the two tables as if they were one, if that is what you need.

If you truly need the amount to show with the first line with the
information from Table B: are you doing that because it is the same invoice
date and/or fee type? If so you could make a calculated field in the query
with an expression like this:
=Iif(([TableA].[Fee Type]=[TableB].Fee Type]) And ([TableA].[Invoice
Date]=[TableB].InvoiceDate]),[TableA].[Amount],Null)
This expression checks to see if the fee type and invoice dates are the same
and if so it will show Amount(A); otherwise it shows nothing.

Finally, if you need it on the first line with the B values regardless of
whether anything else matches or not, I can't think of a way to do it with
only a single query. You could build a more complex set of queries that
pieces this together. Or you could feed the query to a report and have the
report suppress printing AmountA unless it is on the first record.
--
- K Dales

I have created a join between two tables: tableA and tableB. Both the tables
are identical in structure. Table a lists the data for January and table B
lists the data for the whole year (previous). The tables are joined on loan#
and Order#. There is a possibility that TableB has multiple records for the
loan and Order number listed in TableA as shown below:

TableA:
Loan # Order# Amount(A) Fee type Invoice date
525263 N2500 $250.00 Escrow 1/15/06

TableB:
Loan # Order# Amount(B) Fee type Invoice date
525263 N2500 $250.00 Escrow 1/15/06
525263 N2500 $500.00 Title 2/15/05
525263 N2500 $50.00 Refinance 8/20/05

Result of join on Order # and Loan#:

Loan # Order# Amount(A) Amolunt(B) Fee type Invoice
date
525263 N2500 $250.00 $250.00 Escrow
1/15/06
525263 N2500 $250.00 $500.00 Title
2/15/06
525263 N2500 $250.00 $50.00 Refinance
8/20/05

My question is: I want amount(A) to be shown one time only, not three time
as it shows for matching records in tableB. It should show amount in the
first row only and blank in subsequent rows. One option would be to use Sum
on amount in tableB so that there can be only one matching row. I don't want
to use that option because I want to see fee type also. I have tried distinct
function, unique values but nothing gives the results I want. Can someone
help in this regard? thanks.
Purnima