Here is my issue:
[Edit]: Alright, this is almost turning into a separate question at this point...my apologies. To cut right to the chase, here is my business requirement. I have an Employee record (an item in a list) that contains a Salary field. I need to be able to view a report of the Employee's salary history.
That's it. In my mind (which is definitely coming from a SQL/database background) this meant a one-to-many table (one Employee with many Salaries). Turns out this is not so simple in SharePoint since I would have to filter the Salary field to only display Salaries from the SalaryHistory list for a given Employee.
My new-ish question is what's the best way to do this? Is it possible to use only an Employee list with a Salary field and then somehow view or display the history of that particular Salary field? For example, if the Salary was changed from 50,000 to 60,000 for Employee 00001, can I view a report/page of some sort that shows the history of this Salary field?
I want to convert an existing MS Access/VBA legacy system to SharePoint. The existing system is fairly simple: a database of Employees, with typical Employee information. The one issue I am having trouble with is that an Employee has not only a Salary, but a Salary History. What this means is that a supervisor should be able to view an Employee's Salary History, and not just the current salary of the Employee.
In my mind, this is a one-to-many relationship: an Employee table linked to a Salary History table (the one-to-many is that an Employee can have many Salaries over a period of time).
[Edit]: Sorry if I'm a little vague, I'm still trying to wrap my head around the particular implementation of these requirements. I was only given a screenshot of the Access application and told to make a (roughly) equivalent SharePoint site.
A little more info: I have a SalaryHistory list that contains an EmployeeID lookup, so I do have a list of Employee salaries. What I really would like is this:
In the main Employee list, when viewing an existing Employee, I would like to only see the Employee's current (i.e. most recent) Salary. If this Salary gets changed and saved, then a record should be added to the SalaryHistory table. When creating a new Employee, upon Save, a record is added to the SalaryHistory list with the new Employee's Salary.
What's the best way to implement this in SharePoint? I have tried using InfoPath as well as some customized code, but I can't seem to get it correct. In my mind, this is a relatively simple concept, but I'm having trouble taking it from concept to a concrete SharePoint solution.