Conditional Formatting for Duplicates

[Ben]

OK, here's my dilemma:

I'm using Access 2003 to design a holiday order sheet. It's a pretty
standard setup: customers may have more than one order present in the
database with pickup dates on different days. What I would like to do is
have the customers with multiple orders flagged. For example, if Joe Smith
is picking up an order 12/23, 12/24, and 12/30 I would like all his orders to
be yellow, for example. If possible, to make the reports easier to read, I'd
like the colors to be different...either randomly generated or selected by
me. For example, Joe Smith's order might be yellow but Jane White's order
should be red, for example.

Here are the fields on my order sheet:
Last name, First name, Phone Number, Pickup Date, Type of Order, Amount

What I was thinking would be to use conditional formatting on all fields and
have Access search for matching entries in the "Phone" field, turning the
fields a certain color if there are multiple records containing the same
phone number. I did something similar to this last year with pretty good
results. However, how would I make different customers' orders flag with
different colors?

Confused...

 

[roger]

ok Ben,
1st I would suggest adding a CustomerID number, but I guess Phone numbers
will do.

To do what you want you need to string together two queries. One query to
count how many orders each cust has, and another to add that data to whatever
the datasource of your form is.

To count orders make simple two-column query: GROUP by CustID (ok phone) and
COUNT your order IDs (transaction numbers, whatever)

Now make a second query containing your new OrderCountQuery and your form's
table (if your form is already based on a query you can just add it to THAT
query) Make sure you link the queries by CustID (or phone) and drag your
CountOfOrderID Field into the output.

So, your form is now based on a query that contains a column called
CountOfOrderID (which is usually "1" but somtimes "2" or "3") you don't even
need to have this field ON the the form, just as long as its in the query
that the form is based on

Finally in Form design, in conditional formatting: your "condition" is:
me.CountOfOrderID >1
and set the formatting to whatever you want it to be,

get it?

hth roger

 

[Ben]

Thanks. I'll try your method. I was actually able to accomplish the desired
effect by setting the conditional formatting of each field to:

DCount("*","fOrders","[Phone] = " & Chr(34) & [Phone] & Chr(34)) > 1

and have it flag Yellow. I still don't know how to have Access generate
random colors for different duplicates, though. I know this is a pain but I
really need customers with multiple orders to be color-coded. Any
suggestions?