DSum in Query

  • Thread starter Thread starter Peter
  • Start date Start date
P

Peter

DSum in Query
Hi. I think this is probably an error in my syntax and I’d really appreciate
some help please. I have a financial database that has many Tables, each
table has many fields primarily holding currency data.

I understand from research that it is incorrect to calculate and store a
‘result’ in a Table, so, I’ve created a Query in order to calculate and store
my result which is the TOTAL of all the currency fields in a given record.
From my research, I believe the best way of doing that is to add an extra
currency field to my Table so the field name is there for when I build my
query (still holding with the idea you shouldn’t store a result in the Table
itself).

I’ve entered the following formula into the Query by using the ZOOM feature
(it is entered into the filed column [WkCashInUnTOT]. The syntax looks like
this:

DSum("[WkCashInUnTOT]", "[Weekly Cash In Unrestricted Query]", "[UK Wk Num]
= '1'â€)

[WkCashInUnTOT] is the field name in the Table and is thus the column name
in my Query where I’ve entered the formula by using ZOOM.
[Weekly Cash In Unrestricted Query] is the Query name
[UK Wk Num] is the field in my Table that corresponds to a specific week of
the year (I have 52 of these, one for each week).

So you see I’m trying to Sum all fields in a given record if the UK WK Num =
1.

I’ve tried various ways of writing the above formula and keep getting error!
I’d like to think my logic is sound and that maybe the syntax is incorrect.
Hopefully you can help?
 
You do not need to create a field in a table in order to display a
calculated value in a query. Get rid of it.

"currency fields in a given record" sounds like you want to calculate across
fields rather than across records. DSum() is used to calculate across
records and has little value across fields.

If your [UK Wk Num] field is numeric, then lose the single quotes resulting
in:
... "[UK Wk Num] = 1â€)

I don't understand the significant fields in your table and what you
actually want to sum. Is it across records or across fields? What is the
actual field containing the value you want to sum?

How about providing a few sample records and how you want them to appear in
the query?
 
Hello Duane. OK, I’ve got rid of the unnecessary field – the only reason I
had it there was because I don’t know the way to assign it as a currency
field in the Query – I have seen people use txtFieldName: but I don’t know
what the equivalent of ‘txt’ is to make a query field assigned as currency?
I’ve also eliminated the single quotes around the number 1 – I take your
point as that particular field is number anyway rather than text.

You are right, I actually want to calculate across fields (within a single
record) rather than calculate down a column! Therefore, you suggest I should
be using a different function?

What I actually want to sum are approx. 15 fields within a single record;
each holding different money amounts. Examples are “DonationFromJmâ€,
OfferFromJohnâ€, “GeneralDonationsâ€, “Fundraisingâ€etc… Some of the Tables I
have hold about 25 fields similar to the names just mentioned. I have
successfully produced a form that totals fields in this long hand way but it
is very time consuming and prone to error. Hence, I homed in on DSum as
perhaps a shorthand way to save typing every field name – you indicate that
may be the wrong function for me? If it pans-out this is the only way to do
this in the query then so be it, but it just doesn’t seem to be very elegant!

Does this give you a little more to go on?


--
Peter


Duane Hookom said:
You do not need to create a field in a table in order to display a
calculated value in a query. Get rid of it.

"currency fields in a given record" sounds like you want to calculate across
fields rather than across records. DSum() is used to calculate across
records and has little value across fields.

If your [UK Wk Num] field is numeric, then lose the single quotes resulting
in:
... "[UK Wk Num] = 1â€)

I don't understand the significant fields in your table and what you
actually want to sum. Is it across records or across fields? What is the
actual field containing the value you want to sum?

How about providing a few sample records and how you want them to appear in
the query?

--
Duane Hookom
MS Access MVP


Peter said:
DSum in Query
Hi. I think this is probably an error in my syntax and I’d really
appreciate
some help please. I have a financial database that has many Tables, each
table has many fields primarily holding currency data.

I understand from research that it is incorrect to calculate and store a
‘result’ in a Table, so, I’ve created a Query in order to calculate and
store
my result which is the TOTAL of all the currency fields in a given record.
From my research, I believe the best way of doing that is to add an extra
currency field to my Table so the field name is there for when I build my
query (still holding with the idea you shouldn’t store a result in the
Table
itself).

I’ve entered the following formula into the Query by using the ZOOM
feature
(it is entered into the filed column [WkCashInUnTOT]. The syntax looks
like
this:

DSum("[WkCashInUnTOT]", "[Weekly Cash In Unrestricted Query]", "[UK Wk
Num]
= '1'â€)

[WkCashInUnTOT] is the field name in the Table and is thus the column name
in my Query where I’ve entered the formula by using ZOOM.
[Weekly Cash In Unrestricted Query] is the Query name
[UK Wk Num] is the field in my Table that corresponds to a specific week
of
the year (I have 52 of these, one for each week).

So you see I’m trying to Sum all fields in a given record if the UK WK Num
=
1.

I’ve tried various ways of writing the above formula and keep getting
error!
I’d like to think my logic is sound and that maybe the syntax is
incorrect.
Hopefully you can help?
Click to expand...
Click to expand...
 
Your table structure is un-normalized. Each money amount should create its
own record in a table rather than your separate fields. When you need
PaymentToDuane, you shouldn't have to create a new field.

I would expect a table structure that records the Amount, Source, RecvdDate,
etc.

If you set up your tables correctly then you use a simple totals query
rather than adding across fields.

If you don't understand normalization, do a search of the web. If you are
still confused, please come back with some of your application specifications.
--
Duane Hookom
Microsoft Access MVP


Peter said:
Hello Duane. OK, I’ve got rid of the unnecessary field – the only reason I
had it there was because I don’t know the way to assign it as a currency
field in the Query – I have seen people use txtFieldName: but I don’t know
what the equivalent of ‘txt’ is to make a query field assigned as currency?
I’ve also eliminated the single quotes around the number 1 – I take your
point as that particular field is number anyway rather than text.

You are right, I actually want to calculate across fields (within a single
record) rather than calculate down a column! Therefore, you suggest I should
be using a different function?

What I actually want to sum are approx. 15 fields within a single record;
each holding different money amounts. Examples are “DonationFromJmâ€,
OfferFromJohnâ€, “GeneralDonationsâ€, “Fundraisingâ€etc… Some of the Tables I
have hold about 25 fields similar to the names just mentioned. I have
successfully produced a form that totals fields in this long hand way but it
is very time consuming and prone to error. Hence, I homed in on DSum as
perhaps a shorthand way to save typing every field name – you indicate that
may be the wrong function for me? If it pans-out this is the only way to do
this in the query then so be it, but it just doesn’t seem to be very elegant!

Does this give you a little more to go on?


--
Peter


Duane Hookom said:
You do not need to create a field in a table in order to display a
calculated value in a query. Get rid of it.

"currency fields in a given record" sounds like you want to calculate across
fields rather than across records. DSum() is used to calculate across
records and has little value across fields.

If your [UK Wk Num] field is numeric, then lose the single quotes resulting
in:
... "[UK Wk Num] = 1â€)

I don't understand the significant fields in your table and what you
actually want to sum. Is it across records or across fields? What is the
actual field containing the value you want to sum?

How about providing a few sample records and how you want them to appear in
the query?

--
Duane Hookom
MS Access MVP


Peter said:
DSum in Query
Hi. I think this is probably an error in my syntax and I’d really
appreciate
some help please. I have a financial database that has many Tables, each
table has many fields primarily holding currency data.

I understand from research that it is incorrect to calculate and store a
‘result’ in a Table, so, I’ve created a Query in order to calculate and
store
my result which is the TOTAL of all the currency fields in a given record.
From my research, I believe the best way of doing that is to add an extra
currency field to my Table so the field name is there for when I build my
query (still holding with the idea you shouldn’t store a result in the
Table
itself).

I’ve entered the following formula into the Query by using the ZOOM
feature
(it is entered into the filed column [WkCashInUnTOT]. The syntax looks
like
this:

DSum("[WkCashInUnTOT]", "[Weekly Cash In Unrestricted Query]", "[UK Wk
Num]
= '1'â€)

[WkCashInUnTOT] is the field name in the Table and is thus the column name
in my Query where I’ve entered the formula by using ZOOM.
[Weekly Cash In Unrestricted Query] is the Query name
[UK Wk Num] is the field in my Table that corresponds to a specific week
of
the year (I have 52 of these, one for each week).

So you see I’m trying to Sum all fields in a given record if the UK WK Num
=
1.

I’ve tried various ways of writing the above formula and keep getting
error!
I’d like to think my logic is sound and that maybe the syntax is
incorrect.
Hopefully you can help?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top