Flag Duplicate Value in Detail Section

[haydenal]

I have a report that includes last name and citation number in the detail section and sorts by last name. My problem is that there are occasionally duplicate citation numbers (data entry issue). I would like to flag the records on the report that have duplicate values (so if citation number 123 shows up twice on the report maybe the citation number text shows up in red or something). I'm not sure where to go with this.

I was playing around with "hide duplicates" (which isn't what I want to do) for the citation number text box, and none of the citation numbers that are duplicates are hidden. I don't understand why that isn't working or maybe I just don't understand how it's suppose to work. Any tips?

 

[Freshman]

Why don't you set Citation to not allow duplicates at table level.
In that way you handle the duplication before it gets to the report stage.
Or is this not what you aiming for?

 

[haydenal]

I like the idea from a QC perspective and it addresses the root of the problem. Trouble is, 1) this is happening across multiple databases that I'm running a query across 2) the db's are not mine 3) the db's already have dups (bad data) in them.

Could I do some sort of count or dcount on the citation textbox control?

 

[Freshman]

i'm browsing on my mobile phone so not close to my pc to test but try this for an idea:
1. Create a find-duplicates query
2. In the On Format event in the Details section of your report, use DLookup to check if the Citation value equals any of the duplicate results in the query and then set the weight of the field to bold and the colour to red.
Remember to set the weight to normal and the colour to black if it doesnt match.
Let me know if it works as i cannot test at the moment.

 

[haydenal]

I tried doing the find duplicates query and it tells me "Query is too complex."

Prior to that I had tried doing a count of the citation numbers thinking I would change the font size/color to any record where count > 1 but I get "Query is too complex" when I try to run the count.

Stepping back a little, the query I'm using is a union query. Basically, I have 3 queries each to a separate db. I then take each of these queries and do the union query. I thought I could do the count on each of the 3 queries, but there's potential for a citation number to show up in each db so I think I have to do the count on the citation #'s after I bring them all together.

I was wondering if there is anything I can do on the report? Is there a way to determine if a particular value is already sitting on the report?

 

[Freshman]

so I think I have to do the count on the citation #'s after I bring them all together.

Maybe the best way to do it if possible to get rid of that error as then you can count & compare with the real duplicates.
See attachment.

I was wondering if there is anything I can do on the report? Is there a way to determine if a particular value is already sitting on the report?

99% sure all you gonna be able to get is a count of the number of duplicates accross the entire report, which is kinda useless info.

I will give it some thought but hard to test cause I'm not sitting with your multi-db setup.

 

[Freshman]

OK, so maybe I'm just getting tired and not thinking straight but here might be a unconventional method. Someone shoot me or correct me please

Back to the On Format Event:
Use the Find command to check the recordset the Report is based on and do a count on the number of times the current me.Citation value appears.
If more than once, make it red.

Here's the downside - Depending on the size of the db, the report might take a million years to open

Off to bed...zzz

 

[haydenal]

I've tried to clear up the "Query is too complex" issue with no success. I was thinking I could just make a table from the query and do my count on the citation field in this new table. I think this might work but it seems like a hokey way of doing it and then I'm left with handling this table every time someone runs this report.

I was going to give the find command a shot, it seems like a reasonable solution. I've tried constructing it a couple different ways and each time I run it, access crashes. Any chance of getting a suggestion on how the find command might look?

 

[Freshman]

Funny thing - it's been bugging me so I've been sitting for the last hour trying to make it work.

This below does not work
Here's my logic. The FindFirst command is seaching for a value that WILL ALWAYS be there as you looking at the same table the report is based on.
So, I'm only doing a FindFirst, in order to do a FindNext
If the FindNext succeeds it means there are two similar values and I can flag it.
But it doesn't work.
I have now read that, when the Find command does find a match, it also makes that record, the current record.
So maybe you need to put a Move command in there somewhere to bring it back to the start.
I must say - I would SO much like to see this thing work.
Let me know what you think.

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Dim db As Database
Dim rec As Recordset
Set db = CurrentDb()
Set rec = db.OpenRecordset("MainTable", dbOpenDynaset)
With rec
.FindFirst (Me.Citation = !Citation)
.FindNext (Me.Citation = !Citation)
If .NoMatch = False Then Me.Citation.ForeColor = 255
End With
rec.Close
End Sub

 

