SLIMMING DOWN QUERY EXPRESSIONS

[kattz]

Hi

I have a form which is based on a query called Payroll Spreadsheet Query - which in turn is created from 3 other queries 1,2,3.

i can run all the three queries separately but when i get to the Payroll spreadsheet query - i get it is too complex and now my Form won't work either

i have tried to slim down the queries with using alias' but now i am stuck.

i have attached the sql from my number 2 query and my Payroll Spreadsheet query,in the hopes somebody can help me slim it down even more so i can do the same for the others.

Just some further info - i enter data into the form from the guys time cards and trip sheets (they are truck drivers) - it then calculates details such as base pay, time and half and double time plus a lot extra. With my pay there are a lot of variables so i have a lot of IIF functions.

i then have to print out the form to keep for my records then a report is created (from Payroll Spreadsheet Query)- exported to Excel and sent to my payroll department for entering into the system.

i don't keep copies of records on my system except the previous weeks on file as i keep hard copies as my records.

SQL of Query Number 2:
SELECT ED.[NO], TS.[MANIFEST NO], TS.[NORM TRIP BASE], TS.[TRIPLE TRIP BASE], TS.[MANUAL TRIP BASE], TS.[NORM KMS], TS.[TRIPLE KMS], TS.[MANUAL KMS], TS.[NORM LOAD], TS.[TRIPLE LOAD], TS.[MANUAL LOAD], TS.[NORM ONITE], TS.[TRIPLE ONITE], TS.[MANUAL ONITE], TS.[NORM OTHER], TS.[TRIPLE OTHER], TS.[MANUAL OTHER], ([NORM KMS]*[CPK])-Nz([NORM TRIP BASE]*[rates]![HOURLY]) AS [NORM TRIP MONEY], ([TRIPLE KMS]*0.1)*[CPK]+([TRIPLE KMS]-([TRIPLE KMS]*0.1))*[TRIPLE CPK]-Nz([TRIPLE TRIP BASE])*[rates]![HOURLY] AS [TRIPLE TRIP MONEY], Nz([MANUAL KMS])*Nz([MANUAL CPK RATE])-Nz([MANUAL TRIP BASE])*Nz([rates]![HOURLY]) AS [MANUAL TRIP MONEY], [NORM TRIP BASE]*[RATES]![HOURLY]*0.3*[SUPER RATE] AS [NORM SUPER], [TRIPLE TRIP BASE]*[rates]![HOURLY]*0.3*[SUPER RATE] AS [TRIPLE SUPER], [MANUAL TRIP BASE]*[rates]![HOURLY]*0.3*[SUPER RATE] AS [MANUAL SUPER], Nz([NORM TRIP BASE])+Nz([triple TRIP base])+Nz([manual TRIP base]) AS [TOTAL TRIP BASE], Nz([NORM KMS])+Nz([triple kMS])+Nz([manual kMS]) AS [TOTAL KMS], Nz([NORM ONITE])+Nz([TRIPLE ONITE])+Nz([MANUAL ONITE]) AS [TOTAL ONITE], Nz([NORM OTHER])+Nz([triple other])+Nz([manual other]) AS [TOTAL OTHER], Nz([NORM LOAD])+Nz([triple load])+Nz([manual load]) AS [TOTAL L/UNL], Nz([NORM TRIP MONEY])+Nz([triple trip money])+Nz([manual trip money]) AS [TOTAL TRIP MONEY], Nz([NORM SUPER])+Nz([triple super])+Nz([manual super]) AS [TOTAL SUPER], TS.[manual cpk rate], TS.[recharge to linehaul 3]
FROM ((RATES INNER JOIN [EMPLOYEE DATA] AS ED ON RATES.GRADE = ED.[GRADE ID]) INNER JOIN [TIME CARD TABLE] AS TC ON ED.[NO] = TC.[EMPLOYEE ID]) INNER JOIN [TRIP SHEET TABLE] AS TS ON TC.[EMPLOYEE ID] = TS.[EMPLOYEE ID]
WHERE (((ED.DEPARTMENT)<>"SUBCONTRACTOR" And (ED.DEPARTMENT) Not Like "SALARY*") AND ((ED.[TERMINATED DATE]) Is Null));


