Conditional Formatting for Unique Text Values in Mulitple Fields

[Dawnit]

Calling all experts! I need your help!

I need to create conditional formatting (like highlighting) for multiple field values that do not match in my report that is based on a Union All query.

First, you might need to know that my Union All query (detail below) returns all rows in tables A and B that do not completely match all columns in either, and all rows in either table that do not exist in the other table, including nulls. Rows that match completely in both tables, are not returned.

SELECT MIN(TableName) as TableName, ID, COL1, COL2, COL3
FROM
(
SELECT 'Table A' as TableName, A.ID, A.COL1, A.COL2, A.COL3
FROM A
UNION ALL
SELECT 'Table B' as TableName, B.ID, B.COL1, B.COl2, B.COL3
FROM B
) tmp
GROUP BY ID, COL1, COL2, COL3
HAVING COUNT(*) = 1
ORDER BY ID

In my report example below, the words “POLICY” and “ADMIN” would be highlighted in the first two rows. In rows 3 and 4, “CITY COUNCIL” and the null value would highlight, and in rows 5 and 6 “CITIZEN FEEDBACK” and “AGENDAS” would highlight.

Table \ ID \ COL1 \ COL2 \ COL3

A \ 1 \ POLICY \ CITY COUNCIL \ \
B \ 1 \ ADMIN \ CITY COUNCIL \ \
A \ 2 \ POLICY \ CITY COUNCIL \ \
B \ 2 \ POLICY \ \ \
A \ 3 \ POLICY \ CITY COUNCIL \ CITIZEN FEEDBACK
B \ 3 \ POLICY \ CITY COUNCIL \ AGENDAS


Since I have over 60,000 records to proof, I’m hoping someone will have a solution for me.

Thank you!

DC

 

[SkipVought]

How about a Union query like this...

SELECT A.ID, A.COL1, b.COl1, 'COl1'

FROM
  TableA
, TableB

where A.ID=B.ID
  and A.COL1<>B.COL1

union all

SELECT A.ID, A.COL2, b.COl2, 'COl2'

FROM
  TableA
, TableB

where A.ID=B.ID
  and A.COL2<>B.COL2

union all

SELECT A.ID, A.COL3, b.COl3, 'COl3'

FROM
  TableA
, TableB

where A.ID=B.ID
  and A.COL3<>B.COL3

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[MajP]

Skip's query would identify all values without a match by ID and column. If you still needed/wanted to do conditional formatting on your report to show all records and highlight you could use his query and a function.

I would however add an Alias in the union query for column Name. Something like

"SELECT A.ID, A.COL1, b.COl1, 'COl1' AS columnName
....

This is untested, but something like

public function isMatch(id as long, columnName as string) as boolean
  dim rs as dao.recordset
  dim strSql as string
  'pass in an ID and column name and see if a record exists in Skips query
  set rs = currentdb.openrecordset ("Select id from skipsQuery where ID = " & id & " ColumnName = '" & columnName & "'")
  if not (rs.eof and rs.bof) then isMatch = true  
end function

So to use this in conditional formatting from your report you would do something like this on the col1 text box.

expression is: isMatch([ID],"Col1") = true
on column 2
expression is: isMatch([ID],"Col2") = true