Using conditional formatting to find duplicate values in two tables.

[jbake248]

Hello, I search the site and could not find this answer. I'm working with Access and I have two tables each having a column with the same information, I want to use conditional formatting to find the duplicates.
This is what I'm using in the expression part of a new rule. =countif( [Review]![Table1]= [Table2]![Case Number],0).

What I get is you may have entered a comma without a preceding value or identifier. I have no commas in the expression.
Any help would surely be appreciated.

 

[Joe4]

Welcome to the Board!

COUNTIF is not a valid Access function.

Why not just do a query on the two tables in Access to find your matches?

 

[jbake248]

I've tried that, but one table is linked one is not and I've not been able to make it work. Table1 would be the linked table in my case. I would like to change the color of the Case Number to red if at all possible. Can that be done with a query and how would I implement that?

Thanks for the fast response. I used Access a lot 10 years ago and I'm just getting back into it again.

 

[Joe4]

I've tried that, but one table is linked one is not and I've not been able to make it work.

What exactly do you mean by that? Are you talking about the link (really Join) between the two tables, or the source of the data (i.e. data resides outside of Access and is linked in). Access does not care where the data resides and whether or not it is a linked table. You can do queries on them just the same.

I would like to change the color of the Case Number to red if at all possible.

You cannot perform Conditional Formatting in Access directly in a query, but you can in a Form or Report. So if you create the Query, and then use that query as the data source for a Form or Report, you can use Conditional Formatting. See: Access database - conditional formatting on a datasheet

If you are having trouble setting up your query, please provide some data samples from each query, along with your expected results, and we can probably help you set it up.

 

[jbake248]

Here is what I'm using for the query, but it comes up blank. I cannot find the correct criteria for it to give a correct response.

 

[Joe4]

OK, it looks like you currently have a Left Join going from the "VOR Cases" table to the "Review" table, meaning that it would typically return ALL the records from the "VOR Cases" table, andjust those records from the "Review" table that match. But then you have also placed query on the "Case Number" field of the "Review" table to just return those values exactly equal to "eqv".

I am thinking that maybe you should put this criteria on the "Case Number" field fron the "VOR Cases" table, and not the "Reivew" table.
Also, what do the values in the "Case Number" field look like? Are they EXACTLY "eqv"?

 

[jbake248]

Okay strangely enough I checked all 4 boxes and removed the criteria and the query gave me the results I needed. So now is it possible to show those case numbers in the VOR Cases table to a red format to stick out?

 

[Joe4]

Okay strangely enough I checked all 4 boxes and removed the criteria and the query gave me the results I needed. So now is it possible to show those case numbers in the VOR Cases table to a red format to stick out?

Look back at the comments and link my second reply (post 4).

 

[jbake248]

One last question. My query came up with the matching cases and all the cases from the VOR case table which align with the matching cases from Reviews table. What could I put below in the query to only show the matching cases. I hope that makes sense.

 

[Joe4]

One last question. My query came up with the matching cases and all the cases from the VOR case table which align with the matching cases from Reviews table. What could I put below in the query to only show the matching cases. I hope that makes sense.

Change your join type of your query from a Left Join to an Inner Join.

See here: Join tables and queries
You want to select the first option when you open the Join Properties window.