Problem with Fields in Access 2000...

[dnewton]

I have one table and I am trying to get one field to show
a value based of the value of another field. Here is an
example..
I have a field named "Day" and a field named "Sunday
hours"..How do I make the "Sunday" field show a value of 8
when the "Day" field shows Sunday as a value.It is
important that the value in the "Sunday Hours" field shows
up when the value in the "Day" field is selected. This is
easy in Excel, but I have not been able to figure this out
in Access. Am I a rock, or will Access even do this?

 

[Steve Schapel]

Dnewton,

No, you are not a rock. And No, Access will not do this. Access is a
database program, not a spreadsheet. As such, it is important to
realise that tables are for background data storage, and are not
normally seen in day-to-day usage. The kind of thing you are talking
about is done in a Query, but once again, the query's datasheet is not
normally seen... you should use a form or report for this. Another
principle of database design is that you don't store values which are
derived from other existing data, so you should not have a SundayHours
field in the table at all... just delete it, it serves no useful
purpose. The expression you need to use in the calculated field in your
query is...
SundayHours: IIf([Day]="Sunday",8)

By the way, as an aside, the word Day has a special meaning in Access,
it is called a reserved word, and as such should not be used as the name
of a field or control or database object... I recommend this be changed.

 

[John Vinson]

I have one table and I am trying to get one field to show
a value based of the value of another field. Here is an
example..
I have a field named "Day" and a field named "Sunday
hours"..How do I make the "Sunday" field show a value of 8
when the "Day" field shows Sunday as a value.It is
important that the value in the "Sunday Hours" field shows
up when the value in the "Day" field is selected. This is
easy in Excel, but I have not been able to figure this out
in Access. Am I a rock, or will Access even do this?

Excel is a spreadsheet, a good one.

Access is a relational database.

THEY ARE DIFFERENT. You can drive nails with a crescent wrench, but
that doesn't make it a hammer!

Storing derived data such as this in your table accomplishes
three things: it wastes disk space; it wastes time (almost
any calculation will be MUCH faster than a disk fetch); and
most importantly, it risks data corruption. If one of the
underlying fields is subsequently edited, you will have data
in your table WHICH IS WRONG, and no automatic way to detect
that fact.

Just redo the calculation whenever you need it, either as a
calculated field in a Query or just as you're now doing it -
in the control source of a Form or a Report textbox.

Just use a Query with a calculated field

SundayHours: IIF([Day] = "Sunday", 8, 0)

 

[Bas Cost Budde]

THEY ARE DIFFERENT. You can drive nails with a crescent wrench, but

that doesn't make it a hammer!

Ah? I've spent hours looking for my hammer. So you have it?

Funny that if problems look like nails, all tools will get to resemble
hammers, don't they?

 

[John Vinson]

Ah? I've spent hours looking for my hammer. So you have it?

Funny that if problems look like nails, all tools will get to resemble
hammers, don't they?

<g> Yep. I keep finding that problems look like hex-head bolts
though...

 

[Bas Cost Budde]

<g> Yep. I keep finding that problems look like hex-head bolts
though...

Oh, is that hammer called a wrench? ... I am beginning to understand the
origin of all these hexagonal holes in the wall...