Simple Query I am sure - but I just can't see how!

[DubaiDave]

Access Query

I have a table of Applications (ApplicationID) and each application has to be reviewed and approved.

I have a table of people in my organization (PeopleID) and I have a table of roles: RoleID, ApplicationID, PeopleID.

For one Application, Role table has two rows:

Role ID............... ApplicationID................ PersonID'.................Role
AutoNo ..............22 ..................................33 ............................Reviewer
AutoNo+1 ..........22 ..................................44 ............................Approver

Is it possible to build a query that results in a single record set? Eg

ApplicationID'...............Reviewer ...............Approver
22 ................................33 .........................44

I have been trying and researching for most of yesterday and got nowhere.

Cheers

David

 

[Guus2005]

use TOP 1:

select TOP 1 ....

Enjoy!

 

[DCrake]

If each application only has only one reviewer and approver then why not have two fields in the table for Approver and reveiwer?

 

[namliam]

You can do it three more ways...
1) Crosstab query
2) Make seperate queries to only get the "Reviewer"s and only the "Approver"s (and any more for the roles you want/have.
Then Join this to your main application table.
3) Use a group by query and an IIF to shift the Person IDs into the appropriate column.

I think I would go for the Crosstab personaly....

 

[DubaiDave]

If each application only has only one reviewer and approver then why not have two fields in the table for Approver and reveiwer?

This is was my first approach. So I had an Application table:

ApplicationID........ApproverID..........ReviewerID

And a Person table:

PersonID......Name

And created two relationships: ApproverID-PersonID and ReviwerID-PersonID

But not matter what I do, I cannot create a query that shows the ApplicationID and the Reviewer's Name and Approver's Name. It always just shows all the Reviewers's name.

Is it allowed to create the relationships as I have done above?

Dave

 

[namliam]

You would have to add the person table twice to your query... one relationship Approver-Person the other Reviewer-Person.
Also you will have to add the PersonName twice again to your query to show the two different outputs.

 

[DubaiDave]

You would have to add the person table twice to your query... one relationship Approver-Person the other Reviewer-Person.
Also you will have to add the PersonName twice again to your query to show the two different outputs.

Namliam, thanks, I really did not think/know that I had to add the table twice. It is now is working. Thanks