473,327 Members | 1,997 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,327 software developers and data experts.

Total Query from Subtotal Query

Please help.

GOAL: I hope to calculate a total amount in a TotalQuery (or field in
the SubQuery?), based upon three field amounts in a SubQuery.

STATUS: When I create an expression in the TotalQuery based upon
SubQuery fields, e.g:

Total: [SOPAmount] + [PetFilingAmount] + [SummonsFilingAmount]

'The resulting [Total] field displays each of the three SubQuery
amounts sequentially in the same field [156.00 50.00 5.00], rather than
the sum of having added them. However, when I test the expression
using multiplication (*) the [Total] field displays the correct total
amount. For example, the expression:

Total: ([SOPAmount]*[PetFilingAmount])+ [SummonsFilingAmount]
'yields a correct single total amount, [7505.00].

The SubQuery in ms-access 2k sums the amounts from certain checks
entered for specific purposes. All resulting records from the Subquery
are sorted on a [MergeDate] field:

The Subquery SQL statement is as follows:

SELECT FormatCurrency(DSum("[Amount]","tblPaymentAccounting","[Purpose]
= 'Service of Process' "),2) AS SOPAmount,
FormatCurrency(DSum("[Amount]","tblPaymentAccounting","[Purpose] =
'Petition Filing Fees' "),2) AS PetFilingAmount,
FormatCurrency(DSum("[Amount]","tblPaymentAccounting","[Purpose] =
'Summons Filing Fees' "),2) AS SummonsFilingAmount
FROM tblDebtorMasterFileList
WHERE (((tblDebtorMasterFileList.MergeDate)=#1/14/2005#));

I don't work with Access all the time, so I am not that astute at these
queries. Could someone please advise me on the best method to obtain a
TotalFees based upon the added total from the three field amounts in
the underlying query.

Nov 13 '05 #1
2 5622
I suspect your problem may be that the FormatCurrency has turned the fields into a text field by default, so that the + operator acts as a concantenation rather than an addition. In your total query, try explcitly converting them back into a number eg:

TotalFees: CDbl([SOPAmount]) + Cdbl([PetFilingAmount]) + CDbl([SummonsFilingAmount])

(I think it's CDbl - it's whatever the equivalent of CInt and CStr are... CSng may be enough too).

--
Message posted via http://www.accessmonster.com
Nov 13 '05 #2

David Seeto via AccessMonster.com wrote:
I suspect your problem may be that the FormatCurrency has turned the fields into a text field by default, so that the + operator acts as a
concantenation rather than an addition. In your total query, try
explcitly converting them back into a number eg:
TotalFees: CDbl([SOPAmount]) + Cdbl([PetFilingAmount]) + CDbl([SummonsFilingAmount])
(I think it's CDbl - it's whatever the equivalent of CInt and CStr are... CSng may be enough too).
--
Message posted via http://www.accessmonster.com


Yes, you were correct about the FormatCurrency conversion being the
problem. Your suggestion fixed the problem, and I appreciate your
response. Thanks.

Nov 13 '05 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
by: eric | last post by:
Hi,everyone: I have my xml data defined as xml data definition I: <?xml version="1.0"?> <parents> <parent> <id>1000</id>
7
by: rick | last post by:
Can anyone help, I am try to create a simple form using a table, where a user can fill out quanty and price and have a total automatically calculated and inserted in another field. I stuck trying...
0
by: aspbeg | last post by:
subtotal and total in a datagrid for any columns
1
by: mesud | last post by:
helow i'm new but i wanna ask that i need the code to connect database access with vb and calculate total,subtotal,tax etc. and display it
2
by: bobw2961 | last post by:
I have what I hope is a simple question for some of you. I have two fields. Quantity and Price. I enter a text box named SubTotal in the detail of the report and set the control source to =*. ...
5
by: mebrabham | last post by:
Hello, I am trying to create a running subtotal in a query and then graph the running subtotal for each city group. The data looks like this in the table (for illustration): My City My...
7
lee123
by: lee123 | last post by:
hey all, i have made a order form with all the works. in vb 6 and in this form i have been trying to get a total of the items that a customer would have ordered. to make things make sense i have in...
8
by: KUTTAN | last post by:
i want to total of all 7th columns(in all rows) of my table my tables id is ctl00_ContentPlaceHolder1_Quote1_RadGdProductList_ctl01 The first row of the table is headings the following code...
2
by: murch.alexander | last post by:
Hi all, I have a query that uses a subquery to subtotal one of its fields. This part is working fine. The twist is that when I encounter certain values, I want to reset the subtotal to zero and...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.