Query - Sumiif

[isladogs]

Two queries? I can't set two queries as record source for my report. I don't know where to attach this.

I meant one query based on another.
You don't know where to attach your database?

[Jishnu Surendran]

It is a big file.

[isladogs]

Before we go any further, are you aware that you can group and do totals for group in the report . The totals go in the group footers.
In other words, it may not be necessary to group and sum in your queries

However, if that doesn't help, do upload

It is a big file.

Make a copy of your database.
Remove everything that isn't required including any code not relevant to this thread. Compact and zip.
If still too big, remove all but a few records leaving enough to test it. Compact and zip again

[Jishnu Surendran]

Attaching the database now. However, guess I've figured it out. Instead of using all the [Test-TB Query] fields for my report, I used just the relevant ones using Report:Query Builder. As I've told you earlier, I've inserted totals in the report itself but, it takes too much time time to appear when I load the report. Is there some VB code to make this faster. Its like blinking every time I scroll the report.

[isladogs]

Attaching the database now. However, guess I've figured it out. Instead of using all the [Test-TB Query] fields for my report, I used just the relevant ones using Report:Query Builder. As I've told you earlier, I've inserted totals in the report itself but, it takes too much time time to appear when I load the report. Is there some VB code to make this faster. Its like blinking every time I scroll the report.

Whilst that will help, it doesn't address the underlying cause of your slow performance
Your table isn't normalised - it needs to be split into several tables to prevent duplication of records - see later

First lets look at your reports

1. I've simplified report Test-DB removing unnecessary fields - Misc was used several times in header sections
It now runs more smoothly

2. I've removed the Report_Load code from Test-Audit Report & summed the CY(Net) field in category group footer
I've also done an overall total in the report footer
This is MUCH faster .... BUT
a) You shouldn't used special characters like () in field names - CYNet would work just as well
As previously mentioned Group is a reserved word in Access - change to GroupName
b) The report clearly shows the duplication due to bad table design



You need to split your table into several linked tables
As a starting point, consider 3 tables e.g.
tblCategory - C_ID (PK), Category, CatNum
tblGroupName - G_ID (PK), GroupName, GroupNum
tblParticulars - P_ID (PK), Particulars, CYDr, CYCr, PYDr, PYCr, C_ID (FK), G_ID (FK)

Adjust as necessary until the data is normalised
tblParticulars may need splitting down further depending on the data your business will be using

Then create queries linking the tables using the PK fields and FK fields
Finally re-create your reports as necessary

Good luck - you'll get there ....!

[Jishnu Surendran]

I get it. Thanks brother! But what is the duplication you've meant? Same item in particulars column more than once?

[isladogs]

Duplicates - where only the final column is different
a) Cash & bank balances - 4 records 'Bank balance in current account'
b) Cost of revenue - 3 records 'Employee costs'
c) Depreciation - 3 identical records

etc ...

Should each of these be one record in your report?

[Jishnu Surendran]

Particulars field mention the ledger accounts. What you've seen is the trial balance of a Company's accounts.

[isladogs]

My point was that I thought there should only be only record for each of those items showing the sum of the CY(Net) values
However, I'm not an accountant so if its correct to show repeated items then so be it....

What I'm sure about is that you are currently using Access like a spreadsheet
Either split your table into several tables or you may as well go back to doing all of this in Excel