Consolidating the group Trial Balance using VBA open record set (1 Viewer)

[nector]

Sorry People !

I'm back with now something which I thought was going to be easy:

(1) I have consolidated my IFRS Trial Balance after conversion to our functional currency using union queries and before applying some domain functions, If I apply sum and Group BY function within the query it takes something like 45 minutes to compile a report, hence the need to change everything to open record set, but unfortunately my report even if it now takes 5 seconds to come out, it's just brought errors. See the screen shoot + the final query + record set.

Actual select query being opened as record set see below:

SELECT QryTBFinalBranch.AccountCode, QryTBFinalBranch.AccountName, QryTBFinalBranch.Debit, QryTBFinalBranch.Credit, QryTBFinalBranch.GrandTotal
FROM QryTBFinalBranch;

Below is my open record set with parameters embedded in the individual MS Access queries which you cannot see but are required to filter the Trial Balance:

Dim db As DAO.Database
Dim strSql As String
Dim prm As DAO.Parameter
Set db = CurrentDb
strSql = "SELECT [QryTBMonthly].[AccountCode],[QryTBMonthly].[AccountName],Sum([QryTBMonthly].[Debit]) As TDebits,Sum([QryTBMonthly].[Credit]) As TCredits,Sum(GrandTotal) As FinTotal FROM [QryTBMonthly] GROUP BY [QryTBMonthly].[AccountCode],[QryTBMonthly].[AccountName]"
With db.CreateQueryDef("", strSql)
For Each prm In .Parameters
prm.value = Eval(prm.Name)
Next
    With .OpenRecordset(dbOpenSnapshot, dbSeeChanges)
        If Not .EOF() Then
            Me.RevenueAccount = .Fields(0).value
            Me.RevAcc = .Fields(1).value
            Me.Debit = .Fields(2).value
            Me.Credit = .Fields(3).value
            Me.GrandTotal = .Fields(4).value
        End If
    End With
End With
Set prm = Nothing
Set db = Nothing

 

[Josef P.]

For Each prm In .Parameters
prm.value = Eval(prm.Name)
Next

What are these parameters whose name also defines the value?
Are these references to form controls?

What does opening the recordset have to do with the report?

 

[nector]

Below is a full query before the final query with the requested parameter

SELECT QryFinancialStatements.YYDate, QryFinancialStatements.BSID, QryFinancialStatements.AccountCode, QryFinancialStatements.AccountName, QryFinancialStatements.AccountID, Nz(IIf([Total]>0,[Total]),0) AS Debit, Nz(IIf([Total]<0,[Total]),0) AS Credit, Val(Nz([Total],0)) AS GrandTotal
FROM QryFinancialStatements
WHERE (((QryFinancialStatements.YYDate) Between Format(([Forms]![frmTrialBalanceBranchPrinting]![txtStartTB]),"dd\/mm\/yyyy") And Format(([Forms]![frmTrialBalanceBranchPrinting]![TxtEndTb]),"dd\/mm\/yyyy")));

Below is the final query after the parameters:

SELECT QryTBFinalBranch.AccountCode, QryTBFinalBranch.AccountName, QryTBFinalBranch.Debit, QryTBFinalBranch.Credit, QryTBFinalBranch.GrandTotal
FROM QryTBFinalBranch;

 

[Josef P.]

And the last query is used as the data source for the report?

BTW:

WHERE (((QryFinancialStatements.YYDate) Between Format(([Forms]![frmTrialBalanceBranchPrinting]![txtStartTB]),"dd\/mm\/yyyy") And Format(([Forms]![frmTrialBalanceBranchPrinting]![TxtEndTb]),"dd\/mm\/yyyy")));

Is YYDate a string? ... Because the Between parameters are used as strings. (If it is a string, whether then the sorting fits? (dd/mm/yyy) --- 03/02/2023, 04/01/2023, ...)

 

[nector]

Is YYDate a string? ... Because the Between parameters are used as strings. (If it is a string, whether then the sorting fits? (dd/mm/yyy) --- 03/02/2023, 04/01/2023, ...)

Yes that is true

 

[nector]

