Same data being displayed multiple times

[Former Member]

Post Author: C Hall

CA Forum: Data Connectivity and SQL

Not sure if this is the correct forum for this question, so please redirect me as appropriate. I'm working with a mortgage program and the report I'm trying to create is displaying the same data, multiple times. Example: where it should show loan id 12345, the next record 12346, 12347, etc along with the buyer name and property, what's actually being displayed is loan id 12345, joe buyer, 123 main st about 20 times, before it shows the next record. All of the data that I'm trying to retrieve is essentially in one table. So, TableA lists the loan id, buyer name, property, closing date, and every other piece of loan information in it except for a couple of fields. There are two other tables that I'm using for my report: TableB and TableC. TableB has just one field that I'm using and I can probably drop that. TableC I had to create myself and has several fields that I do require. TableA and TableC both use Loan_ID as their primary key. Looking at Database Expert > Links, these tables are joined as follows: Inner Join, Not Enforced, equals. I think that this is where my problem lays, but not sure how to fix. I have tried all the other options and they just return an extreme amount of data--all duplicates. Any help would be appreciated.Chris

[Former Member]

Post Author: C Hall

CA Forum: Data Connectivity and SQL

Foghat, Thanks for the reply....I had looked at the tables, removed B and found that to be the problem. Added table B back in, putzed with the join and everything is working fine. Thanks,Chris

[Former Member]

Post Author: foghat

CA Forum: Data Connectivity and SQL

If all the records you are displaying in your report

truly are duplicated, you could try check off 'select distinct records'

from the File --> Report Options menu. While this may solve the problem for you, it would be worthwhile to determine if you are actually joining your tables correctly.

likely the records aren't an exact duplicate and the problem is with your join criteria. To verify this you can: start by removing table b from the database expert altogether. does

that solve your problem of multiple rows? If it does, you are not joining to table b correctlyIf you still have

multiple rows, loan_id on its own must not make a record unique. Is

loan_id duplicated in either of your tables? Just because loan_id is a

primary key does not necessarily mean it is unique - often a record

will have 2 or more primary keys and only when all primary keys are

used is the record unique. If you display all of the columns

from both tables, you will hopefully see some (maybe just one) columns

where the value is different between your seemingly duplicate data.

You may need to join on this value as well.as for the type of join you are using (inner, not enforced) you should be fine. Good luck