Skip to Content
author's profile photo Former Member
Former Member

Same data being displayed multiple times

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

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

2 Answers

  • author's profile photo Former Member
    Former Member
    Posted on Sep 19, 2007 at 09:26 PM

    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

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Sep 26, 2007 at 03:10 PM

    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

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.