Trying to create an Update query based on HR data to find upline V

Hi All, looking for some advice. I have an HR table that contains employee 
information but does not contain management chain info. Basically i am trying 
to determine who the employees upline VP is. The fields i have to work with 
are [Employee Name], [Manager Name] and [Job Title]. I figure the logic would 
be to check the employees' manager and if the manager is a VP (based on job 
title), return the manager's name to a field called [VP]. If the manager is 
not a VP then check that manager's manager, so on and so forth until a VP is 
found.

Any ideas would be much appreciated!!
0
Utf
2/12/2010 2:00:01 AM
access.queries 6343 articles. 1 followers. Follow

1 Replies
1026 Views

Similar Articles

[PageSpeed] 58

A person can have only one manager (in most situations) and the manager is 
also an employee.  Your table should have a primary key, say an autonumber. 
Add a new field, number long integer, called MGR.   
Run this query --
UPDATE YourTable INNER JOIN YourTable AS YourTable_1 ON YourTable.Manager = 
YourTable_1.FName SET YourTable_1.MGR = [YourTable_1].[PersonID];

In the relations window create a self-join from [YourTable_1].[PersonID] to 
[YourTable_1].[MGR].  

Using this means you can display your management chain.
This query will display it.  You need to add the appropriate number of 
levels --
SELECT YourTable.FName, YourTable_1.FName AS First_Level, YourTable_2.FName 
AS Second_Level
FROM (YourTable LEFT JOIN YourTable AS YourTable_1 ON YourTable.MGR = 
YourTable_1.PersonID) LEFT JOIN YourTable AS YourTable_2 ON YourTable_1.MGR = 
YourTable_2.PersonID
WHERE (((YourTable_1.FName) Is Not Null) AND ((YourTable_2.FName) Is Not 
Null));

-- 
Build a little, test a little.


"Ringking1110" wrote:

> Hi All, looking for some advice. I have an HR table that contains employee 
> information but does not contain management chain info. Basically i am trying 
> to determine who the employees upline VP is. The fields i have to work with 
> are [Employee Name], [Manager Name] and [Job Title]. I figure the logic would 
> be to check the employees' manager and if the manager is a VP (based on job 
> title), return the manager's name to a field called [VP]. If the manager is 
> not a VP then check that manager's manager, so on and so forth until a VP is 
> found.
> 
> Any ideas would be much appreciated!!
0
Utf
2/12/2010 9:56:06 PM
Reply:

Similar Artilces:

Exchange Migration Wizard and creating new SMTP reply addresses
Hello, I'm migrating from a Netware 6.5 environment, but am going to be using another tool because of the need to migrate archives and personal address books. I would, however, like to use the Microsoft tool to populate the GWISE SMTP address with the old GroupWise address. Is there a way to run the Exchange Migration Wizard in a mode that will only create the additional GWISE SMTP addresses for all of my users? Aaron ...

Re: Outlook 2007 create background
I played around with this further. You can still use any HTML editor to = create stationery and place it in the user's Stationery folder. You can = also use FrontPage 2003 to create new themes with background images. = Details at = http://turtleflock-ol2007.spaces.live.com/blog/cns!C1013F1F9A99E3D8!230.e= ntry --=20 Sue Mosher, Outlook MVP Author of Configuring Microsoft Outlook 2003 http://www.turtleflock.com/olconfig/index.htm and Microsoft Outlook Programming - Jumpstart for=20 Administrators, Power Users, and Developers http://www.outlookcode.com/jumpstart.aspx =...

Formula argument based on text
Hi All, I have cells (A1:A10) populated with text ("Pass" or "Fail"). In cell (A11) I want to have a formula that returns the text: "pass" if all the cells (A1:A10) contain the text "pass" "fail" if any one of the cells (A1:A10) contains the text "fail". How would I write the formula to achieve this? Regards gregork In A11 enter: =IF(COUNTIF(A1:A10,"Fail")>0,"Fail","Pass") HTH "gregork" <gregork@paradise.net.nz> wrote in message news:0RXSb.19911$ws.2664893@news02.tsnz.net... > ...

