Calculated field question - Access 2003

[Sokan33]

I have 2 tables.

Table1

TitleID TitleDescr
1 President
2 Vice-President
3 General Manager
4 Manager

Table2
CurrentTitle General Manager (Example)

Position Form
AutoRank (Calculated Field)

OBJECTIVE
Calculate AutoRank by subtracting 1 form TitleID which in this example will
return the value of 2 (Vice-President). My question is how can I return the
corresponding TitleDescr of the new TitleID to AutoRank field in my form?

Thanking you for your assistance!

 

[KARL DEWEY]

Table2 must use TitleID.

Build a query using Table1 and add column --
AutoRank: [TitleID]-1
This query will have three fields -- TitleID, TitleDescr, and AutoRank.

Build you query using Table2 and above query. Join Table2 TitleID to
QUERY.AutoRank.

 

[John Vinson]

I have 2 tables.

Table1

TitleID TitleDescr
1 President
2 Vice-President
3 General Manager
4 Manager

Table2
CurrentTitle General Manager (Example)

Position Form
AutoRank (Calculated Field)

OBJECTIVE
Calculate AutoRank by subtracting 1 form TitleID which in this example will
return the value of 2 (Vice-President). My question is how can I return the
corresponding TitleDescr of the new TitleID to AutoRank field in my form?

Thanking you for your assistance!

How about a different approach? Rather than having a text CurrentTitle
field in Table2, store a Long Integer TitleID. You can display the
text of the title using a Combo Box on a form, or using a Query or a
DLookUp function call in a report; you'll save a bit of disk space
(trivial) and avoid the risk of typos like Genreal Manager (not so
trivial!) in your table; and you'll nave the numeric field right
there.

If you have some good reason (hint: having the title in the Table
Datasheet view is not a good reason <g>) for storing the text title
rather than using Access as a relational database and storing a link,
then you can create a Query joining [Table1] to [Table2], joining
[TitlleDescr] to [CurrentTitle], and using a calculated field

AutoRank: [Table1].[TitleID] - 1


John W. Vinson[MVP]