Results 1 to 9 of 9

Thread: query issue

  1. #1
    Join Date
    Apr 2004
    Location
    ny, ny
    Posts
    224

    Unanswered: query issue

    I generate a query which contains the date, workorder, downtime, mttr, deptgroup

    I need to display the following variables in a report
    Could someone help me with the syntax , also where would i initialize them

    mach_dt = sum(downtime) where deptgroup = 1

    pna_dt = sum(downtime) where deptgroup = 2

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    have you tried
    blah = DSUM("downtime", "whateverYourqueryIsCalled", "deptgroup = 1")

    izy
    currently using SS 2008R2

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    select 'mach_dt'     as total_type
         , sum(downtime) as total_amount
      from yourquery
     where deptgroup = 1
    union all
    select 'mach_wo'    
         , count(workorder) 
      from yourquery
     where deptgroup = 1 
       and status in (1,2))
    union all
    select 'pna_dt'
         , sum(downtime) 
      from yourquery
     where deptgroup = 2
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Apr 2004
    Location
    ny, ny
    Posts
    224
    where do i put it .. wud it be in report_open ()

    also it seems to have many syntax errors
    i m a newbie to sql

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    where do you put it? in the query window

    click on the Queries tab

    alt-N (or click on the New button)

    enter (or click OK) for Design view

    esc (or alt-C or click on the Close button)

    alt-V, Q (or click on the SQL dropdown in the toolbar)

    now you should have a query window

    paste it there, then alt-Q, R (or click on the exclamation mark in the toolbar) to run it
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Apr 2004
    Location
    ny, ny
    Posts
    224
    I get an error
    select 'mach_dt' as total_type , sum(downtime) as total_amount from Uptimequery where deptgroup = 1
    union all
    select 'mach_wo', count(workorderno) from Uptimequery where deptgroup = 1 and workstatus in (1,2)
    union all
    select 'pna_dt' , sum(downtime) from Uptimequery where deptgroup = 2

    This expression is typed incorrectly, or it is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables. (Error 3071)

  7. #7
    Join Date
    Apr 2004
    Location
    ny, ny
    Posts
    224
    i solved all the errors
    how do i display it in a report though

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    the report wizard should take you through it

    the source of the data for the report is the query, there's a spot in the wizard where you are prompted for that, i think
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Apr 2004
    Location
    ny, ny
    Posts
    224
    After i run the query i get the following

    Expr1000 Expr1001
    mach_dt #VALUE#
    mach_wo #VALUE#
    mach_mttr #VALUE#

    I cant use this in a report
    It says the query is too complex

Posting Permissions

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