Results 1 to 7 of 7

Query to select the first record in a set

  1. #1
    yukionna is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2018
    Posts
    4

    Query to select the first record in a set

    I’m posting for help with a query in Access 2010 that doesn’t return the results I expect.

    I have three tables:

    1. FIND (one FIND can have many RPs)
    2. RP (one RP can have many PSUs)
    3. PSU


    For testing, I have the following records in the tables:

    FIND Table
    FIND 1
    FIND 2
    FIND 3



    RP Table
    RP 10 (Pending) – FIND 1
    RP 11 (Closed) – FIND 2
    RP 12 (Open) – FIND 3

    PSU Table
    PSU 01: FIND 1 – RP 10 (Pending) – PSU April
    PSU 02: FIND 1 – RP 10 (Pending) – PSU May
    PSU 03: FIND 1 – RP 10 (Pending) – PSU June

    PSU 04: FIND 2 – RP 11 (Closed) – PSU May
    PSU 05: FIND 2 – RP 11 (Closed) – PSU June
    PSU 06: FIND 2 – RP 11 (Closed) – PSU July
    PSU 07: FIND 2 – RP 11 (Closed) – PSU August
    PSU 08: FIND 2 – RP 11 (Closed) – PSU September

    PSU 09: FIND 3 – RP 12 (Open) – PSU August
    PSU 10: FIND 3 – RP 12 (Open) – PSU September
    PSU 11: FIND 3 – RP 12 (Open) – PSU October

    What I want to accomplish in the query is to select the records that meet the following conditions and the output will contain fields from all three tables:
    IF RP status = “Open” then select the PSU October record
    IF RP status = “Closed” then select any PSU record
    IF RP status = “Pending” then select any PSU record

    The results I want to see from the query are the following three records:
    FIND 1 – RP 10 (Pending) – PSU June (randomly chosen PSU is ok)
    FIND 2 – RP 11 (Closed) – PSU August (randomly chosen PSU is ok)
    FIND 3 – RP 12 (Open) – PSU October

    The results I see from my query are the following two records:
    FIND 2 – RP 11 (Closed) – PSU August (randomly chosen PSU is ok)
    FIND 3 – RP 12 (Open) – PSU October

    What is missing is: FIND 1 – RP 10 (Pending) – PSU June (randomly chosen PSU is ok)
    I don’t know why my query doesn’t select that record

    My query is set up with the following criteria in the RP status and PSU Date fields:
    RP status = “Open” and PSU Date = [Enter Status Period:]
    (This works and correctly selects the October record when I enter a status period of “October”)

    RP status = “Closed” and PSU Date = (Select First([t_Progress Status].[Status Period]) from [t_Progress Status])
    (This works and selects one PSU record from the set.)

    RP status = “Pending” and PSU Date = (Select First([t_Progress Status].[Status Period]) from [t_Progress Status])
    (This does NOT work and doesn’t return any PSU records from the set.)

  2. #2
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    13,060
    What does all of this mean in simple, plain English?
    How would you describe your "business" to a person who doesn't know you or your environment or database?

  3. #3
    yukionna is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2018
    Posts
    4
    Quote Originally Posted by orange View Post
    What does all of this mean in simple, plain English?
    How would you describe your "business" to a person who doesn't know you or your environment or database?
    Apologies...I thought this was a technical forum so I was trying to be as technical as possible. I'm not looking for anyone to write code for me but would like help in figuring out what I'm doing wrong so that is why I provided so many details.

  4. #4
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    13,060
    The forum can handle technical. But we'd like some idea of what we are dealing with. You know --context-- the 30,000 ft overview of how these pieces fit together. A jpg/png of your relationships window would also be helpful. And if you want help with a query it is best to show any attempt you have made/are working with. Preferably the SQL of that query.

    Finding first or last etc often depends on things like a sequence number, a Date(earliest/latest), an Amount(lowest/highest).
    You will also find that Access is real picky with Object and field names that contain embedded spaces--best to avoid these.

    Good luck with your project.

  5. #5
    yukionna is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2018
    Posts
    4
    Quote Originally Posted by orange View Post
    The forum can handle technical. But we'd like some idea of what we are dealing with. You know --context-- the 30,000 ft overview of how these pieces fit together. A jpg/png of your relationships window would also be helpful. And if you want help with a query it is best to show any attempt you have made/are working with. Preferably the SQL of that query.

    Finding first or last etc often depends on things like a sequence number, a Date(earliest/latest), an Amount(lowest/highest).
    You will also find that Access is real picky with Object and field names that contain embedded spaces--best to avoid these.

    Good luck with your project.
    I though Access supported spaces in field names? Is this something new that it is no longer supported?

    I've attached a screen print of the tables and my query criteria. Click image for larger version. 

