1. With ExecuteSQL() you can return the title and the count of the @ of titles.
2. With subsummary Total of, you can show a list view (summary part only).
But you asked about SQL...
You did not specify all the fields, so I'll guess:
SELECT title, sum(title)
GROUP BY title
ORDER BY title
It's not quite clear where the actual title field exists and how it relates to the Employees. Are you just putting the ID of the title into the field and somehow showing the title by that relationship?
If so, please post screenshot(s) of the relationship and the layout showing the fields needed.
1 of 1 people found this helpful
I think that you want the number of unique titles in the employee table as opposed to the number of titles in your titles table. The only reason that the number of titles in the titles table wouldn't be the same would be if you have titles in the titles table that don't match to any employees or vice versa.
Do I have that correct?
if so, the desired count can be produced pretty easily. If not, please clarify.
phil - exactly!
I mean ultimately I need to be able to display the employees with the title "enter title here" but I also need to use that data in a chart later. So yes...
Difficult question to get across because title is dynamic, it can be whatever the user enters for titles and which ever one they choose to sort for this operation.
There are many ways to get such a count. A simple way, assuming a relationship linking titles to employees, is to do this from the context of titles instead of employees.
The simplest, is to perform a Find on a layout based on Titles with the following criteria:
enter an asterisk into the match field from employees and perform your Find. The resulting found set would be the number of unique titles assigned to employees.
ExecuteSQL could be used to get the same count.
Perfect, thx Phil.
Beverly thank you also for chiming in, I have been following your posts also, good stuff. =)