Results 1 to 7 of 7
  1. #1
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query definition not saved (Office XP (aka 10, aka 2002))

    Just tried it on WinXP and A2000, no problems.

    I should try repairing Access first to see if that solves your problem.
    Obviously if you're having the same errors on other queries, the problem probably lies in Access !!

    Hope this helps.

  2. #2
    2 Star Lounger
    Join Date
    Dec 2002
    Location
    Bruges, Belgium
    Posts
    122
    Thanks
    14
    Thanked 2 Times in 2 Posts

    Re: Query definition not saved (Office XP (aka 10, aka 2002))

    Dave, it's reassuring that at least the query can be saved on other systems. But my environment is different from yours (WinMe, Office XP).
    See previous history of this thread at: JET version for Access 2002

    Robert

  3. #3
    2 Star Lounger
    Join Date
    Dec 2002
    Location
    Bruges, Belgium
    Posts
    122
    Thanks
    14
    Thanked 2 Times in 2 Posts

    Query definition not saved (Office XP (aka 10, aka 2002))

    The attached SQL_Tennis.mdb is a downscaled version of a database that contains two tables upon which I want to run an SQL query. So I open a new query in design mode and enter the following SQL query (View -> SQL query window)

    SELECT AVG(T.Total)
    FROM (SELECT P.PlayersNr, SUM(F.Amount) AS Total
    FROM tblPlayers AS P LEFT OUTER JOIN tblFines AS F ON (P.PlayersNr = F.PlayersNr)
    GROUP BY P.PlayersNr) AS T
    WHERE T.PlayersNr IN
    (SELECT PlayersNr
    FROM tblPlayers
    WHERE City IN ('Den Haag','Rijswijk'))

    Executing ( <font color=red> ! </font color=red> ) the query is no problem, but upon saving (actually closing the result window) Access does some (intensive) processing and shuts down with message <font color=red> Microsoft Access has encountered a problem and needs to close. We are sorry for the inconvenience </font color=red>. And an "inconvenience" it is. Although this is just a sample data base to test SQL queries, several similar queries will be used in production.

    I encounter this problem rather frequently (i.e. with other queries too) on my WinMe system.. The error occurs whether or not I enable ANSI 92 SQL compatibility.
    Attached Files Attached Files

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Query definition not saved (Office XP (aka 10, aka 2002))

    Which Access format are you using, 2000 or 2002? If the latter, try converting the database to 2000 format. Does the query behave itself there? If it does, convert it back to 2002 format again and see what happens. Have you tried a decompile on the database? If not, it's worth a try before you drive yourself <img src=/S/nuts.gif border=0 alt=nuts width=15 height=15>.
    Charlotte

  5. #5
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Query definition not saved (Office XP (aka 10, aka 2002))

    Sorry for the delay - holidays and hardware hiccups intervened. Anyhow, I was able to download your database and save the query with no problems, and my environment is the same as yours - WindowsME, and OfficeXP (with no SPs installed). So it sounds like there is something in your Windows install or Office install that is broken.
    Wendell

  6. #6
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Query definition not saved (Office XP (aka 10, aka 2002))

    On way to solve at least some problems is to use the Nz() function to return a 0 value for cases where the field is null. That won't work for all Average calculations, but it does for many. Using that approach should allow you to eliminate the UNION part of your query. Which raises a question:
    Are you doing your development strictly by doing the SQL text? If so, you would probably find it much quicker and less error prone to use the query grid design tool. The group by option and the LIKE clause would simplify the structure and not require the nexted SELECTs and INs. There are times when you need to work with the SQL string, for example in VBA, but it is usually more error prone and slower. One person's opinion though I'm sure there are people who would disagree.
    Wendell

  7. #7
    2 Star Lounger
    Join Date
    Dec 2002
    Location
    Bruges, Belgium
    Posts
    122
    Thanks
    14
    Thanked 2 Times in 2 Posts

    Re: Query definition not saved (Office XP (aka 10, aka 2002))

    Charlotte: Neither 2000 format nor decompile came to rescue.
    Wendell: A difference between our configurations is possibly the Office SP level: Mine is at SP2 (+additional JET 4 SP6). I'll try to check if anything in these SP's could affect the way the queries are saved.

    Both: ironically the query had a basic logic flaw, in that AVG does not take into account NULL values. The original question was : What is the average fine for players living in city A or B? The query that I submitted earlier didn't recognize that some players of those cities had no fines at all!
    A corrected version of the query looks like:

    SELECT AVG (at)
    FROM (SELECT tblplayers.playersnr, 0 AS at
    FROM tblplayers
    WHERE tblplayers.city IN ('Den Haag','Rijswijk')
    AND tblplayers.playersnr NOT IN (SELECT DISTINCT tblfines.playersnr FROM tblfines)

    UNION

    SELECT tblfines.playersnr, SUM(tblfines.amount) AS at
    FROM tblfines LEFT JOIN tblplayers ON ( tblfines.playersnr=tblplayers.playersnr)
    WHERE tblplayers.city IN ('Den Haag','Rijswijk')
    GROUP BY tblfines.playersnr)

    Amazingly, [red]this query can be saved without any problem !!![/red]

    SQL question: is there a simpler way to calculate averages over values that include NULLs?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •