Select statements in the select statement

[simplyJ]

I have a table that stores all information about service tickets. The table has these fields:
- number
- openemployee
- opendate
- closeemployee
- closedate

I am trying to do a search to get the total number of cases an employee opened and closed in a particular time frame.

How do I create multiple queries in the select statment? Or, is there another way to do this.

 

[jrozee]

You could do it as an Append query. First, create a query that counts each employee's open tickets.

Then, under the Query menu choose Append query. That will open a second query within your first one reusing your datamodel. You will see a set of tabs below your outliner area to allow you to switch between queries and a new bar for the query order and union type.

Now, add the same kinds of fields to your request line that you had in your first query except use the CloseEmployee for the count. In fact, you must match data type and number of fields on the request line in the exact order they exist in the first query because the second query's results will be appended to the bottom of the first query and they must coexist in the same results section.

Limit your second query appropriately for the Closed date range.

That should do it.

James