Hello,
I'm running a .accdb database in Access 2010 on a PC with the Windows7 Enterprise operating system.
Whenever I'm trying to open a query (written in Notepad++ and pasted into the "SQL View") in the "Design View" Access crashes.
Googling this lead me to fist believe that something within the Query is corrupt and a re-creation would solve the problem and then I followed the next advice and re-created the relevant portion of the database.
Currently I'm learning SQL so its very possible that there is something wrong with my code... It does execute though. Please excuse the unprofessional code, its just a test file. The code is below.
A fix would be awesome - but if this is a known problem that can't be solved that would be a okay to. I don't really need to use the feature but would like to explain what is going on when handing over the final database
Thank you very much!
Code:
SELECT
query1.PN,
query1.IN_SERVICE_2015,
query1.ON_ORDER_2015,
query1.RETIRED_2015,
query2.IN_SERVICE_2016,
query2.ON_ORDER_2016,
query2.RETIRED_2016,
query3.IN_SERVICE_2017,
query3.ON_ORDER_2017,
query3.RETIRED_2017
FROM (((
SELECT [PN SCOPE TABLE].PN,
Sum([FLG ACT DATA TABLE].[Total In Service]) AS [IN_SERVICE_2015] ,
Sum([FLG ACT DATA TABLE].[Total On Order]) AS [ON_ORDER_2015],
Sum([FLG ACT DATA TABLE].[Total Retired]) AS [RETIRED_2015]
FROM (((([PN SCOPE TABLE]
INNER JOIN [PN TSURPLUS DATA TABLE] ON [PN SCOPE TABLE].PN = [PN TSURPLUS DATA TABLE].PN)
INNER JOIN [PN ACT LINK TABLE] ON [PN SCOPE TABLE].PN = [PN ACT LINK TABLE].PN)
INNER JOIN [ACT FLG ACT LINK TABLE] ON [PN ACT LINK TABLE].ACT = [ACT FLG ACT LINK TABLE].ACT)
INNER JOIN [FLG ACT DATA TABLE] ON [ACT FLG ACT LINK TABLE].[FLG ACT] = [FLG ACT DATA TABLE].[FLG ACT])
WHERE ((([PN SCOPE TABLE].PN)="740119H") AND (([FLG ACT DATA TABLE].Year)=2015))
GROUP BY [PN SCOPE TABLE].PN
) AS query1
INNER JOIN (
SELECT [PN SCOPE TABLE].PN,
Sum([FLG ACT DATA TABLE].[Total In Service]) AS [IN_SERVICE_2016] ,
Sum([FLG ACT DATA TABLE].[Total On Order]) AS [ON_ORDER_2016],
Sum([FLG ACT DATA TABLE].[Total Retired]) AS [RETIRED_2016]
FROM (((([PN SCOPE TABLE]
INNER JOIN [PN TSURPLUS DATA TABLE] ON [PN SCOPE TABLE].PN = [PN TSURPLUS DATA TABLE].PN)
INNER JOIN [PN ACT LINK TABLE] ON [PN SCOPE TABLE].PN = [PN ACT LINK TABLE].PN)
INNER JOIN [ACT FLG ACT LINK TABLE] ON [PN ACT LINK TABLE].ACT = [ACT FLG ACT LINK TABLE].ACT)
INNER JOIN [FLG ACT DATA TABLE] ON [ACT FLG ACT LINK TABLE].[FLG ACT] = [FLG ACT DATA TABLE].[FLG ACT])
WHERE ((([PN SCOPE TABLE].PN)="740119H") AND (([FLG ACT DATA TABLE].Year)=2016))
GROUP BY [PN SCOPE TABLE].PN
) As query2 ON query1.PN = query2.PN)
INNER JOIN (
SELECT [PN SCOPE TABLE].PN,
Sum([FLG ACT DATA TABLE].[Total In Service]) AS [IN_SERVICE_2017] ,
Sum([FLG ACT DATA TABLE].[Total On Order]) AS [ON_ORDER_2017],
Sum([FLG ACT DATA TABLE].[Total Retired]) AS [RETIRED_2017]
FROM (((([PN SCOPE TABLE]
INNER JOIN [PN TSURPLUS DATA TABLE] ON [PN SCOPE TABLE].PN = [PN TSURPLUS DATA TABLE].PN)
INNER JOIN [PN ACT LINK TABLE] ON [PN SCOPE TABLE].PN = [PN ACT LINK TABLE].PN)
INNER JOIN [ACT FLG ACT LINK TABLE] ON [PN ACT LINK TABLE].ACT = [ACT FLG ACT LINK TABLE].ACT)
INNER JOIN [FLG ACT DATA TABLE] ON [ACT FLG ACT LINK TABLE].[FLG ACT] = [FLG ACT DATA TABLE].[FLG ACT])
WHERE ((([PN SCOPE TABLE].PN)="740119H") AND (([FLG ACT DATA TABLE].Year)=2017))
GROUP BY [PN SCOPE TABLE].PN
) As query3 ON query1.PN = query3.PN);