Results 1 to 10 of 10
  1. #1
    Accessnoob is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Feb 2018
    Posts
    9

    Access 2010 crashes upon opening of executable SQL code in Design View

    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);

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    ?? What is the purpose of this query/SQL? --in business terms --plain English??

    Perhaps you could post a copy of your database in zip format.

  3. #3
    Accessnoob is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Feb 2018
    Posts
    9
    Sadly I can't post the database as it contains confidential information.
    The query is simply a test. It returns a table containing example values required for a the life cycle stage determination. What I was testing/learning is how to write a SELECT statement that contains data which is linked through two bridge tables.
    My database contains 12 data tables and select statements with (inner-)joins like tested in the query will be used to extract all information based on parameters from a form + vba. In the I simply used example values in the WHERE clause.
    In case you wanted to know what the query itself is doing: it is using the Part number to aircraft type connection and the aircraft type to flight global connection to return 3 separate sets of flight global data sums of all compatible aircraft types for the specific years next to each other. I hope that is a sufficient explanation, I'm not a native speaker, if I misunderstood please ask again! Thank you!

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Your English is very good and communicates well.
    In most development, the developer would work with data that can be traced/tracked/debugged easily until the logic was validated. This would be true for queries, forms, reports. Once the underlying logic was confirmed and outputs were deemed reasonable, then you might move to sample production(real) data. If you have some test data --Donald Duck, PorkyPig etc then I think you could post same without breaking any confidentiality. If you do not have such, then my approach would be to test individual components individually. Have you successfully got results for 2015; then 2016; then 2017.

    Can you get query1 working without involving query2 or 3 for example?

    Suggest you test individual joins, then add table and revise query ---that is test from smallest unit and gradually add tables and complexity.

    It is difficult to give advice when there is no "touchy-feely" database to test. And no error message(s) etc.

    Good luck.

  5. #5
    Accessnoob is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Feb 2018
    Posts
    9
    Thank you very much! Apparently I didn't describe the problem well enough.
    The query itself works perfectly - and the results match my expectation. That's why I was so surprised to find, that Access crashes when I try to open it in the "Design View".
    This is the "SQL View" - here i can work with the query and I can run it.
    Click image for larger version. 

Name:	sql view.JPG 
Views:	18 
Size:	168.5 KB 
ID:	33701
    However if I try to access the design view (below) Access stops working, restarts and no error code is presented.
    EDIT: The design view is pictured by a Pen, ruler and triangle.
    Click image for larger version. 

Name:	designview.JPG 
Views:	18 
Size:	48.4 KB 
ID:	33702

  6. #6
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    The query itself works perfectly - and the results match my expectation.
    That's the part I didn't understand...

    When I put your sql into my query designer sql window, then try to go to design view I get this message.

    Click image for larger version. 

Name:	Query123Error.png 
Views:	18 
Size:	12.5 KB 
ID:	33703
    Since I don't have any of the tables, I expected it fail.

  7. #7
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Am I correct in thinking this large query just has 9 fields - 3 for each year from 2015 to 2017.
    Also that each field is a sum based on an aggregate query for the same PN value in those years

    Why not instead create an aggregate query grouping each year separately with 4 fields;
    - Year (better to rename as PNYear or similar as Year is a reserved word), InService, OnOrder, Retired
    You would still get the same information but MUCH more easily

    I believe it could also be done using a CROSSTAB query - again much more simply than you have now

    If I've misread what this does, then I'll use the fact that its so convoluted as my excuse!
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  8. #8
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

  9. #9
    Accessnoob is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Feb 2018
    Posts
    9
    Hello,
    this sounds super interesting and though I feel like its not really related to my initial question I would like to get some feedback on this as well.

    This is what I would like to do.
    Part number 2015
    FLG
    DATA
    2016 FLG DATA 2017
    FLG DATA
    2017
    BW DATA
    OTHER DATA
    PN1 ACTIVE AC COUNT RETIRED AC COUNT ON ORDER AC COUNT ACTIVE AC COUNT RETIRED AC COUNT ON ORDER AC COUNT ACTIVE AC COUNT RETIRED AC COUNT ON ORDER AC COUNT AVG FLH ACTIVE AC AVG FLH Many more columns with data
    PN2…














    This is what the database looks like for the AC Data part. I hope that I chose the right connection symbols. The reason why there are 3 different ACT types is that this is a reality that I can't change. All the different departments classify them differently so the bridge tables are necessary as far as I can tell.
    Click image for larger version. 

Name:	ACT.JPG 
Views:	10 
Size:	62.0 KB 
ID:	33718

    My solution to this are several joined SELECT statements. Do you believe there is a better solution?

    When I experimented with the CROSSTAB query it didn't work with the Joins but only with one table...
    I haven't completely understood what is meant with an aggregate query but I will continue to look into it once I find time!
    Thanks so much in advance.

  10. #10
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Sorry I don't understand your latest post at all

    For the aggregate query, link whichever tables you need to get these 5 fields in your query
    Year
    Total In Service
    Total On Order
    Total Retired
    PN

    Now click the Totals symbol in the Design ribbon
    An extra Totals row appears with each field having Group By in that row

    Change that to Sum for the 3 'Total' fields using the drop down
    Change the PN field dropdown to Where and enter 740119H as your filter criteria - note the checkbox will be unticked so it won't be displayed
    If you have data for other years apart from 2015/2016/2017, enter 'Between 2015 And 2017' in the criteria for the Year field
    Add aliases to any field if you want to set specific field names
    Save the query & run it

    You will now have totals for the 3 years in separate rows in your query results
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 7
    Last Post: 12-08-2020, 09:20 AM
  2. Replies: 1
    Last Post: 04-04-2015, 12:24 PM
  3. Access 2010 Executable file
    By Desstro in forum Access
    Replies: 1
    Last Post: 01-22-2014, 03:59 PM
  4. Access 2010 - Form Design View
    By amestreet in forum Access
    Replies: 1
    Last Post: 03-17-2012, 06:02 PM
  5. Design view 'freezes' in Access 2010
    By dknowle in forum Access
    Replies: 1
    Last Post: 08-31-2011, 11:05 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums