Calculated fields in query

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Steve (anonymous_at_discussions.microsoft.com)
Date: 07/23/04 

Date: Fri, 23 Jul 2004 04:48:05 -0700

Hi
what you could do is first create a query that totals the
days taken by each employee etc.
Use that query in a non totalled query and it should do
the trick.
eg: qryTotalDaysTaken(2 Fields)
ID DaysTaken
GroupBy Sum

eg: qryAllowanceRemaining(4 Fields)
ID Allowance SumOfDaysTaken
tblAllowance tblAllowance qryTotalDaysTaken

TotalRem:Allowance-SumOfDaysTaken

>-----Original Message-----
>Hi
>
>I am stuck here and need some help with this one.
>I have a select query which I need to calculate the
>following operation:
>
>The first field:
>[DaysAllowance]
>GroupBy
>
>The second field:
>[Days Taken]
>Sum
>
>For The third field:
>[DaysAllowance]-[Days Taken]
>Expression (does not seem to like another Sum)?
>The query returns the value 20 whereas it should return
0,
>as the first field value is 20 and the second field is
>also 20.
>I really am scratching my head with this one.
>Thanks for your help.
>Chrisopher
>.
>

Relevant Pages

  • Re: Max and 2nd Highest for two identifiers
    ... So for example I can get part of my work by doing a max query: ... second field), AFTER the WHERE clause made its clean up. ... results for 3002 and of coarse you would want the highest test result. ... So here is what the above data needs to look like: ...
    (microsoft.public.access.queries)
  • Re: Add quantity of one on field2
    ... and displays 1 for quantity in the second field and then give me the ... exist then add a row with a tally of one otherwise increment the ... DELETE) per SQL PROCEDURE (Access Query object defined by either a SQL- ... CREATE TABLE Sequence ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Max and 2nd Highest for two identifiers
    ... The query I posted would return two records if there ... "John Spencer" wrote: ... second field), AFTER the WHERE clause made its clean up. ... results for 3002 and of coarse you would want the highest test ...
    (microsoft.public.access.queries)
  • Re: Radom records from a table
    ... You want to build a query with the questionID as one field and a random ... number as the second field. ... Take a look at in the help file for random numbers. ... for example i have some questtionaire in my table which i want to pick ...
    (microsoft.public.access.gettingstarted)
  • Re: Linking Date Fields
    ... It violates relational database principles. ... the second field. ... Use the query wherever you would otherwise have used the ...
    (microsoft.public.access.tablesdbdesign)