Conditional formatting based on Column header

[Guest]

I have a datasheet that shows sold quantities for each month. I want to
highlight column that shows quantities of current month. So for this month,
that will be June 2007. Next month, that will be July 2007. As far as I know,
conditional formatting changes cells format based on the value of the cell.
Is there a way to format the column based on the column header?
Thanks in advance.

 

[missinglinq via AccessMonster.com]

You've got a basic mistake here; in Access records are set up as Rows, not
Columns! A given month's record will be a Row going across. Your various
Fields will be in the Columns. Then Conditional Format the Quantities field
for the current month using the Expression Is and check the Month/Year of the
record to see if it's equal to the current Month/Year.

--
There's ALWAYS more than one way to skin a cat!

Answers/posts based on Access 2000

Message posted via AccessMonster.com

 

[Guest]

Thanks for your reply, but this is how i setup my data (simplified):
Cust Item ActualFeb07 ... ActulJun07 ForecastJun 07 ForecastJul07
ABC Item1 0 15 20 17 10
ABC Item2 10 5 24 0 0
BDC Item3 20 15 120 17 10
BCE Item1 0 15 20 17 10
BCE Item3 0 15 20 17 10

Thanks.

 

[John W. Vinson]

Thanks for your reply, but this is how i setup my data (simplified):
Cust Item ActualFeb07 ... ActulJun07 ForecastJun 07 ForecastJul07
ABC Item1 0 15 20 17 10
ABC Item2 10 5 24 0 0
BDC Item3 20 15 120 17 10
BCE Item1 0 15 20 17 10
BCE Item3 0 15 20 17 10

Then you have your data set up INCORRECTLY.

You're "committing spreadsheet upon a database". A database is NOT a
spreadsheet and doesn't work like one. "Fields are expensive, records are
cheap" - a much better design would be

Cust Item SaleDate Actual Forecast
ABC Item1 #2/1/07# 0 0
ABC Item1 #3/1/07# 10 25
ABC Item1 #4/1/07# 13 25
....
BCE Item3 #6/1/07# 20 17

You can create a Crosstab Query to spread the records out into columns *for
display purposes only*.

John W. Vinson [MVP]

 

[Guest]

OK, here's the thing:
the data is originally set up like this:
Cust Item SaleDate Qty
ABC Item1 #2/1/07# 0
ABC Item1 #2/17/07# 10
ABC Item1 #4/1/07# 13
....
BCE Item3 #6/1/07# 20

Now the users want to see the data in this format:
Cust Item ActualFeb07 ... ActulJun07 ForecastJun 07 ForecastJul07

SaleDate and Qty are actually the ActualDate and ActualQty, so I need to add
the Forecast columns. Users want to be able to enter forecasting data in
those Forecast columns. That's why I create a table based on the crosstab
query of the original table and add the Forecast columns.

If I did this all wrong, is there another way to do it?
Thanks in advance.

Lisa

 

[missinglinq via AccessMonster.com]

You're "committing spreadsheet upon a database"

I love that, John! You know I'll use it! Of course I'll cite you! It's
amazing to me how many times a day you see this committed here and on other
forums!

--
There's ALWAYS more than one way to skin a cat!

Answers/posts based on Access 2000

Message posted via AccessMonster.com

 

[John W. Vinson]

OK, here's the thing:
the data is originally set up like this:
Cust Item SaleDate Qty
ABC Item1 #2/1/07# 0
ABC Item1 #2/17/07# 10
ABC Item1 #4/1/07# 13
....
BCE Item3 #6/1/07# 20

That's the correct setup. You may need additional fields, or perhaps an
additional table, for forecast quantities.

Now the users want to see the data in this format:
Cust Item ActualFeb07 ... ActulJun07 ForecastJun 07 ForecastJul07

Don't confuse data STORAGE with data PRESENTATION.

SaleDate and Qty are actually the ActualDate and ActualQty, so I need to add
the Forecast columns. Users want to be able to enter forecasting data in
those Forecast columns. That's why I create a table based on the crosstab
query of the original table and add the Forecast columns.

A Crosstab query can never be updateable (because the fields are summaries of
multiple records, in a general sense). You'll need to either create a
denormalized "scratchpad" table or use an unbound Form with the 24 textboxes
for the actual and forecast values, and provide code to move the data back
into the normalized table; or... gasp... train your users to think vertically
instead of horizontally.

John W. Vinson [MVP]

 

[Guest]

Hi guys, thanks for all your inputs.

That's the correct setup. You may need additional fields, or perhaps an
additional table, for forecast quantities.


Don't confuse data STORAGE with data PRESENTATION.


A Crosstab query can never be updateable (because the fields are summaries of
multiple records, in a general sense). You'll need to either create a
denormalized "scratchpad" table or use an unbound Form with the 24 textboxes
for the actual and forecast values, and provide code to move the data back
into the normalized table; or... gasp... train your users to think vertically
instead of horizontally.

John W. Vinson [MVP]