I have an employees database consisting of several tables.  I want to write

a query using the employees table and a salary table with 'salary' and

'effective date' fields and a one-to-many relationship with the employees

table.  I would like to select the latest salary with the possibility of

having more than one increase in the same year.





Douglas M. Chatham

chatham@m...



Douglas M. Chatham

chatham@m...

Sounds complex.  You would need to either use a nested query or a

join(more usual), either of which would require some where clauses.

Could you send me table names and field names with a description of the

relation of the fields (if any)?



-----Original Message-----

From: Douglas M. Chatham [mailto:chatham@m...]

Sent: Tuesday, September 04, 2001 3:39 PM

To: Access

Subject: [access] Access queries





I have an employees database consisting of several tables.  I want to

write a query using the employees table and a salary table with 'salary'

and 'effective date' fields and a one-to-many relationship with the

employees table.  I would like to select the latest salary with the

possibility of having more than one increase in the same year.





Douglas M. Chatham

chatham@m...



Douglas M. Chatham

chatham@m...







---

You are currently subscribed to access as: yehuda@i... To

unsubscribe send a blank email to $subst('Email.Unsub')

You may wish to consider creating an index based on the employee Id and

effective date.



The key would be EmployeeID (Ascending), EffectiveDate (Descending) this

would present you with the most recent date for each employeeID.



Walt

Mr. Rosenblum,

Thanks for responding to my question.  There is an employees table

(Employees) with fields of EmployeeNo, Last Name, First Name, Middle

Initial, and several other fields.  There is another table titled Salaries

having an EmployeeNo field, a Salary field and an Effective Date field.  The

two tables are related through the EmployeeNo and the relationship is one to

many.  As I said earlier, I would like to write a query which would return

the latest salary.  The protocol is that salaries are usually adjusted on

Jan. 1 each year, but there can be mid-year adjustments as well.  I would

also like to be able to show the last three salaries for each employee.



Douglas M. Chatham

chatham@m...



-----Original Message-----

From: Yehuda Rosenblum [mailto:Yehuda@I...]

Sent: Tuesday, September 04, 2001 4:34 PM

To: Access

Subject: [access] RE: Access queries





Sounds complex.  You would need to either use a nested query or a

join(more usual), either of which would require some where clauses.

Could you send me table names and field names with a description of the

relation of the fields (if any)?



-----Original Message-----

From: Douglas M. Chatham [mailto:chatham@m...]

Sent: Tuesday, September 04, 2001 3:39 PM

To: Access

Subject: [access] Access queries





I have an employees database consisting of several tables.  I want to

write a query using the employees table and a salary table with 'salary'

and 'effective date' fields and a one-to-many relationship with the

employees table.  I would like to select the latest salary with the

possibility of having more than one increase in the same year.





Douglas M. Chatham

chatham@m...



Douglas M. Chatham

chatham@m...

Mr. Morgan,

Thanks for responding to my question.  There is an employees table

(Employees) with fields of EmployeeNo, Last Name, First Name, Middle

Initial, and several other fields.  There is another table titled Salaries

having an EmployeeNo field, a Salary field and an Effective Date field.  The

two tables are related through the EmployeeNo and the relationship is one to

many.  As I said earlier, I would like to write a query which would return

the latest salary.  The protocol is that salaries are usually adjusted on

Jan. 1 each year, but there can be mid-year adjustments as well.  I would

also like to be able to show the last three salaries for each employee.



Douglas M. Chatham

chatham@m...



-----Original Message-----

From: Walt Morgan [mailto:wmorgan@s...]

Sent: Tuesday, September 04, 2001 4:29 PM

To: Access

Subject: [access] Re: Access queries





You may wish to consider creating an index based on the employee Id and

effective date.



The key would be EmployeeID (Ascending), EffectiveDate (Descending) this

would present you with the most recent date for each employeeID.



Walt

Douglas,



In this case, I would resort to code to create a recordset in Decending date

order and, depending upon how many records you wanted, would set a counter

to that number and iterate through the recordset until I had the records

needed. After all, you must assume that there will be employees with less

than the number of dates your are seeking. The recordset, of course, could

be the results of a query where employeeid = searchedforEmployeeId this

