How to use a parameterized query in Excel using a column as a parameter?

Question

I am trying to create a spreadsheet that can find the relevant records in an external data source. So let's say I have column A with a list of identity values. I want to create column B, which possibly shows the number of rows in the table with this value. Something like:

AB 758348 "=SELECT COUNT(*) FROM MYTABLE WHERE IDVALUE=$A$1" 173483 "=SELECT COUNT(*) FROM MYTABLE WHERE IDVALUE=$A$2" 

... and so on. So, I thought that I would use a parameterized query (where IDVALUE =?), But this prompts me to enter the parameter value, and not use the value from the cell to the left. Is there any way to do this?

Answer 1

I would use a parameterized query (where IDVALUE =?), But this tells me to enter a parameter value

From what you mentioned, I understand that you are using MS Query, and you need to follow these steps

Actions to make a parameterized query in Excel, use the cell value as a parameter

1. First go to the "Data" tab in Excel and select "MS Query" in the "External Data Sources" section

.

2. A pop-up window will appear asking you to select a data source. Select a data source or add a new data source and select it. Uncheck Use the query wizard to create or edit queries , in most cases it is not very useful.

3. Then a pop-up window appears asking you to add a table, select any table at the moment. It is better to select a table with fewer rows.

4. Then a query window appears with the selected table and its data

.

5. Then click on the view menu and uncheck tables . This removes the graphical representation in the query window and makes it easy to modify your SQL query.

6. Click the SQL button under the format menu in the query window. A pop-up window arrives where you can edit the request as you wish. Make sure you add a question mark for the option you want.

7. Then a pop-up window will appear in which you will be asked to enter the parameter values ​​in order to enter valid values.

8. Then the query window shows the result of the query based on the entered parameter

9. Click the exit button under the view menu in the query window. Then the query window closes, and the "Import Data" pop-up window appears asking where to display the result in Excel. Choose accordingly

10. Then click the "Properties" button on the left before clicking "OK." A new popup will appear with the default tab selected.

11. Make the necessary changes to this tab and click the Definition tab. Click the Parameters button at the bottom of the popup window next to the edit query button

12. A Parameters pop-up window Parameters with the parameters used in the request. Here you must select the Get the value from following cell radio button to select the cell value as a parameter for the query. Click OK, and you're done.

    

---

This method is for people without VBA experience. If you know VBA, refer to this answer to achieve something similar with VBA.

Answer 2

To do this, I would create a UDF and have a backend for this function connecting to your access database. Something like that:

 Public Function countMyTable(IDValue As Double) As Long 'Requires:// Microsoft Access 16.0 Object Library 'Requires:// Microsoft Office 16.0 Access database engine Object Library Dim db As DAO.Database Dim rs As DAO.Recordset Set db = Access.DBEngine.Workspaces(0).OpenDatabase(DBFilePath, False, False) Set rs = db.OpenRecordset("SELECT COUNT(1) FROM MyTable WHERE IDValue = " & IDValue, dbOpenSnapshot) rs.MoveLast countMyTable = rs(0) db.close End Function 

Hope this helps, TheSilkCode