1.
4A_I&S
(replaced
dbo.[I&S].OOU_Code from the dbo_Actl2MstrNIIN instead of what was present
in the MAE Version. Replicated this by
looking at the MAN Version. SQL Server
gets results (626045 SQL Server displayed records) and Access does not-cannot
compare output),
SELECT dbo.MTP_master.NIIN,
dbo.[I&S].Mstr_NIIN, dbo.[I&S].Actl_NIIN, dbo.[I&S].OOU_Code
FROM dbo.MTP_master CROSS JOIN
dbo.[I&S]
2.
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 CROSS JOIN
dbo.dbo_requisition
GROUP BY
dbo.MTP_master.NIIN, dbo.dbo_requisition.project_code, dbo.MTP_master.Shop
ORDER BY
SumOfquantity DESC
3.
chart
The where
statement was excluded (WHERE ((([5_data].Shop) Like
[forms]![switchboard]![shop select].[value] & "*"));) the results
are consistant in SQL Server and Access
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 LEFT OUTER JOIN
dbo.[Monthly Production
Archive] ON dbo.[5_data].NIIN = dbo.[Monthly Production Archive].NIIN LEFT
OUTER JOIN
dbo.[Preferred Nouns] ON
dbo.[5_data].NIIN = dbo.[Preferred Nouns].niin
4.
Daily MICAP and BO Report
The where
statement was removed (WHERE (((MTP_master.Shop) Like forms!switchboard![shop
select].value & "*")))
Access crashes on data retrival and SQL Server takes a very long time.
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 CROSS JOIN
dbo.MTP_master
ORDER BY
dbo.MTP_master.Shop, Cntrl#
5.
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));). When removed results are the same
SELECT dbo.dbo_Actl2MstrNIIN.Actl_NIIN,
dbo.dbo_Actl2MstrNIIN.Mstr_NIIN
FROM dbo.MTP_master LEFT OUTER JOIN
dbo.dbo_Actl2MstrNIIN ON
dbo.MTP_master.NIIN = dbo.dbo_Actl2MstrNIIN.InS_NIIN
6.
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.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