query-based distribution group not showing in Outlook
Software: Exchange 2003 standard and Outlook 2003 I have created a query-based distribution group and it isn't showing up in Outlook Address Book. If I create Distribution groups they show up in the Address Book. How do I get the query based groups to show in the Address Book? Under Exchange Advanced I have confirmed that the check box to hide list is unchecked. Thanks, Colin M. On Tue, 27 Dec 2005 10:54:52 -0800, "Colin" <legendsfan@nospam.nospam> wrote: >Software: Exchange 2003 standard and Outlook 2003 > >I have created a query-based distribution ...

Combo Box from data in another tab
Is it possible to create a combo box from data that's in another tab? I have a combo box that's from data in hidden columns. But now I need to move all that hidden data to another tab in the same file. But when I go into properties and go to the ListFillRange option, it won't let me enter another tab name. -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.aspx/ms-excel/200508/1 I have called data from another tab with a combo box by doing th following: First - name the range of the data on the other tab. This can be don with the shortcut on the toolbar (l...

Adding a new data series to an existing chart
I have a stacked bar chart that shows monthly sales by customer type. I want to add an additional data series to show the monthly forecast, to be displayed as a point or line against the existing stacked-bar data. Any ideas? Select the data you want to plot. Drag onto the chart. XL will pop up with a dialog box asking for some information. Provide it and you will be all set. -- Regards, Tushar Mehta MS MVP Excel 2000-2004 www.tushar-mehta.com Excel, PowerPoint, and VBA tutorials and add-ins Custom Productivity Solutions leveraging MS Office In article <1418C2D7-4DF6-4945-A08A...

Promoting and Outlook Email and Creating Contact
After reading an Email in Outlook I promote it to CRM. The author of the Email is not a CRM contact so their name appears in red. Clicking on the authors name, CRM gives the option of reconciling to another contact or creating a new contact record. When I select a new contact record, why doesn't CRM populate the name and Email address just like when you create a contact from an Email in Outlook? Why should I have to go back and look up the Email address from the Email itself to place in the CRM Contact record? I'd post that as a suggestion for future product enhancements. Ho...

How do I change the value in cell based on a future date
I would like certain cells to be cleared after a certain date one way: Put this in the ThisWorkbook code module (Right-click the workbook title bar and choose View Code): Public Sub Workbook_Open() If Date > #12/15/2004# Then _ Sheets("Sheet1").Range("A1,B2,J10,K39").ClearContents End Sub Note that this won't work if the user opens your workbook with macros disabled. Note also that there's no way to prevent a user from accessing your data - i.e., they can set their clock back and open the file. You can make things more difficu...

How to find a value with multi-column, multi-record list
I have a spreadsheet with $costs arranged according to values in both rows and columns. How do I create drop down lists for both the rows and columns and then return the cell value? eg if I have 5 rows (2-6) and 6 columns (B-G) with costs arranged in each cell within this group how do I create drop downs for each selection, 2-6 and B-G, and return the value to a selected cell. In a typical table of this nature there would be descriptive row and column headers. To do a lookup on this table you would then look for the intersection of a specific row header and a specific column ...

Excel 2000
Hi! *First off:* I have created a spreadsheet that has a dynamic range an data validation. *Next:* i know i can add options to the 'dynamic range' and my dro down menu will add the option to its list. *Problem:* How can I make it so the user can add data into th validated data list/drop down menu rather than having the user addin it into the dynamic range? *Example:*this drop down menu indicates how often a system is updated weekly, monthly, etc. etc... i don't want to restrict it, so i want t make it so they can indicate their own time on how often those update occur. TIA -...

