Calculated fields in query

  • Thread starter Thread starter Christopher
  • Start date Start date
C

Christopher

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
 
When you run this query, notice the name Access uses above the Sum([Days
Taken]) column: something like SumOfDaysTaken

Use this name for the 3rd field, e.g.:
[DaysAllowance]-[SumOfDays Taken]
 
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
 
Hi,

Thank you very much, it worked!Brilliant stuff!

Chris.
-----Original Message-----
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
.
Click to expand...
.
Click to expand...
 
Back
Top