Tough modification of existing SQL query

Autoeng

Autoeng

Why me?
Local time
Today, 03:58
Joined
Aug 13, 2002
Messages
1,302
I’m trying to modify an existing SQL query that calculates actual working days between start date in tblECN and end date in tblECNDetail to do the same between start date in tblECNDetail and end date in tblECNDetail (same table).

Where I’ve gotten into a bind is with the joins of the two tables that are needed in the first query but not in the second as both fields come from the same query.

In the current working query I join about tblECN.ECNID and tblECNDetail.ECNID. In the new query I don’t think I need to create these joins at all as BOMEntryStart and BOMEntryEnd both come from the same table (tblECNDetail). I’ve bolded all references to the table no longer required for join but I don’t know which join statements I can be rid of. When trying to save I get an error in the FROM statement if I leave as is. If I get rid of the join statement I get an error

working query
SELECT [tblECN].[ECNID], [tblECN].[RelDate], [tblECNDetail].[PendDate], DateDiff("d",[tblECN].[RelDate],[tblECNDetail].[PendDate],2)- IIf([HCOUNT]>0,[HCOUNT],0) AS DaysDiff, Int([daysdiff]/7) AS Weeks, [daysdiff]-[weeks]*3+IIf(Weekday([tblECNDetail].[PendDate],2)>5,5-Weekday([tblECNDetail].[PendDate],2),0)+IIf(Weekday([tblECN].[RelDate],2)=6,1,0)-IIf(Weekday([tblECN].[RelDate],2)>Weekday([tblECNDetail].[PendDate],2),2,0)+IIf([tblECN].[RelDate]=[tblECNDetail].[PendDate],1,0) AS weekdays, Query5.HCOUNT
FROM (tblECN INNER JOIN tblECNDetail ON [tblECN].[ECNID] = [tblECNDetail].[ECNID]) LEFT JOIN [SELECT Query4.[tblECN].[ECNID], Sum(Query4.TEMPVAL) AS HCOUNT
FROM (SELECT [tblECN].[ECNID], 1 AS TEMPVAL
FROM tblHoliday, [tblECN] INNER JOIN [tblECNDetail] ON [tblECN].[ECNID] = [tblECNDetail].[ECNID]
WHERE (((tblHoliday.Holiday) Between [tblECN].[RelDate] And [tblECNDetail].[PendDate]))) AS Query4
GROUP BY Query4.[tblECN].[ECNID]]. AS Query5 ON [tblECN].[ECNID] = Query5.[tblECN].[ECNID]
WHERE ((([tblECNDetail].[PendDate]) Between [Forms]![frmDates]![StartDate] And [Forms]![frmDates]![StopDate]) AND (([tblECN].[DoNotProcess])<>"Do Not Process"));


Not working query
SELECT [tblECN].[ECNID], [tblECNDetail].[BOMEntryStart], [tblECNDetail].[BOMEntryEnd], DateDiff("d",[tblECNDetail].[BOMEntryStart],[tblECNDetail].[BOMEntryEnd],2)- IIf([HCOUNT]>0,[HCOUNT],0) AS DaysDiff, Int([daysdiff]/7) AS Weeks, [daysdiff]-[weeks]*3+IIf(Weekday([tblECNDetail].[BOMEntryEnd],2)>5,5-Weekday([tblECNDetail].[BOMEntryEnd],2),0)+IIf(Weekday([tblECNDetail].[BOMEntryStart],2)=6,1,0)-IIf(Weekday([tblECNDetail].[BOMEntryStart],2)>Weekday([tblECNDetail].[BOMEntryEnd],2),2,0)+IIf([tblECNDetail].[BOMEntryStart]=[tblECNDetail].[BOMEntryEnd],1,0) AS weekdays, Query5.HCOUNT
FROM (tblECN INNER JOIN tblECNDetail ON [tblECN].[ECNID] = [tblECNDetail].[ECNID]) LEFT JOIN [SELECT Query4.[tblECN].[ECNID], Sum(Query4.TEMPVAL) AS HCOUNT
FROM (SELECT [tblECN].[ECNID], 1 AS TEMPVAL
FROM tblHoliday, [tblECN] INNER JOIN [tblECNDetail] ON [tblECN].[ECNID] = [tblECNDetail].[ECNID]
WHERE (((tblHoliday.Holiday) Between [tblECNDetail].[BOMEntryStart] And [tblECNDetail].[BOMEntryEnd]))) AS Query4
GROUP BY Query4.[tblECN].[ECNID]]. AS Query5 ON [tblECN].[ECNID] = Query5.[tblECN].[ECNID]
WHERE ((([tblECNDetail].[BOMEntryEnd]) Between [Forms]![frmDates]![StartDate] And [Forms]![frmDates]![StopDate]) AND (([tblECN].[DoNotProcess])<>"Do Not Process"));