And the last query is used as the data source for the report? Yes the query below is used as the report data sourece

SELECT QryTBFinalBranch.AccountCode, QryTBFinalBranch.AccountName, QryTBFinalBranch.Debit, QryTBFinalBranch.Credit, QryTBFinalBranch.GrandTotal
FROM QryTBFinalBranch;

 

[nector]

Below after the final query above I'm coming up with the sub below:

Private Sub Report_Load()
Dim db As DAO.Database
Dim strSql As String
Dim prm As DAO.Parameter
Set db = CurrentDb
strSql = "SELECT [QryTBMonthly].[AccountCode],[QryTBMonthly].[AccountName],Sum([QryTBMonthly].[Debit]) As TDebits,Sum([QryTBMonthly].[Credit]) As TCredits,Sum(GrandTotal) As FinTotal FROM [QryTBMonthly] GROUP BY [QryTBMonthly].[AccountCode],[QryTBMonthly].[AccountName]"
With db.CreateQueryDef("", strSql)
For Each prm In .Parameters
prm.value = Eval(prm.Name)
Next
    With .OpenRecordset(dbOpenSnapshot, dbSeeChanges)
        If Not .EOF() Then
            Me.RevenueAccount = .Fields(0).value
            Me.RevAcc = .Fields(1).value
            Me.Debit = .Fields(2).value
            Me.Credit = .Fields(3).value
            Me.GrandTotal = .Fields(4).value
        End If
    End With
End With
Set prm = Nothing
Set db = Nothing
End Sub

 

[nector]

But I'm able to see the correct account code even if it says "You cannot assign a value this object"

 

[Josef P.]

Is this possibly the bound control?

 

[nector]

Yes it is

 

[Minty]

As I said in one of your other threads, why don't you create a view on the server that does your grouping and totals then either use that directly or use a pass-through query to just return the records you want for the report.

When you are dealing with millions of records let the server do the heavy lifting, and leave Access to do the presentation.

 

[nector]

As I said in one of your other threads, why don't you create a view on the server that does your grouping and totals then either use that directly or use a pass-through query to just return the records you want for the report.

The trouble here is that the query you seeing is made of 35 union queries, then is there a way to unionize all the 35 queries and then aggregate them in a view in SQL Server?

 

[Minty]

Yes. But needing 35 union queries sounds as if you have a serious data structure problem.
What are they doing? Can you post the SQL of them?

 

[nector]

I have now started unionizing these step by step though I love harder things but this one but the time I will finish I will learnt alot . So far the queries below are working in an SQL view.

Now I have hope to move on, when I get there I will to figure how to sum and group by and the date parameter.

SELECT        ShipDate, StockAccount, StCAcc, ROUND(COALESCE (QtySold * CostValue * - 1, 0), 2) AS Total
FROM            dbo.tblSaleAccounting
WHERE        (StatusFinal IS NOT NULL)

UNION ALL

SELECT        dbo.tblVoucher.YYDate, dbo.tblaccounts.AccountCode, dbo.tblaccounts.AccountName, (dbo.tblVoucher.Dr - dbo.tblVoucher.Cr) * dbo.tblJournalHeader.FCRate AS Total
FROM            dbo.tblaccounts INNER JOIN
                         dbo.tblJournalHeader INNER JOIN
                         dbo.tblVoucher ON dbo.tblJournalHeader.CreateID = dbo.tblVoucher.CreateID ON dbo.tblaccounts.AccountID = dbo.tblVoucher.AccountID
WHERE        (dbo.tblJournalHeader.Status = '1')

 

[gemma-the-husky]

When you say "group TB" do you mean different companies, or different departments of a single company?

Out of interest, do you maintain an account total on each NL account, or are you summing transactions to get the total.

 

[Josef P.]

But I'm able to see the correct account code even if it says "You cannot assign a value this object"

You cannot assign a value to a bound control in a report.
I assume that should be the totals line.
=> This does not require any VBA code in the report.
If this is the sum of the values contained above in the report, you can create a group in the report and simply calculate the sum in it with =Sum([xxx]) as the source of the controls in the group.
Or you can use the query with totals, create an extra report with it, and insert this new report as a subreport into the existing report.

