Max Value - pull first of multiple max values

[bailey11]

In our time entry, Per Diem needs to follow the job with the maximum amount of hours, provided total hours for the day exceed 5 hours. My problem is that an employee make work 3 hours on two different jobs and 2 hours on another so the maximum value(s) returned in my query are two records for this employee.

How do I tell the make table query to write the per diem to one or the other of these entries, but not to both?

Thanks, Bailey11

 

[dhookom]

You can't do this without a tie-breaker.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[bailey11]

Okay,

I have two potential tie breakers, but one would override the other.

If the job the time is coded to is a Change Order, I want and hours are at least 5, that would be where I want the per diem coded to.

If the job is not a change order or the change order has less than 5 hours,then I want the per diem coded to the main job, phase of 00.

THe fields are:
Job Number 81044
Phase - (00 is the main job)
COR (Change Order)(Y/N box)

 

[dhookom]

Do you have some table and field names as well as a few sample records with desired results? I hate guessing and making up stuff which might or might not be understood by you.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[bailey11]

Table: tbltimeentry
Fields: workdate, EmployeeID, Jobnumber, COR (Y/N), Phase, Activity Code, Units, Pay ID (0,1: RT,OT),PerDiemAmount

Append Query needs looks at all fields above and say for an employee and if the COR field in the record is Yes and the units in the record are >= 5, write a new record in table "tbltimeentry": workdate, EmployeeID, Jobnumber, COR (Y/N), Phase, Activity Code, Units= "1", Pay ID="201" (PerDiem), PerDiemAmount

If the query looks at the records for an employee and COR=No, then I need the hours to follow the jobnumber, phase, activity combo with the most units, if more than one record has the same number of units, I need it to follow the record with the phase "01", ActivityCode "1000".


Example 1 Time Entry:
Date EmpID JobNo Phs ActCode PayID Units COR
09/25/06 12345 29000 01 1000 201 8 No
09/25/06 12345 29000 01 1900 201 5 Yes
09/25/06 12345 29000 01 2010 201 1 No


In this case the entry would go to record 2 because COR is Yes.


Example 2 Time Entry:
Date EmpID JobNo Phs ActCode PayID Units COR
09/25/06 12345 29000 01 1000 201 5 No
09/25/06 12345 29000 01 1900 201 5 No
09/25/06 12345 29000 01 2010 201 1 No


In this case, the entry would go to Record 1, because non have a COR=Yes and even though two records have 5 units (hours) each, it goes to the main job indicated by Phase "01", ActivityCode "1000"


Example 3 Time Entry:
Date EmpID JobNo Phs ActCode PayID Units COR
09/25/06 12345 29000 01 1000 201 8 No
09/25/06 12345 29000 01 1900 201 5 No
09/25/06 12345 29000 01 2010 201 1 No

In this case, COR=No, and units are not the same for any records, so entry would follow record 1 because it has the most hours.

Confused enough?

Thanks, Bailey11

 

[dhookom]

Do you have a primary key field in your table? If not, can you add one?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[bailey11]

The primary key in the table is the record number. THe error handling is all done through code on a time entry form that calculates RT, OT, Rig Pay, etc. If a record already exists for the employee, job, phase, activity code, it is deleted if a new record is written.

 

[dhookom]

THere are no record numbers in Access. Do you have a field that is an Autonumber? If so can you provide the name of your primary key field?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[bailey11]

Sorry, yes. The autonumber field is called "TimeRecordID".

 

[bailey11]

Is it easier if we just said out of the multipe entries for an employee, just choose the first on written, i.e., the one with the lowest TimeRecordID?

 

[bailey11]

To throw in more confusion, I will have multiple employees.

I have no idea how to do this.

Thanks, Bailey11