query domain agrigate function

[Guest]

dear friends,
my problom is (1) kindly tell me the differnece between the normal functions
used in select querys eigther sum
,datediff,,,,,etc or anything
else and the errors displayed by the access while
executing the
qry as Cannot have agrigate function
(sum(x+y)),,,etc
under what circumstences such errors will arraise
and how to
come out of this !

 

[Allen Browne]

If I understand your question correctly, the question you need to ask is:
What fields are available after the query has run?

If you choose Group By in the Total row under a field, that field ends up in
the query results, and so you can use the name of that field inside a
function such as Nz() or DateDiff().

However, if you choose Sum in the Total row under a field named Amount, the
query does not output the Amount field. Instead it produces a total, and
gives it the name SumOfAmount. You can therefore use SumOfAmount in an
expression like this:
Tax: [SumOfAmount] * 0.07
(assuming a 7% tax rate.)

Likewise, any field that has Where in the Total row is applied before the
results are calculated, and so you can't use that filed in another
expression at the end.

In summary, you can only use the *aggregated* fields in the calculated
fields and expressions in your query.

 

[Guest]

If I understand your question correctly, the question you need to ask is:
What fields are available after the query has run?

If you choose Group By in the Total row under a field, that field ends up in
the query results, and so you can use the name of that field inside a
function such as Nz() or DateDiff().

However, if you choose Sum in the Total row under a field named Amount, the
query does not output the Amount field. Instead it produces a total, and
gives it the name SumOfAmount. You can therefore use SumOfAmount in an
expression like this:
Tax: [SumOfAmount] * 0.07
(assuming a 7% tax rate.)

Likewise, any field that has Where in the Total row is applied before the
results are calculated, and so you can't use that filed in another
expression at the end.

In summary, you can only use the *aggregated* fields in the calculated
fields and expressions in your query.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

dear friends,
my problom is (1) kindly tell me the differnece between the normal
functions
used in select querys eigther sum
,datediff,,,,,etc or anything
else and the errors displayed by the access while
executing the
qry as Cannot have agrigate function
(sum(x+y)),,,etc
under what circumstences such errors will arraise
and how to
come out of this !

 

[Guest]

dearfriend,
i would like to know how/where to put code/and if not possible why please!
about
for eg,my table banktrans
field ,deposit (curr)
" , withdraw (cur)
balence (cur)
now i would like to have field level validation viz,
1) if sum (withdraw) > (sum(deposit))
cancel the save action with msgbox
and more conditions with many field levels during the table design
time it self or/and while data entry on form
please advise.

If I understand your question correctly, the question you need to ask is:
What fields are available after the query has run?

If you choose Group By in the Total row under a field, that field ends up in
the query results, and so you can use the name of that field inside a
function such as Nz() or DateDiff().

However, if you choose Sum in the Total row under a field named Amount, the
query does not output the Amount field. Instead it produces a total, and
gives it the name SumOfAmount. You can therefore use SumOfAmount in an
expression like this:
Tax: [SumOfAmount] * 0.07
(assuming a 7% tax rate.)

Likewise, any field that has Where in the Total row is applied before the
results are calculated, and so you can't use that filed in another
expression at the end.

In summary, you can only use the *aggregated* fields in the calculated
fields and expressions in your query.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

dear friends,
my problom is (1) kindly tell me the differnece between the normal
functions
used in select querys eigther sum
,datediff,,,,,etc or anything
else and the errors displayed by the access while
executing the
qry as Cannot have agrigate function
(sum(x+y)),,,etc
under what circumstences such errors will arraise
and how to
come out of this !

 

[Allen Browne]

It would probably be best to ask this as a new question rather than a
followup.

In any case, do *not* store the balance in the table. For more info, see:
Calculated Fields
at:
http://allenbrowne.com/casu-14.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

dearfriend,
i would like to know how/where to put code/and if not possible why please!
about
for eg,my table banktrans
field ,deposit (curr)
" , withdraw (cur)
balence (cur)
now i would like to have field level validation viz,
1) if sum (withdraw) > (sum(deposit))
cancel the save action with msgbox
and more conditions with many field levels during the table design
time it self or/and while data entry on form
please advise.

If I understand your question correctly, the question you need to ask is:
What fields are available after the query has run?

If you choose Group By in the Total row under a field, that field ends up
in
the query results, and so you can use the name of that field inside a
function such as Nz() or DateDiff().

However, if you choose Sum in the Total row under a field named Amount,
the
query does not output the Amount field. Instead it produces a total, and
gives it the name SumOfAmount. You can therefore use SumOfAmount in an
expression like this:
Tax: [SumOfAmount] * 0.07
(assuming a 7% tax rate.)

Likewise, any field that has Where in the Total row is applied before the
results are calculated, and so you can't use that filed in another
expression at the end.

In summary, you can only use the *aggregated* fields in the calculated
fields and expressions in your query.

dear friends,
my problom is (1) kindly tell me the differnece between the normal
functions
used in select querys eigther sum
,datediff,,,,,etc or anything
else and the errors displayed by the access
while
executing the
qry as Cannot have agrigate function
(sum(x+y)),,,etc
under what circumstences such errors will
arraise
and how to
come out of this !

 

[Tom Wickerqueer]

im not so sure I agree with a diagnosis like this

a) if they were usign SQL Server; you could use calculated FIELDS, so that
you could reuse business logic. in this situation; of course you would
store the balance in the table (as a calculation)

b) Access MDB is too slow for real world usage; of course you'll need to
store a calculated value

c) Access MDB is too slow for real world usage; of course you'll need to
store a calculated value

It would probably be best to ask this as a new question rather than a
followup.

In any case, do *not* store the balance in the table. For more info, see:
Calculated Fields
at:
http://allenbrowne.com/casu-14.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

dearfriend,
i would like to know how/where to put code/and if not possible why
please!
about
for eg,my table banktrans
field ,deposit (curr)
" , withdraw (cur)
balence (cur)
now i would like to have field level validation viz,
1) if sum (withdraw) > (sum(deposit))
cancel the save action with msgbox
and more conditions with many field levels during the table design
time it self or/and while data entry on form
please advise.

If I understand your question correctly, the question you need to ask
is:
What fields are available after the query has run?

If you choose Group By in the Total row under a field, that field ends
up in
the query results, and so you can use the name of that field inside a
function such as Nz() or DateDiff().

However, if you choose Sum in the Total row under a field named Amount,
the
query does not output the Amount field. Instead it produces a total, and
gives it the name SumOfAmount. You can therefore use SumOfAmount in an
expression like this:
Tax: [SumOfAmount] * 0.07
(assuming a 7% tax rate.)

Likewise, any field that has Where in the Total row is applied before
the
results are calculated, and so you can't use that filed in another
expression at the end.

In summary, you can only use the *aggregated* fields in the calculated
fields and expressions in your query.

dear friends,
my problom is (1) kindly tell me the differnece between the normal
functions
used in select querys eigther sum
,datediff,,,,,etc or anything
else and the errors displayed by the access
while
executing the
qry as Cannot have agrigate function
(sum(x+y)),,,etc
under what circumstences such errors will
arraise
and how to
come out of this !