would considerably reduce the amount of traffic over the network.



Undoubtedly there is a more elegant solution out there waiting to be

discovered. Perhaps others will provide it...



Best wishes,



Walt

I believe the last salary problem is the same problem addressed in the

appended message.



Last three is tougher--I'm not sure how I'd do that...



HTH,



-Roy



Here's the appended message:



' I can think of two ways to go with this--one where you use two 

' queries, and one where you use a subquery.  The two-query 

' solution is probably easiest.  Pretend with me that your table is 

' called tblProjectMilestones, and is set up like so:

' 

' ProjectName MilestoneText MilestoneDate

' 

' Let's say that each combination of ProjectName + MilestoneDate is 

' unique in the table.  The first query looks like so:

' 

' qryLastProjectMilestoneDate: 

' SELECT  ProjectName, Max(MilestoneDate) as LastMilestoneDate FROM 

' tblProjectMilestones GROUP BY ProjectName ;

' 

' Then you join this to the original table to pick out the 

' appropriate Milestone text:

' 

' qryLastProjectMilestone: 

' SELECT  t.ProjectName, MilestoneText as LastMilestone, 

' LastMilestoneDate FROM    tblProjectMilestones as t INNER JOIN 

' qryLastProjectMilestoneDate as q ON t.ProjectName   = 

' q.ProjectName AND t.MilestoneDate = q.LastMilestoneDate ;

' 

' HTH,

' 

' -Roy

' 

' ' -----Original Message----- From: karl.hammett@b... 

' ' [mailto:karl.hammett@b...] Sent: Tuesday, September 04, 2001 

' ' 3:18 AM To: Access Subject: [access] to display the latest 

' ' records per project

' ' 

' ' 

' ' hi 

' ' 

' ' i have a database with records that are constantly updated but 

' ' the old records need to be kept for history.

' ' 

' ' example

' ' 

' ' programmes have many projects and each project has a milestone. 

' ' (e.g inception, feasibility etc) and the planned date to complete 

' ' the task.

' ' 

' ' i can generate a query to show all the projects and milestones 

' ' but i need a query to only show the latest milestone for each of 

' ' the different projects.

' ' 

' ' i have tried to write a query to get the latest ID for each 

' ' project but i come up with nothing. i have also tried the same on 

' ' the latest date. i have to show the details on the web and would 

' ' be gratefull for any help thanks 

' ' 

' ' karl

' ' 

' ' 



-----Original Message-----

From: Douglas M. Chatham [mailto:chatham@m...]

Sent: Wednesday, September 05, 2001 7:17 PM

To: Access

Subject: [access] RE: Access queries





Mr. Rosenblum,

Thanks for responding to my question.  There is an employees table

(Employees) with fields of EmployeeNo, Last Name, First Name, Middle

Initial, and several other fields.  There is another table titled Salaries

having an EmployeeNo field, a Salary field and an Effective Date field.  The

two tables are related through the EmployeeNo and the relationship is one to

many.  As I said earlier, I would like to write a query which would return

the latest salary.  The protocol is that salaries are usually adjusted on

Jan. 1 each year, but there can be mid-year adjustments as well.  I would

also like to be able to show the last three salaries for each employee.



Douglas M. Chatham

chatham@m...



-----Original Message-----

From: Yehuda Rosenblum [mailto:Yehuda@I...]

Sent: Tuesday, September 04, 2001 4:34 PM

To: Access

Subject: [access] RE: Access queries





Sounds complex.  You would need to either use a nested query or a

join(more usual), either of which would require some where clauses.

Could you send me table names and field names with a description of the

relation of the fields (if any)?



-----Original Message-----

From: Douglas M. Chatham [mailto:chatham@m...]

Sent: Tuesday, September 04, 2001 3:39 PM

To: Access

Subject: [access] Access queries





I have an employees database consisting of several tables.  I want to

write a query using the employees table and a salary table with 'salary'

and 'effective date' fields and a one-to-many relationship with the

employees table.  I would like to select the latest salary with the

possibility of having more than one increase in the same year.





Douglas M. Chatham

chatham@m...



Douglas M. Chatham

chatham@m...