duplicate problem

[hokie]

I have a database with duplicate invoice numbers and that is not a problem. The problem is the shipping charge for each of the duplicates has the total shipping charge for each one so if I have 5 duplicate invoice numbers the shipping rate is 4 times too much. Is there any way I can have a query or macro find these duplicates and delete all the shipping charges except for the first one.


Edit: Also not every record has a shipping charge so I assume I need an iif statement that says if there is a shipping charge then do all of the above otherwise do nothing. I want the field to stay just change the 4 of the 5 duplicates to zero. Any help would be appreciated.

[RuralGuy]

You may wish to review the structure of your tables as this indicates a fundamental problem but the Query Wizard can create a "Find Duplicate Record" query.

[hokie]

I can find the duplicate entries but I wanted to automate a way to only have the 1st instance shown for the field [shipping charge] and the other duplicate fields to remain but the [shipping charge] to be set to zero instead of $50.
ie. I have 5 lines with the same invoice # and the shipping charge should be $50 for the entire invoice but because of the way the system I get my information from(small business threw this database together) it comes up with $50.00 shipping for each of the 5 lines overstating shipping charge by $200.00.

The reason this has to be done is because there are 2 seperate databases the information is coming from and matching up based on an order number. One is internal the other is external and the problem lies in the external reporting. Hope this cleared it up.

Thanks for your reply.

[RuralGuy]

Is this for a report? What does the RecordSource SQL look like?

[hokie]

The end results will go into a report. I did everything from the GUI side but here is the SQL code from the query.

SELECT Dropship.*, SupplierZone.[Invoice Date], SupplierZone.[Vendor #], SupplierZone.Total, SupplierZone.Difference, SupplierZone.Name, SupplierZone.CodeDate, SupplierZone.InvoiceNumber, SupplierZone.Freight, SupplierZone.Handling
FROM Dropship INNER JOIN SupplierZone ON Dropship.[Order#] = SupplierZone.[Order#];


The SupplierZone.Freight and SupplierZone.Handling are the fields I need to have only 1 charge when there is a duplicate invoice number. I hope this helps.

Thanks again

[RuralGuy]

So you are totaling these values on a report?

[hokie]

Yes I am using this report to total these 2 fields mentioned in the post above.

[RuralGuy]

Will setting the Hide Duplicates property of the control to YES work for you?

[hokie]

That will not work for my situation because there are situations where the shipping or handling will be the same as a different invoice that should be calculated in the total.

[RuralGuy]

It sounds like you will need code in the Detail_Print event. If there are more than one detail line with the same invoice number, does the first record *always* have the values you need? In other words, can we jjst hide all of the subsequent charges for the same invoice?

[hokie]

Yes that would work perfect. The 1st line of the duplicate invoices always has the correct total amount for that invoice. Should I just look up "Detail_Print event" in a google search because I have not used this before. I have an access for dummies book i'll look it up in there in the meantime.

Thanks for all your help.

[RuralGuy]

We need the actoal control names for these three fields: InvoiceNumber, Freight, Handling. It would be nice if thenames were txtInvoiceNumber, txtFreight, txtHandling.

[hokie]

The names are InvoiceNumber, Freight, Handling. InvoiceNumber is a text field the others are numbers.

[RuralGuy]

Please change the names of the controls to what I suggested so there is no confusion referencing them. I'll create the code for you.

[RuralGuy]

Then you need to get to the event tab of the properties sheet for the Detail Section and press the "..." button and select code. Then *replace* the stub of code provided with the following:

Option Compare Database
Option Explicit
Dim LastInvoiceNumber As String
Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
   If Me.txtInvoiceNumber = LastInvoiceNumber Then
      Me.txtFreight.Visible = False
      Me.txtHandling.Visible = False
   Else
      LastInvoiceNumber = Me.txtInvoiceNumber
      Me.txtFreight.Visible = True
      Me.txtHandling.Visible = True
   End If
End Sub