Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2004
    Posts
    3

    Unanswered: Multiple Conditional counts in one query

    Here's my dilemma, I am trying to do multiple conditional counts and have the results show up in one query. I can do any one of the conditional counts easily, but I'm not sure how to do more than one without restricting the dataset on account of the WHERE statement. Here is an example of the table

    FYEAR SCODE SCRSTAT INTDATE SELSTAT
    04 49 U 12DEC03 SUB
    04 49 UU 12DEC03 NONE
    04 38 UD 13NOV03 SURF
    03 49 D
    02 49 UU 15OCT01 SURF
    01 49 DROP
    01 49 U DROP
    01 36 U 12OCT00 SUB

    Essentially, from this data I want to return a table that looks like this for one particular SCODE (in this example 49 from the table above)

    FYEAR UPSCREENED DROPS INTERVIEWED SELECTED
    04 2 0 2 1
    03 0 0 0 0
    02 1 0 1 1
    01 1 2 0 0

    Here is the breakdown of how each column is calculated. Note, each column is for a given SCODE, in the example above 49
    FYEAR is simply the FYEAR from the table
    UPSCREENED is the count where SCRSTAT = U, UU, or UD
    DROPS is the sum of the DROP entries in SCRSTAT and SELSTAT
    INTERVIEWED is the COUNT where INTDATE != NULL
    SELECTED is the COUNT where SELSTAT = SUB or SURF

    As I mentioned earlier, I can write the SQL for any of the given columns relatively easily, but am not sure how to combine them into one result. I need one result as this will be going into a report format. Thank you for your help.

  2. #2
    Join Date
    Feb 2004
    Posts
    142
    either use a temp table to place the individual rollups into or you can create a query that looks up the first value and use domain aggregates for the other values.

    To use domain aggregates in the QBE window start a new field and Alias it. Then do the sum or count you need e.g.
    dcount("[FieldName]","tablename","[Criteria Field] = 'something').

    If it is a large dataset then use the temp table, the domain aggregates are a bit slow.

    If you need to supply user entered criteria you could also do a union query, but if you do then you will need to create dummy fields for proper field placement in the union query (union queries require the same number of fields and names in the proper order).
    KC

  3. #3
    Join Date
    Mar 2004
    Posts
    3
    I do have user entered data. Does this require that I start a UNION query? Or will the temp table method still work? The temp table method would be my preferred method, as I'm semi-familiar with that, and not so much familiar at all with union queries and the other method.

  4. #4
    Join Date
    Mar 2004
    Posts
    3
    I'm currently trying to implement this by creating temporary tables but am apparently clueless as to the syntax. This is how I thought it was done:

    PARAMETERS [Begin Year] Text ( 255 ), [End Year] Text ( 255 ), [School Code] Text ( 255 );

    CREATE TEMPORARY TABLE UPSCREEN(
    SELECT NEWDB.FYEAR AS FY, COUNT(NEWDB.SCRSTAT) AS UPSCREENED
    FROM NEWDB
    WHERE (NEWDB.FYEAR between [Begin Year] and [End Year]) AND (NEWDB.SCRSTAT = "U" OR NEWDB.SCRSTAT = "UU" OR NEWDB.SCRSTAT = "DU") AND NEWDB.SCODE =[School Code]
    GROUP BY NEWDB.FYEAR);


    However, this query gives the error "INVALID SQL STATEMENT: EXPECT SELECT, UPDATE, INSERT, OR DELETE" and then highlights CREATE in my SQL VIEW window. What is the correct syntax?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •