Great Trouble need VBA or something (1 Viewer)

[syedadnan]

I am using AC 2010 where i am having a query named Payrol through this query i am updating (Append) a table named Payroll everything is going fine but query geting slow on appending data and this slow increasing gradually after data of months increasing i have used multiples of formulas in this query may be this is the reason of slowing ... here is list of formulas below is there any posibility to do all theses through VBA

IIf([in] Is Null And [wd]<>"Sunday" And [offday]<>[wd] And [nature]<>"Public holiday","Absent","Present")

Dt: Val(Nz(Format([dates],"mmddyyyy")))

Nature: IIf(DLookUp("comments","hl","dts = " & [dt]),"Public Holiday","Routine")

LateM: Val(Nz(IIf([shift slot]="hourly",DateDiff("n",[start],[in])/60*60,"0")))

EarlyM: Val(Nz(IIf([nature]="Routine",IIf([shift slot]="Hourly",DateDiff("n",[out],[end])/60*60,"0"),"0")))

Absent: Val(Nz(IIf([status]="absent","1","0")))

Present: Val(Nz(IIf([status]="present","1","0")))

dayWage: Nz([cp],0)/Nz([md],0)

hWage: Val(Nz(Nz([daywage],0)/Nz([dhour],0)))

MWage: Val(Nz(Nz([hwage],0)/60))

Absentwage: Nz([daywage],0)*Nz([absent],0)

Presentwage: Nz([daywage],0)*Nz([present],0)

LWage: Val(Nz(Nz([latem],0)*Nz([mwage],0)))

EWage: Nz([earlym],0)*Nz([mwage],0)

ded: Nz([lwage],0)+Nz([ewage],0)

Net: (Nz([arrears],0)+Nz([presentwage],0))-Nz([ded],0)

CP: Val(Nz(DLookUp("currnetpay","app3","empl = " & [empid])))

 

[plog]

I think your unnecessary functions are a cause of the slowness.

1. Dlookups have no place in queries. Instead, you JOIN the external table to the existing one and obtain your data that way.

2. NZ function around IIF statement that is going to return a value are not needed (e.g. Absent). If your IIf is going to return "1" or "0" there's no point accounting for an impossible Null value.

3. Val function around Iif statement that returns a numeric value in a string (e.g. Absent). Instead, just return the number not inside a string.

All of those are unnecessary function calls. Additionally, the table you are putting this data into is not normalized. You should not be storing values in field names. I see a ton of "_wage" fields. When you do that, you are essentially storing the type of wage not in a field, but in a field name. Most likely you need a new table to hold all that wage data. Instead of all the data in one record, you would put each type in its own record within that new table.

You also are storing calculated values in a table which you need not do. ded is is calculated based on Lwage and ewage which are in the table, so you don't need to store ded value as well. I see this error a few times in your data.

Lastly, I see a few functions that could possibly divide by 0 (e.g. hWage, dayWage) . You should test for those possibilities and avoid those errors.

 

[jdraw]

syedadnan,
You might get some responses with options/streamlining ideas if readers knew WHAT you are trying to achieve.
Just seeing code that you have written and that you feel may be contributing to the "slowing down" of processing isn't getting to the root of the issue necessarily. plog has offered some suggestions based on the code you have shown.

Good luck.

 

[The_Doc_Man]

Another issue is whether this table is indexed by anything at all. A typical observation of non-normalized, non-indexed tables is that when they search for things, their speed drops according the formula 1/(n*(n-1)) - so I have to ask if the table is indexed in any way that helps you more rapidly access what you need to access?

Usually, for payroll functions, you would have a table for which the employee ID number is a contributor key, even though it cannot be the prime key by itself. If you then did your updates by employee ID, the index would make it much faster to find the records you wanted.