How to make duplicate records bold?

Hi experts,
We have a table with many columns, including the following
ID (unique)
Client_Last_Name
Client_First_Name
visit_date
shift_from_hour
Nurse_Signature_Last_Name
Nurse_Signature_First_Name

Open in new window

And we have a continuous form bound to that table displaying all the records.
Now we would like to add a feature that makes the duplicate records appear in BOLD.
Duplicate records are considered those that the 6 fields listed above (excluding ID) are the same.
How can I accomplish that in the most efficient manner?
Thanks in advance
LVL 5
bfuchsAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Gustav BrockCIOCommented:
You might be able to use Conditional Formatting.

The condition could be:

ID <> DMin("ID", "YourTable", <long list of fields and controls to match>)

When True, set Bold = True
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
bfuchsAuthor Commented:
Hi,
ID <> DMin("ID", "YourTable", <long list of fields and controls to match>)
Not sure I get the filter logic?
However would like to ensure
A- All duplicate records will be bold.
B- Would not be a heavy cost on the form while scroolong up/down the records.
Thanks,
Ben
0
Gustav BrockCIOCommented:
A: True
B: True

Or you could create a function that returns True for a duplicate record and include that in the query you use as source for the form. That will slow down loading, but browsing should be a lot faster.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

PatHartmanCommented:
The best solution would be to not allow duplicates to begin with.  To prevent duplicates, you can add a unique index to the table.  Access supports indexes with up to 10 columns.  You need six so you're fine.
A.  No problem
B.  Problem - if you are happy with bolding only a single control, fine.  Otherwise, you will need to run the domain function for each control you want to bold.

Another better solution is to use a Find Duplicates function.   That way, EVERYTHING shown is a duplicate.

Reports are always more efficient for doing linear stuff like this where you want to look at the previous record.  However, with a report, I would just hide the duplicate fields..

The real answer depends on whether you need to update the data in the form or if you can get by with a report..
0
bfuchsAuthor Commented:
@Gustav,
Can you post what code I should use, enough to list two fields as example.

@Pat,
This is needed for a form to be updated.
I dont have full control of the data as its coming from Caspio.
However I need to make sure no duplicate records gets printed in report.
And users should select which one to be printed.

Re function, what would be the logic of best performance?

Thanks,
Ben
0
bfuchsAuthor Commented:
As a start I wanted to use the following SQL to get all duplicate records.
SELECT Min(Skilled_Nursing_Visit_Note.SNV_ID) AS MinOfSNV_ID, Max(Skilled_Nursing_Visit_Note.SNV_ID) AS MaxOfSNV_ID, Skilled_Nursing_Visit_Note.Client_Last_Name, Skilled_Nursing_Visit_Note.Client_First_Name, Skilled_Nursing_Visit_Note.Visit_Date, Skilled_Nursing_Visit_Note.Shift_From_Hour, Skilled_Nursing_Visit_Note.Nurse_Signature_Last_Name, Skilled_Nursing_Visit_Note.Nurse_Signature_First_Name, Count(Skilled_Nursing_Visit_Note.ID) AS CountOfID
FROM Skilled_Nursing_Visit_Note
GROUP BY Skilled_Nursing_Visit_Note.Client_Last_Name, Skilled_Nursing_Visit_Note.Client_First_Name, Skilled_Nursing_Visit_Note.Visit_Date, Skilled_Nursing_Visit_Note.Shift_From_Hour, Skilled_Nursing_Visit_Note.Nurse_Signature_Last_Name, Skilled_Nursing_Visit_Note.Nurse_Signature_First_Name
HAVING (((Count(Skilled_Nursing_Visit_Note.ID))>1));

Open in new window

However not sure exactly how to proceed..
Thanks,
Ben
0
bfuchsAuthor Commented:
What do you say for this function below, (on top of the above query)?
Public Function IsSchedDup(pFirst As String, pLast As String, vDate As Date, sFrom As Integer, nFirst As String, nLast As String) As Boolean
    Dim db As Database, rs As Recordset, sSql As String
    Set db = CurrentDb
    sSql = "Select 0 from Skilled_Nursing_Visit_Note_Dup_Qry where Client_First_Name = '" & pFirst & "' and Client_Last_Name = '" & pLast & "'"
    sSql = sSql & " And Visit_Date = #" & vDate & "# and Shift_From_Hour = " & sFrom
    sSql = sSql & " And Nurse_Signature_First_Name = '" & nFirst & "' and Nurse_Signature_Last_Name = '" & nLast & "'"
    Set rs = db.OpenRecordset(sSql)
    If rs.EOF Then
        IsSchedDup = False
    Else
        IsSchedDup = True
    End If
    Set rs = Nothing
    Set db = Nothing
    
End Function

Open in new window


Thanks,
Ben
0
bfuchsAuthor Commented:
Thank you!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.