VB6 ADO 2.6 miscalculating sums in an Access Union Query

[RJMac]

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!

[willakawill]

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?

[RJMac]

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.

Dim rstTextQuery As New ADODB.Recordset
Dim sSQL As String
 
sSQL = "SELECT ItemName, Sum(Amount) AS SumOfAmount FROM qryJoinBalanceData GROUP BY ItemName, SortOrder ORDER BY SortOrder;"
 
rstTextQuery.CursorLocation = adUseClient
rstTextQuery.Open (sSQL), cnngDatabase, adOpenKeyset, adLockOptimistic
 

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:

SELECT tblAccountsEtc.ItemName, tblAccountsEtc.OpeningBal AS Amount, tblAccountsEtc.SortOrder, tblAccountsEtc.ItemName AS Credit, Null AS Debit,  tblAccountUsage.ShowAsPositive
FROM tblNames RIGHT JOIN (tblAccountUsage INNER JOIN tblAccountsEtc ON tblAccountUsage.UsageID = tblAccountsEtc.UsageID) ON tblNames.NameID = tblAccountsEtc.NameID
UNION ALL SELECT tblAccountsEtc.ItemName, IIF(tblAccountUsage.ShowAsPositive="Debit",-(tblMoney.Value),tblMoney.Value) AS Amount, tblAccountsEtc.SortOrder, tblMoney.Credit, tblMoney.Debit, tblAccountUsage.ShowAsPositive
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
UNION ALL SELECT tblAccountsEtc.ItemName, IIF(tblAccountUsage.ShowAsPositive="Credit",-(tblMoney.Value),tblMoney.Value) AS Amount, tblAccountsEtc.SortOrder, tblMoney.Credit, tblMoney.Debit, tblAccountUsage.ShowAsPositive
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;
 

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!

[willakawill]

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

[RJMac]

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.

SELECT ItemName, Sum(Amount) AS SumOfAmount FROM qryJoinBalanceData GROUP BY ItemName, SortOrder ORDER BY SortOrder;
 

[willakawill]

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.

[RJMac]

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:-

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
FROM tblNames RIGHT JOIN (tblAccountUsage INNER JOIN tblAccountsEtc ON tblAccountUsage.UsageID = tblAccountsEtc.UsageID) ON tblNames.NameID = tblAccountsEtc.NameID
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
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
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
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;
 

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.

SELECT ItemName, Sum(Amount) AS SumOfAmount FROM qryJoinBalanceData GROUP BY ItemName, SortOrder ORDER BY SortOrder;
 

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!!

[willakawill]

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:

Dim rstTextQuery As New ADODB.Recordset
Dim sSQL As String
 
sSQL = "SELECT ItemName, Sum(Amount) AS SumOfAmount FROM qryJoinBalanceData GROUP BY ItemName, SortOrder ORDER BY SortOrder;"
 
rstTextQuery.CursorLocation = adUseClient
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

[RJMac]

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.

rstmFormData.Open "SELECT * FROM tblQueries ORDER BY SortOrder", _
    cnngDatabase, adOpenKeyset, adLockOptimistic
 
 
Private Sub subUpdateRecords()
Dim sSQL As String
 
rstmFormData.Update
If rstmTextQuery.State = 1 Then rstmTextQuery.Close
 
sSQL = Nz(rstmFormData!QuerySQL, "")
rstmTextQuery.CursorLocation = adUseClient
rstmTextQuery.Open (sSQL), cnngDatabase, adOpenKeyset, adLockOptimistic
grdDataGrid.DataSource = rstmTextQuery
End Sub
 

The grid in use is a Microsoft Data Bound Grid Control 5.0 (SP3), from DBGRID32.OCX

Thanks again!!