BTW: I would get the data into the report via a pass-through query and a SQL procedure (with min/max date as parameters).

 

[nector]

Dear all

Thank you for assisting but I have settled on creating a direct union view from SQL Server , pardon me on this I'm using an advance Trial balance for a complex tax system for the see growing union union view below:

SELECT        ShipDate, StockAccount, StCAcc, ROUND(COALESCE (QtySold * CostValue * - 1, 0), 2) AS Total
FROM            dbo.tblSaleAccounting
WHERE        (StatusFinal IS NOT NULL)

UNION ALL

SELECT        dbo.tblVoucher.YYDate, dbo.tblaccounts.AccountCode, dbo.tblaccounts.AccountName, (dbo.tblVoucher.Dr - dbo.tblVoucher.Cr) * dbo.tblJournalHeader.FCRate AS Total
FROM            dbo.tblaccounts INNER JOIN
                         dbo.tblJournalHeader INNER JOIN
                         dbo.tblVoucher ON dbo.tblJournalHeader.CreateID = dbo.tblVoucher.CreateID ON dbo.tblaccounts.AccountID = dbo.tblVoucher.AccountID
WHERE        (dbo.tblJournalHeader.Status = '1')

UNION ALL

SELECT dbo.tblSaleAccounting.ShipDate, dbo.tblSaleAccounting.CostOfSales, dbo.tblSaleAccounting.CosAcc, Round(COALESCE((dbo.tblSaleAccounting.QtySold*dbo.tblSaleAccounting.CostValue),0),2) AS Total
FROM dbo.tblSaleAccounting
WHERE (((dbo.tblSaleAccounting.StatusFinal) Is Not Null))

UNION ALL

SELECT dbo.tblSaleAccounting.ShipDate, dbo.tblSaleAccounting.DebtorsControl, dbo.tblSaleAccounting.DebtAcc, ((((((dbo.tblSaleAccounting.Sellingprice)/(1+COALESCE(dbo.tblSaleAccounting.TAX,0)))+(IIf(dbo.tblSaleAccounting.Sellingprice > dbo.tblSaleAccounting.RRP,((dbo.tblSaleAccounting.Sellingprice*COALESCE(dbo.tblSaleAccounting.TAX,0))/(1+COALESCE(dbo.tblSaleAccounting.TAX,0))),((dbo.tblSaleAccounting.RRP *COALESCE(dbo.tblSaleAccounting.TAX,0))/(1+COALESCE(dbo.tblSaleAccounting.TAX,0))))))*dbo.tblSaleAccounting.QtySold)-((IIf((dbo.tblSaleAccounting.RRP>dbo.tblSaleAccounting.Sellingprice),((dbo.tblSaleAccounting.RRP*COALESCE(dbo.tblSaleAccounting.TAX,0))/(1+COALESCE(dbo.tblSaleAccounting.TAX,0))),((dbo.tblSaleAccounting.Sellingprice*COALESCE(dbo.tblSaleAccounting.TAX,0))/(1+COALESCE(dbo.tblSaleAccounting.TAX,0)))))*dbo.tblSaleAccounting.QtySold))*dbo.tblSaleAccounting.FCRate)+((((IIf((dbo.tblSaleAccounting.RRP>dbo.tblSaleAccounting.SellingPrice),((dbo.tblSaleAccounting.RRP*COALESCE(dbo.tblSaleAccounting.Tax,0))/(1+COALESCE(dbo.tblSaleAccounting.Tax,0))),((dbo.tblSaleAccounting.SellingPrice*COALESCE(dbo.tblSaleAccounting.Tax,0))/(1+COALESCE(dbo.tblSaleAccounting.Tax,0)))))*dbo.tblSaleAccounting.QtySold))*dbo.tblSaleAccounting.FCRate)-(Round(((dbo.tblSaleAccounting.QtySold*tblSaleAccounting.SellingPrice)/(1.16)*COALESCE(dbo.tblSaleAccounting.Discount,0)),2)) AS Total
FROM dbo.tblSaleAccounting
WHERE (((dbo.tblSaleAccounting.DebtorsControl) Is Not Null) AND ((dbo.tblSaleAccounting.StatusFinal)='1'));

 

