By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,806 Members | 1,380 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,806 IT Pros & Developers. It's quick & easy.

VB6 ADO 2.6 miscalculating sums in an Access Union Query

P: 9
Iím using ADO 2.6 in VB6 to read in data from a Union query (qryJoinBalanceData) in Access 2000. The Union query contains 3 sub-queries, each of which joins several tables, but they all generate exactly the same fields containing the same type of data.
Iím using qryJoinBalanceData as the source for a Group By and Sum query which compresses 4600 rows of data to 73. When this query is run in Access, it works perfectly, however when run in VB, everything appears to run ok, except that all of the Sums add up wrong, very VERY wrongÖ

I have tried several re-writes of the Union query, but all Iíve achieved is to change the way in which the answer was wrong. Iíve even added an extra query in Access which is simply ĎSELECT * FROM qryJoinBalanceData;í and used this as the recordsource in VB, it made no difference. The only way I can get the correct answer is to write the results of qryJoinBalanceData off to a table and use that, but this is an option Iíd rather avoid if at all possible.

All of the field names in qryJoinBalanceData are legal, they donít contain any odd chars, and there are no WHERE, HAVING, GROUP BY, or ORDER BY clauses in it. The 3 sub-queries are being joined together with UNION ALLs.

I would be very grateful for any help with this!
Mar 16 '07 #1
Share this Question
Share on Google+
8 Replies


100+
P: 1,646
Hi. Would you please post the code that is not working. Also, could you open the query in access that does work in design mode, switch the view to sql and post the query here?
Mar 16 '07 #2

P: 9
Hi there,

The variable sSQL is normally populated from data in a table as there are a number of different queries which can be run using qryJoinBalanceData as their datasource, but they are all suffering from the same problem as the one shown below.

Expand|Select|Wrap|Line Numbers
  1. Dim rstTextQuery As New ADODB.Recordset
  2. Dim sSQL As String
  3.  
  4. sSQL = "SELECT ItemName, Sum(Amount) AS SumOfAmount FROM qryJoinBalanceData GROUP BY ItemName, SortOrder ORDER BY SortOrder;"
  5.  
  6. rstTextQuery.CursorLocation = adUseClient
  7. rstTextQuery.Open (sSQL), cnngDatabase, adOpenKeyset, adLockOptimistic
  8.  

The following is an edited version of qryJoinBalanceData - as like I said this query is a datasource to a lot of different queries, some of which need different info. The version below holds all of the necessary fields for the above query, and most importantly has the exact same problem as the full version:

Expand|Select|Wrap|Line Numbers
  1. SELECT tblAccountsEtc.ItemName, tblAccountsEtc.OpeningBal AS Amount, tblAccountsEtc.SortOrder, tblAccountsEtc.ItemName AS Credit, Null AS Debit,  tblAccountUsage.ShowAsPositive
  2. FROM tblNames RIGHT JOIN (tblAccountUsage INNER JOIN tblAccountsEtc ON tblAccountUsage.UsageID = tblAccountsEtc.UsageID) ON tblNames.NameID = tblAccountsEtc.NameID
  3. UNION ALL SELECT tblAccountsEtc.ItemName, IIF(tblAccountUsage.ShowAsPositive="Debit",-(tblMoney.Value),tblMoney.Value) AS Amount, tblAccountsEtc.SortOrder, tblMoney.Credit, tblMoney.Debit, tblAccountUsage.ShowAsPositive
  4. FROM (tblNames RIGHT JOIN (tblAccountUsage INNER JOIN tblAccountsEtc ON tblAccountUsage.UsageID = tblAccountsEtc.UsageID) ON tblNames.NameID = tblAccountsEtc.NameID) INNER JOIN tblMoney ON tblAccountsEtc.ItemName = tblMoney.Credit
  5. UNION ALL SELECT tblAccountsEtc.ItemName, IIF(tblAccountUsage.ShowAsPositive="Credit",-(tblMoney.Value),tblMoney.Value) AS Amount, tblAccountsEtc.SortOrder, tblMoney.Credit, tblMoney.Debit, tblAccountUsage.ShowAsPositive
  6. FROM tblNames RIGHT JOIN (tblAccountUsage INNER JOIN (tblAccountsEtc INNER JOIN tblMoney ON tblAccountsEtc.ItemName = tblMoney.Debit) ON tblAccountUsage.UsageID = tblAccountsEtc.UsageID) ON tblNames.NameID = tblAccountsEtc.NameID;
  7.  
