finding latest date worked

  • Thread starter Thread starter tracy
  • Start date Start date
T

tracy

I have a labor table that holds the employees name, date
worked and job number, among other things. Very often we
have different people posting work hours to the same job
on the same day. I need to be able to extract the latest
date a job was worked on. I don't care who it was that
did the work, just which date was the last.

How do I write that?
Thank you in advance.
Tracy
 
Dear Tracy:

To start with, if you aren't already aware of it, you need to become
conversant in "Totals Queries" (as Access calls it in the Query Design
Grid) or, perhaps more properly, "Aggregate Queries".

You could basically query the Job column and the DateWorked column.
Click the "sigma" (a greek letter that looks like an M on its side)
and the grid will change. Ask for the Job column to be GROUP BY and
the DateWorked column to be MAX. Now you can see the latest date for
each job.

Does this get it for you?

I have a labor table that holds the employees name, date
worked and job number, among other things. Very often we
have different people posting work hours to the same job
on the same day. I need to be able to extract the latest
date a job was worked on. I don't care who it was that
did the work, just which date was the last.

How do I write that?
Thank you in advance.
Tracy
Click to expand...

Tom Ellison
Ellison Enterprises - Your One Stop IT Experts
 
Try an SQL String something like:

SELECT JobNumber, Max(DateWorked)
FROM YourTable
GROUP BY JobNumber

HTH
Van T. Dinh
MVP (Access)
 
My query is a little more complex than that. The report I
am trying to build will have all jobs we are invoicing, so
I am not bringing back just one job number and its max
date. We are using a program that uses Access as the
database. Here is the query as it is currently written.
If I add what you suggested I get 'Join expression not
supported' I am fairly new at this and don't really know
what that means.

SELECT Customer.Name, User_Values.Amount1,
User_Values.Amount2, (Job.Act_Total_Hrs*60) AS TotalHrs,
(Job.Act_Material*1.25) AS TotalMtl, (TotalHrs+TotalMtl)
AS JobTtl, (User_Values.Amount1-JobTtl) AS Expr1,
Job.Status, Job.Description, Job.Time_And_Materials,
Job.Part_Number, Job.Act_Material, Job.Act_Total_Hrs,
Delivery.Promised_Date, Job.Sales_Rep, Job.Job,
Job.Customer_PO, Employee.First_Name, Employee.Last_Name,
User_Values.Text5, Invoice_Detail.Document,
Job_Operation_Time.Work_Date, max (Work_Date)
FROM (((Delivery INNER JOIN (User_Values RIGHT JOIN
(Customer INNER JOIN Job ON Customer.Customer =
Job.Customer) ON User_Values.User_Values =
Job.User_Values) ON Delivery.Job = Job.Job) LEFT JOIN
Employee ON Job.Sales_Rep = Employee.Employee) LEFT JOIN
Invoice_Detail ON Job.Job = Invoice_Detail.Job) INNER JOIN
Job_Operation_Time ON Employee.Employee =
Job_Operation_Time.Employee
WHERE (((Job.Status) In ("active","complete")) AND
((Delivery.Promised_Date)>=[Enter Promise Date:]))
ORDER BYJOB.JOB;
 
You cannot nest an outer join (Left or Right Join) inside an inner join.

Check Access Help on the notes near the end of the topic "Inner Join".
 
Back
Top