Loop through Employees in report

[valgore]

Hello, i've attached my DB because it's confusing to explain without having it. basically, i have three tables i'm worried about; Employees, AssignmentData, and EmployeeSalaryHistory. What our DB is used for is it tracks which employee is on which project on any given month. The AssignmentData table holds all this stuff. The EmployeeSalaryHistory shows us, for any given range of months, what their monthly salary is.
What im having problems with is for the AssignmentData report. when you open frmReports, you can select a date range (12 months) and it populates the report based on this information. i used to store their individual salaries by month in the AssignmentData table, which made this report easy. but we decided to have a separate table to keep track of past salaries. I'm having trouble telling the report which salary in the EmployeeSalaryHistory table to put into the month fields.

Any help is appreciated
also, if anyone has a suggestion on how to restructure these three tables more effectively, i'm all ears


Valgore

 

[GinaWhipp]

Do not put the Employees Past Salary in a seperate table. Instead put a Yes/No field for Historical. Then your report will be easy. If you still want to keep it seperate, you'll have to use a UNION query to combie the data and then base your report off of that... Note, this will reduce speed of the report opening based on how many records there are, the more records the slower it gets.