Find a max date in one table less than a date in another

[Eric Christoph]

I have two tables:

Time Card: Employee ID, Date, Hours, Job ID
Salary Changes: Employee ID, From Date, Pay, Pay Hours

I need a query that returns the record in Salary Changes with the highest [From Date] that is less than the [Date] in the time card field, for every record in Time Card. Many variations of using the Max command in the query totals section, followed by criteria of <=[Date] do not appear to work.

Any help would be appreciated, and will probably save my computer from destruction. Thanks!

 

[Travis]

Try this:

Select Top 1 Employee ID, From Date, Pay, Pay Hours From [Salary Changes] Where [From Date]<=#" & tcDate & "# and [Employee ID] =" & lCurrentEmpID & " Order by [From Date] Desc;"

This query will return the most recent record in the Salary table that is <= the Record in the Time Card Table.

 

[Eric Christoph]

That works for returning the most recent record, yes. What I need is to return ALL the fields in both tables (well, except for Employee, because those are related), but only for the most recent salary change.

Example:

Time Card Table
Employee Date Hours Job
1 2/1/00 8 A
1 2/2/00 4 B
1 2/2/00 4 A
2 2/1/00 4 A
2 2/1/00 4 B
2 2/2/00 8 B

Salary Changes Table
Employee Date Pay Pay Hours
1 1/1/00 50 1
1 2/2/00 3000 80
2 1/1/00 1500 80
2 2/2/00 2000 80

This shows that employees 1 and 2 are working on jobs A and B for the first two days in February. I need to figure out how much the job cost (how much they were paid for those hours on that job). Now both employees had a salary change take effect on Feb 2. Emp 1 went from making $50/hour to a salary of $3,000 for every 2 week payroll period of 80 hours. Emp 2 got a raise, from $1500 every 2 weeks to $2000.

I need the Query to return the following:
Emp Date Hours Job Pay [Pay Hours]
1 2/1/00 8 A 50 1
1 2/2/00 4 A 3000 80
1 2/2/00 4 B 3000 80
2 2/1/00 4 A 1500 80
2 2/1/00 4 B 1500 80
2 2/2/00 8 B 2000 80

Hope you can help!

 

[Travis]

Eric,

At looking at what you want to do I'm going to be frank. I don't see a way to do this with a query alone. This is a calculation that is going to require that you build a temporay table (easiest method) with the fields that you require. First add time card stuff to the table and then use an update query to retreive the most recent values for each as far as pay goes.

 

[Eric Christoph]

Thanks for the advice, I'll try it (now where is that help manual?

.

I was trying to use a Where statement with the DMax function in the criteria, but couldn't get it to work. In theory this seems pretty simple, but everytime I work with access things get complicated really fast! I'll update here on my progress; thanks for your help!

 

[Eric Christoph]

I have tried going through the help, but I can't see how that suggestion gets me what I want.

It just doesn't make sense to me. There are no calculated fields, just finding a record where the date field in one table is greater than or equal to the date field in another table. Is it really that hard?

 

[DaveW]

I had success using the max expression with [TimeCard]![Date] > [Salary]![Date]-1.
I'll email you a simple database which you can adapt to suit.

 

[Happy YN]

just noticed that some time back you had a problem querying a table where the date is one below another date. Did you have any success? someone offered to send you a database with a solution, did he and did it work ? Would appreciate any info on this
Happy YN
Thanks
PS you could email me direct - see profile