[Freshman]

Mmm - here might be the problem:
The value "5" appears twice in my dummy data.
So I decided to test with it:

With rec
.FindFirst (Me.Citation = "5")
If .NoMatch = False Then
MsgBox "Found"
Exit Sub
End If
End With

The result is, it gives me a "Found" twice. The problem is, I cannot get it to stop between the 2 finds. So I cannot run code inbetween the two finds.

Where are the big guns on this topic??

 

[Freshman]

This is getting very close:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Dim db As Database
Dim rec As Recordset
Set db = CurrentDb()
Set rec = db.OpenRecordset("MainTable", dbOpenDynaset)
With rec
.FindFirst (Me.Citation = "5")
If .NoMatch = False Then
If Me.Citation.ForeColor = 0 Then Me.Citation.ForeColor = 255 Else Me.Citation.ForeColor = 0
End If
End With
rec.Close
End Sub

 

[haydenal]

For the life of me, I can't get that to run. Any chance you could attach the db your poking around with?

 

[haydenal]

Never mind. I didn't realize I had saved the db as v2007. I don't want to get on too much of a tangent, but any idea why Me.Citation would throw a "Method or data member not found" in 2007?

 

[Freshman]

Not sure. Have to go out now, but this line might be a winner!!:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If DCount("Citation", "MainTable", "[Citation] = " & Me.Citation) > 1 Then Me.Citation.ForeColor = 255 Else Me.Citation.ForeColor = 0
End Sub

Could not test fully as I need to run. Will be back later.

 

[haydenal]

Well the good news is that with a little tweak it works great.

DCount("Citation", "MainTable", "[Citation] = '" & Me.Citation & "'")

The bad news (for me) is that when I apply it to my query I once again get "Run-time error '3360'" "Query is too complex."

I may just resort to trying a make-table query and then doing the count on the table instead.

I appreciate you helping me work through this. If you have any other thoughts I'd love to hear them.

 

[Freshman]

Gosh what a pity. So both solutions that worked did not work in you spesific circumstances.
Go for the makeup table idea as you can always delete it when the report closes and re-create it each time.

Please keep me in the loop when you sort this out as i would like to learn from it.
Cheers

 

[haydenal]

Well, it wasn't as bad as I thought it was going to be.

Essentially, these queries are pulling data from SQL Server and DB2 databases mashing it all together and plopping it on a report. I have a calendar control where the user picks a date, the queries run, it's all spit out blah, blah blah.

Although I wasn't able to track down the issue with why I kept running in to the "Query is too complex" I'll only guess it has something to do with the nature of where all my data is coming from.

In the end, what I did was:

1) Convert my query to a make table query
2) Create a new query on this new table that does a count on the citation field
3) Create one more query to join the new table to the citation count query

Then when the user picks a date on the calendar control I blow away anything in the table from step 1, run the queries and plop the info from the newly joined table/citation count query and color code any record in the detail section that has a citation count > 1.

It seems a little slow but not bad. From the users perspective, it all runs and they get a nice report that shows them where they've got some data entry issues.

I'd be happy to try any other suggestions to try to resolve the "Query is too complex" but the work around seems ok.

 

[Freshman]

Great work!! Glad you got it to work (even if it was not in the way you wanted).

 

[gemma-the-husky]

just a thought -

if the citation number is so important to you, then how can you (I mean your company) permit a situation where citation numbers can be duplicated). At the very least, have a physical book to record the cases, and allocate a number, so there can be no possiblity of two users picking the same case number.

And change the existing duplicates by adding a A,B,C to distinguish them.

---------
Access can do a lot of things, but it cant do everything, and the importance of control of these numbers is so basic.

 

[haydenal]

I think that's initially what Freshman had suggested (not allow duplicates at table level).

I agree, it's a good suggestion to prevent the problem (which I will be passing along to the "powers that be").