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

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
0
aaron
2/6/2008 5:33:56 PM
access 16762 articles. 3 followers. Follow

2 Replies
761 Views

Similar Articles

[PageSpeed] 59

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


"aaron.usa@gmail.com" wrote:

> 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
> 
0
Utf
2/6/2008 6:04:01 PM
On Feb 6, 1:04 pm, KARL DEWEY <KARLDE...@discussions.microsoft.com>
wrote:
> 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
>
> "aaron....@gmail.com" wrote:
> > 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

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

Kind Regards,
Aaron
0
aaron
2/7/2008 10:52:06 AM
Reply:

Similar Artilces:

Data -> Table Problem
Hi, I'm getting incorrect values from the data table function found under the Data menu, the function is listed as "table." I set up a simple example of this problem to demonstrate the bug in Excel. (This is a simple example, -I know this objective could be achieved without data table- but the project that I actually need to do is MUCH more complicated. The example is just to demonstrate the potential bug in Excel.) In the diagram below, imagine the blank cell is A1 and the value 1 is in B1. The simple request says that cells in column B = column A +1. Cells in column A = c...

Can I change names of fields in calendar?
I am using a calendar like a project manager. I would like to change the names of fields eg Location to Action to be done, Contacts to Action officer. I have been able to change the column headings in Custome view, but this doesn't change teh names of the fields on the calendar form. That is teh form that appears when you make an appointment. No, you can't change them. You could use in-cell editing and create the items in table view, where the column display names can be changed. -- Diane Poremsky [MVP - Outlook] Outlook Tips: http://www.outlook-tips.net/ Outlook &a...

envelope sign in Num field
Hello group, For some transactions MS Money 2002 puts an envelope with question mark in Num fiels. What does it mean? Thanks in advance Regards Aivars See http://umpmfaq.info/faqdb.php?q=32 -- Regards Bob Peel, Microsoft MVP - Money For UK tips & fixes see http://support.microsoft.com/default.aspx?scid=fh;EN-GB;mny. For wishes or suggestions see http://register.microsoft.com/mswish/suggestion.asp or for UK wishes http://www.microsoft.com/uk/support/money/feedback I do not respond to any emails that I have not specifically asked for. "Aivars" <aenkuzens@apollo.lv&...

Pivot table Troubleshooting
I've created a pivot table report where I update the data on a regular basis with a large query text file(8MB). My problem is that the prior month row grouping headings still appear in the drop down box along with the current month's groupings, even after I refresh the table. I know the data doesn't exist in the table because I can deselect the show all and filter on the a prior month grouping and nothing appears. What is really strange is that if I move the row up into the page area, all I see is the current month headings as I would expect. If I create a new pivot t...

All my icons look the same (except IE8 and the recycle bin)
i cant open any windows...when i click on them the open with window pops up and wants me to pick wat i want to open it with...i can only get on the internet in safe mode with networking. and when i try to open...for example paint it says invalid bitmap, or its format is not currently supported and other windows wont even open. got any ideas on how i can fix this?? On Sun, 30 May 2010 06:54:01 -0700, xxkoreanxx wrote in message <news:999F2CE9-8E21-47DC-8BA4-BAC1F1329F6E@microsoft.com>: > i cant open any windows...when i click on them the open with window pops up > a...

Subform comparison of 2 tables excluding non zero value
I am creating a subform (at least that is what I am assuming would be best choice)(Access 2003/NT) in which the user will type in a Melt# (contained in the tblChemicalResults). I need to create a side by side comparison of the chemical requirements from the tblChemicalRequirements to the actual results contained in the tblChemicalResults, but I need it to exclude any non zero values from both tables. Each table contains all of the possible 14 different metal elements that could possibly go into making a particular part. Obviously, not all 14 go into every part, so how do I display only ...

Looking for feedback
Hey everyone just started my own business doing consulting / training for Microsoft Outlook please check out my site www.outlookchallenged.com and provide any suggestions / feedback please. Any advice is good advice at this point. Thanks -- www.outlookchallenged.com Br Sense <outlookhelp@ftard.com> wrote: > Hey everyone just started my own business doing consulting / training > for Microsoft Outlook please check out my site > www.outlookchallenged.com and provide any suggestions / feedback > please. Any advice is good advice at this point. The best advice I have to offer ...

