Results 1 to 3 of 3
  1. #1
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Query (Year) (A2k)

    I've had this answered here before but ith the search facility not running yet, I can't find my post.

    The following query is picking up entries for last year and last years month of May.

    How do I alter it to select only this month and this years data.

    SELECT LABOUREFFSINGLEOPERATIVE.NAME, Sum(LABOUREFFSINGLEOPERATIVE.SumOfTG) AS SumOfSumOfTG, Sum(LABOUREFFSINGLEOPERATIVE.SumOfTT) AS SumOfSumOfTT, [SUMOfTG]-[SUMOFTT] AS Expr1, [sumOfTG]/[sumoftt]*100 AS Expr2
    FROM LABOUREFFSINGLEOPERATIVE
    GROUP BY LABOUREFFSINGLEOPERATIVE.NAME, [SUMOfTG]-[SUMOFTT], [sumOfTG]/[sumoftt]*100;

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

    Re: Query (Year) (A2k)

    The SQL you posted does not contain a date field and no WHERE clause. It suspect that LABOUREFFSINGLEOPERATIVE is a query that selects records from certain dates. So you need to look at the design (or the SQL) of LABOUREFFSINGLEOPERATIVE to find out how dates are handled. If necessary, post the SQL for LABOUREFFSINGLEOPERATIVE.

  3. #3
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query (Year) (A2k)

    Thanks Hans.

    I just needed pointing in the right direction.
    I've alterred the query "LABOUREFFSINGLEOPERATIVE" to:-

    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, Year([date]) AS Expr1
    FROM [LABOUR BOOKING]
    GROUP BY [LABOUR BOOKING].NAME, Year([date]), Month([Date])
    HAVING (((Year([date]))=Year(Now())) AND ((Month([Date]))=Month(Now())));

    Please ignore the naming conventions as it is one of my first attempts of using Access going back some years now.
    (bit embarassing really)

Posting Permissions

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