Group by Similar Values - Not Exact Values

[johnfrani]

I am using MS Access 2000.

I am trying to create an Access report that lists and subtotals all the people that have entered our department and lists and subtotals all the people that have left our department for a given date range.


This is my query...it works.


SELECT tblMember.Lname & ", " & tblMember.Fname AS Name,
tblHistory.Date, LK_Event.Event, tblHistory.Comments

FROM LK_Event INNER JOIN (tblMember INNER JOIN tblHistory ON tblMember.MemberID = tblHistory.MemberID) ON LK_Event.EventID = tblHistory.EventID

WHERE ((LK_Event.Event) In ("Transferred In","Transferred Out","Retired - City","Retired - Airport","Terminated","'Deceased"))

ORDER BY LK_Event.Event;



In the WHERE clause, I have 6 values: "Transferred In" describes someone coming into our department. The other 5 values describe the ways a person can leave our department.

My Problem is that I want to create an MS Access report that would subtotal the number of people that joined our department and subtotal the number of people that left our department and I don't know how to group the 1 "in value" and the 5 "out values" so I can subtotal.


I would like to do this without adding any fields to the database tables.

Is there a better approach to a solution?

Thanks
John

 

[lespaul]

Something like this maybe:

SELECT tblMember.Lname & ", " & tblMember.Fname AS Name,
tblHistory.Date, iif(LK_Event.Event = "Transferred In", "Transferred In", "Transferred Out") As WhichOne, tblHistory.Comments

FROM LK_Event INNER JOIN (tblMember INNER JOIN tblHistory ON tblMember.MemberID = tblHistory.MemberID) ON LK_Event.EventID = tblHistory.EventID

WHERE ((LK_Event.Event) In ("Transferred In","Transferred Out","Retired - City","Retired - Airport","Terminated","'Deceased"))

ORDER BY LK_Event.Event;

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[Golom]

SELECT SUM (IIF(LK_Event.Event ="Transferred In",1,0)) As Incoming,
       SUM (IIF(LK_Event.Event<>"Transferred In",1,0)) As Leaving

FROM LK_Event INNER JOIN (tblMember INNER JOIN tblHistory ON tblMember.MemberID = tblHistory.MemberID) ON LK_Event.EventID = tblHistory.EventID

WHERE ((LK_Event.Event) In ("Transferred In","Transferred Out","Retired - City","Retired - Airport","Terminated","'Deceased"))

 

[johnfrani]

Thank you, the calculations worked perfectly.
John