Can this SQL code be simplified?

[Ron]

Hello everyone,

I have this SQL code in a Select Query that basically
looks to see if a received date has been entered in its
appropriate field. If there is no date in that received
field then it determines if the job is
due "today", "tomorrow" or if its "past due". If a date
has been entered then it's marked as "received".
The problem I'm running into is when I try to add another
level field programming to the SQL statement. Access
tells me that the expression is too complex. Can this
statement be simplified, and if so, how can I add more
fields to the mix?
Any help (in simple programming terms - I'm not an
advanced coder) would be greatly appreciated.

Thanks - Ron
See code below.
-----------------------------
SELECT [Job Tracking].[Traffic Person], [Job Tracking].
[Status of Job], [Job Tracking].Job, [Job
Tracking].Version, [Job Tracking].Product, [Job
Tracking].Source, [Job Tracking].[Ca1 Due], [Job Tracking].
[CA1 Received], [Job Tracking].[Ca2 Due], [Job Tracking].
[CA2 Received], [Job Tracking].[Ca3 Due], [Job Tracking].
[CA3 Received], [Job Tracking].[Ca4 Due], [Job Tracking].
[CA4 Received], [Job Tracking].[Start Date], [Job
Tracking].Category, [Job Tracking].Creative, [Job
Tracking].Media
FROM [Job Tracking] WHERE
(((IIf([CA1 Due]=Date() And [CA1 Received] Is Null,"Due
Today",
IIf([CA2 Due]=Date() And [CA2 Received] Is Null,"Due
Today",
IIf([CA3 Due]=Date() And [CA3 Received] Is Null,"Due
Today",
IIf([CA4 Due]=Date() And [CA4 Received] Is Null,"Due
Today",
IIf([CA1 Received] And [CA2 Received] And [CA3 Received]
And [CA4 Received] Is Not Null,"Received",
IIf([CA1 Due] Between Date()+1 And Date()+2 And [CA1
Received] Is Null,"Due Tomorrow",
IIf([CA2 Due] Between Date()+1 And Date()+2 And [CA2
Received] Is Null,"Due Tomorrow",
IIf([CA3 Due] Between Date()+1 And Date()+2 And [CA3
Received] Is Null,"Due Tomorrow",
IIf([CA4 Due] Between Date()+1 And Date()+2 And [CA4
Received] Is Null,"Due Tomorrow",
IIf([CA1 Received] And [CA2 Received] And [CA3 Received]
And [CA4 Received] Is Not Null,"Received",
IIf([CA1 Due]<Date() And [CA1 Received] Is Null,"Past Due",
IIf([CA2 Due]<Date() And [CA2 Received] Is Null,"Past Due",
IIf([CA3 Due]<Date() And [CA3 Received] Is Null,"Past Due",
IIf([CA4 Due]<Date() And [CA4 Received] Is Null,"Past
Due","Received"))))))))))))))<>"received") AND (([Job
Tracking].[Cancelled Job])<>Yes))
ORDER BY [Job Tracking].[Traffic Person], [Job
Tracking].Job, [Job Tracking].Version;

 

[Guest]

Looking at the Where conditions, the first series of IF conditions if true
would result in "Due Today". Instead of separate IIFs you can put the 4
conditions in one IIF using 'OR':

IIf(([CA1 Due]=Date() And [CA1 Received] Is Null) OR ([CA2 Due]=Date() And
[CA2 Received] Is Null) OR ([CA3 Due]=Date() And [CA3 Received] Is Null) OR
([CA4 Due]=Date() And [CA4 Received] Is Null),"Due Today",....

Now you end up with only 4 IIF tests, "Due Today", "Due Tomorrow", "Past
Due" and "Received".

Here is the WHERE and ORDER BY:

'----
WHERE (IIf(([CA1 Due]=Date() And [CA1 Received] Is Null) OR ([CA2
Due]=Date() And [CA2 Received] Is Null) or ([CA3 Due]=Date() And [CA3
Received] Is Null) or ([CA4 Due]=Date() And [CA4 Received] Is Null),"Due
Today", IIf(([CA1 Due] Between Date()+1 And Date()+2 And [CA1 Received] Is
Null) or ([CA2 Due] Between Date()+1 And Date()+2 And [CA2 Received] Is Null)
or ([CA3 Due] Between Date()+1 And Date()+2 And [CA3 Received] Is Null) or
([CA4 Due] Between Date()+1 And Date()+2 And [CA4 Received] Is Null) ,"Due
Tomorrow", IIf(([CA1 Due]<Date() And [CA1 Received] Is Null) or ([CA2
Due]<Date() And [CA2 Received] Is Null) or ([CA3 Due]<Date() And [CA3
Received] Is Null) or ([CA4 Due]<Date() And [CA4 Received] Is Null),"Past
Due", IIf([CA1 Received] Is Not Null And [CA2 Received] Is Not Null And [CA3
Received] Is Not Null And [CA4 Received] Is Not Null,"Received",
"Received")))<>"received") AND (([Job Tracking].[Cancelled Job])<>Yes)) ORDER
BY [Job Tracking].[Traffic Person], [Job Tracking].Job, [Job
Tracking].Version ;
'----

Other things I noticed/changed:

There were two tests for

IIf([CA1 Received] And [CA2 Received] And [CA3 Received] And [CA4 Received]
Is Not Null,"Received",

I deleted one of them.

I also changed the above IFF test to

IIf([CA1 Received] Is Not Null And [CA2 Received] Is Not Null And [CA3
Received] Is Not Null And [CA4 Received] Is Not Null,"Received",

If you have
IIF([CA1 Received] AND [CA2 Received] AND [CA3 Received] AND [CA4
Received] Is Not Null,....
it is the same as
IIF([CA1 Received]=TRUE AND [CA2 Received]=TRUE AND [CA3 Received]=TRUE
AND [CA4 Received] Is Not Null,.....

I don't think this is what you want..?

One thing I don't understand is, in evaluating the WHERE clause, there are
only four results:

("Due Today" <> "received") AND (([Job Tracking].[Cancelled Job])<>Yes))
("Due Tomorrow" <> "received") AND (([Job Tracking].[Cancelled Job])<>Yes))
("Past Due" <> "received") AND (([Job Tracking].[Cancelled Job])<>Yes))
("Received" <> "received") AND (([Job Tracking].[Cancelled Job])<>Yes))

which will then evaluate to either:

FALSE AND (([Job Tracking].[Cancelled Job])<>Yes))

or

TRUE AND (([Job Tracking].[Cancelled Job])<>Yes))


Maybe I am missing something.... the only time a record will not be returned
is when [CA1 Received], [CA2 Received], [CA3 Received] and [CA4 Received] all
have dates entered AND ([Job Tracking].[Cancelled Job])<>Yes).

Would you get the same recordset if the WHERE clause was:

..... WHERE [CA1 Received] Is Null AND [CA2 Received] Is Null AND [CA3
Received] Is Null AND [CA4 Received] Is Null AND [Job Tracking].[Cancelled
Job]<>Yes ....

?

HTH
Steve
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

Hello everyone,

I have this SQL code in a Select Query that basically
looks to see if a received date has been entered in its
appropriate field. If there is no date in that received
field then it determines if the job is
due "today", "tomorrow" or if its "past due". If a date
has been entered then it's marked as "received".
The problem I'm running into is when I try to add another
level field programming to the SQL statement. Access
tells me that the expression is too complex. Can this
statement be simplified, and if so, how can I add more
fields to the mix?
Any help (in simple programming terms - I'm not an
advanced coder) would be greatly appreciated.

Thanks - Ron
See code below.
-----------------------------
SELECT [Job Tracking].[Traffic Person], [Job Tracking].
[Status of Job], [Job Tracking].Job, [Job
Tracking].Version, [Job Tracking].Product, [Job
Tracking].Source, [Job Tracking].[Ca1 Due], [Job Tracking].
[CA1 Received], [Job Tracking].[Ca2 Due], [Job Tracking].
[CA2 Received], [Job Tracking].[Ca3 Due], [Job Tracking].
[CA3 Received], [Job Tracking].[Ca4 Due], [Job Tracking].
[CA4 Received], [Job Tracking].[Start Date], [Job
Tracking].Category, [Job Tracking].Creative, [Job
Tracking].Media
FROM [Job Tracking] WHERE
(((IIf([CA1 Due]=Date() And [CA1 Received] Is Null,"Due
Today",
IIf([CA2 Due]=Date() And [CA2 Received] Is Null,"Due
Today",
IIf([CA3 Due]=Date() And [CA3 Received] Is Null,"Due
Today",
IIf([CA4 Due]=Date() And [CA4 Received] Is Null,"Due
Today",
IIf([CA1 Received] And [CA2 Received] And [CA3 Received]
And [CA4 Received] Is Not Null,"Received",
IIf([CA1 Due] Between Date()+1 And Date()+2 And [CA1
Received] Is Null,"Due Tomorrow",
IIf([CA2 Due] Between Date()+1 And Date()+2 And [CA2
Received] Is Null,"Due Tomorrow",
IIf([CA3 Due] Between Date()+1 And Date()+2 And [CA3
Received] Is Null,"Due Tomorrow",
IIf([CA4 Due] Between Date()+1 And Date()+2 And [CA4
Received] Is Null,"Due Tomorrow",
IIf([CA1 Received] And [CA2 Received] And [CA3 Received]
And [CA4 Received] Is Not Null,"Received",
IIf([CA1 Due]<Date() And [CA1 Received] Is Null,"Past Due",
IIf([CA2 Due]<Date() And [CA2 Received] Is Null,"Past Due",
IIf([CA3 Due]<Date() And [CA3 Received] Is Null,"Past Due",
IIf([CA4 Due]<Date() And [CA4 Received] Is Null,"Past
Due","Received"))))))))))))))<>"received") AND (([Job
Tracking].[Cancelled Job])<>Yes))
ORDER BY [Job Tracking].[Traffic Person], [Job
Tracking].Job, [Job Tracking].Version;

 

[Michel Walsh]

Hi,


It would be preferable to normalized your data. As I understand the logic,
just ONE field should have a value, right? If so, I think something along
the following may perform well under Access 2003, even if I haven't tested
the [ ] used in the virtual table:

---------------------
SELECT *
FROM
( SELECT *,
SWITCH( [CA1 Received] , [CA1 Due],
[CA2 Received] , [CA2 Due],
[CA3 Received] , [CA3 Due],
[CA4 Received] , [CA4 Due] ,
-1 , Null) As theDate
FROM [Job tracking] ) As x
WHERE
SWITCH( x.theDate Is null, "Received",
x.TheDate <= Date(), "Past Due",
x.TheDate=Date(), "Due today",
x.TheDate <= Date() +2, "tomorrow",
-1, "unscheduled")

ORDER BY whatever
------------------


If the inner most SELECT fails, make it a saved query and replace the final
statement with:

--------------------------
SELECT *
FROM savedQuery As x
WHERE
SWITCH( x.theDate Is null, "Received",
x.TheDate < Date(), "Past Due",
x.TheDate = Date(), "Due today",
x.TheDate <= Date() +1, "Tomorrow",
-1, "Unscheduled")

ORDER BY whatever
------------------------



Note also that I changed a couple of details, like having an Unscheduled
rather than a "Received" by default.

It would definitively be preferable to use a normal table, even for your OWN
work, independently of the source of data (which is probably not
normalized). It would be more secure. The code I supplied won't detect the
case where both [CA1 Received] and [CA2 Received] are true, which, I
assume, is an error.


Hoping it may help,
Vanderghast, Access MVP

Hello everyone,

I have this SQL code in a Select Query that basically
looks to see if a received date has been entered in its
appropriate field. If there is no date in that received
field then it determines if the job is
due "today", "tomorrow" or if its "past due". If a date
has been entered then it's marked as "received".
The problem I'm running into is when I try to add another
level field programming to the SQL statement. Access
tells me that the expression is too complex. Can this
statement be simplified, and if so, how can I add more
fields to the mix?
Any help (in simple programming terms - I'm not an
advanced coder) would be greatly appreciated.

Thanks - Ron
See code below.
-----------------------------
SELECT [Job Tracking].[Traffic Person], [Job Tracking].
[Status of Job], [Job Tracking].Job, [Job
Tracking].Version, [Job Tracking].Product, [Job
Tracking].Source, [Job Tracking].[Ca1 Due], [Job Tracking].
[CA1 Received], [Job Tracking].[Ca2 Due], [Job Tracking].
[CA2 Received], [Job Tracking].[Ca3 Due], [Job Tracking].
[CA3 Received], [Job Tracking].[Ca4 Due], [Job Tracking].
[CA4 Received], [Job Tracking].[Start Date], [Job
Tracking].Category, [Job Tracking].Creative, [Job
Tracking].Media
FROM [Job Tracking] WHERE
(((IIf([CA1 Due]=Date() And [CA1 Received] Is Null,"Due
Today",
IIf([CA2 Due]=Date() And [CA2 Received] Is Null,"Due
Today",
IIf([CA3 Due]=Date() And [CA3 Received] Is Null,"Due
Today",
IIf([CA4 Due]=Date() And [CA4 Received] Is Null,"Due
Today",
IIf([CA1 Received] And [CA2 Received] And [CA3 Received]
And [CA4 Received] Is Not Null,"Received",
IIf([CA1 Due] Between Date()+1 And Date()+2 And [CA1
Received] Is Null,"Due Tomorrow",
IIf([CA2 Due] Between Date()+1 And Date()+2 And [CA2
Received] Is Null,"Due Tomorrow",
IIf([CA3 Due] Between Date()+1 And Date()+2 And [CA3
Received] Is Null,"Due Tomorrow",
IIf([CA4 Due] Between Date()+1 And Date()+2 And [CA4
Received] Is Null,"Due Tomorrow",
IIf([CA1 Received] And [CA2 Received] And [CA3 Received]
And [CA4 Received] Is Not Null,"Received",
IIf([CA1 Due]<Date() And [CA1 Received] Is Null,"Past Due",
IIf([CA2 Due]<Date() And [CA2 Received] Is Null,"Past Due",
IIf([CA3 Due]<Date() And [CA3 Received] Is Null,"Past Due",
IIf([CA4 Due]<Date() And [CA4 Received] Is Null,"Past
Due","Received"))))))))))))))<>"received") AND (([Job
Tracking].[Cancelled Job])<>Yes))
ORDER BY [Job Tracking].[Traffic Person], [Job
Tracking].Job, [Job Tracking].Version;