Sort by calculated field

I have a report that lists the Incidents for each member of our company. The
SQL for the report is

SELECT tblIncidents.IncidentNumber, tblIncidents.IncidentDate, tblMembers.
MemberNumber, tblMembers.MemberLName, tblMembers.MemberFName, tblMembers.
MemberID
FROM tblMembers INNER JOIN (tblIncidents INNER JOIN tjxMemberIncidents ON
tblIncidents.IncidentID = tjxMemberIncidents.IncidentID) ON tblMembers.
MemberID = tjxMemberIncidents.MemberID
WHERE (((tblIncidents.IncidentDate) Between [Enter Start Date] And [Enter End
Date]));

I have another field on the report that was created using:

=Count(tblIncidents!IncidentNumber)

I want to sort on that county but I understand I cannot. So, following some
posts on this board, I created a query that is:
SELECT tblIncidents.IncidentId, Count(*) AS TotalInc
FROM tblIncidents
GROUP BY tblIncidents.incidentid;

and tried to incorpoate that into my report as part of the record source.
But I just cannot get it to work despite all my efforts. I am new to all of
this and very confused. Your hlep is appreciated.

Hi,

How are your grouping and sorting your report? In what part of the
report is the =Count(tblIncidents!IncidentNumber) text box? It looks like
the second query counts on a per-incident basis, not a per-member basis. Is
that what you want? What is the difference between IncidentNumber and
IncidentID? Do they both uniquely identify the same incident? If so, it may
make sense to get rid on one of them and only have one value. Do you want
the counts to be limited to the date range or to be over all time?

Clifford Bass

"EMess" wrote:

I have a report that lists the Incidents for each member of our company. The
SQL for the report is

SELECT tblIncidents.IncidentNumber, tblIncidents.IncidentDate, tblMembers.
MemberNumber, tblMembers.MemberLName, tblMembers.MemberFName, tblMembers.
MemberID
FROM tblMembers INNER JOIN (tblIncidents INNER JOIN tjxMemberIncidents ON
tblIncidents.IncidentID = tjxMemberIncidents.IncidentID) ON tblMembers.
MemberID = tjxMemberIncidents.MemberID
WHERE (((tblIncidents.IncidentDate) Between [Enter Start Date] And [Enter End
Date]));

I have another field on the report that was created using:

=Count(tblIncidents!IncidentNumber)

I want to sort on that county but I understand I cannot. So, following some
posts on this board, I created a query that is:
SELECT tblIncidents.IncidentId, Count(*) AS TotalInc
FROM tblIncidents
GROUP BY tblIncidents.incidentid;

and tried to incorpoate that into my report as part of the record source.
But I just cannot get it to work despite all my efforts. I am new to all of
this and very confused. Your hlep is appreciated.

Don't you want to sort by the count grouped by member? Your totals query
count members by incident. I would think you want:
SELECT MemberID, Count(*) As NumOf
FROM tjxMemberIncidents
GROUP BY MemberID;

Then add this totals query to your report's record source and join the
MemberID fields. You should then be able to sort/group by NumOf.

--
Duane Hookom
Microsoft Access MVP


"EMess" wrote:

I have a report that lists the Incidents for each member of our company. The
SQL for the report is

SELECT tblIncidents.IncidentNumber, tblIncidents.IncidentDate, tblMembers.
MemberNumber, tblMembers.MemberLName, tblMembers.MemberFName, tblMembers.
MemberID
FROM tblMembers INNER JOIN (tblIncidents INNER JOIN tjxMemberIncidents ON
tblIncidents.IncidentID = tjxMemberIncidents.IncidentID) ON tblMembers.
MemberID = tjxMemberIncidents.MemberID
WHERE (((tblIncidents.IncidentDate) Between [Enter Start Date] And [Enter End
Date]));

I have another field on the report that was created using:

=Count(tblIncidents!IncidentNumber)

I want to sort on that county but I understand I cannot. So, following some
posts on this board, I created a query that is:
SELECT tblIncidents.IncidentId, Count(*) AS TotalInc
FROM tblIncidents
GROUP BY tblIncidents.incidentid;

and tried to incorpoate that into my report as part of the record source.
But I just cannot get it to work despite all my efforts. I am new to all of
this and very confused. Your hlep is appreciated.

What I am trying to do is count the number of incidents each member has.
Then, I am reporting that number for each member. I want those members with
the most incididents to come out first. The Group by Incident ID was just
something I was trying.

Duane Hookom wrote:
Don't you want to sort by the count grouped by member? Your totals query
count members by incident. I would think you want:
SELECT MemberID, Count(*) As NumOf
FROM tjxMemberIncidents
GROUP BY MemberID;

Then add this totals query to your report's record source and join the
MemberID fields. You should then be able to sort/group by NumOf.

I have a report that lists the Incidents for each member of our company. The
SQL for the report is
[quoted text clipped - 21 lines]
But I just cannot get it to work despite all my efforts. I am new to all of
this and very confused. Your hlep is appreciated.

--
Message posted via http://www.accessmonster.com

Did you try the solution I suggested?
If you want a count per member, then you must group by member.

--
Duane Hookom
Microsoft Access MVP


"EMess via AccessMonster.com" wrote:

What I am trying to do is count the number of incidents each member has.
Then, I am reporting that number for each member. I want those members with
the most incididents to come out first. The Group by Incident ID was just
something I was trying.

Duane Hookom wrote:
Don't you want to sort by the count grouped by member? Your totals query
count members by incident. I would think you want:
SELECT MemberID, Count(*) As NumOf
FROM tjxMemberIncidents
GROUP BY MemberID;

Then add this totals query to your report's record source and join the
MemberID fields. You should then be able to sort/group by NumOf.

I have a report that lists the Incidents for each member of our company. The
SQL for the report is
[quoted text clipped - 21 lines]
But I just cannot get it to work despite all my efforts. I am new to all of
this and very confused. Your hlep is appreciated.

--
Message posted via http://www.accessmonster.com