Duplicate detail records 1

[harding1031]

CR 9.0
I am printing duplicate records in my detail, but there are no duplicate records on the database. Any ideas that might cause this?

 

[synapsevampire]

There are a myraid of reasons, however it's more likely that your joins have caused what's termed "row inflation"

Are they truly duplicates, meaning that all columns are identical?

If so, use Database->Select Distinct Records

I'd guess that is not the problem and what you need to do is learn more about how the data should be joined and filtered, try contacting your dba if you have one.

Otherwise you'll need to post technical information, your question is the equivalent of saying I have data but it's not right, please fix it.

Please post:

Database/connectivity used
Example data and joins used
Expected output

-k

 

[harding1031]

CR 9.0
ODBC
You are correct!!!!
Question, my main table needs some data from another table. I use a shipping number to link the tables(left outer join), the problem is the shipping number occurs on the secondary table twice--I only need it once. What do I do?

 

[synapsevampire]

How are we to know which data it is that you need from another table?

You need to understand what you need and convey it in the form of the information I requested.

If you only need the maximum of them, then create a group on the shipping number, and then use some date or some such to limit only the maximum of that field by using the Report->Selection Formulas->Group and entering something like:

{table.date=Maximum({table.date},{table.shippingnumber})

Now you'll only get the maximum back.

You can also just suppress the data by right clicking any field and selecting Format Field->Common->Suppress if duplicated. Then you can right click the section the fields are in and select format expert->suppress if duplicated and it will give the same illusion.

-k

 

[harding1031]

Thanks, but I solve the problem by adding another link to another field that will force only one record to be retrieved from the second table.

I guess my question was a generic question for future use. What I wanted to know was if I have two tables linked by a field(say order number). Both tables have the order number, but I only want to display one detail record that contains data from primary table and data from secondary table. Could this be done without having to link more than one field?

Thanks, synaps...you have been a big help.

 

[synapsevampire]

It's dependent enitirely on the database.

I assure you, you do not have duplicate rows, you have row inflation. Otherewise an additional link wouldn't have resolved it because everything would still be identical.

To eliminate row inflation, you either identify it's cause (such as an additional link or some where filtering, which is the equivalent of the record selection in Crystal) and eliminate it using the record selection, or within Crystal you can suppress the data.

-k

 

[Buenacolt11]

So I was reading this thread and thought since I have a similar problem I would try to just piggyback here instead of starting a whole new thread.
First, I am not a programmer or even an IT person for that matter. I have been tasked with creating an Access databse that will be used to report on data from two other databases.
In doing this my process was to write "reports" in the two other systems using the reporting tools associated one of which is Crystal, export them and then import them into my Access database. One of the reports, the one not using Crystal worked fine and I have my data in Access. The other, the one that does use Crystal, is, I guess inflating rows. Now as I understand this, row inflation involves records that while having a common key do not contain the exact same data which is why they are not duplicates. And that is exactly what I am seeing. I have multiple rows for the same record but with some minor variances in data. I need an easy way to get only one row per record. I dont have any totals or anything like that. Just straight data, and lots of it; name, address, SSN, phone number, principal balance... blah blah blah. Particulars: I am using CR 10, the data that I am collecting is coming from an OLE DB... what else?

 

[synapsevampire]

What fields are not indentical?

The whole project sounds bizarre, why have Crystal report off of Access only to export back to Access, jsut use an Access query to handl;e it all.

I would also suggest starting a new thread.

But to address your immediate concern, you should build data which is NOT duplicated. How you do so is dependent upon what I have already posted, but more importantly, you can probably create the Access Query as the report datasource which has the dupes removed.

If you're unfamiliar with databases, reporting and IT, this is no small task.

As a quick fix, I would group on whatever fields you need, place the fields in the group footer, suppress the group header and details, and then export the data.

You should be able to eliminate the pseudo-dupes this way.

-k