Any help is greatly approciated.
 
Last edited:
OK Hmz... Subselects??? *grmbl* and totaly unreadable too... lets make it readable first....
Autoeng said:
working query
Code: 
SELECT [tblECN].[ECNID]
,      [tblECN].[RelDate]
,      [tblECNDetail].[PendDate]
,      DateDiff("d",[tblECN].[RelDate],[tblECNDetail].[PendDate],2)- IIf([HCOUNT]>0,[HCOUNT],0) AS DaysDiff
,      Int([daysdiff]/7) AS Weeks
,      [daysdiff]-[weeks]*3+IIf(Weekday([tblECNDetail].[PendDate],2)>5
                               ,5-Weekday([tblECNDetail].[PendDate],2)
                               ,0)
                           +IIf(Weekday([tblECN].[RelDate],2)=6,1,0)
                           -IIf(Weekday([tblECN].[RelDate],2)>Weekday([tblECNDetail].[PendDate],2),2,0)
                           +IIf([tblECN].[RelDate]=[tblECNDetail].[PendDate],1,0) AS weekdays
,      Query5.HCOUNT
FROM (           tblECN 
      INNER JOIN tblECNDetail ON [tblECN].[ECNID] = [tblECNDetail].[ECNID]) 
       LEFT JOIN [SELECT Query4.[tblECN].[ECNID]
                  ,      Sum(Query4.TEMPVAL) AS HCOUNT
                  FROM  (SELECT [tblECN].[ECNID]
                         ,      1 AS TEMPVAL
                         FROM   tblHoliday
                         ,                 [tblECN] 
                                INNER JOIN [tblECNDetail] ON [tblECN].[ECNID] = [tblECNDetail].[ECNID]
                         WHERE  tblHoliday.Holiday Between [tblECN].[RelDate] And [tblECNDetail].[PendDate]) AS Query4
                  GROUP BY Query4.[tblECN].[ECNID]]. AS Query5 ON [tblECN].[ECNID] = Query5.[tblECN].[ECNID]
WHERE [tblECNDetail].[PendDate] Between [Forms]![frmDates]![StartDate] 
                                    And [Forms]![frmDates]![StopDate]
AND   [tblECN].[DoNotProcess] <> "Do Not Process";

Not working query
Code: 
SELECT [b][tblECN].[ECNID][/b]
,      [tblECNDetail].[BOMEntryStart]
,      [tblECNDetail].[BOMEntryEnd]
,      DateDiff("d",[tblECNDetail].[BOMEntryStart],[tblECNDetail].[BOMEntryEnd],2)- IIf([HCOUNT]>0,[HCOUNT],0) AS DaysDiff
,      Int([daysdiff]/7) AS Weeks
,      [daysdiff]-[weeks]*3+IIf(Weekday([tblECNDetail].[BOMEntryEnd],2)>5
                              ,5-Weekday([tblECNDetail].[BOMEntryEnd],2)
                              ,0)
                           +IIf(Weekday([tblECNDetail].[BOMEntryStart],2)=6,1,0)
                           -IIf(Weekday([tblECNDetail].[BOMEntryStart],2)>Weekday([tblECNDetail].[BOMEntryEnd],2),2,0)
                           +IIf([tblECNDetail].[BOMEntryStart]=[tblECNDetail].[BOMEntryEnd],1,0) AS weekdays