What is equivalent to Act! contact management software?
Hello! What would be the MS equivalent of Act! for contact management software? Business Contact Manager added to Outlook 2003, CRM, etc? Thank you! Gregg Hill I'm guessing it would be Outlook with BCM. "Gregg Hill" <bogus@nowhere.com> wrote in message news:uc9EsTlHGHA.344@TK2MSFTNGP09.phx.gbl... > Hello! > > What would be the MS equivalent of Act! for contact management software? > Business Contact Manager added to Outlook 2003, CRM, etc? On 20-Jan-2006, "Gregg Hill" <bogus@nowhere.com> wrote: > What would be the MS equivalent...

how to retrieve varbinary field through ADO inVC++?
...

share custom calendar wtih mac, export custom fields
Hi, I have two questions 1. Is it possible to share a custom calendar form with a mac user so that they can use it? 2. How do I export my custom fields when I export my calendar. Basically, we are trying to use our calendars to gather some custom data on mac and pcs. Then we want to be able to export the calendar data and the data collected in the cusomt fields 1) Not possible. 2) Outlook doesn't support exporting custom fields directly. Quick and dirty method: 1. Put all the items from your form in one folder. 2. Use a table view to display all the data fields t...

Looking for CRM Solution
Does anyone use, or has anyone encountered, a CRM based solution used for residentail, multifamily property management? On Apr 28, 12:55=A0pm, MrB <M...@discussions.microsoft.com> wrote: > Does anyone use, or has anyone encountered, a CRM based solution used for > residentail, multifamily property management? The beauty of Microsoft CRM is that it can be customized for any business in any industry. It's ability to be customized using front- end tools (not within code) is , as far as I know, unparalleled by any other software package. You have the ability to create new entiti...

=COUNTA() with multiple look-up
Hi All, I would like to count the number of items that have a value "AAA" in column A and an amount >0 in column B. As I am not quite sure how to incorporate two look-ups in a counta formula I was wondering if anyone can give me the formula? Many thanks! Rgds, Robert Try something like this: =SUMPRODUCT((A1:A100="AAA")*(B1:B100>0)) or....if there may be some text cells in B1:B100 interspersed with the numbers =SUMPRODUCT((A1:A100="AAA")*ISNUMBER(B1:B100)*(B1:B100>0)) Is that something you can work with? *********** Regards, Ron XL2002, WinXP &...

Multiple Fore Colors in one Field
I have a single result text field in which I string some numbers together in aseparated by a comma, such as "1,5,(9),12" Is there anyway that I can have different colored text in the same field? In my example above, 1 would be green, 5 would be blue, and (9),12 would be red. Thanks, Mike I suppose it might be possible if you used an RTF control, but other than that, no. Why are you trying to put multiple values into a single field? That's a violation of database normalization principles, which is a major reason why Access doesn't provide any means of doing it. --...

can I edit a picture to look faded?
I want to edit my picture to be faded so I can use it as background with text over it. "deborahm" <deborahm@discussions.microsoft.com> wrote in message news:4D93DD63-8553-447D-A0AC-44EE0E1BD9B3@microsoft.com... > I want to edit my picture to be faded so I can use it as background with > text > over it. In Publisher you can do it one of two ways. For both ways start by inserting the picture. Method 1, right click on picture and select Format Picture, click on picture tab, then click on the dropdown for Color under Image Control and select wash...

No Calculated Field Option in Excel 2002?
Hi there - I am at a new job and we have Excel 2002 - in pivot table I don't get an option to insert a calculated field - in fact "calculated field" doesn't even show up in the help section. any ideas? David Open the Pivot Table Toolbar (Right click on any toolbar to select) and in the dropdown on the left you will see Formulas>Calculated field... (and item for that matter) -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS "David" <anonymous@discussions.microsoft.com> wrote in message news:28d5a...

Option Grp and Field If statement
I apologize. I accidently posted this in the Modules forum first. I have a form called "frm_Credits". I have an option group called "Frame_Credit". Within this group are 4 options. They populate the "Credit" field. I also have a field called "Units". What I need to do is go through a series of scenarios that could occur and perform an action for each one. This is what I have for code and I put it after updating the "Units" field and also after updating the Option Group. After updating either one of these fields, nothing happens. ...

