How not to have Duplicates in Rows

[AndrewCorkery]

Hi,

I have the following data and need to return just the one record for 2 fields and if it does have a duplicate still return the record but have a zero value in the numeric field.

Company Field1 Field2 Field3 Field4
A 12 121 122 232
B 11 144 431 332
C 10 101 182 543
A 12 132 552 987
B 14 119 212 432

As you can see there is a duplicate for Company A in Field1. How can I return a 0 in Field1 if a company already has that value? This needs to be done in a query.

Thanks

Andrew

 

[Darrylles]

Hi,

You can do this, but why not solve the cause of the problem rather than create a workaround?

Obvious question is why are there duplicates? Do you want duplicates elsewhere in the app.?

If not, then:

Write a query to strip them out.
Add a pk value to the table or make the Company field an index and define as 'no duplicates'.

Regards,

Darrylle


"Never argue with an idiot, he'll bring you down to his level - then beat you with experience." darrylles@totalise.co.uk

 

[AndrewCorkery]

I am getting this information from two tables joined by unique fields using an ODBC connection to a professional accounting package.

I wish I could fix it from the source but it is more of a running total figure and therefore all I need to get is the one figure and make the rest of the dups zero as I then bring the info into Excel as a Pivot table.

Any Ideas?

 

[Darrylles]

Hi again,

OK Andrew, I now understand - it's late here in the UK and I don't have time to investigate. (I have no idea off the top of my head).

Your best bet for a quicker response is to re-post this with your extra info.

Change the post title to 'Indicate duplicate row in returned field' or something similar.

If you don't re-post, then my responses (on the main form) tend to indicate that you've been answered.

Sorry, but more detail is there for your new post.

Regards,

Darrylle

"Never argue with an idiot, he'll bring you down to his level - then beat you with experience." darrylles@totalise.co.uk