Multiple scenarios in queries

H

hughess7

Hi, I have an IIF statement which calculates a date field value depending on
the value of another field. I run an append query to achieve the results. I
now need to introduce a third option though and also check the value of a
different field. Am I right in thinking this is too complex to be done in a
query and I will have to maybe apply this to the recordset after it has been
created?

Basically if a dealer rank is red I want to add 11 months to a review date,
Orange add 23 months and Green blank date. But if AuditNo = 0 then add 3
months.

Thanks in advance for any help.
Sue
 
R

Rick B

These are calculated fields and should not be stored in the table using an
append query (or in any other manner).

In your reports, queries, and tables just perform the calculation you need.
No need to store a "review date" if you can calculate it on the fly. To
help drive this point home, what if the dealer rank changes? Are you going
to constantly go back and run update queries to correct values? I would
save the "base review date" and then perform my "adjustments" in my queries,
forms, and reports.
 
H

hughess7

Mmmm not quite as simple/straightforward as all that! I know you shouldn't
store calculated fields and they should be done on the fly... but yes
actually every time the ranking changes the append query will need to be run
again as it affects which dealers will appear in the list. Basically we get
the ranking every month from a supplier and this planning process is run
approx every quarter. The append query puts the data in a temporary table,
the user then has this displayed on screen in a continuous form and selects
which dealers they want to audit. The selections will then be written to the
permanent itinerary table. The next audit date being stored as review date.

The next audit date is a suggested date of next audit, calculated as
previously described by rank and audit type but also taking into account
exclusion of any weekends or holidays (from a holiday table). This is all
done in code before the form is displayed on screen with the results for the
user to make their selections from. Took me a while to get it all working
this far... ;-). Now they want me to include the different audit types and an
extra rank which I am not sure how to using my existing method.

Sue
 
Top