Review Cycles

[Luke]

My database has a bunch of reports. The company I'm working for uses review cycles (monthly quarterly, and annually). I have a Reviews form with a review cycle based off of this table:

CycleID.........CycleDesc
1....................Monthly
2....................Quarterly
3....................Yearly

I HAVE to keep this table for my forms and reports to work.

I also have another field called "ReviewCycle" in a separate table. The records under "Review Cycle" are "M" "Q" and "YYYY" (monthly, quarterly, yearly). I need this field in order to create a query using the DateAdd function. My query contains the following:

Entity Name, Review Cycle (M,Q,YYYY), Review Date, and my Next Review Expression using the Dateadd function:

Next Review: DateAdd([ReviewCycle],1,[ReviewDate])

Currently my boss has to select "CycleDesc" (Monthly, Quarterly, Annually) from the first table AND "ReviewCycle" (M,Q,YYYY) from the other table. He only wants to select it once.

I would like to either:


(1) Delete my "ReviewCycle" field and use "CycleDesc" in my query's Expression above, but the Dateadd function needs to use "M,Q,and YYYY" for the expression to work.
(2) Somehow LINK my "ReviewCycle" field to my "CycleDesc" and "CycleID" fields so that I only have to choose the Review Cycle for each company once in the forms.

Any auggestions?

Thanks.

-Luke

[June7]

You could use a nested IIF in the expression.

DateAdd(IIF([CycleDesc]="Monthly","M",IIF([CycleDesc]="Quarterly","Q",IIF([CycleDesc]="Yearly","Y",""))),1,[ReviewDate])

Other table not needed for this. Is anything else in the Other table? I am not even sure why you need the first table. These three values can be offered to user without pulling from a table.