The tables and fields in use being:
tblAccountsEtc: UsageID, NameID, ItemName, OpeningBal, SortOrder
tblAccountUsage: UsageID, ShowAsPositive
tblNames: NameID
tblMoney: Value, Credit, Debit

tblNames may appear to be useless here, but it is used by other queries!
Thanks!
Mar 16 '07 #3

100+
P: 1,646
Is one of these the access version that works? I want you to post the version that you said works in access. Go to design mode and change the view to sql. Then copy and paste it here
Thanks
Mar 16 '07 #4

P: 9
The query held in sSQL (shown below) is one of the queries I am using to pull data out of qryJoinBalanceData, all of the queries work fine in Access, itís only in VB where the answers come out wrong, e.g. one row of data which should sum to around Ė19,000 actually sums to +533,000 in VB.

Expand|Select|Wrap|Line Numbers
  1. SELECT ItemName, Sum(Amount) AS SumOfAmount FROM qryJoinBalanceData GROUP BY ItemName, SortOrder ORDER BY SortOrder;
  2.  
Mar 17 '07 #5

100+
P: 1,646
Just so you don't think I have lost it or something. I understand your problem completely. In order to locate the error I want you to post the sql statement that you are using in your vb code that is not working without editing and the exact same sql statement taken from access that is working.

This request is based upon your original statement that it works in access and not in vb. I need to see both statements. If you edit either of them I can't help you.
Mar 17 '07 #6

P: 9
Hi there,

I'm using a single Access database with a number of tables and built in queries. For the problem I'm having at the moment, only 4 of those tables and one built in query is important, the tables are listed below with some of their fields (All fields relevant to the query in question):-

tblAccountsEtc: UsageID, NameID, ItemName, OpeningBal, SortOrder, DateOpened, Colour, Group
tblAccountUsage: UsageID, ShowAsPositive, Usage, BalanceCalculated
tblNames: NameID, Name
tblMoney: Value, Credit, Debit, DateTrans, Paid, DebitStatement, CreditStatement

The database also includes the built in Union Query, qryJoinBalanceData, which uses the above tables, the complete version of this is below:-
Expand|Select|Wrap|Line Numbers
  1. SELECT tblAccountsEtc.ItemName, tblAccountsEtc.DateOpened As DateTrans, tblAccountsEtc.OpeningBal AS Amount, tblAccountUsage.Usage, tblAccountUsage.BalanceCalculated, tblNames.Name, tblAccountsEtc.Colour, tblAccountsEtc.SortOrder, tblAccountsEtc.Group, tblAccountsEtc.ItemName AS Credit, Null AS Debit, True As Paid, 0 AS DebitStatement, 0 As CreditStatement, tblAccountUsage.ShowAsPositive
  2. FROM tblNames RIGHT JOIN (tblAccountUsage INNER JOIN tblAccountsEtc ON tblAccountUsage.UsageID = tblAccountsEtc.UsageID) ON tblNames.NameID = tblAccountsEtc.NameID
  3. UNION ALL SELECT tblAccountsEtc.ItemName, tblMoney.DateTrans, IIF(tblAccountUsage.ShowAsPositive="Debit",-(tblMoney.Value),tblMoney.Value) AS Amount, tblAccountUsage.Usage, tblAccountUsage.BalanceCalculated, tblNames.Name, tblAccountsEtc.Colour, tblAccountsEtc.SortOrder, tblAccountsEtc.Group, tblMoney.Credit, tblMoney.Debit, tblMoney.Paid, tblMoney.DebitStatement, tblMoney.CreditStatement, tblAccountUsage.ShowAsPositive
  4. FROM (tblNames RIGHT JOIN (tblAccountUsage INNER JOIN tblAccountsEtc ON tblAccountUsage.UsageID = tblAccountsEtc.UsageID) ON tblNames.NameID = tblAccountsEtc.NameID) INNER JOIN tblMoney ON tblAccountsEtc.ItemName = tblMoney.Credit
  5. UNION ALL SELECT tblAccountsEtc.ItemName, tblMoney.DateTrans, IIF(tblAccountUsage.ShowAsPositive="Credit",-(tblMoney.Value),tblMoney.Value) AS Amount, tblAccountUsage.Usage, tblAccountUsage.BalanceCalculated, tblNames.Name, tblAccountsEtc.Colour, tblAccountsEtc.SortOrder, tblAccountsEtc.Group, tblMoney.Credit, tblMoney.Debit, tblMoney.Paid, tblMoney.DebitStatement, tblMoney.CreditStatement, tblAccountUsage.ShowAsPositive
  6. FROM tblNames RIGHT JOIN (tblAccountUsage INNER JOIN (tblAccountsEtc INNER JOIN tblMoney ON tblAccountsEtc.ItemName = tblMoney.Debit) ON tblAccountUsage.UsageID = tblAccountsEtc.UsageID) ON tblNames.NameID = tblAccountsEtc.NameID;
  7.  
