Grouping or Subreport?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

John - 21 Mar 2006 20:45 GMT

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

Reply to this Message

Jana - 21 Mar 2006 22:50 GMT

John:

I would add two calculated fields to your query, something like this:
CountAsIn: IIF(LK_Event.Event like "*In",1,0)
CountAsOut: IIF(LK_Event.Event like "*In",0,1)

Then you can total these fields in your report to get the desired
numbers. WARNING: This will only work if your event descriptions
always end in the word 'In' for an incoming event. Although you said
that you didn't want to add any fields to the database, a much
preferred method would be to add a field that allows the user to
categorize an event as an incoming event when they define a new event.

HTH,
Jana

Reply to this Message