If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
DSum in Query
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? -- Peter |
#2
|
|||
|
|||
DSum in Query
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" wrote in message ... 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? -- Peter |
#3
|
|||
|
|||
DSum in 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" wrote: 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" wrote in message ... 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? -- Peter |
#4
|
|||
|
|||
DSum in Query
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" wrote: 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" wrote: 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" wrote in message ... 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? -- Peter |
Thread Tools | |
Display Modes | |
|
|