Query to update specific field in table

[Jblackbelt]

Hi all,

I need some help making a query to update a table as balances change. I need to make a query that changes the field CRM Threshold (checkbox) from TRUE to FALSE if the following happens:

If Tot Cd 2 or 3 has a Curr Princ Bal of less than $1,000,000 CRM Threshold = False

If Tot Cd 4 has a Curr Princ Bal of less than $2,000,000 CRM Threshold = False

If Tot Cd 10,11,14,15,16,17,21 has a Curr Princ Bal of less than $2,000,000 CRM Threshold = False

and then vice versa

If Tot Cd 2 or 3 has a Curr Princ Bal of greater than $1,000,000 CRM Threshold = True



If Tot Cd 4 has a Curr Princ Bal of greater than $2,000,000 CRM Threshold = True

If Tot Cd 10,11,14,15,16,17,21 has a Curr Princ Bal of greater than $2,000,000 CRM Threshold = True

and that if the Tot Code is not listed above then CRM Threshold = False


Unfortunately with my limited access knowledge I am not sure where to begin. All help is much appreciated.

Thanks,

JB

[June7]

Saving calculated data is usually a bad idea, especially aggregate data. Calculate when needed. Review http://allenbrowne.com/AppInventory.html

[Jblackbelt]

Unfortunately my hands are tied with the fact that the database was setup previously saving calculated data. The former user of the database set it up that it updates the balances based on a query on a monthly basis. I need to calculate what needs to be reviewed based on balances. The report I created brings those in based on the CRM Threshold checkbox.

We have a new solution to the database coming in approximately a year, so in the mean time I have to work with what they gave me. Looking at the link (if I understand it correctly) its basing on the # rather than the $ amount, which would be the opposite of what I need. Let me know if I am missing something.

Thanks again for all your help thus far.

[June7]

Any query constructed to do the table update could just as well be a SELECT query to calculated the data when needed.

I am confused on what you need. You say the db is already set up to update the balances - performed monthly? You want to do the update with every data edit? Couldn't you just run the established UPDATE whenever you want?

[Jblackbelt]

Correct. the balance field, Curr Princ Bal, is updated on a monthly basis.

When they update the Curr Princ Bal field, I want it to update the CRM Threshold Field (a checkbox field that uses true to check the box, false to keep it unchecked) based on the two factors of the records "Tot Cd" and "Curr Princ Bal"

If Tot Cd 2 or 3 has a Curr Princ Bal of less than $1,000,000 CRM Threshold = False

If Tot Cd 4 has a Curr Princ Bal of less than $2,000,000 CRM Threshold = False

If Tot Cd 10,11,14,15,16,17,21 has a Curr Princ Bal of less than $2,000,000 CRM Threshold = False

and then vice versa

If Tot Cd 2 or 3 has a Curr Princ Bal of greater than $1,000,000 CRM Threshold = True

If Tot Cd 4 has a Curr Princ Bal of greater than $2,000,000 CRM Threshold = True

If Tot Cd 10,11,14,15,16,17,21 has a Curr Princ Bal of greater than $2,000,000 CRM Threshold = True

and that if the Tot Code is not listed above then CRM Threshold = False

Let me know if that helps clear up what I am trying to do. I know its a lot of fields.

[June7]

Yeh, makes my head spin. I think will need custom function to program all those conditional criteria. The function can be called from the UPDATE query.