Works .. but not quite what I am looking for
The suggested code exports the query as a text file but not in SQL view.
I am looking for the code as SQL (as seen in design / view as SQL) rather than the output given by the code above.
Any further suggestions?
e.g.
Wanted:
PARAMETERS LowDate DateTime, HighDate DateTime;
SELECT tblPGPPerformance.ID, tblPGPPerformance.ProductGroup, tblPGPPerformance.[Product Description], tblPGPPerformance.[Pack Size], tblPGPPerformance.[Process MfgPro], tblPGPPerformance.PreferredRoute, tblPGPPerformance.OutputPerShift, tblPGPPerformance.ManningDirect, tblPGPPerformance.ManningIndirect, tblPGPPerformance.BillingRate, tblPGPPerformance.StartDate, tblPGPPerformance.EndDate, tblPGPPerformance.TheoreticalMaxOutPerHour, tblPGPPerformance.SubProductGroup, tblPGPPerformance.SubProcess, tblPGPPerformance.[Fixed Indirect Hours], tblPGPPerformance.RoutingPercentage, IIf([tblpgpvariations].[startdate] Is Null,[lowdate]-(Weekday([lowdate],2)-1),[tblpgpvariations].[startdate]) AS VariationStartDate, IIf([tblpgpvariations].[enddate] Is Null,[highdate]+(7-(Weekday([highdate],2))),[tblpgpvariations].[enddate]) AS VariationEndDate, Weekday([lowdate],2) AS LowDayOfWeek, IIf([tblpgpvariations].[PercentOfRouting] Is Null,1,[tblpgpvariations].[PercentOfRouting]) AS RoutingVariationPercent, tblPGPVariations.RoutingType, tblPGPVariations.RoutingReason
FROM tblPGPPerformance LEFT JOIN tblPGPVariations ON (tblPGPPerformance.[Process MfgPro] = tblPGPVariations.[Process MfgPro]) AND (tblPGPPerformance.ProductGroup = tblPGPVariations.ProductGroup);
rather than
Code suggested writes as:
Operation = 1
Option = 0
Begin InputTables
Name ="tblPGPVariations"
Name ="tblPGPPerformance"
End
Begin OutputColumns
Expression ="tblPGPPerformance.ID"
Expression ="tblPGPPerformance.ProductGroup"
Expression ="tblPGPPerformance.[Product Description]"
Expression ="tblPGPPerformance.[Pack Size]"
Expression ="tblPGPPerformance.[Process MfgPro]"
Expression ="tblPGPPerformance.PreferredRoute"
Expression ="tblPGPPerformance.OutputPerShift"
Expression ="tblPGPPerformance.ManningDirect"
Expression ="tblPGPPerformance.ManningIndirect"
Expression ="tblPGPPerformance.BillingRate"
Expression ="tblPGPPerformance.StartDate"
Expression ="tblPGPPerformance.EndDate"
Expression ="tblPGPPerformance.TheoreticalMaxOutPerHour"
Expression ="tblPGPPerformance.SubProductGroup"
Expression ="tblPGPPerformance.SubProcess"
Expression ="tblPGPPerformance.[Fixed Indirect Hours]"
Expression ="tblPGPPerformance.RoutingPercentage"
Alias ="VariationStartDate"
Expression ="IIf([tblpgpvariations].[startdate] Is Null,[lowdate]-(Weekday([lowdate],2)-1),[t"
"blpgpvariations].[startdate])"
Alias ="VariationEndDate"
Expression ="IIf([tblpgpvariations].[enddate] Is Null,[highdate]+(7-(Weekday([highdate],2))),"
"[tblpgpvariations].[enddate])"
Alias ="LowDayOfWeek"
Expression ="Weekday([lowdate],2)"
Alias ="RoutingVariationPercent"
Expression ="IIf([tblpgpvariations].[PercentOfRouting] Is Null,1,[tblpgpvariations].[PercentO"
"fRouting])"
Expression ="tblPGPVariations.RoutingType"
Expression ="tblPGPVariations.RoutingReason"
End
Begin Parameters
Name ="LowDate"
Flag = 8
Name ="HighDate"
Flag = 8
End
Begin Joins
LeftTable ="tblPGPPerformance"
RightTable ="tblPGPVariations"
Expression ="tblPGPPerformance.ProductGroup = tblPGPVariations.ProductGroup"
Flag = 2
LeftTable ="tblPGPPerformance"
RightTable ="tblPGPVariations"
Expression ="tblPGPPerformance.[Process MfgPro] = tblPGPVariations.[Process MfgPro]"
Flag = 2
End
dbBoolean "ReturnsRecords" ="-1"
dbInteger "ODBCTimeout" ="60"
dbByte "RecordsetType" ="0"
dbBoolean "OrderByOn" ="0"
dbText "Description" ="Link tblPGPPerformance and tblPGPVariations. Shows all standards available with"
"in the date range"
Begin
Begin
dbText "Name" ="tblPGPPerformance.RoutingPercentage"
dbInteger "ColumnWidth" ="1980"
dbBoolean "ColumnHidden" ="0"
End
Begin
dbText "Name" ="RoutingVariationPercent"
dbInteger "ColumnWidth" ="2490"
dbBoolean "ColumnHidden" ="0"
End
End
Begin
State = 2
Left = -6
Top = -25
Right = 802
Bottom = 487
Left = -1
Top = -1
Right = 797
Bottom = 144
Left = 0
Top = 0
ColumnsShown = 539
Begin
Left = 172
Top = 6
Right = 311
Bottom = 113
Top = 4
Name ="tblPGPVariations"
End
Begin
Left = 38
Top = 6
Right = 134
Bottom = 113
Top = 0
Name ="tblPGPPerformance"
End
End