1 / 23

Issues Found in MAE DREP

Issues Found in MAE DREP. Criteria in Access is the Filter in SQL Server 2005 dbo_Actl2MstrNIIN.OOU_Code had to insert in I&S so that it could be used in 4A_I&S like it is in the MAN_DREP

mahsa
Download Presentation

Issues Found in MAE DREP

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Issues Found in MAE DREP • Criteria in Access is the Filter in SQL Server 2005 • dbo_Actl2MstrNIIN.OOU_Code had to insert in I&S so that it could be used in 4A_I&S like it is in the MAN_DREP • 6_Goal1 issues possibly linked to Excel file, Formulas as follows: (BO/5+QDR: (([BO_tot]/5)+[QDR]), Target: IIf([PSSDs] Is Null,[EXP_goal],[G019C]), EXP_Goal: IIf([bo/5+QDR]<0,[QDR],[bo/5+qdr])) and We do not know what this means (PSSDs: LIP_PBA_PSSDs.[New PSSD]) possibly linked to Excel file • Chart (Color, overall) fields

  2. 1a_MTP_master Query in Access SELECT dbo_RepairData.NIIN, dbo_RepairData.Repaired AS Prod, dbo_RepairData.Quarter FROM dbo_RepairData, [Date] WHERE (((dbo_RepairData.Quarter)=[date].[current qtr])); 1a_MTP_master Query in SQL Server 2005 SELECT dbo.dbo_RepairData.NIIN, dbo.dbo_RepairData.Repaired AS Prod, dbo.dbo_RepairData.Quarter FROM dbo.dbo_RepairData INNER JOIN dbo.date ON dbo.dbo_RepairData.Quarter = dbo.date.[Current Qtr]

  3. 1b_MTP_Dueouts Query in Access SELECT Mid([dbo_nsn_niin_id_xref.nsn],5,9) AS niin, Sum(dbo_part_base.base_due_outs) AS SumOfbase_due_outs FROM dbo_nsn_niin_id_xref INNER JOIN dbo_part_base ON Mid(dbo_nsn_niin_id_xref.nsn,5,9) = dbo_part_base.niin GROUP BY Mid([dbo_nsn_niin_id_xref.nsn],5,9); 1b_MTP_Dueouts in SQL Server 2005 SELECT dbo.dbo_nsn_niin_id_xref.niin_id, SUM(dbo.dbo_part_base.base_due_outs) AS Expr1 FROM dbo.dbo_nsn_niin_id_xref CROSS JOIN dbo.dbo_part_base GROUP BY dbo.dbo_nsn_niin_id_xref.niin_id niin: Mid([dbo_nsn_niin_id_xref.nsn],5,9) was replaced withniin_idin SQL Server 2005. This is incorrect.

  4. 1c_Field Micaps Query in Access SELECT dbo_requisition.NIIN, Sum(dbo_requisition.quantity) AS [Field MICAP] FROM dbo_requisition WHERE (((([dbo_requisition].[sran])="FB2029" Or ([dbo_requisition].[sran])="fb2039" Or ([dbo_requisition].[sran])="fb2069" Or ([dbo_requisition].[sran]) Like "e*")=False) AND ((dbo_requisition.Stack_Cat)="MICAP")) GROUP BY dbo_requisition.NIIN; 1c_Field Micaps in SQL Server 2005 SELECT NIIN, SUM(quantity) AS [Field MICAP] FROM dbo.dbo_requisition WHERE (NOT (sran = 'FB2029' OR sran = 'fb2039' OR sran = 'fb2069' OR sran LIKE 'e%')) AND (Stack_Cat = 'MICAP') GROUP BY NIIN

  5. 4_NRO Query in Access SELECT dbo_spt_results.NIIN, Count(dbo_spt_results.NIIN) AS CountOfNIIN FROM dbo_spt_results GROUP BY dbo_spt_results.NIIN; 4_NRO Query in SQL Server 2005 SELECT NIIN, COUNT(NIIN) AS CountOfNIIN FROM dbo.dbo_spt_results GROUP BY NIIN

  6. 4A_I&S in Access Different Between the 2 versions Possibly SELECT MTP_master.NIIN AS Expr1, [I&S].Mstr_NIIN AS Expr2, [I&S].Actl_NIIN AS Expr3, [I&S].OOU_Code AS Expr4 FROM MTP_master, [I&S]; 4A_I&S in Access Query in SQL Server 2005 SELECT dbo.MTP_master.NIIN, dbo.[I&S].Mstr_NIIN, dbo.[I&S].Actl_NIIN, dbo.[I&S].OOU_Code FROM dbo.[I&S] CROSS JOIN dbo.MTP_master Expr4: [I&S].OOU_Codereplaced with OOU_Codein the [I&S] query

  7. 5_data in Access Different Between the 2 versions SELECT MTP_master.NIIN, MTP_master.FSC, MTP_master.Noun, MTP_master.MMAC, MTP_master.SOR, MTP_master.[CNTL-NR], MTP_master.Shop, MTP_master.QDR, MTP_master.Prod, MTP_master.MICAP, MTP_master.YBQ, MTP_master.BO_Tot, MTP_master.F_days, MTP_master.RSP_A, MTP_master.POS_A, MTP_master.CSI_OH, MTP_master.G019C, MTP_master.Master_NSN, MTP_master.CRI, MTP_master.OWO, MTP_master.AWP_G, [4_NRO].CountOfNIIN AS rqd, MTP_master.SOS, MTP_master.Item_Count, MTP_master.Carc_Avail, MTP_master.Parts_avail, MTP_master.Hours_Avail, MTP_master.Funds_Avail, MTP_master.Sort_Value, MTP_master.Pushed, working_level.w_level, MTP_master.AIS_WL, IIf(working_level.w_level Is Null,MTP_master.AIS_WL,working_level.w_level) AS WL, MTP_master.HP_Total, MTP_master.[BACKORDERS-JCS], MTP_master!YBQ+MTP_master![BACKORDERS-JCS]+MTP_master!HP_Total AS [BOA+YBQ], targets.Target, MTP_master.ORG, MTP_master.NAME, MTP_master.PHONE, past_repair.FirstQuarterPastRepair, past_repair.SecondQuarterPastRepair, past_repair.ThirdQuarterPastRepair, past_repair.FirstQuarterPastAddlRepair, past_repair.SecondQuarterPastAddlRepair, past_repair.ThirdQuarterPastAddlRepair, MTP_master.[Base Assets], MTP_master.RO, MTP_master.[Due Out], MTP_master.[Field MICAP], MTP_master.[Cond-Y], MTP_master.[Cond-Z] FROM (((MTP_master INNER JOIN targets ON MTP_master.NIIN = targets.NIIN) LEFT JOIN past_repair ON MTP_master.NIIN = past_repair.NIIN) LEFT JOIN working_level ON MTP_master.NIIN = working_level.NIIN) LEFT JOIN 4_NRO ON MTP_master.NIIN = [4_NRO].NIIN; 5_data in Query in SQL Server 2005 SELECT dbo.MTP_master.NIIN, dbo.MTP_master.FSC, dbo.MTP_master.Noun, dbo.MTP_master.MMAC, dbo.MTP_master.SOR, dbo.MTP_master.[CNTL-NR], dbo.MTP_master.Shop, dbo.MTP_master.QDR, dbo.MTP_master.Prod, dbo.MTP_master.MICAP, dbo.MTP_master.YBQ, dbo.MTP_master.BO_Tot, dbo.MTP_master.F_days, dbo.MTP_master.RSP_A, dbo.MTP_master.POS_A, dbo.MTP_master.CSI_OH, dbo.MTP_master.G019C, dbo.MTP_master.Master_NSN, dbo.MTP_master.CRI, dbo.MTP_master.OWO, dbo.MTP_master.AWP_G, dbo.[4_NRO].CountOfNIIN AS rqd, dbo.MTP_master.SOS, dbo.MTP_master.Item_Count, dbo.MTP_master.Carc_Avail, dbo.MTP_master.Parts_avail, dbo.MTP_master.Hours_Avail, dbo.MTP_master.Funds_Avail, dbo.MTP_master.Sort_Value, dbo.MTP_master.Pushed, dbo.working_level.w_level, dbo.MTP_master.AIS_WL, dbo.MTP_master.HP_Total, dbo.MTP_master.[BACKORDERS-JCS], dbo.targets.Target, dbo.MTP_master.ORG, dbo.MTP_master.NAME, dbo.MTP_master.PHONE, dbo.past_repair.FirstQuarterPastRepair, dbo.past_repair.SecondQuarterPastRepair, dbo.past_repair.ThirdQuarterPastRepair, dbo.past_repair.FirstQuarterPastAddlRepair, dbo.past_repair.SecondQuarterPastAddlRepair, dbo.past_repair.ThirdQuarterPastAddlRepair, dbo.MTP_master.[Base Assets], dbo.MTP_master.RO, dbo.MTP_master.[Due Out], dbo.MTP_master.[Field MICAP], dbo.MTP_master.[Cond-Y], dbo.MTP_master.[Cond-Z] FROM dbo.MTP_master INNER JOIN dbo.past_repair ON dbo.MTP_master.NIIN = dbo.past_repair.NIIN INNER JOIN dbo.working_level ON dbo.MTP_master.NIIN = dbo.working_level.NIIN INNER JOIN dbo.[4_NRO] ON dbo.MTP_master.NIIN = dbo.[4_NRO].NIIN INNER JOIN dbo.[count subs] ON dbo.MTP_master.NIIN = dbo.[count subs].Mstr_NIIN INNER JOIN dbo.targets ON dbo.MTP_master.NIIN = dbo.targets.NIIN WL: IIf(working_level.w_level Is Null,MTP_master.AIS_WL,working_level.w_level) and BOA+YBQ: MTP_master!YBQ+MTP_master![BACKORDERS-JCS]+MTP_master!HP_Total Don’t know what these do, did not include in SQL Server.

  8. 6_Goal1 in Access SELECT MTP_master.Shop AS Expr1, MTP_master.NIIN AS Expr2, MTP_master.FSC AS Expr3, MTP_master.Noun AS Expr4, MTP_master.MMAC AS Expr5, MTP_master.BO_Tot AS Expr6, MTP_master.G019C AS Expr7, MTP_master.QDR AS Expr8, (([BO_tot]/5)+[QDR]) AS [BO/5+QDR], IIf([PSSDs] Is Null,[EXP_goal],[G019C]) AS Target, IIf([bo/5+QDR]<0,[QDR],[bo/5+qdr]) AS EXP_Goal, MTP_master.[CNTL-NR] AS Expr9, LIP_PBA_PSSDs.[New PSSD] AS PSSDs FROM MTP_master, LIP_PBA_PSSDs; 6_Goal1 in Query in SQL Server 2005 SELECT Shop, NIIN, FSC, Noun, MMAC, BO_Tot, G019C, QDR, [CNTL-NR] FROM dbo.MTP_master BO/5+QDR: (([BO_tot]/5)+[QDR]) and Target: IIf([PSSDs] Is Null,[EXP_goal],[G019C]) and EXP_Goal: IIf([bo/5+QDR]<0,[QDR],[bo/5+qdr]) and PSSDs: LIP_PBA_PSSDs.[New PSSD] and BOA+YBQ: MTP_master!YBQ+MTP_master![BACKORDERS-JCS]+MTP_master!HP_Total Don’t know what these do, did not include in SQL Server.

  9. 8_9GF_Qty in Access SELECT MTP_master.NIIN AS Expr1, Sum(dbo_requisition.quantity) AS SumOfquantity, dbo_requisition.project_code, MTP_master.Shop AS Expr2 FROM MTP_master, dbo_requisition GROUP BY MTP_master.NIIN, dbo_requisition.project_code, MTP_master.Shop HAVING (((dbo_requisition.project_code) Like [Enter Project code] & "*") AND ((MTP_master.Shop) Like [forms]![switchboard]![shop select] & "*")) ORDER BY Sum(dbo_requisition.quantity) DESC; 8_9GF_Qty Query in SQL Server 2005 SELECT TOP (100) PERCENT dbo.MTP_master.NIIN, SUM(dbo.dbo_requisition.quantity) AS quantity, dbo.dbo_requisition.project_code, dbo.MTP_master.Shop FROM dbo.dbo_requisition CROSS JOIN dbo.MTP_master GROUP BY dbo.MTP_master.NIIN, dbo.dbo_requisition.project_code, dbo.MTP_master.Shop ORDER BY quantity DESC 2 Unknown like expressions Like [Enter Project code] & "*“ and Like [forms]![switchboard]![shop select] & "*"

  10. chart in Access Different Between the 2 versions SELECT [5_data].Master_NSN AS nsn, [5_data].Shop AS PSSD, [Preferred Nouns].NOUN AS Noun, [5_data].[CNTL-NR] AS PDN, [5_data].F_days AS [Flow Days], [5_data].WL, [5_data].CSI_OH AS [09 Assets], [5_data].CRI, [5_data].G019C AS Expr3, [5_data].rqd AS NRO, [5_data].BO_Tot AS [Total Backorders], [5_data].MICAP AS MICAPs, [5_data].OWO, Null AS Color, Null AS overall, [5_data].YBQ AS Expr4, [5_data].Prod, [5_data].QDR, [5_data].FSC, [5_data].MMAC, [5_data].NIIN, [Preferred Nouns].Engine AS TMS, problems_table.Forecast, problems_table.Act_FD, problems_table.AWP_F, problems_table.[Parts Problem], problems_table.Other, problems_table.Cont_Prod, problems_table.[Equip 1], problems_table.[Equip 2], problems_table.[Equip 3], problems_table.[Equip 4], problems_table.[Equip 5], problems_table.[Equip 6], problems_table.P_N, problems_table.Contract_OWO, [5_data].SOS, [5_data].ORG, problems_table.Personnel, [5_data].POS_A, [5_data].Item_Count, [5_data].Carc_Avail, [5_data].Parts_avail, [5_data].Hours_Avail, [5_data].Funds_Avail, [5_data].Sort_Value, [5_data].Pushed AS Expr19, [5_data].RSP_A, [5_data].CSI_OH AS Expr21, [5_data].[BOA+YBQ] AS Expr22, problems_table.Cont_forecast, problems_table.contract_cri, problems_table.[I&S], [5_data].Target, problems_table.Awp_G, problems_table.[AWP Days], [5_data].NAME, [5_data].PHONE, [5_data].FirstQuarterPastRepair, [5_data].SecondQuarterPastRepair, [5_data].ThirdQuarterPastRepair, [5_data].FirstQuarterPastAddlRepair, [5_data].SecondQuarterPastAddlRepair, [5_data].ThirdQuarterPastAddlRepair, [5_data].RO, [5_data].[Base Assets], [5_data].[Due Out], [Preferred Nouns].FINAL, [5_data].[Field MICAP], [5_data].[Cond-Y] AS Expr36, [5_data].[Cond-Z] AS Expr37, [Monthly Production Archive].[end month1 prod], [Monthly Production Archive].[End month2 prod] FROM ((5_data INNER JOIN problems_table ON [5_data].NIIN = problems_table.niin) LEFT JOIN [Monthly Production Archive] ON [5_data].NIIN = [Monthly Production Archive].NIIN) LEFT JOIN [Preferred Nouns] ON [5_data].NIIN = [Preferred Nouns].niin WHERE ((([5_data].Shop) Like [forms]![switchboard]![shop select].[value] & "*")); chart Query in SQL Server 2005 SELECT dbo.[5_data].Master_NSN AS nsn, dbo.[5_data].Shop AS PSSD, dbo.[Preferred Nouns].NOUN, dbo.[5_data].[CNTL-NR] AS PDN, dbo.[5_data].F_days AS [Flow Days], dbo.[5_data].WL, dbo.[5_data].CSI_OH AS [09 Assets], dbo.[5_data].CRI, dbo.[5_data].G019C, dbo.[5_data].rqd AS NRO, dbo.[5_data].BO_Tot AS [Total Backorders], dbo.[5_data].MICAP AS MICAPs, dbo.[5_data].OWO, dbo.[5_data].YBQ, dbo.[5_data].Prod, dbo.[5_data].QDR, dbo.[5_data].FSC, dbo.[5_data].MMAC, dbo.[5_data].NIIN, dbo.[Preferred Nouns].Engine AS TMS, dbo.problems_table.Forecast, dbo.problems_table.Act_FD, dbo.problems_table.AWP_F, dbo.problems_table.[Parts Problem], dbo.problems_table.Other, dbo.problems_table.Cont_Prod, dbo.problems_table.[Equip 1], dbo.problems_table.[Equip 2], dbo.problems_table.[Equip 3], dbo.problems_table.[Equip 4], dbo.problems_table.[Equip 5], dbo.problems_table.[Equip 6], dbo.problems_table.P_N, dbo.problems_table.Contract_OWO, dbo.[5_data].SOS, dbo.[5_data].ORG, dbo.problems_table.Personnel, dbo.[5_data].POS_A, dbo.[5_data].Item_Count, dbo.[5_data].Carc_Avail, dbo.[5_data].Parts_avail, dbo.[5_data].Hours_Avail, dbo.[5_data].Funds_Avail, dbo.[5_data].Sort_Value, dbo.[5_data].Pushed, dbo.[5_data].RSP_A, dbo.[5_data].CSI_OH, dbo.[5_data].[BOA+YBQ], dbo.problems_table.Cont_forecast, dbo.problems_table.contract_cri, dbo.problems_table.[I&S], dbo.[5_data].Target, dbo.[5_data].AWP_G, dbo.problems_table.[AWP Days], dbo.[5_data].NAME, dbo.[5_data].PHONE, dbo.[5_data].FirstQuarterPastRepair, dbo.[5_data].SecondQuarterPastRepair, dbo.[5_data].ThirdQuarterPastRepair, dbo.[5_data].FirstQuarterPastAddlRepair, dbo.[5_data].SecondQuarterPastAddlRepair, dbo.[5_data].ThirdQuarterPastAddlRepair, dbo.[5_data].RO, dbo.[5_data].[Base Assets], dbo.[5_data].[Due Out], dbo.[Preferred Nouns].FINAL, dbo.[5_data].[Field MICAP], dbo.[5_data].[Cond-Y], dbo.[5_data].[Cond-Z], dbo.[Monthly Production Archive].[end month1 prod], dbo.[Monthly Production Archive].[End month2 prod] FROM dbo.[5_data] INNER JOIN dbo.problems_table ON dbo.[5_data].NIIN = dbo.problems_table.niin INNER JOIN dbo.[Monthly Production Archive] ON dbo.[5_data].NIIN = dbo.[Monthly Production Archive].NIIN INNER JOIN dbo.[Preferred Nouns] ON dbo.[5_data].NIIN = dbo.[Preferred Nouns].niin Like [forms]![switchboard]![shop select].[value] & "*“ and Color: Null and overall: Null Don’t know what these do, did not include in SQL Server.

  11. count subs Query in Access SELECT [temp table subs in 2 column format].Mstr_NIIN, [temp table subs in 2 column format].Actl_NIIN, Count([temp table subs in 2 column format].Mstr_NIIN) AS CountOfMstr_NIIN FROM [temp table subs in 2 column format] INNER JOIN [temp table subs in 2 column format] AS [temp table subs in 2 column format_1] ON [temp table subs in 2 column format].Mstr_NIIN = [temp table subs in 2 column format_1].Mstr_NIIN GROUP BY [temp table subs in 2 column format].Mstr_NIIN, [temp table subs in 2 column format].Actl_NIIN; count subs Query in SQL Server 2005 SELECT dbo.[temp table subs in 2 column format].Mstr_NIIN, dbo.[temp table subs in 2 column format].Actl_NIIN, COUNT(dbo.[temp table subs in 2 column format].Mstr_NIIN) AS CountOfMstr_NIIN FROM dbo.[temp table subs in 2 column format] INNER JOIN dbo.[temp table subs in 2 column format] AS [temp table subs in 2 column format_1] ON dbo.[temp table subs in 2 column format].Mstr_NIIN = [temp table subs in 2 column format_1].Mstr_NIIN GROUP BY dbo.[temp table subs in 2 column format].Mstr_NIIN, dbo.[temp table subs in 2 column format].Actl_NIIN

  12. Daily MICAP and BO Report Query in Access Different Between the 2 versions SELECT TOP (100) PERCENT dbo.MTP_master.Shop, dbo.MTP_master.[CNTL-NR] AS Cntrl#, dbo.MTP_master.FSC, dbo.MTP_master.NIIN, dbo.MTP_master.MMAC AS MC, dbo.MTP_master.Noun, dbo.MTP_master.ORG, dbo.MTP_master.MICAP, dbo.MTP_master.[BACKORDERS-JCS] AS JCS, dbo.MTP_master.YBQ, dbo.MTP_master.HP_Total AS [700s], dbo.MTP_master.BO_Tot AS [Total BOs], dbo.dbo_MasterQueryTable.[DEPOT-ASSETS-COND-A] AS [A Cond], dbo.dbo_MasterQueryTable.[DEPOT-ASSETS-COND-Z] AS [Z Cond], dbo.MTP_master.OWO, dbo.MTP_master.Prod, dbo.dbo_MasterQueryTable.[DEPOT-ASSETS-COND-F] AS [F Cond], dbo.MTP_master.Carc_Avail AS Carc, dbo.MTP_master.Parts_avail AS Parts, dbo.MTP_master.Hours_Avail AS Hours, dbo.MTP_master.Funds_Avail AS Funds FROM dbo.MTP_master INNER JOIN dbo.dbo_MasterQueryTable ON dbo.MTP_master.NIIN = dbo.dbo_MasterQueryTable.NIIN ORDER BY dbo.MTP_master.Shop, Cntrl# Daily MICAP and BO Report Query in SQL Server 2005 SELECT MTP_master.Shop AS Expr1, MTP_master.[CNTL-NR] AS [Cntrl#], MTP_master.FSC AS Expr2, MTP_master.NIIN AS Expr3, MTP_master.MMAC AS MC, dbo_MasterQueryTable.Noun, MTP_master.ORG AS Expr4, MTP_master.MICAP AS Expr5, MTP_master.[BACKORDERS-JCS] AS JCS, MTP_master.YBQ AS Expr6, MTP_master.HP_Total AS 700s, MTP_master.BO_Tot AS [Total BOs], dbo_MasterQueryTable.[DEPOT-ASSETS-COND-A] AS [A Cond], dbo_MasterQueryTable.[DEPOT-ASSETS-COND-Z] AS [Z Cond], MTP_master.OWO AS Expr7, MTP_master.Prod AS Expr8, dbo_MasterQueryTable.[DEPOT-ASSETS-COND-F] AS [F Cond], MTP_master.Carc_Avail AS Carc, MTP_master.Parts_avail AS Parts, MTP_master.Hours_Avail AS Hours, MTP_master.Funds_Avail AS Funds FROM dbo_MasterQueryTable, MTP_master WHERE (((MTP_master.Shop) Like forms!switchboard![shop select].value & "*")) ORDER BY MTP_master.Shop, MTP_master.[CNTL-NR]; Like [forms]![switchboard]![shop select].[value] & "*“ Don’t know what these do, did not include in SQL Server.

  13. I&S Query in Access Different Between the 2 versions SELECT dbo_Actl2MstrNIIN.Actl_NIIN, dbo_Actl2MstrNIIN.Mstr_NIIN FROM MTP_master LEFT JOIN dbo_Actl2MstrNIIN ON MTP_master.NIIN = dbo_Actl2MstrNIIN.InS_NIIN WHERE (((dbo_Actl2MstrNIIN.Mstr_NIIN)=[forms]![problems_table].[niin])); I&S Query in SQL Server 2005 SELECT dbo.dbo_Actl2MstrNIIN.Actl_NIIN, dbo.dbo_Actl2MstrNIIN.Mstr_NIIN, dbo.dbo_Actl2MstrNIIN.OOU_Code FROM dbo.dbo_Actl2MstrNIIN INNER JOIN dbo.MTP_master ON dbo.dbo_Actl2MstrNIIN.Mstr_NIIN = dbo.MTP_master.NIIN [forms]![problems_table].[niin] Don’t know what these do, did not include in SQL Server.

  14. MTAA BO BURNDOWN DATA Query in Access Different Between the 2 versions SELECT MTP_master.Shop AS Expr1, MTP_master.[CNTL-NR] AS [Cntrl#], MTP_master.FSC AS Expr2, MTP_master.NIIN AS Expr3, MTP_master.MMAC AS MC, MTP_master.Noun AS Expr4, MTP_master.MICAP AS Expr5, MTP_master.[BACKORDERS-JCS] AS JCS, MTP_master.BO_Tot AS [Total BO], dbo_MasterQueryTable.[DEPOT-ASSETS-COND-A] AS [A Cond], dbo_MasterQueryTable.[DEPOT-ASSETS-COND-Z] AS [Z cond], dbo_MasterQueryTable.[DEPOT-ASSETS-COND-F] AS [F Cond], MTP_master.OWO AS Expr6, MTP_master.Prod AS Expr7, MTP_master.Carc_Avail AS Carc, MTP_master.Parts_avail AS Parts, MTP_master.Hours_Avail AS Hours, MTP_master.Funds_Avail AS Funds, MTP_master.ORG AS Expr8, MTP_master.NAME AS Expr9, MTP_master.PHONE AS Expr10 FROM dbo_MasterQueryTable, MTP_master WHERE (((MTP_master.Shop) Like "MTAA**")) ORDER BY MTP_master.Shop, MTP_master.[CNTL-NR]; MTAA BO BURNDOWN DATA Query in SQL Server 2005 SELECT TOP (2147483647) WITH TIES dbo.MTP_master.Shop, dbo.MTP_master.[CNTL-NR] AS Cntrl#, dbo.MTP_master.FSC, dbo.MTP_master.NIIN, dbo.MTP_master.MMAC AS MC, dbo.MTP_master.Noun, dbo.MTP_master.MICAP, dbo.MTP_master.[BACKORDERS-JCS] AS JCS, dbo.MTP_master.BO_Tot AS [Total BO], dbo.dbo_MasterQueryTable.[DEPOT-ASSETS-COND-A] AS [A Cond], dbo.dbo_MasterQueryTable.[DEPOT-ASSETS-COND-Z] AS [Z cond], dbo.dbo_MasterQueryTable.[DEPOT-ASSETS-COND-F] AS [F Cond], dbo.MTP_master.OWO, dbo.MTP_master.Prod, dbo.MTP_master.Carc_Avail AS Carc, dbo.MTP_master.Parts_avail AS Parts, dbo.MTP_master.Hours_Avail AS Hours, dbo.MTP_master.Funds_Avail AS Funds, dbo.MTP_master.ORG, dbo.MTP_master.NAME, dbo.MTP_master.PHONE FROM dbo.dbo_MasterQueryTable CROSS JOIN dbo.MTP_master WHERE (dbo.MTP_master.Shop LIKE 'MTAA%%') ORDER BY dbo.MTP_master.Shop, Cntrl# Like "MTAA**“ Don’t know what these do, did not include in SQL Server.

  15. MTAA DAILY MICAP AND BO REPORT Query in Access Different Between the 2 versions SELECT MTP_master.Shop AS Expr1, MTP_master.[CNTL-NR] AS [Cntrl#], MTP_master.FSC AS Expr2, MTP_master.NIIN AS Expr3, MTP_master.MMAC AS MC, dbo_MasterQueryTable.Noun, MTP_master.ORG AS Expr4, MTP_master.MICAP AS Expr5, MTP_master.[BACKORDERS-JCS] AS JCS, MTP_master.YBQ AS Expr6, MTP_master.HP_Total AS 700s, MTP_master.BO_Tot AS [Total BOs], dbo_MasterQueryTable.[DEPOT-ASSETS-COND-A] AS [A Cond], dbo_MasterQueryTable.[DEPOT-ASSETS-COND-Z] AS [Z Cond], MTP_master.OWO AS Expr7, MTP_master.Prod AS Expr8, dbo_MasterQueryTable.[DEPOT-ASSETS-COND-F] AS [F Cond], MTP_master.Carc_Avail AS Carc, MTP_master.Parts_avail AS Parts, MTP_master.Hours_Avail AS Hours, MTP_master.Funds_Avail AS Funds FROM dbo_MasterQueryTable, MTP_master WHERE (((MTP_master.Shop) Like "MTAA**")) ORDER BY MTP_master.Shop, MTP_master.[CNTL-NR]; MTAA DAILY MICAP AND BO REPORT Query in SQL Server 2005 SELECT TOP (100) PERCENT dbo.MTP_master.Shop, dbo.MTP_master.[CNTL-NR] AS Cntrl#, dbo.MTP_master.FSC, dbo.MTP_master.NIIN, dbo.MTP_master.MMAC AS MC, dbo.MTP_master.Noun, dbo.MTP_master.ORG, dbo.MTP_master.MICAP, dbo.MTP_master.[BACKORDERS-JCS] AS JCS, dbo.MTP_master.YBQ, dbo.MTP_master.HP_Total AS [700s], dbo.MTP_master.BO_Tot AS [Total BO], dbo.dbo_MasterQueryTable.[DEPOT-ASSETS-COND-A] AS [A Cond], dbo.dbo_MasterQueryTable.[DEPOT-ASSETS-COND-Z] AS [Z Cond], dbo.MTP_master.OWO, dbo.MTP_master.Prod, dbo.dbo_MasterQueryTable.[DEPOT-ASSETS-COND-F] AS [F Cond], dbo.MTP_master.Carc_Avail AS Carc, dbo.MTP_master.Parts_avail AS Parts, dbo.MTP_master.Hours_Avail AS Hours, dbo.MTP_master.Funds_Avail AS Funds FROM dbo.dbo_MasterQueryTable INNER JOIN dbo.MTP_master ON dbo.dbo_MasterQueryTable.NIIN = dbo.MTP_master.NIIN ORDER BY dbo.MTP_master.Shop, Cntrl# Like "MTAA**" Don’t know what these do, did not include in SQL Server.

  16. MTCC-MTCF Daily MICAP and BO Report Query in Access Different Between the 2 versions SELECT dbo_MasterQueryTable.Shop, MTP_master.[CNTL-NR] AS [Cntrl#], MTP_master.FSC AS Expr1, MTP_master.NIIN AS Expr2, MTP_master.MMAC AS MC, MTP_master.Noun AS Expr3, MTP_master.MICAP AS Expr4, MTP_master.YBQ AS Expr5, MTP_master.[BACKORDERS-JCS] AS JCS, MTP_master.HP_Total AS 700s, MTP_master.BO_Tot AS [Total BOs], dbo_MasterQueryTable.[DEPOT-ASSETS-COND-A] AS [A cond], dbo_MasterQueryTable.[DEPOT-ASSETS-COND-Z] AS [Z Cond], dbo_MasterQueryTable.[DEPOT-ASSETS-COND-F] AS [F Cond], dbo_MasterQueryTable.[DEPOT-ASSETS-COND-Y] AS [Y-Int], MTP_master.QDR AS Expr6, MTP_master.OWO AS Expr7, MTP_master.Prod AS Expr8, MTP_master.Carc_Avail AS Carc, MTP_master.Parts_avail AS Parts, MTP_master.Hours_Avail AS Hours, MTP_master.Funds_Avail AS Funds FROM dbo_MasterQueryTable, MTP_master WHERE (((dbo_MasterQueryTable.Shop) Like "MTCC**" Or (dbo_MasterQueryTable.Shop) Like "MTCF**")) ORDER BY dbo_MasterQueryTable.Shop, MTP_master.[CNTL-NR]; MTCC-MTCF Daily MICAP and BO Report Query in SQL Server 2005 SELECT TOP (100) PERCENT dbo.dbo_MasterQueryTable.Shop, dbo.MTP_master.[CNTL-NR] AS Cntrl#, dbo.MTP_master.FSC, dbo.MTP_master.NIIN, dbo.MTP_master.MMAC AS MC, dbo.MTP_master.Noun, dbo.MTP_master.MICAP, dbo.MTP_master.YBQ, dbo.MTP_master.[BACKORDERS-JCS] AS JCS, dbo.MTP_master.HP_Total AS [700s], dbo.MTP_master.BO_Tot AS [Total BOs], dbo.dbo_MasterQueryTable.[DEPOT-ASSETS-COND-A] AS [A cond], dbo.dbo_MasterQueryTable.[DEPOT-ASSETS-COND-Z] AS [Z Cond], dbo.dbo_MasterQueryTable.[DEPOT-ASSETS-COND-F] AS [F Cond], dbo.dbo_MasterQueryTable.[DEPOT-ASSETS-COND-Y] AS [Y-Int], dbo.MTP_master.QDR, dbo.MTP_master.OWO, dbo.MTP_master.Prod, dbo.MTP_master.Carc_Avail AS Carc, dbo.MTP_master.Parts_avail AS Parts, dbo.MTP_master.Hours_Avail AS Hours, dbo.MTP_master.Funds_Avail AS Funds FROM dbo.dbo_MasterQueryTable INNER JOIN dbo.MTP_master ON dbo.dbo_MasterQueryTable.NIIN = dbo.MTP_master.NIIN ORDER BY dbo.dbo_MasterQueryTable.Shop, Cntrl# Like "MTCC**" Or Like "MTCF**“ Don’t know what these do, did not include in SQL Server.

  17. One stop additional info Query in Access SELECT past_repair.FirstQuarterPastRepair, past_repair.SecondQuarterPastRepair, past_repair.ThirdQuarterPastRepair, past_repair.FirstQuarterPastAddlRepair, past_repair.SecondQuarterPastAddlRepair, past_repair.ThirdQuarterPastAddlRepair, [Monthly Production Archive].[end month1 prod], [Monthly Production Archive].[End month2 prod], targets.Target, [Preferred Nouns].NOUN, MTP_master.NIIN AS Expr1, MTP_master.Shop AS Expr2, [Preferred Nouns].Engine AS TMS FROM MTP_master, past_repair, targets, [Monthly Production Archive], [Preferred Nouns]; One stop additional info in SQL Server 2005 SELECT dbo.past_repair.FirstQuarterPastRepair, dbo.past_repair.SecondQuarterPastRepair, dbo.past_repair.ThirdQuarterPastRepair, dbo.past_repair.FirstQuarterPastAddlRepair, dbo.past_repair.SecondQuarterPastAddlRepair, dbo.past_repair.ThirdQuarterPastAddlRepair, dbo.[Monthly Production Archive].[end month1 prod], dbo.[Monthly Production Archive].[End month2 prod], dbo.targets.Target, dbo.[Preferred Nouns].NOUN, dbo.MTP_master.NIIN, dbo.MTP_master.Shop, dbo.[Preferred Nouns].Engine AS TMS FROM dbo.MTP_master CROSS JOIN dbo.past_repair CROSS JOIN dbo.targets CROSS JOIN dbo.[Monthly Production Archive] CROSS JOIN dbo.[Preferred Nouns]

  18. One stop problems table Query in Access SELECT problems_table.niin, problems_table.P_N, problems_table.Forecast, problems_table.Cont_forecast, past_repair.FirstQuarterPastRepair, past_repair.SecondQuarterPastRepair, past_repair.ThirdQuarterPastRepair, past_repair.FirstQuarterPastAddlRepair, past_repair.SecondQuarterPastAddlRepair, past_repair.ThirdQuarterPastAddlRepair, [Monthly Production Archive].[end month1 prod], [Monthly Production Archive].[End month2 prod], targets.Target, [Preferred Nouns].NOUN, problems_table.Act_FD, problems_table.AWP_F, problems_table.Contract_OWO, problems_table.[Parts Problem], problems_table.Other, problems_table.Cont_Prod, problems_table.[Equip 1], problems_table.[Equip 2], problems_table.[Equip 3], problems_table.[Equip 4], problems_table.[Equip 5], problems_table.[Equip 6], problems_table.Personnel, problems_table.contract_cri, problems_table.[I&S], problems_table.Awp_G, problems_table.[AWP Days], [Preferred Nouns].Engine AS TMS FROM (((problems_table INNER JOIN past_repair ON problems_table.niin = past_repair.NIIN) INNER JOIN [Preferred Nouns] ON problems_table.niin = [Preferred Nouns].niin) INNER JOIN [Monthly Production Archive] ON problems_table.niin = [Monthly Production Archive].NIIN) INNER JOIN targets ON problems_table.niin = targets.NIIN; One stop problems table Query in SQL Server 2005 SELECT dbo.problems_table.niin, dbo.problems_table.P_N, dbo.problems_table.Forecast, dbo.problems_table.Cont_forecast, dbo.past_repair.FirstQuarterPastRepair, dbo.past_repair.SecondQuarterPastRepair, dbo.past_repair.ThirdQuarterPastRepair, dbo.past_repair.FirstQuarterPastAddlRepair, dbo.past_repair.SecondQuarterPastAddlRepair, dbo.past_repair.ThirdQuarterPastAddlRepair, dbo.[Monthly Production Archive].[end month1 prod], dbo.[Monthly Production Archive].[End month2 prod], dbo.targets.Target, dbo.[Preferred Nouns].NOUN, dbo.problems_table.Act_FD, dbo.problems_table.AWP_F, dbo.problems_table.Contract_OWO, dbo.problems_table.[Parts Problem], dbo.problems_table.Other, dbo.problems_table.Cont_Prod, dbo.problems_table.[Equip 1], dbo.problems_table.[Equip 2], dbo.problems_table.[Equip 3], dbo.problems_table.[Equip 4], dbo.problems_table.[Equip 5], dbo.problems_table.[Equip 6], dbo.problems_table.Personnel, dbo.problems_table.contract_cri, dbo.problems_table.[I&S], dbo.problems_table.Awp_G, dbo.problems_table.[AWP Days], dbo.[Preferred Nouns].Engine AS TMS FROM dbo.problems_table INNER JOIN dbo.past_repair ON dbo.problems_table.niin = dbo.past_repair.NIIN INNER JOIN dbo.[Preferred Nouns] ON dbo.problems_table.niin = dbo.[Preferred Nouns].niin INNER JOIN dbo.[Monthly Production Archive] ON dbo.problems_table.niin = dbo.[Monthly Production Archive].NIIN INNER JOIN dbo.targets ON dbo.problems_table.niin = dbo.targets.NIIN

  19. PastRepairQtrs Query in Access SELECT dbo.past_repair.NIIN, (CASE WHEN RIGHT([date].[current qtr], 1) = '1' THEN '0' + (CAST((CAST(ROUND(LEFT([date].[current qtr], 2), 0) AS smallint)) - 1 AS varchar)) + '/4' ELSE LEFT([date].[current qtr], 3) + (CAST((CAST(ROUND(RIGHT([date].[current qtr], 1), 0) AS smallint)) - 1 AS varchar)) END) AS FirstQtrPast FROM dbo.date CROSS JOIN dbo.past_repair PastRepairQtrs Query in SQL Server 2005 SELECT past_repair.NIIN, IIf(Right([date].[current qtr],1)='1','0' & CStr(CInt(Left([date].[current qtr],2))-1) & '/4',Left([date].[current qtr],3) & CStr(CInt(Right([date].[current qtr],1))-1)) AS FirstQtrPast FROM [Date], past_repair; FirstQtrPast: IIf(Right([date].[current qtr],1)='1','0' & CStr(CInt(Left([date].[current qtr],2))-1) & '/4',Left([date].[current qtr],3) & CStr(CInt(Right([date].[current qtr],1))-1)) Don’t know what these do, did not include in SQL Server.

  20. problems_table Without Matching MTP_master Query in Access SELECT problems_table.niin, problems_table.P_N, problems_table.TMS, problems_table.Forecast, problems_table.Cont_forecast, problems_table.Act_FD, problems_table.AWP_F, problems_table.Contract_OWO, problems_table.[Parts Problem], problems_table.Other, problems_table.Cont_Prod, problems_table.[Equip 1], problems_table.[Equip 2], problems_table.[Equip 3], problems_table.[Equip 4], problems_table.[Equip 5], problems_table.[Equip 6], problems_table.Personnel, problems_table.contract_cri, problems_table.[I&S], problems_table.Awp_G, problems_table.[AWP Days] FROM problems_table LEFT JOIN MTP_master ON problems_table.niin = MTP_master.NIIN WHERE (((MTP_master.NIIN) Is Null)); problems_table Without Matching MTP_master Query in SQL Server 2005 SELECT dbo.problems_table.niin, dbo.problems_table.P_N, dbo.problems_table.TMS, dbo.problems_table.Forecast, dbo.problems_table.Cont_forecast, dbo.problems_table.Act_FD, dbo.problems_table.AWP_F, dbo.problems_table.Contract_OWO, dbo.problems_table.[Parts Problem], dbo.problems_table.Other, dbo.problems_table.Cont_Prod, dbo.problems_table.[Equip 1], dbo.problems_table.[Equip 2], dbo.problems_table.[Equip 3], dbo.problems_table.[Equip 4], dbo.problems_table.[Equip 5], dbo.problems_table.[Equip 6], dbo.problems_table.Personnel, dbo.problems_table.contract_cri, dbo.problems_table.[I&S], dbo.problems_table.Awp_G, dbo.problems_table.[AWP Days] FROM dbo.problems_table LEFT OUTER JOIN dbo.MTP_master ON dbo.problems_table.niin = dbo.MTP_master.NIIN WHERE (dbo.MTP_master.NIIN IS NULL)

  21. Select Airframe by NIIN Query in Access SELECT dbo_ApplicationData.NIIN, dbo_ApplicationData.MDS FROM dbo_ApplicationData WHERE (((dbo_ApplicationData.MDS) Like "[A,B,C,F]*")); Select Airframe by NIIN Query in SQL Server 2005 SELECT NIIN, MDS FROM dbo.dbo_ApplicationData WHERE (MDS LIKE '[A,B,C,F]%')

  22. Select Problem_items by PSSD Query in Access SELECT problems_table.*, MTP_master.Shop FROM problems_table LEFT JOIN MTP_master ON problems_table.niin = MTP_master.NIIN WHERE (((MTP_master.Shop) Like [forms]![switchboard]![shop select].[value] & "*")); Select Problem_items by PSSD Query in SQL Server 2005 SELECT dbo.problems_table.niin, dbo.problems_table.P_N, dbo.problems_table.TMS, dbo.problems_table.Forecast, dbo.problems_table.Cont_forecast, dbo.problems_table.Act_FD, dbo.problems_table.AWP_F, dbo.problems_table.Contract_OWO, dbo.problems_table.[Parts Problem], dbo.problems_table.Other, dbo.problems_table.Cont_Prod, dbo.problems_table.[Equip 1], dbo.problems_table.[Equip 2], dbo.problems_table.[Equip 3], dbo.problems_table.[Equip 4], dbo.problems_table.[Equip 5], dbo.problems_table.[Equip 6], dbo.problems_table.Personnel, dbo.problems_table.contract_cri, dbo.problems_table.[I&S], dbo.problems_table.Awp_G, dbo.problems_table.[AWP Days], dbo.problems_table.SSMA_TimeStamp, dbo.MTP_master.Shop FROM dbo.MTP_master INNER JOIN dbo.problems_table ON dbo.MTP_master.NIIN = dbo.problems_table.niin Like [forms]![switchboard]![shop select].[value] & "*“ Don’t know what these do, did not include in SQL Server.

  23. subs against actual data Query in Access Different Between the 2 versions SELECT [5_data].NIIN AS Expr41, [count subs].Actl_NIIN, [count subs].CountOfMstr_NIIN FROM 5_data, [count subs] GROUP BY [5_data].NIIN, [count subs].Actl_NIIN, [count subs].CountOfMstr_NIIN; subs against actual data Query in SQL Server 2005 SELECT dbo.[5_data].NIIN, dbo.[count subs].Actl_NIIN, dbo.[count subs].CountOfMstr_NIIN FROM dbo.[5_data] CROSS JOIN dbo.[count subs] GROUP BY dbo.[5_data].NIIN, dbo.[count subs].Actl_NIIN, dbo.[count subs].CountOfMstr_NIIN

More Related