Access 2000 - Colour Coding Fields?

[TobyR]

Hi, I need to colour code fields in my database based on whether the data entered is a guess, derived data or factual data...

I have no idea how to do this! Apart from create a field corresponding to each field in my database with the values 1-3 in (1 for guess, 2 for derived etc) but I'm pretty sure this will dash any hopes I have of getting normal form in my database.

Anyone know an easy way to do this? As far as I know conditional formatting won't work as it's the user that tells the database what colour code to use for each field.

Thanks very much for any help you can provide.

Cheers,

Toby

 

[Pat Hartman]

If you don't tell Access, how will it know if something is a guess, factual, or derived? You need the additional field. Having the field doesn't violate third normal form since it is information not contained anywhere else. You can then use the value of the flag field to control the color of the value field in forms and reports.

 

[TobyR]

Unfortunately I have 100+ fields, adding another 100 to colour code them is going to be mammoth task!

Any workarounds you know of?

Thanks,

T

 

[Pat Hartman]

1. I ask the question again. How is Access to know whether each individual field is a guess, factual, or derived? That is a separate piece of information from the actual value. If you have two pieces of information, you need two attributes. There is no getting around it!
2. If you have a hundred of these fields in a single table, you probably have created a spreadsheet and called it a table. You need to review your design.

 

[TobyR]

1. I ask the question again. How is Access to know whether each individual field is a guess, factual, or derived? That is a separate piece of information from the actual value. If you have two pieces of information, you need two attributes. There is no getting around it!

The user needs to input whether it is fact, guess or derived, but I don't see any way of doing it apart from creating a field for each field that needs the fact/guess/deri information entering.

2. If you have a hundred of these fields in a single table, you probably have created a spreadsheet and called it a table. You need to review your design.

They are all added to "Class" tables (I have a table for Engine, Chassis, Tyres, Brakes, etc)

My table structure is posted in this thread here:

Table for Car Gear Ratios

Many thanks for your persistant help.

Toby

 

[Pat Hartman]

Could every single field be a guess, factual, or derived? It looks to me that the tables are properly normalized except for the performance summary. That table has what we call a repeating group. You need to separate the data from the column names and make it a code field. That will reduce the table to something like the following. So instead of having a table with many columns, you end up with few columns and many rows:

tblPerfSummary
SummaryID (pk)
CarID (fk to car table)
MeasurementType
Speed
MeasurementStatus (guess, derived, factual)

PS, it is non-standard to prefix columns in tables with their data types.
PPS, it is poor practice to use the same name for every primary key. Use a descriptive name to help the poor human have a chance at visualizing your table structure without having to open the relationship diagram. The foreign key names would be appropriate (without the prefix) to be the primary key names.

 

[TobyR]

Could every single field be a guess, factual, or derived? It looks to me that the tables are properly normalized except for the performance summary. That table has what we call a repeating group. You need to separate the data from the column names and make it a code field. That will reduce the table to something like the following. So instead of having a table with many columns, you end up with few columns and many rows:

tblPerfSummary
SummaryID (pk)
CarID (fk to car table)
MeasurementType
Speed
MeasurementStatus (guess, derived, factual)

PS, it is non-standard to prefix columns in tables with their data types.
PPS, it is poor practice to use the same name for every primary key. Use a descriptive name to help the poor human have a chance at visualizing your table structure without having to open the relationship diagram. The foreign key names would be appropriate (without the prefix) to be the primary key names.

Hi,

Thanks for that, unfortunately, yes, most of them need a guess/factual/derived field... I can't see any way of doing it apart from creating a field for each piece of data in my table.

Regarding the table structure you gave me, thanks, but its all mixed data, some of them are speeds, some of them are times, G-force units, etc so they can't be mix and matched.

Thanks,

Toby

 

[Pat Hartman]

So you're telling me that dbl060, dbl100, dbl150, dbl600, dbl700, dbl800 aren't all different instances of the same data. And int600slalom, int700slalom aren't different instances of the same data. How about dblQuarterMileTime and dblStandingMileTime? And dblQuarterMileSpeed and dblStandingMileSpeed? When a name contains data as these do, that means that they are a repeating group. It is the same as Jan, Feb, Mar - Dec. Yes they are all different names but they are all names of a month and instead of having one row with 12 columns, they should be stored as 12 rows each with only one month of data.

 

[TobyR]

So you're telling me that dbl060, dbl100, dbl150, dbl600, dbl700, dbl800 aren't all different instances of the same data. And int600slalom, int700slalom aren't different instances of the same data. How about dblQuarterMileTime and dblStandingMileTime? And dblQuarterMileSpeed and dblStandingMileSpeed? When a name contains data as these do, that means that they are a repeating group. It is the same as Jan, Feb, Mar - Dec. Yes they are all different names but they are all names of a month and instead of having one row with 12 columns, they should be stored as 12 rows each with only one month of data.

Hi, thanks, I see what you mean now, I will modify my design.

Just done the form as well, D'oh.

 

[TobyR]

Hi,

I'm still not sure how to do this...

Say I have the fields in CarStats:

060speed
laptime
maxbhp
maxbhprevs
skidpadg600

And I want to add fields to say whether the data is factual, or estimated, would I add the fields to the same table so it looks like this?


060speed
060speedFactDerived
laptime
laptimeFactDerived
maxbhp
maxbhpFactDerived
maxbhprevs
maxbhprevsFactDerived
skidpadg600
skidpadg600FactDerived

It doesn't look like a very near way to do it, and I will have to do this in about 30 fields spread through about 10 tables...

Isn't there anything more elegant I can do with this?

 

[Pat Hartman]

Not that I can think of. It sucks doesn't it? The only thing would be if every value in a record were derived, factual, or estimated. Then you could have only one flag per record. As it stands, it looks like one flag per field is your only choice.

 

[TobyR]

Not that I can think of. It sucks doesn't it? The only thing would be if every value in a record were derived, factual, or estimated. Then you could have only one flag per record. As it stands, it looks like one flag per field is your only choice.

Hiya,

Thanks for all of your help,

Looks like I will have to do it this way.

Much appreciated