Search code examples
ms-accessms-access-2016

Include a customized calculated field in MS Access Query

I am trying to execute a calculated field in MS Access. Background

I have a single table including fields such as:

Name Week Hours_Charged
X    21-06  10
Y    21-06  20
X    21-06  30
Z    21-06  40

I am trying to create a new field in the query wherein it says Gap and contains the substraction from 40.

Additionally, it will should group according to the Same names. So the expected output would be:

Name Week Hours_Charged Gap
    X    21-06  40      0
    Y    21-06  20      -20   
    Z    21-06  40      0

Conditions: With the same name, there can be various dates and hours. However, the idea remains same.

Any leads on this would be appreciated.

Thanks in advance.

I have tried implementing through the design mode but was not successful.

Solution

  • I replied your table like this:

    enter image description here

    And got this query:

    enter image description here

    SQL code of this query (please note I've used different field names):

    SELECT Table1.MyName, Table1.MyWeek, Sum(Table1.Hours_Charged) AS SumaDeHours_Charged, 40-[SumaDeHours_Charged] AS GAP
FROM Table1
GROUP BY Table1.MyName, Table1.MyWeek;

    And design view of this query in MS Access (Please, note my Access is in spanish, so name of rows in design view are in spanish, not in english. If you want to know the meaning of something, you can ask me or use the SQL code:

    enter image description here