My Payroll Spreadsheet Query SQL is as follows (this is what my form is based on:

SELECT ED.DIVISION, ED.[DEDUCT LUNCH], ED.DAY, IIf(ED!CASUAL=Yes,1,"") AS numerical, ED.DEPARTMENT, ED.[NO], ED.[LAST NAME], ED.[FIRST NAME], [1].[MON START], [1].[MON END], [1].[TUE START], [1].[TUE END], [1].[WED START], [1].[WED END], [1].[THU START], [1].[THU END], [1].[FRI START], [1].[FRI END], [1].[SAT START], [1].[SAT END], [1].[SUN START], [1].[SUN END], [1].[MON DED], [1].[TUE DED], [1].[WED DED], [1].[THU DED], [1].[FRI DED], [1].[MON BASE], [1].[TUE BASE], [1].[WED BASE], [1].[THU BASE], [1].[FRI BASE], [1].[MON TIMEHALF], [1].[TUE TIMEHALF], [1].[WED TIMEHALF], [1].[THU TIMEHALF], [1].[FRI TIMEHALF], [1].[SAT TIMEHALF], [1].[MON DUB], [1].[TUE DUB], [1].[WED DUB], [1].[THU DUB], [1].[FRI DUB], [1].[SAT DUB], [1].[SUN DUB], [1].[MON LWP], [1].[TUE LWP], [1].[WED LWP], [1].[THU LWP], [1].[FRI LWP], [1].[MON CRIB], [1].[TUE CRIB], [1].[WED CRIB], [1].[THU CRIB], [1].[FRI CRIB], [1].[MON MEAL], [1].[TUE MEAL], [1].[WED MEAL], [1].[THU MEAL], [1].[FRI MEAL], [1].[MON ANOON], [1].[TUE ANOON], [1].[WED ANOON], [1].[THU ANOON], [1].[FRI ANOON], [1].[MON NITE], [1].[TUE NITE], [1].[WED NITE], [1].[THU NITE], [1].[FRI NITE], [1].[MON PH], [1].[TUE PH], [1].[WED PH], [1].[THU PH], [1].[FRI PH], [1].[MON SL], [1].[TUE SL], [1].[WED SL], [1].[THU SL], [1].[FRI SL], [1].[MON RDO], [1].[TUE RDO], [1].[WED RDO], [1].[THU RDO], [1].[FRI RDO], [1].[MON AL], [1].[TUE AL], [1].[WED AL], [1].[THU AL], [1].[FRI AL], [1].[MON L HAND], [1].[TUE L HAND], [1].[WED L HAND], [1].[THU L HAND], [1].[FRI L HAND], [1].[MON LOAD], [1].[TUE LOAD], [1].[WED LOAD], [1].[THU LOAD], [1].[FRI LOAD], [1].[TOTAL BASE], [1].[TOTAL TIMEHALF], [1].[TOTAL DOUBLE], [1].[TOTAL LWP], [1].[TOTAL CRIB], [1].[TOTAL MEAL], [1].[TOTAL ANOON], [1].[TOTAL NITE], [1].[TOTAL PH], [1].[TOTAL SL], [1].[TOTAL RDO], [1].[TOTAL AL], [1].[TOTAL LEAD HAND], [1].[TOTAL LOAD], [1].[MON CAL], [1].[TUE CAL], [1].[WED CAL], [1].[THU CAL], [1].[FRI CAL], [1].[SAT CAL], [1].ADIS, [1].[sick cert supplied], [1].[SL START], [1].[SL END], [2].[MANIFEST NO], [2].[NORM TRIP BASE], [2].[TRIPLE TRIP BASE], [2].[MANUAL TRIP BASE], [2].[NORM KMS], [2].[TRIPLE KMS], [2].[MANUAL KMS], [2].[NORM LOAD], [2].[TRIPLE LOAD], [2].[MANUAL LOAD], [2].[NORM ONITE], [2].[TRIPLE ONITE], [2].[MANUAL ONITE], [2].[NORM OTHER], [2].[TRIPLE OTHER], [2].[MANUAL OTHER], [2].[NORM TRIP MONEY], [2].[TRIPLE TRIP MONEY], [2].[MANUAL TRIP MONEY], [2].[NORM SUPER], [2].[TRIPLE SUPER], [2].[MANUAL SUPER], [2].[TOTAL TRIP BASE], [2].[TOTAL KMS], [2].[TOTAL ONITE], [2].[TOTAL OTHER], [2].[TOTAL L/UNL], [2].[TOTAL TRIP MONEY], [2].[TOTAL SUPER], [2].[manual cpk rate], [2].[recharge to linehaul 3], [3].[TOTAL PAY COST], [3].NWE, [3].WCM, RATES.GRADE, RATES.WEEKLY, RATES.HOURLY, RATES.CPK, RATES.[TRIPLE CPK], RATES.[SUPER RATE], ED.[LEADING HAND], ED.[MPC LOADER], ED.CASUAL, [1].[recharge mon], [1].[recharge tue], [1].[recharge wed], [1].[recharge thu], [1].[recharge fri], [1].[recharge sat], [1].[recharge sun]
FROM (((RATES INNER JOIN [EMPLOYEE DATA] AS ED ON RATES.GRADE = ED.[GRADE ID]) INNER JOIN [PAYROLL CALCULATION QUERY 1] AS 1 ON ED.[NO] = [1].[NO]) INNER JOIN [PAYROLL CALCULATION QUERY 2] AS 2 ON ED.[NO] = [2].[NO]) INNER JOIN [PAYROLL CALCULATION QUERY 3] AS 3 ON ED.[NO] = [3].[NO]
ORDER BY ED.[LAST NAME];

I don't work my expressions in SQL - i do it in design view but i'm learning.

thanks in advance for your time

 

[KenReay]

Hi

Not a direct answer to your question, but I have sometimes found that the "too complex.. " error message is given, when in fact the problem is Nulls in the data

I note you are using NZ() incorrectly ie Nz([NORM LOAD])+, it should be Nz([NORM LOAD],0)+

this is repeated in numerous places in your SQL, could be the problem

good luck

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[Crowley16]

you could try to reduce your queries by removing the calculations from your queries, and doing them on the form/report/excel...

i.e. just include the base information in your queries...

 

[scriverb]

I don't know if this will help but when you said they all run fine individually but when you run the last one you expereince the problem. Sometimes I have found that just by getting rid of all the direct references to fields from the tables and queries in a very complex situation sometimes helps. I have left in the only calculated field numerical. Everything else I just made a reference to selecting all fields. let me know if this helps.

SELECT ED.*, IIf(ED!CASUAL=Yes,1,"") AS numerical, [1].*, [2].*, [3].*, RATES.* 
FROM (((RATES INNER JOIN [EMPLOYEE DATA] AS ED ON RATES.GRADE = ED.[GRADE ID]) INNER JOIN [PAYROLL CALCULATION QUERY 1] AS 1 ON ED.[NO] = [1].[NO]) INNER JOIN [PAYROLL CALCULATION QUERY 2] AS 2 ON ED.[NO] = [2].[NO]) INNER JOIN [PAYROLL CALCULATION QUERY 3] AS 3 ON ED.[NO] = [3].[NO]
ORDER BY ED.[LAST NAME];

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[kattz]

thanks guys but none of those worked

is there a way i can sum for example , TOTAL BASE: Nz([MON BASE])+Nz([TUE BASE])+Nz([WED BASE])+Nz([THU BASE])+Nz([FRI BASE])+Nz([2]![TOTAL TRIP BASE])without writing the whole lot out? Maybe this would make the query smaller/leaner.
i've tried to use the 'sum' function but i'm afraid i just don't get it

i need to keep the calculation in the queries as the query is what i use to create the report to export to excel to send off to my pay office. i also need the calculations in the form i enter data in because i need to keep copies of those on file to show my calculations.

i'm trying to think how i can simplify some of my IIF formulas but because there are so many variables - i can't see how.

 

[BobJacksonNCI]

Does sympathy count?

I don't know what the problem is, but I recently learned to be wary of NZ - thread703-868415

If I were you, I'd look into having fields default to zero and not allow null, if possible. Obviously, in that case you wouldn't know whether a zero was entered or defaulted. But, if you could do that and remove the NZ's entirely, it might help.

Also, bear in mind that if NZ is used with a text field that is null, the result is not zero, it is "".



HTH,
Bob

 

[KenReay]

Hi

I repeat:

Not a direct answer to your question, but I have sometimes found that the "too complex.. " error message is given, when in fact the problem is Nulls in the data

I note you are using NZ() incorrectly ie Nz([NORM LOAD])+, it should be Nz([NORM LOAD],0)+

this is repeated in numerous places in your SQL, could be the problem


have you tried this?

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[JonFer]

It looks like you have unnormalized data but it is hard to tell just from the query. Would you be willing to re-design your tables to make them more normalized? It would require some effort to then redo your queries but it would probably be worth it if you are going to use the database for a long time. Almost everything you try to do with unnormalized data will be difficult and require monstrous queries.

If you are the only user, I would seriously consider it. You may want to consult someone on a new design or write a new post in the forums about how to best structure your data. Provide examples of the current tables and see what the experts say. It usually is fairly straightforward to load a new database structure.

 

[kattz]

Ken thanks - yes i did try that but it didn't work - i've had no problems with the Nz before now so it seems to be working just fine. I think i have just too much data I am going to try redesigning as i think there is a way i can take some data from my query and keep it separate.

Jonfer, what do you mean by unnormalised data?

thanks bob - if what i am going to try works - i'll give your idea a go. (and yes, sympathy does count

thanks everyone for your time, muchly appreciated

 

[JonFer]

In your payroll spreadsheet query, you have columns for each day of the week for several categories. If this is how the underlying table is structured, it is not normalized because you are putting information (the day of the week) in the column name instead of in a field in the table. This makes extracting information much more difficult that it needs to be. If the table is normalized, you can get the spreadsheet "format" from a crosstab query with more compact SQL.

There are several other normalization concepts which help ensure the integrity of your database and allow it to be flexible for future changes so it is worthwhile to learn about. Post your table structures if you want further feedback.

 

[kattz]

Hi Jonfer

Sorry this has taken so long but i had to leave it for a while unfortunately - more important issues to deal with.

One of the tables i have underlying my spreadsheet query is a 'time card table'

this consists of some of these field names (obviously for every day of the week).
emp id
mon start
tues start (etc)
mon end
tues end (etc)
mon timehalf
tue timehalf
mon dub
tue dub

So for every emp id i have a record of their start end times etc.. and the table looks a little like this:-

Emp id mon start tues start mon end tues end
1111 7.50 8.00 15.00 15.20

 

[JonFer]

Here are some suggestions for your table design:

empID
StartDateTime (DateTime field)
EndDateTime (DateTime field)
TimeHalf
Dub

Or

empID
StartDateTime (DateTime field)
TimeWorked (Double field representing minutes or hrs)
TimeHalf
Dub

You can determine the day of the week for the date using Format(StartDateTime,"ddd") and then use this as your column heading for a crosstab query.

Work hours would be 24*(StartDateTime-EndDateTime) for the first table design.

 

[kattz]

Thanks Jonfer

I will give it a go and let you know - i have to do some more investigating into crosstab queries.

thanks again
kattz