Auto Update in Forms
Please help. I am totally terrible in Access and need simple instructions. I have a form that all it has is the employee name control and the employee id number control listed like this: requestor (employee name) tag number (employee id) Can someone give me a simple method for putting in the requestor name and the tag number will pop up automatically. Created a query called qrytaguser which has both fields in it to try to make this happen. Read several forums, but I don't understand VBA code to translate the suggestions. Thanks in advance. -- Johnny ...

Updating all Fields in Word Document
I need to update all fields in the Word document. I have some of the fields in drawing canvas too. I have tried this, which works fine, but except drawing canvas. http://word.tips.net/Pages/T003879_Updating_a_Field_in_a_Text_Box.html Thank you -- Regards Jan Kratochvil Windows Vista Business SP2, Office 2007 SP 2 Sub UpdateAllFields() Dim oStory As Range For Each oStory In ActiveDocument.StoryRanges oStory.Fields.Update If oStory.StoryType <> wdMainTextStory Then While Not (oStory.NextStoryRange Is Nothing) Set oStory = oStory.NextStoryRange ...

Data within a cell
If names are in cells as "last name, first name, middle initial" can a macro be run to change the order to "first name, middle initial, last name"? A formula will do Assuming you have spaces between commas and names, =MID(A2,FIND(",",A2)+2,FIND(",",A2,FIND(",",A2)))&RIGHT(A2,FIND(",",A2,FIND(",",A2)))&", "&LEFT(A2,FIND(",",A2)-1) Then just copy down. If you want this more permanent, copy the cells with formulas, right click, paste special. -- Best Regards, Luke M *Remember to click &q...

How do I make the x axis data the y axis data?
My graph automatically makes certain data the y axis and other data the x axis. I need to just reverse it for the graph I am looking for. Any suggestions? SLG, One option is to reverse the positions of the data on the spreadsheet. Assuming an XY chart, you would change a setup like this: x y 5 4 4 2 5 3 7 4 6 5 to this: y x 4 5 2 4 3 5 4 7 5 6 If this doesn't work, can you post back to let us know what type of chart and data you're using? ---- Regards, John Mansfield http://www.pdbook.com "SLG" wrote: > My graph automatically makes certain data the y axis ...

Data entered from list automatically enters number in another cell
I am using Excel 2007 and here is an simplified example of what I need. Items Price Potatoes 4.35 Apples 5.55 Oranges 7.95 Onions 4.55 Carrots 3.75 Items Column is List for Valid entries in table below Prices are the numbers I want entered when I enter the Item If I enter Oranges from Dropdown List in A16 , I want Excel to automatically enter the number 7.95 3 columns over in D16,or if it is quite a bit easier, just 1 column over in cell B16 Example of table I want: My Entries: Excel Enters ...

Prevent auto recoloring of chart when using Pivot Table data
I have crated a Pivot tabel with data I want to show in a Chart. My problem is when I refresh data the colours of the "data series" changes. For exaple I want one of my series "OK" always be shown in green colour, but after a refresh of pivot all colours changes. This problem is not only colouring. If I have made changes to my chart so that one series ar plotted on a secondary axis, and I change it to another chart type (e.g. the secondary should be shown as a line instead of column) this also changes when refreshing the data. Most irritating! Anyone that knows how to p...

converting plain data to table format
It has been a while since I used excel but I am almost sure there is a way to convert just plain rows and columns of data into a table format. This way I believe that adding rows to this block of data is as simple as tabbing after the last cell and it should just drop down to the next row and insert one??? correct?? Excel should recognize a contiguous range as a table, or list. Include headings in the first row, and leave at least one blank row and column between the table and other items on the worksheet. You can use the built-in data form to add records to the table (Data>Form). Or,...

Data Migration Framework 08-04-04
Hi, I am not understanding something pretty fundamental regarding the Data Migration Framework (DMF). I am looking to migrate a limited number of records (approx 1K) from Goldmine into MS CRM as Account records. I understand that the DTS in SQL can be used to load the data into the CDF database. On page 73 of the Data Migration Framework Guide (v1.2) it states that you use the Goldmine export utility to extract data from Goldmine. However, how do you use DTS to load this data into the CDF database as Goldmine exports it into Excel? Also, using the export facility in Goldmine you can only e...

