Two Many-To-One Relationships

[Guest]

I have a table that stores an EmployeeID twice, once for RequestedBy and once
for AuthorisedBy (these are usually different, but not always). In a report,
how would I display the FirstName and LastName of the EmployeeID stored in
RequestedBy, and the FirstName and LastName of the EmployeeID stored in
AuthorisedBy? It's easy for one occurance of EmployeeID, but not for two
occurances.

Thanks

Dave

 

[Joseph Meehan]

I have a table that stores an EmployeeID twice, once for RequestedBy
and once for AuthorisedBy (these are usually different, but not
always). In a report, how would I display the FirstName and LastName
of the EmployeeID stored in RequestedBy, and the FirstName and
LastName of the EmployeeID stored in AuthorisedBy? It's easy for one
occurance of EmployeeID, but not for two occurances.

Thanks

Dave

Easy way is to create a query that creates a RequestedByLong: filed and
a ApprovebByLong:

Something like:

RequestedByLong: [NAMESTABLE.FirstName]&" "&[NAMESTABLE.LastName]

Where NAMESTABLE is the table with the names.

I assume that your have a filed "RequestedBy" holding a Employee ID and
a filed AuthorizedBy holding an Employee ID that may or may not be the same.
further that table is listing the Employee ID number as stored in an related
table.

 

[Larry Daugherty]

You haven't explained how you show things in the report when the ID
numbers are different but simple logic says show them the same when
the different roles are performed by the same person.

HTH

 

[Guest]

It sounds like you are using lookup fields in your table. You need to search
post about the perils of using them.

You should have an employee table with EmployeeID as primary key. Them you
can set a one-to-many relation to both RequestedBy and AuthorisedBy.

Then when you need to display the names you would join the employee table in
your query so as to outut the name.

 

[Guest]

I had already set it up the way you described. I'm having problems getting
Access to show the FirstName and LastName. It seems to get confused about the
one-to-many relationships in the table. How would you design the query for
such a situation?