qryJoinBalanceData is for use as a recordsource to around 15 different queries, all of which are to be run from VB. However for ease of creating them, they were all created and tested in the Access database alongside qryJoinBalanceData, so they are all thoroughly checked and tested as being accurate. These queries were then copied into VB where they are actually to be used. This is where everything started to fall down - in Access they all produce correct answers, in VB they are all wrong.

The query below is one of those 15, I used it as an example as it was simply the first in the list, and I'm hoping that if this one can be made to produce the correct answers in VB then all of the others will follow suit, as they are all put together in much the same way.
Expand|Select|Wrap|Line Numbers
  1. SELECT ItemName, Sum(Amount) AS SumOfAmount FROM qryJoinBalanceData GROUP BY ItemName, SortOrder ORDER BY SortOrder;
  2.  
My cutting stuff down harks back to my earlier days of programming my BBC B computer - keep cutting the working stuff out of the code until all that's left is the bug, then try and fix it!! I try to find better ways of doing it these days, but occasionally...

Many thanks for all your time!!
Mar 17 '07 #7

100+
P: 1,646
I'm not sure what I am putting here that is unclear.
If a sql statement works in access and the exact same statement using the exact same database does not work from vb then you have an error in the vb code. Please post the code that uses this sql statement:
Expand|Select|Wrap|Line Numbers
  1. Dim rstTextQuery As New ADODB.Recordset
  2. Dim sSQL As String
  3.  
  4. sSQL = "SELECT ItemName, Sum(Amount) AS SumOfAmount FROM qryJoinBalanceData GROUP BY ItemName, SortOrder ORDER BY SortOrder;"
  5.  
  6. rstTextQuery.CursorLocation = adUseClient
  7. rstTextQuery.Open (sSQL), cnngDatabase, adOpenKeyset, adLockOptimistic
again including where you use the recordset values and get the wrong answer. Please only post that code. You don't need to explain it to me. Thanks
Mar 17 '07 #8

P: 9
Hi there,

Iím sorry about the confusion, but the data is displayed directly on screen using a DataGrid, so there is very little code involved other than the SQL of the queries themselves.

The SQL for the final queries is held in a table with the appropriate one being selected by a combo box. subUpdateRecords is then fired off. rstmTextQuery is a module level recordset on the form which also holds the DataGrid so that the DataGrid canít lose itís data at an inconvenient momentÖ I was setting the CursorLocation as when I get everything working I was going to insert a recordsource.sort command as the DataGrid doesnít support sorting.
Expand|Select|Wrap|Line Numbers
  1. rstmFormData.Open "SELECT * FROM tblQueries ORDER BY SortOrder", _
  2.     cnngDatabase, adOpenKeyset, adLockOptimistic
  3.  
  4.  
  5. Private Sub subUpdateRecords()
  6. Dim sSQL As String
  7.  
  8. rstmFormData.Update
  9. If rstmTextQuery.State = 1 Then rstmTextQuery.Close
  10.  
  11. sSQL = Nz(rstmFormData!QuerySQL, "")
  12. rstmTextQuery.CursorLocation = adUseClient
  13. rstmTextQuery.Open (sSQL), cnngDatabase, adOpenKeyset, adLockOptimistic
  14. grdDataGrid.DataSource = rstmTextQuery
  15. End Sub
  16.  
The grid in use is a Microsoft Data Bound Grid Control 5.0 (SP3), from DBGRID32.OCX

Thanks again!!
Mar 18 '07 #9

Post your reply

Sign in to post your reply or Sign up for a free account.