1a_MTP_master

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]

4_NRO

SELECT NIIN, COUNT(NIIN) AS CountOfNIIN

FROM dbo.dbo_spt_results

GROUP BY NIIN

4A_I&S

SQL Server gets results (3380 SQL Server displayed records) and Access does not display anything-cannot compare output), SQL Statements are the same

SELECT dbo.MTP_master.NIIN, dbo.[I&S].Mstr_NIIN, dbo.[I&S].Actl_NIIN, dbo.[I&S].OOU_Code

FROM dbo.MTP_master INNER JOIN

dbo.[I&S] ON dbo.MTP_master.NIIN = dbo.[I&S].Mstr_NIIN

5_data

SELECT DISTINCT

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,

CASE WHEN working_level.w_level IS NULL THEN MTP_master.AIS_WL ELSE working_level.w_level END AS WL, dbo.MTP_master.HP_Total,

dbo.MTP_master.[BACKORDERS-JCS], dbo.MTP_master.YBQ + dbo.MTP_master.[BACKORDERS-JCS] + dbo.MTP_master.HP_Total AS [BOA+YBQ],

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

FROM dbo.MTP_master LEFT OUTER JOIN

dbo.[4_NRO] ON dbo.MTP_master.NIIN = dbo.[4_NRO].NIIN LEFT OUTER JOIN

dbo.working_level ON dbo.MTP_master.NIIN = dbo.working_level.NIIN LEFT OUTER JOIN

dbo.targets ON dbo.MTP_master.NIIN = dbo.targets.NIIN LEFT OUTER JOIN

dbo.[count subs] ON dbo.MTP_master.NIIN = dbo.[count subs].Mstr_NIIN LEFT OUTER JOIN

dbo.past_repair ON dbo.MTP_master.NIIN = dbo.past_repair.NIIN

6_Goal1

Do not have any results in SQL Server or in Access. In Access the Query is broken, No design view or output, can only see the SQL statement.

Import excel file into MAN Version and was excluded

SELECT MTP_master.Shop, MTP_master.NIIN, MTP_master.FSC, MTP_master.Noun, MTP_master.MMAC, MTP_master.BO_Tot, MTP_master.G019C, MTP_master.QDR, ((mtp_master.BO_tot/4)+mtp_master.QDR) AS [BO/5+QDR], IIf([PSSDs] Is Null,[EXP_goal],mtp_master.G019C) AS Target, IIf([bo/5+QDR]<0,mtp_master.QDR,[bo/5+qdr]) AS EXP_Goal, MTP_master.[CNTL-NR], LIP_PBA_PSSDs.[New PSSD] AS PSSDs, targets.Target AS [Prev Target], past_repair.FirstQuarterPastRepair AS [Prev Qtr Org Repair], past_repair.FirstQuarterPastAddlRepair AS [Prev Qtr Addl Repair]

FROM ((MTP_master LEFT JOIN LIP_PBA_PSSDs ON MTP_master.Shop=LIP_PBA_PSSDs.[New PSSD]) LEFT JOIN past_repair ON MTP_master.NIIN=past_repair.NIIN) LEFT JOIN targets ON MTP_master.NIIN=targets.NIIN;

8_9GF_Qty

The having statement was removed (HAVING (((dbo_requisition.project_code) Like [Enter Project code] & "*") AND ((MTP_master.Shop) Like [forms]![switchboard]![shop select] & "*"))). Results in SQL Server and Access are the same.

SELECT TOP (100) PERCENT dbo.MTP_master.NIIN, SUM(dbo.dbo_requisition.quantity) AS SumOfquantity, dbo.dbo_requisition.project_code,

dbo.MTP_master.Shop

FROM dbo.MTP_master INNER JOIN

dbo.dbo_requisition ON dbo.MTP_master.NIIN = dbo.dbo_requisition.NIIN

GROUP BY dbo.MTP_master.NIIN, dbo.dbo_requisition.project_code, dbo.MTP_master.Shop

ORDER BY SumOfquantity DESC

Chart

The where statement was excluded (WHERE ((([5_data].Shop) Like [forms]![switchboard]![shop select].[value] & "*"));) the results are the same in SQL Server and Access. The SQL Server Statements are the same otherwise.

SELECT dbo.[5_data].Master_NSN AS nsn, dbo.[5_data].Shop AS PSSD, dbo.[5_data].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, NULL AS Color, NULL

AS overall, dbo.[5_data].YBQ, dbo.[5_data].Prod, ROUND(dbo.[5_data].QDR, 0) AS QDR_, dbo.problems_table.Forecast, dbo.[5_data].FSC,

dbo.[5_data].MMAC, dbo.[5_data].NIIN, dbo.problems_table.TMS, dbo.problems_table.Forecast AS 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.Cont_Prod AS Cont_Prod_, 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.problems_table.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]