[nector]

The posted union query is working ok, I just have to finish all of them, but I like the speed! So people I'm a trained UK Chartered Certified Accountant working for a big group of companies. Some of my queries you may have some challenges to read them because the mathematics is very complicated.

kindly pardon me on this one, however, I like all your contributions I'm learning a lot.

Regards

Christopher

 

[Minty]

Your Query line

    ((((((dbo.tblSaleAccounting.Sellingprice)/(1+COALESCE(dbo.tblSaleAccounting.TAX,0)))+(IIf(dbo.tblSaleAccounting.Sellingprice > dbo.tblSaleAccounting.RRP,((dbo.tblSaleAccounting.Sellingprice*COALESCE(dbo.tblSaleAccounting.TAX,0))/(1+COALESCE(dbo.tblSaleAccounting.TAX,0))),((dbo.tblSaleAccounting.RRP *COALESCE(dbo.tblSaleAccounting.TAX,0))/(1+COALESCE(dbo.tblSaleAccounting.TAX,0))))))*dbo.tblSaleAccounting.QtySold)-((IIf((dbo.tblSaleAccounting.RRP>dbo.tblSaleAccounting.Sellingprice),((dbo.tblSaleAccounting.RRP*COALESCE(dbo.tblSaleAccounting.TAX,0))/(1+COALESCE(dbo.tblSaleAccounting.TAX,0))),((dbo.tblSaleAccounting.Sellingprice*COALESCE(dbo.tblSaleAccounting.TAX,0))/(1+COALESCE(dbo.tblSaleAccounting.TAX,0)))))*dbo.tblSaleAccounting.QtySold))*dbo.tblSaleAccounting.FCRate)+((((IIf((dbo.tblSaleAccounting.RRP>dbo.tblSaleAccounting.SellingPrice),((dbo.tblSaleAccounting.RRP*COALESCE(dbo.tblSaleAccounting.Tax,0))/(1+COALESCE(dbo.tblSaleAccounting.Tax,0))),((dbo.tblSaleAccounting.SellingPrice*COALESCE(dbo.tblSaleAccounting.Tax,0))/(1+COALESCE(dbo.tblSaleAccounting.Tax,0)))))*dbo.tblSaleAccounting.QtySold))*dbo.tblSaleAccounting.FCRate)-(Round(((dbo.tblSaleAccounting.QtySold*tblSaleAccounting.SellingPrice)/(1.16)*COALESCE(dbo.tblSaleAccounting.Discount,0)),2)) AS Total

Could almost certainly be much easier to read and digest if you used a sub select to setup the underlying main values and a CASE Statement to do the switching.

It shouldn't affect performance and is much easier to debug if you aren't getting the correct values out at some point.
It would also allow you to remove a chunk of the brackets which are adding to the unintelligible nature of it all.
To demonstrate your first IIF could be rewritten - note that you can spread the statement out onto separate lines as well.