,      Query5.HCOUNT
FROM ([b]tblECN[/b] 
      INNER JOIN tblECNDetail ON [b][tblECN].[ECNID][/b] = [tblECNDetail].[ECNID]) 
      LEFT JOIN [SELECT Query4.[b][tblECN].[ECNID][/b]
                 ,      Sum(Query4.TEMPVAL) AS HCOUNT
                 FROM  (SELECT [b][tblECN].[ECNID][/b]
                        ,      1 AS TEMPVAL
                        FROM   tblHoliday
                        ,              [b][tblECN][/b] 
                               INNER JOIN [tblECNDetail] ON [b][tblECN].[ECNID][/b] = [tblECNDetail].[ECNID]
                        WHERE  tblHoliday.Holiday Between [tblECNDetail].[BOMEntryStart] And [tblECNDetail].[BOMEntryEnd]) AS Query4
                 GROUP BY Query4.[b][tblECN].[ECNID][/b]]. AS Query5 ON [b][tblECN].[ECNID][/b] = Query5.[b][tblECN].[ECNID][/b]
WHERE [tblECNDetail].[BOMEntryEnd]) Between [Forms]![frmDates]![StartDate] And [Forms]![frmDates]![StopDate]
AND   [tblECN].[DoNotProcess]<>"Do Not Process";
Click to expand...
Now that is way more readable (to me anyway....) now that we/I have it readable...

Isnt it as easy as simply removing the tblECN?? something or other like this:
Code: 
SELECT [b][tblECNDetail].[ECNID][/b]
,      [tblECNDetail].[BOMEntryStart]
,      [tblECNDetail].[BOMEntryEnd]
,      DateDiff("d",[tblECNDetail].[BOMEntryStart],[tblECNDetail].[BOMEntryEnd],2)- IIf([HCOUNT]>0,[HCOUNT],0) AS DaysDiff
,      Int([daysdiff]/7) AS Weeks
,      [daysdiff]-[weeks]*3+IIf(Weekday([tblECNDetail].[BOMEntryEnd],2)>5
                              ,5-Weekday([tblECNDetail].[BOMEntryEnd],2)
                              ,0)
                           +IIf(Weekday([tblECNDetail].[BOMEntryStart],2)=6,1,0)
                           -IIf(Weekday([tblECNDetail].[BOMEntryStart],2)>Weekday([tblECNDetail].[BOMEntryEnd],2),2,0)
                           +IIf([tblECNDetail].[BOMEntryStart]=[tblECNDetail].[BOMEntryEnd],1,0) AS weekdays
,      Query5.HCOUNT
FROM (tblECNDetail 
      LEFT JOIN [SELECT Query4.[b][tblECNDetail].[ECNID][/b]
                 ,      Sum(Query4.TEMPVAL) AS HCOUNT
                 FROM  (SELECT [b][tblECNDetail].[ECNID][/b]
                        ,      1 AS TEMPVAL
                        FROM   tblHoliday
                        ,      [tblECNDetail]
                        WHERE  tblHoliday.Holiday Between [tblECNDetail].[BOMEntryStart] And [tblECNDetail].[BOMEntryEnd]) AS Query4
                 GROUP BY Query4.[b][tblECNDetail].[ECNID][/b]]. AS Query5 ON [b][tblECNDetail].[ECNID][/b] = Query5.[b][ECNID][/b]
WHERE [tblECNDetail].[BOMEntryEnd]) Between [Forms]![frmDates]![StartDate] And [Forms]![frmDates]![StopDate]
AND   [tblECN].[DoNotProcess]<>"Do Not Process";
 
Syntax error in FROM clause...