FROM dbo.[5_data] INNER JOIN

dbo.problems_table ON dbo.[5_data].NIIN = dbo.problems_table.niin

count subs

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

Daily MICAP and BO Report

The where statement was removed (WHERE (((MTP_master.Shop) Like [forms]![switchboard]![shop select].[value] & "*"))) Data retrievals are identical.

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.dbo_MasterQueryTable.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.dbo_MasterQueryTable INNER JOIN

dbo.MTP_master ON dbo.dbo_MasterQueryTable.NIIN = dbo.MTP_master.NIIN

ORDER BY dbo.MTP_master.Shop, Cntrl#

I&S

Only works in SQL Server and Access after the where statement being removed, results are the same.

(WHERE (((dbo_Actl2MstrNIIN.Mstr_NIIN)=[forms]![problems_table].[niin]));).

SELECT dbo.dbo_Actl2MstrNIIN.Actl_NIIN, dbo.dbo_Actl2MstrNIIN.Mstr_NIIN, dbo.dbo_Actl2MstrNIIN.OOU_Code

FROM dbo.MTP_master LEFT OUTER JOIN

dbo.dbo_Actl2MstrNIIN ON dbo.MTP_master.NIIN = dbo.dbo_Actl2MstrNIIN.InS_NIIN

MTAA BO BURNDOWN DATA

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.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 INNER JOIN

dbo.MTP_master ON dbo.dbo_MasterQueryTable.NIIN = dbo.MTP_master.NIIN

WHERE (dbo.MTP_master.Shop LIKE 'MTAA%%')

ORDER BY dbo.MTP_master.Shop, Cntrl#

MTAA DAILY MICAP AND BO REPORT

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.dbo_MasterQueryTable.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.dbo_MasterQueryTable INNER JOIN

dbo.MTP_master ON dbo.dbo_MasterQueryTable.NIIN = dbo.MTP_master.NIIN

WHERE (dbo.MTP_master.Shop LIKE 'MTAA%%')

ORDER BY dbo.MTP_master.Shop, Cntrl#

MTCC-MTCF Daily MICAP and BO Report

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

WHERE (dbo.dbo_MasterQueryTable.Shop LIKE 'MTCC%%') OR

(dbo.dbo_MasterQueryTable.Shop LIKE 'MTCF%%')

ORDER BY dbo.dbo_MasterQueryTable.Shop, Cntrl#

Select Airframe by NIIN

SELECT NIIN, MDS

FROM dbo.dbo_ApplicationData

WHERE (MDS LIKE '[A,B,C,F]%')

Select Problem_items by PSSD

The where statement was removed (WHERE (((MTP_master.Shop) Like [forms]![switchboard]![shop select].[value] & "*"));), Results are the same, but had to rewrite/Change the Query to get matching results.

SELECT dbo.MTP_master.Shop, dbo.MTP_master.NIIN AS Expr1, 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

FROM dbo.MTP_master INNER JOIN

dbo.problems_table ON dbo.MTP_master.NIIN = dbo.problems_table.niin

Subs against actual data

SELECT dbo.[5_data].NIIN, dbo.[count subs].Actl_NIIN, dbo.[count subs].CountOfMstr_NIIN

FROM dbo.[5_data] LEFT OUTER JOIN

dbo.[count subs] ON dbo.[5_data].NIIN = dbo.[count subs].Mstr_NIIN

GROUP BY dbo.[5_data].NIIN, dbo.[count subs].Actl_NIIN, dbo.[count subs].CountOfMstr_NIIN

x 6_Goal1

Do not have any results in SQL Server or in Access. In Access the Query is broken, No design view or output, can only see the SQL statement.

Import excel file into MAN Version and was excluded

SELECT MTP_master.Shop, MTP_master.NIIN, MTP_master.FSC, MTP_master.Noun, MTP_master.MMAC, MTP_master.BO_Tot, MTP_master.G019C, MTP_master.QDR, ((mtp_master.BO_tot/4)+mtp_master.QDR) AS [BO/5+QDR], IIf([PSSDs] Is Null,[EXP_goal],mtp_master.G019C) AS Target, IIf([bo/5+QDR]<0,mtp_master.QDR,[bo/5+qdr]) AS EXP_Goal, MTP_master.[CNTL-NR], LIP_PBA_PSSDs.[New PSSD] AS PSSDs, targets.Target AS [Prev Target], past_repair.FirstQuarterPastRepair AS [Prev Qtr Org Repair], past_repair.FirstQuarterPastAddlRepair AS [Prev Qtr Addl Repair]

FROM ((MTP_master LEFT JOIN LIP_PBA_PSSDs ON MTP_master.Shop=LIP_PBA_PSSDs.[New PSSD]) LEFT JOIN past_repair ON MTP_master.NIIN=past_repair.NIIN) INNER JOIN targets ON MTP_master.NIIN=targets.NIIN;