Noobie to FM7, payroll database problem

[mappler]

I am new to FM7. I understand databases and have done most of my work in SQL Server, MySQL, or MS Access. I am trying to move one of our databases to FM7. It is very possible the layout I am attempting to use is foolish for an FM7 database, and I am more than happy to change that. Let me describe what I am trying to do.

I have 3 tables right now. 1) List of all employees in the company. 2) A table that consists of EmployeeID, EffectiveDate, Salary. 3) A table of all valid paydates.

I require 2 views of this data. 1) For any employee, look up their salary history. This is easily accomplished with portals, and works great.

2) The ability to look up any paydate, and show the payroll information that is "in-effect" for this date.

#2 I can't figure out how to do in FM7. The effective dates could be any date, and may not be contained in the paydates table. Paydates are currently dates at a 2-week interval for 30-40 years.

So, for a given paydate, I need to see only ONE salary for each employee, and that salary would be the maximum effective date that is also less than or equal to the paydate.

Thanks in advance,

-Matt

[transpower]

Use three tables: employee, paydate, and employee-paydate (join table). The join table should have employee data as of that paydate.

[mappler]

That would be the obvious solution, but there are 2 problems with that.

1) I don't want to have to do data entry EVERY pay date, only when the information changes (typically once a year for most employees)

2) The effective date may or may not be a valid pay date. Some employees start mid-cycle.

-Matt