An automated way of comparing an old spreadsheet with an updated version
Hi, I recieve an updated spreadsheet each month showing the newer entries at the bottom of the sheet. What i need to do is find a way that this newer data can be compared to previous entries to see if it has been entered before (i.e Mr Smith was entered in Jan and again in May) Currently I am having to manually search through the spreadsheet (about 400 cells) and I figure there must be an easier way! Thanks in advance Alex Hi Alex Assuming that you have headers in row 1 and your data you are wanting to search is in column A, enter in a blank column =COUNTIF(A:A,A2) and copy down as far a...

SP3 Problem
Ever since I installed SP3 for Office XP, I have been getting the following error - "A program is trying to access email addresses you have stored in Outlook. Do you want to allow this?" I get this error only when I use Word as my email editor. Hence what is happening is that Outlook thinks that Word is a rogue program trying to send unauthorized emails. My question is, is there a way to prevent this - a whitelist of programs for example? Ajit Disable the PDFMaker addin. (See http://www.slipstick.com/outlook/ol2002sp3.htm#problems for details on how to do this.) "Aj...

Updating a form after using a workflow rule to update the entity.
I have a workflow rule that, on create of a contact, calls an assembly and updates an attribute of that assembly. It works a treat, but after saving, the attribute does not appear in the form. I have to close and re-open the contact to see the attribute. Is there any way to have this display immediately? Thanks Saira ...

cannot download updates for office mac 2004 student/teacher edition
Hello, I have not been able to download office updates for my 2004 version of Office Mac student/teacher since 2005/2006 at home. I first had it on my iBook G4 and received updates no problem until late 2005 early 2006. Then I could only get the updates when I used the open internet connection at the Apple store or another open connection not in my house. When I bought my new iMac G5 and installed the second of three licenses on this computer, I could not get updates for this one either. However, my friend, who is using the third license, had no problem getting the updates for his Macbook...

Problem with KB982526 Microsoft .NET Framework 3.5 SP1 Update for
If I install this update and then reboot my computer freezes right after the boot screen animation... The only way I can get my system back is to boot safe mode and uninstall the update.... Tried this three times, twice thru windows updates, once with the downloaded update files from microsoft I'm running Win7 ultimate x64 HELP! HOW TO Hide an [Optional, non-security] Update... http://www.sevenforums.com/tutorials/24376-windows-update-hide-restore-hidden-updates.html Tim S wrote: > If I install this update and then reboot my computer freezes right after > the...

Why Out of memory error when trying to create a thread?
I'm using MS Visual C++ .NET, Windows XP, and 1GB RAM. Why could be the reason why I get an error message saying Out of memory when running the following code CClientThread* pThread = (CClientThread*) AfxBeginThread(RUNTIME_CLASS(CClientThread), THREAD_PRIORITY_NORMAL, 0, CREATE_SUSPENDED); My application does only have this additional thread. Joachim wrote: > I'm using MS Visual C++ .NET, Windows XP, and 1GB RAM. > Why could be the reason why I get an error message saying > > Out of memory > > when running the following code > > CClientThread* pThread =...

News: Mac BU sweetens Office 2008 upgrade deal (Updated)
Microsoft has modified its special offer for potential Mac Office users. Those who are just dying for a copy of Office now but are waiting for the January release of Office 2008 can now buy any version of Office 2004 for Mac and upgrade to 2008 Special Media Edition for the cost of shipping. That's $6.99 in the US or $10 in Canada-I guess Microsoft hasn't heard yet that the US dollar is valued below the Canadian dollar now. But how is this different from the previous offer? In September, the "free" upgrade to 2008 was for an equivalent version of Office. So, for exampl...