Code: 
...
FROM (tblECNDetail 
      LEFT JOIN [SELECT Query4.[tblECNDetail].[ECNID]
                 ,
 
I forgot crappy access cannot work with its own SQL... It changes the () around a subquery to [] some/most-times but if you put it in like that it doesnt understand....

Try this:
Code: 
SELECT [b][tblECNDetail].[ECNID][/b]
,      [tblECNDetail].[BOMEntryStart]
,      [tblECNDetail].[BOMEntryEnd]
,      DateDiff("d",[tblECNDetail].[BOMEntryStart],[tblECNDetail].[BOMEntryEnd],2)- IIf([HCOUNT]>0,[HCOUNT],0) AS DaysDiff
,      Int([daysdiff]/7) AS Weeks
,      [daysdiff]-[weeks]*3+IIf(Weekday([tblECNDetail].[BOMEntryEnd],2)>5
                              ,5-Weekday([tblECNDetail].[BOMEntryEnd],2)
                              ,0)
                           +IIf(Weekday([tblECNDetail].[BOMEntryStart],2)=6,1,0)
                           -IIf(Weekday([tblECNDetail].[BOMEntryStart],2)>Weekday([tblECNDetail].[BOMEntryEnd],2),2,0)
                           +IIf([tblECNDetail].[BOMEntryStart]=[tblECNDetail].[BOMEntryEnd],1,0) AS weekdays
,      Query5.HCOUNT
FROM (tblECNDetail 
      LEFT JOIN (SELECT Query4.[b][tblECNDetail].[ECNID][/b]
                 ,      Sum(Query4.TEMPVAL) AS HCOUNT
                 FROM  (SELECT [b][tblECNDetail].[ECNID][/b]
                        ,      1 AS TEMPVAL
                        FROM   tblHoliday
                        ,      [tblECNDetail]
                        WHERE  tblHoliday.Holiday Between [tblECNDetail].[BOMEntryStart] And [tblECNDetail].[BOMEntryEnd]) AS Query4
                 GROUP BY Query4.[b][tblECNDetail].[ECNID][/b]) AS Query5 ON [b][tblECNDetail].[ECNID][/b] = Query5.[b][ECNID][/b]
WHERE [tblECNDetail].[BOMEntryEnd]) Between [Forms]![frmDates]![StartDate] And [Forms]![frmDates]![StopDate]
AND   [tblECN].[DoNotProcess]<>"Do Not Process";
Still no guarantee as I cannot test it...
There is one problem I see right now... which is the last line, tblECN isnt used anymore. You want that table removed but need it?? :confused:
 
I'll give it a try on Monday. tblECN still exist and records that = DoNotProcess are not to be included in the results of the old and new query so hopefully it won't cause any problems.

Thanks for your help,
 
The table doesnt excist inside this query (i.e. I dont see it anywhere in the where clause)
 
namliam said:
The table doesnt excist inside this query (i.e. I dont see it anywhere in the where clause)
Click to expand...

Of course you were right. Syntax error in JOIN operation. I need to bring tblECN, field DONOTPROCESS into the query so that records that <> Do Not Process are not included. Will I have to create a secondary query to do that or can I somehow do it in this one?

Update -- I removed AND [tblECN].[DoNotProcess]<>"Do Not Process" and still got the JOIN error. I was going to try a secondary query just to see if I was getting the result expected. I now wonder if there isn't an error besides this one.

Thank you for your help. Very close now...
 
Reading this again, doesnt your original query just work?? :eek:

I think it should...
 
Not sure what you mean. The original query does work but I am trying to modify it to calculate the work days between two different fields than the first query. Still not able to get the modified one to work.
 
Code: 
SELECT [b][tblECN].[ECNID][/b]
,      [tblECNDetail].[BOMEntryStart]
,      [tblECNDetail].[BOMEntryEnd]
,      DateDiff("d",[tblECNDetail].[BOMEntryStart],[tblECNDetail].[BOMEntryEnd],2)- IIf([HCOUNT]>0,[HCOUNT],0) AS DaysDiff
,      Int([daysdiff]/7) AS Weeks
,      [daysdiff]-[weeks]*3+IIf(Weekday([tblECNDetail].[BOMEntryEnd],2)>5
                              ,5-Weekday([tblECNDetail].[BOMEntryEnd],2)
                              ,0)
                           +IIf(Weekday([tblECNDetail].[BOMEntryStart],2)=6,1,0)
                           -IIf(Weekday([tblECNDetail].[BOMEntryStart],2)>Weekday([tblECNDetail].[BOMEntryEnd],2),2,0)
                           +IIf([tblECNDetail].[BOMEntryStart]=[tblECNDetail].[BOMEntryEnd],1,0) AS weekdays
,      Query5.HCOUNT
FROM ([b]tblECN[/b] 
      INNER JOIN tblECNDetail ON [b][tblECN].[ECNID][/b] = [tblECNDetail].[ECNID]) 
      LEFT JOIN [SELECT Query4.[b][tblECN].[ECNID][/b]
                 ,      Sum(Query4.TEMPVAL) AS HCOUNT
                 FROM  (SELECT [b][tblECN].[ECNID][/b]
                        ,      1 AS TEMPVAL
                        FROM   tblHoliday
                        ,              [b][tblECN][/b] 
                               INNER JOIN [tblECNDetail] ON [b][tblECN].[ECNID][/b] = [tblECNDetail].[ECNID]
                        WHERE  tblHoliday.Holiday Between [tblECNDetail].[BOMEntryStart] And [tblECNDetail].[BOMEntryEnd]) AS Query4
                 GROUP BY Query4.[b][tblECN].[ECNID][/b]]. AS Query5 ON [b][tblECN].[ECNID][/b] = Query5.[b][tblECN].[ECNID][/b]
WHERE [tblECNDetail].[BOMEntryEnd]) Between [Forms]![frmDates]![StartDate] And [Forms]![frmDates]![StopDate]
AND   [tblECN].[DoNotProcess]<>"Do Not Process";
I was talking about this query... Or what is actually wrong with this one??
You need the ECN table to use the "DoNotProcess" so... why wont this one do what you need it to?
 
Autoeng said:
Syntax error in FROM clause same in post #4.
Click to expand...

Solution is the same as well.... Replace the []. around the subquery and replace it by ()
Code: 
SELECT [tblECN].[ECNID]
,      [tblECNDetail].[BOMEntryStart]
,      [tblECNDetail].[BOMEntryEnd]
,      DateDiff("d",[tblECNDetail].[BOMEntryStart],[tblECNDetail].[BOMEntryEnd],2)- IIf([HCOUNT]>0,[HCOUNT],0) AS DaysDiff
,      Int([daysdiff]/7) AS Weeks
,      [daysdiff]-[weeks]*3+IIf(Weekday([tblECNDetail].[BOMEntryEnd],2)>5
                              ,5-Weekday([tblECNDetail].[BOMEntryEnd],2)
                              ,0)
                           +IIf(Weekday([tblECNDetail].[BOMEntryStart],2)=6,1,0)
                           -IIf(Weekday([tblECNDetail].[BOMEntryStart],2)>Weekday([tblECNDetail].[BOMEntryEnd],2),2,0)
                           +IIf([tblECNDetail].[BOMEntryStart]=[tblECNDetail].[BOMEntryEnd],1,0) AS weekdays
,      Query5.HCOUNT
FROM (tblECN 
      INNER JOIN tblECNDetail ON [tblECN].[ECNID] = [tblECNDetail].[ECNID]) 
      LEFT JOIN (SELECT Query4.[tblECN].[ECNID]
                 ,      Sum(Query4.TEMPVAL) AS HCOUNT
                 FROM  (SELECT [tblECN].[ECNID]
                        ,      1 AS TEMPVAL
                        FROM   tblHoliday
                        ,              [tblECN] 
                               INNER JOIN [tblECNDetail] ON [tblECN].[ECNID] = [tblECNDetail].[ECNID]
                        WHERE  tblHoliday.Holiday Between [tblECNDetail].[BOMEntryStart] And [tblECNDetail].[BOMEntryEnd]) AS Query4
                 GROUP BY Query4.[tblECN].[ECNID]) AS Query5 ON [tblECN].[ECNID] = Query5.[tblECN].[ECNID]
WHERE [tblECNDetail].[BOMEntryEnd]) Between [Forms]![frmDates]![StartDate] And [Forms]![frmDates]![StopDate]
AND   [tblECN].[DoNotProcess]<>"Do Not Process";
 
Thank You! Finally got it. Sorry I missed the bracket change.

Thank you again for your help!
 
You must log in or register to reply here.

Similar threads

M
Optimized query
Replies
3
Views
566
plog
P
A
Crosstab query, Can I "force" rows? - left join?
Replies
1
Views
2,558
Pat Hartman
P
mattkorguk
Converting query to SQL pass through issue
Replies
3
Views
1,514
mattkorguk
mattkorguk
Autoeng
A tough DateDiff SQL query
Replies
11
Views
2,779
Autoeng
Autoeng
Autoeng
DateDiff query not returning correct results
Replies
7
Views
2,632
Autoeng
Autoeng

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom