Results 1 to 7 of 7
  1. #1
    Bob Blooms is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Aug 2012
    Location
    FL and New England
    Posts
    19

    Retrieve the nth Record in the y Field, where n = 1st argument and y = 2nd argument

    New to Accesss. Trying to migrant project from Excel to Access. I have a personnel listing that includes a job class and step for each employee. I would like to create a query that lists the employee and his/her salary. In Excel I would use a vlookup function (Employee,SalaryTable,Step,False). Haven't the slightest idea how to do this in Access. I have developed relationships and can retreive the match for the job class, but cannot move over to the proper field to get the salary.

  2. #2
    John_G is offline VIP
    Windows XP Access 2003
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    Hi -

    It sounds as if your data may not be properly normalized - can you post the structure of your tables, and explain in a bit more detail what it is you need to do?

    John

  3. #3
    orange's Avatar
    orange is online now Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

  4. #4
    Bob Blooms is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Aug 2012
    Location
    FL and New England
    Posts
    19
    Database1.zip

    I hope I did this correctly. Converted it to a MDB and zipped it to fit.

  5. #5
    orange's Avatar
    orange is online now Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    JUst opened your mdb.

    What are the field names in Salary Table???
    I would expect

    JobTitle some identifier representing a Job Type/Class
    JobLevel (1-...)
    Salary some numeric value


    The other table CS-3Compilation seems to be a concoction of many different things.

    However, I created a new SalaryForJobClassLevels table that is structured.

    JobClass text
    JobLevel number
    Salary currency (could have been number)

    JobClass & JobLevel is the Primary key (it is composite)

    I created a query AddRecordsToSalaryForJobClassLevels that I manually used to create the records in table SalaryForJobClassLevels

    I then created a query ShowNamesAndSalariesUsingJobClassAndStep that joins your CS_3Compilation table to the new SalaryForJobClassLevels

    Here is the query sql (It's just a sample showing how to get data from the tables)
    Code:
    SELECT [CS-3Compilation].Name
    , [CS-3Compilation].Identifier
    , [CS-3Compilation].Source
    , [CS-3Compilation].JobClass
    , [CS-3Compilation].Step
    , SalaryForJobClassLevels.Salary
    FROM [CS-3Compilation] INNER JOIN SalaryForJobClassLevels ON 
    ([CS-3Compilation].Step = SalaryForJobClassLevels.JobLevel) AND
    ([CS-3Compilation].JobClass = SalaryForJobClassLevels.JobClass);

    I had to copy my revisions to a new zipfile (attached)


    Note: I did move some of the salary info 1 record where there were 0's in the first 5 or 6 levels/steps

    You will need to study Normalization and Data base design to move to Access or any RDBMS.
    Attached Thumbnails Attached Thumbnails SampleSalaryQuery.jpg  
    Attached Files Attached Files

  6. #6
    Bob Blooms is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Aug 2012
    Location
    FL and New England
    Posts
    19
    Orange thank you. The first thing I do today will be to get a MSAcess VBA book. It appears that the code is a bit different that in Excel. The objective is to take the salary and add the educational bonus and supplemental pay for wage. Then I can forecast the health benefit costs, etc, etc. to finally arrive at a per employee cost which can then be allocated against program area.

    Need to figure out how to mark this SOLVED. Will post my progress as I developed this project.

  7. #7
    orange's Avatar
    orange is online now Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Note: It is not the vba that is different. Access has a different object model. The key to database is Normalization and Tables and Relationships.
    If you do not get the tables set up first, you will spend many frustrated hours trying to do something (get information) that "structurally" won't work or at best "can't be done easily".

    Keep working at it. Good luck. I'll mark it solved.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. How do i add a new statement to this IF argument?
    By xwnoob in forum Programming
    Replies: 1
    Last Post: 12-22-2011, 03:35 AM
  2. Invalid argument
    By wharting in forum Import/Export Data
    Replies: 4
    Last Post: 10-19-2011, 11:49 PM
  3. Pass argument to a report from VBA?
    By degras in forum Reports
    Replies: 2
    Last Post: 01-25-2011, 11:13 AM
  4. Compile Error: Argument Not Optional
    By bg18461 in forum Access
    Replies: 1
    Last Post: 12-01-2010, 08:47 AM
  5. Invalid Argument Error
    By koper in forum Access
    Replies: 2
    Last Post: 06-14-2010, 11:22 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums