Limit Access web app query result to one.

Question

Hi All,

I have a simple table(Refer below) from which I want to get a single Status field as result that is last created filter by Project Id.

For example, if I send ProjectId 2 as parameter value then I should get "Complete" as Status value. I am able to create a query but don't know how to limit the result to single record. The result should have single column and single value.

Below is my query and query result.

Thanks in Advance.

Regards

---

Monali

Answer 1

Hi,

If I'm understanding your scenario correctly, the only way to determine what is the last status for a specific project is to include the Created field in the query. It doesn't appear that you included that into the query.

To achieve your result, follow these steps:

- Add the Created field to the query output.
- Sort the Created field to be Descending so the latest one appears at the top of the returned query datasheet.
- Remove the Descending sort you currently have on the ID field.
- On the Query Design ribbon enter 1 into the Return box. It is located just below the Delete Columns button in the Query Design ribbon tab. The Return box is a drop-down list of values so you will have to manually type in 1 for the value here. This will tell Access to only return one row for the query.
- Optionally you can hide the ID and Created fields in the query output if you don't want to see those values by clearing the Show check boxes for those columns (like you did for the ProjectID column).
- Save your query design changes now.
- Run the query and provide the value of 2 for the ProjectNo parameter when prompted in Access.
- Check the result. Access should only display one row for this project with the status as Completed since 11/25/2015 4:37:13 AM was the latest entry.

I hope that helps,

--------------------
Jeff Conrad - Access Junkie - MVP Alumnus
Senior Content Developer - Modern Assistance and Support Experience - Microsoft Corporation

Author - Microsoft Access 2013 Inside Out
Author - Microsoft Access 2010 Inside Out
Co-author - Microsoft Office Access 2007 Inside Out
Access 2007/2010/2013 Info: http://www.AccessJunkie.com

----------
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/en-us/legal/Copyright/Default.aspx
----------

Answer 2

Hi Jeff,

Thank you for your answer. That partially solves my problem. I am now able to restrict my query result to one. However, when I unchecked the Created field to not show in query I get below access error. Is there a workaround for this where I can use the Created field to sort my query but not show them in the result.

---

Monali

Answer 3

Hi Monali,

You're correct that Access won't allow you hide the Created field in this case since we need to sort on it to get the latest status. This shouldn't be a problem though to include this field in the query output though because you can't directly view the results of a query datasheet within the browser interface of an Access web app. You can't just run a command in the browser that says display the results of a query in a query datasheet view. You can open a view that is based on this parameter query for example and then just hide that field from the view so your users won't see it.

--------------------
Jeff Conrad - Access Junkie - MVP Alumnus
Senior Content Developer - Modern Assistance and Support Experience - Microsoft Corporation

Author - Microsoft Access 2013 Inside Out
Author - Microsoft Access 2010 Inside Out
Co-author - Microsoft Office Access 2007 Inside Out
Access 2007/2010/2013 Info: http://www.AccessJunkie.com

----------
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/en-us/legal/Copyright/Default.aspx
----------