Need some help regarding duplicate values....

Hi,

I am using access database and cystal reports XI. Since I am facing some problems.

The following are the details:

Table1 = Sales
Table2 = Collections

Sales (table fields):

1. CustomerName
2. InvoiceNumber
3. InvoiceDate
4. InvoiceAmount

Collections (table fields):

1. CustomerName
2. DepositDate
3. ReceivedAmount

I need these fields in a report. There is no relationship in the database for the CustomerName field.

I tried my best to get the report. But there are repeated values (Duplicate values which are not existing in the table/database) present on the report. Beside to the date values there shows 00:00:00 (time) as well which I dont need.

How to solve this problem. Please help.

Sweetie.The following jpg file shows the duplicate/repeated data.|||Make sure "select Distinct Records" is turned on under the Database menu.|||Make sure "select Distinct Records" is turned on under the Database menu.

Hi, thought I have selected the option "select Distinct Records" the same thing happens that the data in collections.AmountReceived repeats.

Any further help please. (Using Crystal Reports XI).

Sweetei.|||click on database menu, select database expert and go to link tab.
drag customername from one table to other to make a link.

then u will have a join which ensures data is not repeated.|||click on database menu, select database expert and go to link tab.
drag customername from one table to other to make a link.

then u will have a join which ensures data is not repeated.

Hi, Raheem! I did the same as you have advised but it didn't work. Actually though the both Sales & Collections have CustomerName field but there is no relationship between them in the database.

Could you please help me out of this problem?

Sweetie.|||Hello Sweetie,
It was not the CR code problem, it's your database structure.

While you try to connect the Sales table to the Collection table using CustomerName (seems like the only relationship you can make between the 2), every distinct record in the Sales table will connect to all records in the Collection table which has the same CustomerName. If you look at the DepositDate on your report, you'll see the pattern. It looks like a duplicate problem. In deed, it just a coincident that for each of the 3 customers, you have 2 records in the Collection table.

You can check this out by adding another record for one of the 3 customers in your Collection table. You'll see your report showing triplicate for this customer.

Now before I can help you to solve the problem, you'll need to be more specific about what you're looking for:

1. For each of the customers, you need a balance between Invoice Amount & AmountReceived? If this is the case, then you'll have to make either one a subreport to the other, using CustomerName as a link.

2. If a collection has to refer to an invoice, you must have an InvoiceNumber field in your Collection table. Use both CustomerName & InvoiceNumber to link the 2 tables.|||Hello Sweetie,
It was not the CR code problem, it's your database structure.

Now before I can help you to solve the problem, you'll need to be more specific about what you're looking for:

1. For each of the customers, you need a balance between Invoice Amount & AmountReceived? If this is the case, then you'll have to make either one a subreport to the other, using CustomerName as a link.

2. If a collection has to refer to an invoice, you must have an InvoiceNumber field in your Collection table. Use both CustomerName & InvoiceNumber to link the 2 tables.

Hi, thanks for your kind help. Actually I need the both ways you have mentioned above. But I need to know it step by step to understand easily.
So, Could you please let me know the first way .... ?

"1. For each of the customers, you need a balance between Invoice Amount & AmountReceived? If this is the case, then you'll have to make either one a subreport to the other, using CustomerName as a link."

Sweetie.|||Sweetie, problem is with your database.

even if u join customername and salesperson u will get duplicate, u need to
include some distinguishing column in collection table, maybe invoice no. on which amount is collected.|||Sweetie, problem is with your database.

even if u join customername and salesperson u will get duplicate, u need to
include some distinguishing column in collection table, maybe invoice no. on which amount is collected.

Hi, Raheem. Thanks for the advise. But the problem is, assume that if the customer doesn't pay the full payment for each invoice and pay it in 3 installments and by the time he contines purchasing some items (means he will have another 1 - 2 invoices before he pay the first invoice amount). Then it will be messed.

The example is attached.

Regards,

Seema.|||Hi Seema,
Use FIFO method of adjustment i.e, payments shd be updated in same invoice until balance is 0 then adjust remaining amount with next invoice.
Raheem|||Hi Seema,
Use FIFO method of adjustment i.e, payments shd be updated in same invoice until balance is 0 then adjust remaining amount with next invoice.
Raheem

Wow! is it ? Actually what I needed for my program is the same. This is the first time that I heard about that method. Could you please explain me in detail, only if you dont mind?

Seema_S|||when i get free i will reply you.|||when i get free i will reply you.

Hi, Raheem!

I feel happy to get your reply which can solve my problem.

Thanks in advance.

Sweetie.|||Hi, guys!

I am waiting for the reply to my querry. Could somebody help me in this regard?

Sweetie.|||Sweetie,

I see you've been patiently waiting for over two weeks now!

It has to be said that the 'database' structure is a problem and very prone to errors.
One such error is the city 'JAYPUR' in Collections when it's 'JAIPUR' in Customers and Sales - explains the lack of duplication for that customer!
And what's the FileNumber column in Sales and Collections about? It seems to uniquely identify a customer but this isn't on the customer table. Messy.
You might want to consider ditching all the duplicated rubbish and putting the CustomerID into the Sales and Collections tables at least.

I've knocked together something I think is more like what you're after.See the attached report.
It assumes that the customer name is unique. If you put the CustomerID into the tables then you should use that instead as it really is (should be) unique.

Anyway, have a play with it to see what it does and how. Hope it's of some use.|||Sweetie,

I see you've been patiently waiting for over two weeks now!

It has to be said that the 'database' structure is a problem and very prone to errors.

Anyway, have a play with it to see what it does and how. Hope it's of some use.

Hi,

Thanks for your kind help. I am happy that at last I got my querry answered. There is one problem which is attached as jpg file.

Thanks again.

Sweetie.|||But that's the point - as you have stated before, there is no correlation between sales and collections.

The collections of 17,000 and 4,000 are applied in a First In First Out (FIFO) process to the sales (invoices).

The first invoice (103) was for 8,000 and the second invoice (109) was for 17,000.
The first collection was 17,000 so 8,000 of this is applied to invoice 103.
The remaining 9,000 is left over and applied to the next invoice, 109.
The next collection was for 4,000 and is also applied to invoice 109.

If you want to make it clear that a single collection has been split over multiple invoices then you might want to add the original collection amount / collection id to the report.|||But that's the point - as you have stated before, there is no correlation between sales and collections.

Thanks. It is better that it should show the full amount received.

Regards.

Sweetie.|||Hi sweetie,
i was busy all these days,i am attaching ur database zip file check it.

In Collections table, I added invoice number in it and check two

queries. JaganEllis did the same thing. if u remove invoice date then u will get correct collections amounts displayed.

collecionn 1 for 4000 adjusted in 105
collection 2 for 2000 adjusted in 105
collection 3 for 5200 adjustted in 105 and 111

so if u want to omit invoice date, then u can use query2 to make ur report ..else u u can use query1..

Atlast Hope I helped you.

Raheem|||Hi sweetie,
i was busy all these days,i am attaching ur database zip file check it.
Raheem

Thanks for your kind help. I need to learn more about Normalizing database and Crystal reports. If you know any good tutorials site for the above, plz let me know.

Regards.

Sweetie.sql