Duplicate records

[Coatezy]

I am creating a database for refund requests.. Sometimes the refund request can be duplicated so I have a query using

" In (SELECT [OrderNumber] FROM [tblRefunds] As Tmp GROUP BY [OrderNumber] HAVING Count(*)>1 ) "

to identify these duplicates..

I also then have another column called Allowed? Which is a Yes/No tick box.. And Then another Column Called Checked which is a again a Yes/No. If the duplicate entry is allowed and we want the customer to be refunded twice the user selects Yes in the allowed column. I then have an update query that then checks all orders and if it see's allowed it marks it as checked in the checked column.. Both can then be refunded. The problem I have is I then dont want these duplicate records to appear again unless a new record is entered which causes a third duplicate.. I then want all 3 to be displayed until I either mark the 3rd duplicate as allowed or I delete it. I know this probably isnt something that is correct in the world of access but unfortunatly we sometimes require 2 refunds to be raised under the same order details.

Hope all of the above makes sense....

Thanks guys!

 

[namliam]

Simply insert a "WHERE" into your sub-select to handle this...

SELECT [OrderNumber] FROM [tblRefunds] As Tmp WHERE [YOURCOLUMN] = TRUE GROUP BY [OrderNumber] HAVING Count(*)>1

This will only count the rows where yourcolumn is true hence skip those as beeing duplicate. This is perfectly good with access as long as these are your business rules, this is what the database must do !

 

[Coatezy]

Hi namliam thanks for you reply!

I don't think I explained what I need terribly well.. I will have another go at it. If I have 3 duplicates and of these 3 duplicates, 2 of them have "TRUE" in the "AllowRefund" Column but 1 has "FALSE" I would like all 3 to show.. If I then put a "TRUE" In the 3rd duplicate all 3 need to disappear.. If then a 4th duplicate is added and "AllowRefund" does not equal "TRUE" I would like all 4 to appear again until I mark the most recent 4th duplicate as "TRUE" to make them all dissapear again.

Hopefully I explained myself a little better this time!?

Thanks again!
Tom

 

[Coatezy]

Anybody else have any idea's on this one?

 

[neileg]

Create a select query that selects the records where AllowRefund is false. Join this query back to your original table in another query with an inner join. This will only return records where one of the AllowRefund entries is false.

 

[namliam]

something like so...

SELECT [OrderNumber] FROM [tblRefunds] As Tmp 
WHERE [OrderNumber] in (select [ordernumber] FROM [tblRefunds] where [YOURCOLUMN] = FALSE)
GROUP BY [OrderNumber] HAVING Count(*)>1

Or do a proper subselect with a nice innerjoin as Neil suggested.