Results 1 to 4 of 4
  1. #1
    JayRab is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Oct 2013
    Posts
    78

    Subquery messed up but only on a few values

    Ok made a subquery to grab next value on autonumber, for some reason it works at first then in themiddle it messes up and at end it is correct again.

    Here is SQL code
    Code:
    SELECT [Equipment DownTime].Equipment, [Equipment DownTime].ID, 
    (SELECT TOP 1 Dupe.ID
    FROM [Equipment DownTime] as dupe
    where dupe.equipment = [Equipment DOwntime].Equipment and Dupe.ID > [Equipment Downtime].ID) AS NextID
    FROM [Equipment DownTime]
    WHERE ((([Equipment DownTime].Equipment)="8071 TORO"))
    ORDER BY [Equipment DownTime].Equipment, [Equipment DownTime].ID;
    Click image for larger version. 

Name:	SubqueryIssue.jpg 
Views:	4 
Size:	64.1 KB 
ID:	15710
    Here is picture explaining what it is doing. As you can see in the beginning it is pulling in next ID properly then after 801 it starts grabbing 2362 not 1106, 1131, 1134 etc..??

    What is going on here?

    Any help appreciate.

    Thanks

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    I built a table with the sample data you posted and used your query. It works. Cannot replicate the issue.

    If you want to provide db for analysis, follow instructions at bottom of my post.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    JayRab is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Oct 2013
    Posts
    78
    I created another database as well and ran it and it worked.

    So went through and Compact&Repair all databases this was attached to and it worked. So thankyou for that.

    Future reference if it is acting stupid Compact and Repair it.

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Good point, I should have thought to explicitly suggest. Glad you figured it out.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

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

Similar Threads

  1. Messed up and need help
    By Diasflac in forum Access
    Replies: 1
    Last Post: 07-17-2013, 02:15 PM
  2. Messed up Listbox
    By mikewj1900 in forum Access
    Replies: 7
    Last Post: 08-10-2012, 11:54 AM
  3. Back from Vacation to find my database messed up
    By imintrouble in forum Access
    Replies: 4
    Last Post: 04-05-2012, 04:52 AM
  4. Input mask messed up my field data type
    By ghostmachine in forum Access
    Replies: 2
    Last Post: 11-11-2010, 05:32 PM
  5. Rookie Messed Up Main Input Form
    By genest11 in forum Forms
    Replies: 2
    Last Post: 01-14-2010, 02:17 PM

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