If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
Sort by calculated field
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. |
#3
|
|||
|
|||
Sort by calculated field
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. |
#4
|
|||
|
|||
Sort by calculated field
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 |
#5
|
|||
|
|||
Sort by calculated field
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 |
Thread Tools | |
Display Modes | |
|
|