Results 1 to 12 of 12
  1. #1
    Star Lounger
    Join Date
    Feb 2002
    Posts
    56
    Thanks
    0
    Thanked 0 Times in 0 Posts

    !!Panic!! Query In List Box (A2000)

    I have a list which has an expression,
    the expression works out the efficiency of operatives.

    Time Given Divided by Time Taken for this month.

    The problem is I can't get into the expression to see whats happening.
    The list is picking up May 2001 data as well as 2002.

    Heres the SQL View:

    SELECT DISTINCTROW OPERATIVEEFF.NAME, OPERATIVEEFF.Expr1
    FROM OPERATIVEEFF
    GROUP BY OPERATIVEEFF.NAME, OPERATIVEEFF.Expr1
    ORDER BY OPERATIVEEFF.Expr1 DESC;


    What do I do ????

    Dave

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: !!Panic!! Query In List Box (A2000)

    Perhaps OPERATIVEEFF is a query with a calculated field that hasn't been named explicitly. In that case, Access assigns the name Expr1 automatically.
    If this is correct, you should look at the design of OPERATIVEEFF to see what the expression behind Expr1 is.

  3. #3
    Star Lounger
    Join Date
    Feb 2002
    Posts
    56
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: !!Panic!! Query In List Box (A2000)

    I think the problem lies in this statement.

    SELECT Count([LABOUR BOOKING].DATE) AS CountOfDATE, Count([LABOUR BOOKING].[EST No]) AS [CountOfEST No], [LABOUR BOOKING].NAME, Count([LABOUR BOOKING].OPERATION) AS CountOfOPERATION, Sum([LABOUR BOOKING].TG) AS SumOfTG, Sum([LABOUR BOOKING].TT) AS SumOfTT
    FROM [LABOUR BOOKING]
    GROUP BY [LABOUR BOOKING].NAME, Month([Date])
    HAVING (((Month([Date]))=Month(Now())));

    Is there any way to eliminate last year's data

    Dave

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: !!Panic!! Query In List Box (A2000)

    Change the last part to

    <pre>HAVING (Year([Date])=Year(Now())) And (Month([Date])=Month(Now()))</pre>


    (Access will probably add lots of parentheses to this if you switch from SQL view to design view and back)

  5. #5
    Star Lounger
    Join Date
    Feb 2002
    Posts
    56
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: !!Panic!! Query In List Box (A2000)

    I'm getting this

    You did not enter an aggregate function in the TRANSFORM statement.

    ?????
    Dave

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: !!Panic!! Query In List Box (A2000)

    Hello Dave,

    TRANSFORM is used in crosstab queries. The queries you've included in your posts on this thread are not crosstab queries. Can you provide a bit more information on what you're doing?

    Regards,
    Hans

  7. #7
    Star Lounger
    Join Date
    Feb 2002
    Posts
    56
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: !!Panic!! Query In List Box (A2000)

    Can I mail a demo to you, it's 1 meg

    Dave

  8. #8
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: !!Panic!! Query In List Box (A2000)

    I would like to it (your db) too.
    Pat

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: !!Panic!! Query In List Box (A2000)

    Hello Dave,

    I have looked at your database. I had to have it converted from A2K to A97, because that's what I use. Both the queries and the form opened without errors. I don't know if something got lost in the conversion, or perhaps you accidentally removed the problem when preparing the database for emailing it to me. If so, have somebody with A2K look at it, or send a more complete version (preferably in A97 format).

    I *do* have some remarks, which in the light of the above may be entirely off the mark.

    The query OPERATIVEEFF seems superfluous. It totals data that have already been totalled in LABOUREFFSINGLEOPERATIVE. The calculated field Expr1 (Effectiveness) can be put in LABOUREFFSINGLEOPERATIVE. The SQL for the latter query then becomes:

    SELECT [LABOUR BOOKING].NAME, Count([LABOUR BOOKING].DATE) AS CountOfDATE, Count([LABOUR BOOKING].[EST No]) AS [CountOfEST No], Count([LABOUR BOOKING].OPERATION) AS CountOfOPERATION, Sum([LABOUR BOOKING].TG) AS SumOfTG, Sum([LABOUR BOOKING].TT) AS SumOfTT, Format(((Sum([TG])/Sum([TT]))*100),"Standard") AS Effectiveness
    FROM [LABOUR BOOKING]
    WHERE (((Month([Date]))=Month(Now())))
    GROUP BY [LABOUR BOOKING].NAME
    ORDER BY Format(((Sum([TG])/Sum([TT]))*100),"Standard") DESC;

    Both the OPERATIVEEFF form and the list box List6 in this form can then use this query.

    I have attached the modified database (in A97 format, zipped) so you and others can have a look at it. To cut it down in size, I removed the big WORK SOURCE table that isn't referred to in this version.
    Attached Files Attached Files

  10. #10
    Star Lounger
    Join Date
    Feb 2002
    Posts
    56
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: !!Panic!! Query In List Box (A2000)

    Thanks Hans for your help.
    Just one thing, it's still picking up May of last year,

    I need the results from May of this year only.
    Dave

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: !!Panic!! Query In List Box (A2000)

    The WHERE condition in the query checks for month only:

    WHERE (((Month([Date]))=Month(Now())))

    Change that to

    WHERE (Year([Date])=Year(Now())) And (Month([Date])=Month(Now()))

  12. #12
    Star Lounger
    Join Date
    Feb 2002
    Posts
    56
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: !!Panic!! Query In List Box (A2000)

    Perfect Hans
    Thanks for all your help

    Dave

Posting Permissions

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