Name:	Access issue.png 
Views:	11 
Size:	38.9 KB 
ID:	36166

    The SQL for the query is:

    SELECT [t_Remediation Plan].[Remediation Plan Status], [t_Progress Status].[Status Period], [t_Remediation Plan].[Remediation ID], IIf([t_Remediation Plan].[Remediation Plan Status]="Closed","V",IIf([t_Remediation Plan].[Remediation Plan Status]="Pending Closure","Closed",[t_Progress Status].[RAG Status])) AS RAG_Status, IIf([t_Remediation Plan].[Remediation Plan Status]="Closed","Complete - Validated",IIf([t_Remediation Plan].[Remediation Plan Status]="Pending Closure","Submitted to EBS Risk & Compliance for closure approval",[t_Progress Status].[Progress Status Update])) AS Progress_Status_Update, Left([t_Finding].[Overall Risk Rating],1) AS Overall_Risk_Rating, [t_Progress Status].[Progress Status ID], [t_Remediation Plan].[RP ID], t_Finding.[Finding ID], [t_Remediation Plan].Phase, [t_Remediation Plan].[FDL ID], [t_Remediation Plan].[Audit Flag], [t_Remediation Plan].[Legal Flag], t_Finding.[Issue Identified Date], [t_Remediation Plan].[Remediation Plan Target Due Date], [t_Remediation Plan].[Revised RP Target Due Date], [t_Progress Status].[RAG Status], [t_Remediation Plan].[Remediation Plan Name], [t_Progress Status].[Progress Status Update], t_Finding.[Overall Risk Rating], t_Finding.[Issue Owner], [t_Remediation Plan].[Remediation Plan Owner], [t_Remediation Plan].[Remediation Plan Owner Delegate], [t_Remediation Plan].[Associated Findings], [t_Progress Status].[Associated Remediation Plan]
    FROM (t_Finding INNER JOIN [t_Remediation Plan] ON t_Finding.[Finding ID] = [t_Remediation Plan].[Associated Findings]) INNER JOIN [t_Progress Status] ON [t_Remediation Plan].[Remediation ID] = [t_Progress Status].[Associated Remediation Plan]
    WHERE ((([t_Remediation Plan].[Remediation Plan Status])="Pending Closure") AND (([t_Progress Status].[Status Period])=(Select First([t_Progress Status].[Status Period]) from [t_Progress Status])) AND (([t_Remediation Plan].[Remediation ID])="RP-1030")) OR ((([t_Remediation Plan].[Remediation Plan Status])="Closed") AND (([t_Progress Status].[Status Period])=(Select First([t_Progress Status].[Status Period]) from [t_Progress Status])) AND (([t_Remediation Plan].[Remediation ID])="RP-28")) OR ((([t_Remediation Plan].[Remediation Plan Status])="Open") AND (([t_Progress Status].[Status Period])=[Enter Status Period ("Month Year"):]) AND (([t_Remediation Plan].[Remediation ID])="RP-34")) OR ((([t_Remediation Plan].[Remediation ID])="RP-54"));

  6. #6
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    13,060
    I though Access supported spaces in field names? Is this something new that it is no longer supported?
    Yes, Access supports it. As I said it gets real picky(syntax) with spaces in names, it requires such field names to be enclosed within [ and ].

    Perhaps you could describe the business so we can understand your set up. I tried to Google your PSU reference and found:

    Acronym Definition
    PSU Power Supply Unit
    PSU Plymouth State University (Plymouth, New Hampshire)
    PSU Pennsylvania State University
    PSU Portland State University (Portland, OR USA)

    My guess is you will get more focused responses when you supply a description in simple terms --no jargon.

    Good luck.

  7. #7
    yukionna is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2018
    Posts
    4
    The Finding table contains the information about various programs. The Remediation table contains information about the project(s) in each program. The PSU (project status update) table contains the monthly status report for each project.

    I want to create a monthly report which displays the status of every project regardless of its open/closed status. So essentially every record in the Remediation table will be part of the report. Once a project is completed or pending completion, it will not have any new entries added to the PSU table so that is why I have to manipulate those records differently than projects that are open.

    In my SQL where you see me filtering on specific remediation plan IDs, that code is included to pick specific records for testing.

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

Similar Threads

  1. Query to select a specific record from a Table
    By ismafoot in forum Queries
    Replies: 2
    Last Post: 05-24-2018, 12:34 PM
  2. select team based on max record .. query
    By mabughazza in forum Queries
    Replies: 1
    Last Post: 11-02-2015, 09:19 AM
  3. How to move to next record in a select query
    By lawdy in forum Programming
    Replies: 3
    Last Post: 02-17-2014, 12:31 PM
  4. Replies: 32
    Last Post: 05-23-2013, 03:16 PM
  5. Replies: 3
    Last Post: 02-27-2012, 11:12 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
  •  
Tech Forums: Microsoft Office Forums