Look at field, check to see if that value exists in another table andact accordingly.

A

aaron.usa

Hello ms.pub.access,

I've been assigned to generate a DB to help analyze certain capability
deficiencies within the US Army. I'm at a real show stopper based on
my
current knowledge level.

I'm in need of some kind of routine that will check the value of the
'Tier3'
field against a list of high priority tier3 values located in a
separate
table called 'mpmi'.

If a high priority tier3 value is found within my table 'analyze' from
field
'tier3' then I need the routine to generate a score of 1 and place it
into
the field called 'score'. If it does not exist then a score of 0
would be
populated instead.

I hope this question makes sense, and it seems rather complex to me so
any
assistance toward even the right angle of attack would be highly
appreciated!

All the best,
Aaron
 
K

KARL DEWEY

Try this after backing up your database ---
UPDATE analyze LEFT JOIN mpmi ON analyze.Tier3 = mpmi.Tier3 SET
analyze.score = IIf([mpmi].[Tier3] Is Not Null,1,0);
 
A

aaron.usa

Try this after backing up your database ---
UPDATE analyze LEFT JOIN mpmi ON analyze.Tier3 = mpmi.Tier3 SET
analyze.score = IIf([mpmi].[Tier3] Is Not Null,1,0);

--
KARL DEWEY
Build a little - Test a little

[email protected] said:
Hello ms.pub.access,
Click to expand...
I've been assigned to generate a DB to help analyze certain capability
deficiencies within the US Army. I'm at a real show stopper based on
my
current knowledge level.
Click to expand...
I'm in need of some kind of routine that will check the value of the
'Tier3'
field against a list of high priority tier3 values located in a
separate
table called 'mpmi'.
Click to expand...
If a high priority tier3 value is found within my table 'analyze' from
field
'tier3' then I need the routine to generate a score of 1 and place it
into
the field called 'score'. If it does not exist then a score of 0
would be
populated instead.
Click to expand...
I hope this question makes sense, and it seems rather complex to me so
any
assistance toward even the right angle of attack would be highly
appreciated!
Click to expand...
All the best,
Aaron
Click to expand...
Click to expand...

Sweet!
Thanks a million!
That worked like a charm.

Kind Regards,
Aaron
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Access Macro Repeat Expression using value of a field in table 0
Access How to check that the value of a field in a new record is not present in 2 fields of the table? 0
Setting field value if a value exists in another table 3
Lookup a value in a table and display text if a value exists 1
check box is checked if value of textbox appears in another table 3
Check a range to see if a value exists in it 5
check box to update field in table with 4 or 0 6
Saving a calculated value in a text box to a field in a table 2

Top