It wont let me type the letters "la" in the "To" field...
very, very strange...i have rebooted several times. One of my clients is laura, and when i go to type it, when i get to "LA", it erases the message. Any clues here? Thanks! you can send to logan.bragg@udig.com ...

Table Definitions
I am new in Great Plain. We just bought the 8.0 version. In the SDK on cd 2 I found the definition for the any new changes to new or old table. But what about a complete definition for all table? Where can I find a complete definition for table and work flow? Here is my summary on how to get info about tables and fields. 1) Open the window, then select Tools >> Integrate >> Table Import to see the tables associated with the Dexterity Form. 2) Open the window, then select Tools >> Customise >> Customise Current Window. Once in Modifier Layout mode, look at the ...

Shading Rows and/or Columns in an Excel Table
I created a financial table with interest rates on the first row an time periods on the first column. For each combination of interest rat and time period, I've computed a future value factor. What I'd like t do is this - when I click on a certain interest rate, the column for i will change to a different color, and when I click on a certain period the row for it will change to the same color. It will then be easier t locate the future value factor - one that lies in the intersection o the highlighted row and column. Thanks in advance for your help -- Nerdzo ------------------------...

Bulk Import fails on simple single record,single field import
Hi I cannot get CRM 3.0 to import data into any entities. For testing, I created a simple csv file with 1 line (record) and only a single field in it. It has no spaces, non-text chars are anything else that could be strange. It's UTF-8 encoded. CRM allows me to upload the file and map the field, but when I click next, I get a "Import Source File is Corrupt". I'm now at a total loss... Any help would be a much appreciated! Thanks James Okay - finally figured it out. If you have the same problem and you've done everything everyone has told you to try and it still doesn...

data look up and return values across a row
I have a list in one spreadsheet, we'll call it 'spreadsheet A' that I need to cross reference with another larger spreadsheet, which we'll call 'spreadsheet B'. I'd like to remove everything from B that isn't on A. Is there an easy way of doing that? Chip Pearson has lots of info about working with duplicates at: http://www.cpearson.com/excel/duplicat.htm Betsey wrote: > > I have a list in one spreadsheet, we'll call it 'spreadsheet A' that I need > to cross reference with another larger spreadsheet, which we'll call > &#...

matching unequal text fields
I want to find records that contain disparent records with data in comparable fields like this: Data in Table 1 = BRG, ROLLER, 6020, FLANDERS, ER53 Data in Table 2 = BRG, ROLLER, 6020, FLANDER, ER53, 6.22X5.11, OD 5" Notes: the data layout is not standard for all records (i.e. the commas, naming convention are not universal, so we can't just strip-out the data). I'm not sure what you want to compare between the two fields? Which text from Table 1 is to match which text in Table 2? Show specific examples, please, and provide table and field names so that we can ta...

Looking for basic multiplaction formula
HI ALL, I'm trying to multiply cells d5-d9 by cells a5-a9 and put the answer in cells d14 - d18 ( using caps ) I have tried =(D5*A5) in d14 and then pulling it down to d18 PLEASE HELP ME IVE BEEN AT THIS FOR AN HOUR AND A HALF!! Sounds to me like you had it right... what's not working? In cell D14 enter "=D5*A5" Copy that and paste it in cells D15 to D18. Ken Puls, CMA - Microsoft MVP (Excel) www.excelguru.ca Concarp wrote: > HI ALL, > I'm trying to multiply cells d5-d9 by cells a5-a9 and > put the answer in cells d14 - d18 ( using caps ) > ...

Convert Hyperlink field to straight text
Hello, I am having to migrate an access database to SQL and one of the fields is the email address of type Hyperlink. I want to get just the straight email address and store that in my sql database! Does anybody have any suggestions? Thanks dbl Hi David, If you use a calculated field like this, where Hyper is the name of the field, CStr(Hyper) AS sHyper in a query you'll see the actual contents of the field. Typically this will be something like: MAILTO:joe.blow@address.com or Joe Blow#MAILTO:joe.blow@address.com So all you need to do is use an expression in the calculated fi...

Error message 7465 doesn't exist message
When I try to delete a user in GP 9.0 I get a popup with this message in it: Error message 7465 doesn't exist Can anyone tell me what that is supposed to mean? ...