Report Takes too long to process (1 Viewer)

AnilBagga

Member
Local time
Today, 20:51
Joined
Apr 9, 2020
Messages
223
I have 2 simple tables - an item Master and a table for the Bill of Material for the items in the master.

Master (tblItemMasterMain) has all the items - those which need a BOM (Serial no ends with .1) and those which are part of the BOM . All items in this table are unique - no duplicates.

The second table (tblItemMasterBOM) captures the relationship of the Main items and the BOM items.

The issue is the report to capture this relationship. There are 2 reports in the enclosed DB. rptItemMaster is the main report which includes items from tblItemMasterMain ending in 0.1 and the sub-report for the BOM- report rptItemBOM and link field is the SerialNo for the 2 reports.

For some reason, the report takes ages to populate! I am making some mistake which I am not able to identify. Will appreciate some help

I have reduced the 2 tables to reduce the DB size. Original table has about 4000 records in the main table.
 

Attachments

  • AccessBOM_09Nov21.zip
    385.8 KB · Views: 247

MajP

You've got your good things, and you've got mine.
Local time
Today, 11:21
Joined
May 21, 2018
Messages
8,463
Not sure why but that is a lot of subreports. I would think you could simply do Grouping and it would be faster and look better.
Another thing. Numbers are things you can do math on and it has meaning (add, subtract, multiply,...). These need to get saved as numbers. But things like SN, Social security numbers, account numbers, etc, should be stored as text. You have SNs as single. This can cause lots of problems.
21.0 is not the same SN as 21 or 21.000.
I would change these fields to text.

Gets real problematic here which you have Right([ItemSrNo],2)) = 0.1
It may cast the single to a string correctly and then recast it back to a single, but I would not count on it.
 
Last edited:

AnilBagga

Member
Local time
Today, 20:51
Joined
Apr 9, 2020
Messages
223
Not sure why but that is a lot of subreports. I would think you could simply do Grouping and it would be faster and look better.
Another thing. Numbers are things you can do math on and it has meaning (add, subtract, multiply,...). These need to get saved as numbers. But things like SN, Social security numbers, account numbers, etc, should be stored as text. You have SNs as single. This can cause lots of problems.
21.0 is not the same SN as 21 or 21.000.
I would change these fields to text.
1. I had an issue with sorting in text fields with Serial Nos. 1.2, 1.21, 1.22, 1.31 - they were not appearing in this sequence and I had changed the database structure. Changing it back to text could create other problems which I may not be able to handle. The DB is a subset of a large functional DB
2. Is it the Numeric field which is creating this issue or multiple sub reports ?
3. Are you suggesting grouping in the query and using this query for the report? I started out initially and got errors in the text field, maybe due to the Numeric field
4. I will try changing the text field in the table copies and see if it helps.
5. Is there a way I can improve the performance w/o changing to a text field?
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 11:21
Joined
May 21, 2018
Messages
8,463
This runs instantaneously. You can pretty it up.
group.jpg

Read this
 

CJ_London

Super Moderator
Staff member
Local time
Today, 15:21
Joined
Feb 19, 2013
Messages
16,555
Not a full suggestion but your erpmcode which you join on is text but you only have numeric data. Suggest this should be a number - your text index will be 16 bytes against 4 bytes for numeric one. So very simplistically your indexing will be slower, not 4 times slower, but still slower

Further your subreport joins twice to the main form, once in the recordsource and again in the subreport linkchild/master. You don't need to link in the subreport recordsource - you can reference the field in the mainreport

=parent.somecontorol
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 11:21
Joined
May 21, 2018
Messages
8,463
Is there a way I can improve the performance w/o changing to a text field?
As I said Grouping instead of a sub report is instantaneous.
 

Attachments

  • Group_AccessBOM_09Nov21.accdb
    2.6 MB · Views: 245

MajP

You've got your good things, and you've got mine.
Local time
Today, 11:21
Joined
May 21, 2018
Messages
8,463
I had an issue with sorting in text fields with Serial Nos. 1.2, 1.21, 1.22, 1.31
In your case you can simply add another calcualted column in the query
Sort:Cdbl([SerialNumber])

Sometimes you have to roll your own function. A lot of times people have stuff like
1.1
1.2
1.2.1
1.2.1.2

Or maybe
012.0
12
12.01
12.010
12.0100

Sometimes you can write a function to convert to correct number. Other times it is just easier to have a manually Populated Sort Field if you cannot gurantee they will be in the correct order based on autonumber.
 

Users who are viewing this thread

Top Bottom