2 relationships between tables?

[rc-alex]

Hello,

I have two tables in question.

tbl_Employee
tbl_SalaryChange (will record each salary change for all employees)

In tblSalaryChange, both the "AppliesTo" and "Approver" field are linked to the EmployeeID in tbl_Employee, as both fields specify an employee.

I have not found way to query and report both employee names. Surely there is a way to do this, because this doesn't seem to be a rare situation.

Am I going about this the wrong way?

Thanks,
Alex

 

[pbaldy]

In your query, add the employee table twice. Access will alias the second instance to something like tbl_Employee_1. Join one of your fields to the original table, the other to the aliased table.

 

[rc-alex]

Thanks,

However, because the two fields point to the same field in the Employee table, I can't add them to the query (how would you know which is which?)

and in order to find only the last salarychange for each employee, I have to make every field in the SalaryChange query "Last", except for "EmployeeName" which is "Group."

When I do this, the Employee value for Approver reverts to showing the EmployeeID instead of the text name.

Thanks,
Alex

 

[The_Doc_Man]

because the two fields point to the same field in the Employee table, I can't add them to the query (how would you know which is which?)

They are references to the same table... but from different fields. You know which is which by the field that got you to that record. In the query, if you first established the relationship correctly, Access would name the tables correctly for you.

You are thinking that two tables are involved because otherwise you would not have asked which was which. But it is not that way. This bit about adding the same table twice on the query design grid is just an artifice so that Access can double-join to the same table.

 

[rc-alex]

I have set the query up as suggested. I have tbl_SalaryChange one time, and tbleEmployees twice. One link from each employee in tbl_SalaryChange to the employeeID in tbl_Employee.

However, to only show the latest record for each employee, I have to set all fields in the query to "last", except for the employee receiving the salarychange. When I set the approver to last, it shows the employee ID instead of employee name. Same for other similar fields in the query.

Thanks,
Alex

 

[The_Doc_Man]

When I set the approver to last, it shows the employee ID instead of employee name. Same for other similar fields in the query.

With Access, you ALWAYS get what you ask for. If you didn't get a name, you didn't ask for a name. The relationship will get you the ID because of the JOIN created by the relationship. You must edit the query to specify the names. If you do so from the query design grid, Access will keep the names straight. The last trick is that if you do this, you need to supply alternate names for the query's returned values.

Normally, in the design-grid field box you would put [EmpName] and be done with it. But in this case, you might need EmployeeName:tEmpl.[EmpName] in one of the boxes and ApproverlName:tEmpl_2.[EmpName] or some similar syntax.

 

[rc-alex]

When I put EmployeeName: [tb_Employeess].[Emplpyeel_Name] in the field line and tb_Employeess in the table line, and group by, when I run the query it asks me to type an employee name, and whatever I type is shown as the value for all fields in that column, regardless of what it should be.

The query returned the names correctly until I tried to only show the last, at which point it switched to showing IDs. That's where I'm confused.

Thanks,
Alex

 

[pbaldy]

Can you post the db, or a representative sample? You might also find this technique applicable:

http://www.baldyweb.com/LastValue.htm

 

[rc-alex]

Ok, so I have qry_LastChange. This returns just the primary key from this table, along with the affected employee. the affected employee is grouped, and the key field is set to Last. This works, only returning the Key of the last change for each employee.

The issue becomes getting the next query to show the names of both the affected employee and the approver. I can have it show just what was in the first query fine. But how can I add the second name? That's the big hangup right now in each of these sets of tables/queries.

Thanks,

 

[pbaldy]

Again, this would be simpler with a sample of the data.

 

[stopher]

Here's an example exploiting the points raised by The Doc Man and pbaldy.

I created a query called qryMaxDates as per PB's link. Then I created a query called qryLatestSalaries which links qryMaxDates (to get the latest dates) with tbl_SalaryChange. It also links tbl_Employee twice (as per previous comments about using an alias) so we can show ApproverName and AppliesToName in our query.

hth

Chris

 

[rc-alex]

I didn't see how to attach a sample DB. I see that now.

Here's an example exploiting the points raised by The Doc Man and pbaldy.

Chris

Chris,

That looks like exactly what I was trying (unsuccessfully) to achieve. I'll study that and see if I can replicate it on my own.

This was a major hangup because I'm going to use the same procedure for change of jobTitle, change of Department, most recent EvalScore, etc.

Thank you very much,
Alex

 

[rc-alex]

Chris,

The issue that remains is you used the Max function to return only the last record for each employee. Web Queries can't use the total line (Max, Last, etc). How can I get around this and show the last record?

Thanks,
Alex