Excel 2003 Pulling data according to date

[Bill Sublette]

Morning all!

Okay so here's the situation... I have employees that have had rate changes during a job and if I edit the information and run a report for a previous week ending date the payrate is wrong (obviously because I changed it) and what I need to be able to do is list the person's name and rate i.e.
Name ST RATE OT RATE
John Smith 25.00 37.50 This is before the rate change

John Smith 30.00 45.00 After rate change

What I was thinking is that I could place a couple columns with a range of dates and then if the week ending date falls within that window it'll pull the current rate as opposed to the previous rate. I've tried a few things and it doesn't seem to want to recognize the dates and it especially doesn't like an open date. Can someone help me out on this? It's kicking my butt...

I attached an example. Please note that the two rows at the end of the table are just me trying to figure out how to do it and in no way required. The part below the week ending date is just a copy of what I need pulled.

Thanks,

Bill Sublette

[NeoPa]

Unless I'm much mistaken Bill, this is a case of redesigning your data rather than looking at your logic.

If I understand the situation correctly (and I don't look at databases for the question. I expect everything relevant to be laid out clearly in the post.) then you are not storing dates with the rates. Without such data, any handling of this issue is likely to be a fudge. Fudges can sometimes work, but they don't often, and they're never good for building on going forward.

Does that make sense?

[Bill Sublette]

I have their start date saved in the database. All I have to do is add another column with the rate change date. The big issue is having it pull the correct data within the database according to my selected Week Ending date. The example is miniscule compaired to my real database (over 12Mb file) as it has over 4k entries per week.

[NeoPa]

If I'm even going to look at this Bill I will need more details posted in the thread. What is in the post is not going to enable me to give you any help beyond what I've already posted.

[Bill Sublette]

Oh okay. What I need to be able to do is pull a person's rate according to the selected week ending date which is on a separate tab. When the date is selected I need it to pull their current rate, the hours worked during that week (including the days) and then where they worked and the quantities (if applicable) that were completed during that date. A person's rate can change for several reasons but tracking it is the important thing. It then needs to total the hours worked per person including the person's Overtime (OT) and any/all per diem associated with that employee. I have it now where it'll pull all the required information except the rate change information. I'll attach the current working file that will show you better what I am talking about. The "Daily Time Report" tab is where the rates are being pulled in. The "Direct (2)" tab is where I am pulling the rate and all the other data is being pulled from the "Master" tab. Thanks in advance for taking a look at it.

Thanks,

Bill Sublette

[MMcCarthy]

I think you misunderstood the original point NeoPa was trying to make.

What you are trying to do requires a different structural approach to the database. You can't just add columns to your existing table to handle this you need whats normally called a transactional table. A transactional table basically handles the history of a value that changes for a record. A sample structure that works along these lines is laid out below to show you what I mean.

tblEmployee
EmployeeID (Primary Key)
EmpName
StartDate

tblRateOfPay
RateOfPayID (Primary Key)
EmployeeID (Foreign key to tblEmployee)
PayRateDate
PayRate

tblHoursWorked
HoursWorkedID (Primary Key)
EmployeeID (Foreign key to tblEmployee)
WeekEndDate
NoHoursWorked

This structure allows you to reference any employee using their EmployeeID and reference the Payrate based off a particular date. Sample queries would be something like the following:

SELECT tblEmployee.EmployeeID, tblEmployee.EmpName, tblHoursWorked.WeekEndDate, tblHoursWorked.HoursWorked, tblPayRate.PayRateDate, tblPayRate.PayRate
FROM (tblEmployee INNER JOIN tblHoursWorked ON tblEmployee.EmployeeID = tblHoursWorked.EmpoyeeID) INNER JOIN tblPayRate ON tblEmployee.EmployeeID = tblPayRate.EmployeeID
WHERE (((tblPayRate.PayRateDate)<[tblHoursWorked]![WeekEndDate]));
 
SELECT Query1.EmployeeID, Query1.EmpName, Query1.WeekEndDate, Query1.HoursWorked, Last(Query1.PayRate) AS LastOfPayRate, Max(Query1.PayRateDate) AS MaxOfPayRateDate
FROM Query1
GROUP BY Query1.EmployeeID, Query1.EmpName, Query1.WeekEndDate, Query1.HoursWorked;
 

There are nicer ways of doing this if I had time to think about it but I'm just trying to show you the logic right now.

[Bill Sublette]

And that would work beautifully if I weren't doing this in Excel. Unfortunately I'm stuck with using Excel... And that's the biggest problem I have... I explaied to my Site Manager that all of this would be so much easier in Access, but for some reason he's still resistant.

[MMcCarthy]

:) That will teach me to read the question title. It's probably a good idea to mention something like this in the post as well as it helps people like me who are too lazy to read the title.

You can't do this logic with a flat table of records. You could use a different sheet to represent each table.

Since I would never do something like this in Excel I can't think of how to approach it right now and honestly don't have to time to give it proper consideration. The only thing I can think of is a sheet with all employees in column A as row tags and in row 1 put column tag headers for each weekending date for the year. Then fill in the values something like the following:

NeoPa may have some ideas but you really need to get accross to your boss that Excel was never designed to work as a database.

[Rabbit]

If you have the start date and end date, then for any input date, you can find the row(s) where that date is larger than or equal to the start date AND that date is less than or equal to the end date. If true, you could say, print out the name of the employee or their ID. Then you could use the various lookup or match functions in excel to get the row and use that to find the rate.

[NeoPa]

Like Mary I missed the part where it said it was all supposed to be done in Excel. I'm sure you've already explained to your boss that such facilities are designed into Access (an RDBMS) whereas Excel is not remotely designed to handle such niceties. Nevertheless I will say it again in case a third-party expert carries more weight with them.

Bill's right. This really isn't something Excel is designed to handle and, though it is not impossible (Rabbit's references to the VLookup() etc functions might need to be called heavily.), it is a serious headache and not to be undertaken without appreciation of the problems that will result.