1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
|
SELECT deliv_table.[Deliverable reference] , WP, [Product/deliverable description], customer, [% Work], 'NEW'
AS previousPlanDate, IIf(Not IsNull([Reference Date])
AND IsNull([Postponed & Agreed])
AND IsNull([Forecast not agreed]),[Reference Date],
IIf( (IsNull([Reference Date])
AND Not IsNull([Postponed & Agreed])
AND IsNull([Forecast not agreed]))
OR (not IsNull([Reference Date])
AND Not IsNull([Postponed & Agreed])
AND IsNull([Forecast not agreed])),[Postponed & Agreed],[Forecast not agreed])) AS PostOrNewDate,[Deliverable Status],
IIf( (Not IsNull([Reference Date])
AND IsNull([Postponed & Agreed])
AND IsNull([Forecast not agreed])) OR (IsNull([Reference Date]) AND not IsNull([Postponed & Agreed])
AND IsNull([Forecast not agreed]))
OR (not IsNull([Reference Date])
AND Not IsNull([Postponed & Agreed])
AND IsNull([Forecast not agreed])) ,'Yes','No') AS customerAgreed,deliv_table.[Reporting Comment]
FROM Deliv_Table
WHERE cstr([Deliverable reference])
NOT IN (SELECT cstr([Output Data Reference])
FROM output_data_Table)
AND isNull(Deliv_Table.[Achieved])
ORDER BY WP , [Deliverable reference]
UNION SELECT deliv_table.[Deliverable reference] , WP, [Product/deliverable description], customer, [% Work],
IIf(Not IsNull(output_data_Table.[Postponed and Agreed date]), output_data_Table.[Postponed and Agreed date], output_data_Table.[Reference Date])
AS previousPlanDate, Deliv_Table.[Reference date]
AS PostOrNewDate, Deliv_Table.[Deliverable Status], 'Yes'
AS customerAgeed, deliv_table.[Reporting Comment]
FROM Deliv_Table, output_data_Table
WHERE cstr(output_data_Table.[Output Data Reference]) = cstr(Deliv_Table.[Deliverable reference])
AND ((Format(cdate(Deliv_Table.[Reference Date]),'dd-mmm-yy') <> Format(cdate(output_data_Table.[Reference Date]),'dd-mmm-yy')
OR (Not isNull(Deliv_Table.[Reference Date])
AND IsNull(output_data_Table.[Reference Date])))
And ((Format(cdate(Deliv_Table.[Postponed & Agreed]),'dd-mmm-yy') = Format(cdate(output_data_Table.[Postponed and Agreed date]),'dd-mmm-yy'))
OR (isNull(Deliv_Table.[Postponed & Agreed])
AND IsNull(output_data_Table.[Postponed and Agreed date])))
And ((Format(cdate(Deliv_Table.[Forecast not agreed]),'dd-mmm-yy') = Format(cdate(output_data_Table.[Forecast not agreed date]),'dd-mmm-yy'))
OR (isNull(Deliv_Table.[Forecast not agreed])
AND IsNull(output_data_Table.[Forecast not agreed date]))))
AND isNull(Deliv_Table.[Achieved])
ORDER BY WP , [Deliverable reference]
SELECT deliv_table.[Deliverable reference] , WP, [Product/deliverable description], customer, [Achieved], [Domain QI], [Customer QI],
[Deliverable Status], deliv_table.[Reporting Comment]
FROM deliv_table WHERE Not isnull([Achieved])
AND [Achieved] >= #04/01/2006# AND [Achieved] <= #04/30/2006# ORDER BY WP , [Deliverable reference]
SELECT deliv_table.[Deliverable reference] , WP, [Product/deliverable description], customer, [Achieved], [Domain QI], [Customer QI],
[Deliverable Status], deliv_table.[Reporting Comment]
FROM deliv_table WHERE Not isnull([Achieved])
AND [Achieved] >= #04/01/2006# AND [Achieved]<= #04/30/2006# ORDER BY WP , [Deliverable reference] |