(CASE WHEN dbo.tblSaleAccounting.Sellingprice > dbo.tblSaleAccounting.RRP
    THEN ((dbo.tblSaleAccounting.Sellingprice * COALESCE(dbo.tblSaleAccounting.TAX , 0)) / (1 + COALESCE(dbo.tblSaleAccounting.TAX , 0)))
    ELSE ((dbo.tblSaleAccounting.RRP * COALESCE(dbo.tblSaleAccounting.TAX , 0)) / (1+ COALESCE(dbo.tblSaleAccounting.TAX , 0))) END

I know which version I would prefer to revisit in three months' time to check something out.

 

[nector]

Below is the final output and the speed has improved from 2:45 hours to 10 seconds in terms of execution

SELECT
  ShipDate,
  BSIDRev,
  RevenueAccount,
  RevAcc,
  SellingPrice / (1 + COALESCE(Tax, 0) + COALESCE(TourismLevy, 0)) * QtySold * COALESCE(FCRate, 0) * -1 AS NetRevenues
FROM dbo.tblSaleAccounting
WHERE (StatusFinal IS NOT NULL)
UNION ALL
SELECT
  ShipDate,
  dbo.tblSaleAccounting.BSIDCos,
  StockAccount,
  StCAcc,
  ROUND(COALESCE(QtySold * CostValue * -1, 0), 2) AS Total
FROM dbo.tblSaleAccounting
WHERE (StatusFinal IS NOT NULL)
UNION ALL
SELECT
  dbo.tblVoucher.YYDate,
  dbo.tblaccounts.BSID,
  dbo.tblaccounts.AccountCode,
  dbo.tblaccounts.AccountName,
  (dbo.tblVoucher.Dr - dbo.tblVoucher.Cr) * dbo.tblJournalHeader.FCRate AS Total
FROM dbo.tblaccounts
INNER JOIN dbo.tblJournalHeader
INNER JOIN dbo.tblVoucher
  ON dbo.tblJournalHeader.CreateID = dbo.tblVoucher.CreateID
  ON dbo.tblaccounts.AccountID = dbo.tblVoucher.AccountID
WHERE (dbo.tblJournalHeader.Status = '1')
UNION ALL
SELECT
  dbo.tblSaleAccounting.ShipDate,
  dbo.tblSaleAccounting.BSIDCos,
  dbo.tblSaleAccounting.CostOfSales,
  dbo.tblSaleAccounting.CosAcc,
  ROUND(COALESCE((dbo.tblSaleAccounting.QtySold * dbo.tblSaleAccounting.CostValue), 0), 2)
  AS Total
FROM dbo.tblSaleAccounting
WHERE (((dbo.tblSaleAccounting.StatusFinal) IS NOT NULL))
UNION ALL
SELECT
  dbo.tblSaleAccounting.ShipDate,
  dbo.tblSaleAccounting.BSIDRec,
  dbo.tblSaleAccounting.DebtorsControl,
  dbo.tblSaleAccounting.DebtAcc,
  ((((((dbo.tblSaleAccounting.Sellingprice) / (1 + COALESCE(dbo.tblSaleAccounting.TAX, 0)))
  + (IIf(dbo.tblSaleAccounting.Sellingprice > dbo.tblSaleAccounting.RRP, ((dbo.tblSaleAccounting.Sellingprice * COALESCE(dbo.tblSaleAccounting.TAX, 0)) / (1 + COALESCE(dbo.tblSaleAccounting.TAX, 0))),
  ((dbo.tblSaleAccounting.RRP * COALESCE(dbo.tblSaleAccounting.TAX, 0)) / (1 + COALESCE(dbo.tblSaleAccounting.TAX, 0)))))) * dbo.tblSaleAccounting.QtySold)
  - ((IIf((dbo.tblSaleAccounting.RRP > dbo.tblSaleAccounting.Sellingprice), ((dbo.tblSaleAccounting.RRP * COALESCE(dbo.tblSaleAccounting.TAX, 0)) / (1 + COALESCE(dbo.tblSaleAccounting.TAX, 0))),
  ((dbo.tblSaleAccounting.Sellingprice * COALESCE(dbo.tblSaleAccounting.TAX, 0)) / (1 + COALESCE(dbo.tblSaleAccounting.TAX, 0))))) * dbo.tblSaleAccounting.QtySold)) * dbo.tblSaleAccounting.FCRate)
  + ((((IIf((dbo.tblSaleAccounting.RRP > dbo.tblSaleAccounting.SellingPrice), ((dbo.tblSaleAccounting.RRP * COALESCE(dbo.tblSaleAccounting.Tax, 0)) / (1 + COALESCE(dbo.tblSaleAccounting.Tax, 0))),
  ((dbo.tblSaleAccounting.SellingPrice * COALESCE(dbo.tblSaleAccounting.Tax, 0)) / (1 + COALESCE(dbo.tblSaleAccounting.Tax, 0))))) * dbo.tblSaleAccounting.QtySold)) * dbo.tblSaleAccounting.FCRate)
  - (ROUND(((dbo.tblSaleAccounting.QtySold * tblSaleAccounting.SellingPrice) / (1.16) * COALESCE(dbo.tblSaleAccounting.Discount, 0)), 2)) AS Total
FROM dbo.tblSaleAccounting
WHERE (((dbo.tblSaleAccounting.DebtorsControl) IS NOT NULL)
AND ((dbo.tblSaleAccounting.StatusFinal) = '1'))
UNION ALL
SELECT
  dbo.tblSaleAccounting.ShipDate,
  dbo.tblSaleAccounting.BSIDVat,
  dbo.tblSaleAccounting.VATAccount,
  dbo.tblSaleAccounting.VATAcc,
  ((((((IIf((dbo.tblSaleAccounting.RRP > dbo.tblSaleAccounting.SellingPrice),
  ((dbo.tblSaleAccounting.RRP) / ((1 + (dbo.tblSaleAccounting.Tax) + COALESCE(dbo.tblSaleAccounting.TourismLevy, 0)))), ((dbo.tblSaleAccounting.SellingPrice) / ((1 + (dbo.tblSaleAccounting.Tax)
  + COALESCE(dbo.tblSaleAccounting.TourismLevy, 0))))))) * dbo.tblSaleAccounting.Tax) * -1) * dbo.tblSaleAccounting.FCRate) * dbo.tblSaleAccounting.QtySold) AS Total
FROM dbo.tblSaleAccounting
WHERE (((dbo.tblSaleAccounting.StatusFinal) IS NOT NULL))
UNION ALL
SELECT
  dbo.tblSaleAccounting.ShipDate,
  dbo.tblSaleAccounting.BSIDExcis,
  dbo.tblSaleAccounting.ExciseDuty,
  dbo.tblSaleAccounting.ExcisDu,
  (COALESCE(((((((dbo.tblSaleAccounting.QtySold * dbo.tblSaleAccounting.SellingPrice)
  * dbo.tblSaleAccounting.FCRate)) * dbo.tblSaleAccounting.Duty)) * -1) + ((((dbo.tblSaleAccounting.QtySold * dbo.tblSaleAccounting.DutyFixed)) * -1) * dbo.tblSaleAccounting.FCRate), 0) * 0) AS Total
FROM dbo.tblSaleAccounting
WHERE (((dbo.tblSaleAccounting.StatusFinal) IS NOT NULL))
UNION ALL
SELECT
  dbo.tblSaleAccounting.ShipDate,
  dbo.tblSaleAccounting.BSIDDisc,
  dbo.tblSaleAccounting.DiscountAllowed,
  dbo.tblSaleAccounting.DiscoAll,
  ROUND(((dbo.tblSaleAccounting.QtySold * dbo.tblSaleAccounting.SellingPrice) / (1.16)
  * COALESCE(dbo.tblSaleAccounting.Discount, 0)), 2) AS TotalDisc
FROM dbo.tblSaleAccounting
WHERE (((((dbo.tblSaleAccounting.QtySold * dbo.tblSaleAccounting.SellingPrice) / (1.16) * COALESCE(dbo.tblSaleAccounting.Discount, 0))) <> 0))
UNION ALL
SELECT
  dbo.tblPosAccounts.POSDate,
  dbo.tblPosAccounts.BISIDRev,
  dbo.tblPosAccounts.RevenueAccount,
  dbo.tblPosAccounts.RevAcc,
  ((((((dbo.tblPosAccounts.SellingPrice) / (1 + COALESCE(dbo.tblPosAccounts.Tax, 0))))
  * dbo.tblPosAccounts.QtySold) * COALESCE(dbo.tblPosAccounts.FCRate, 0)) * -1) AS Ledger
FROM dbo.tblEfdReceiptsPOS
INNER JOIN dbo.tblPosAccounts
  ON dbo.tblEfdReceiptsPOS.INVID = dbo.tblPosAccounts.SoldID
WHERE (((dbo.tblEfdReceiptsPOS.InvoiceNumber) IS NOT NULL))
UNION ALL
/CODE]


Now I need to fix the Income Statement, Balance Sheet and Cashflow Statement in the similar manner.

By the way someone mentioned something on speeding the cursor movement from the parent form to the subform its taking a good 20 seconds instead of instant. The same happen when moving to the new line. What is supposed to be done on this one also?

Thank you so much for bringing in workable ideas.
: