CODEWITHSUNDEEP
excelparameter-passingmicrosoft-query
giordano
giordano

Reputation: 3162

Excel: Parametrizing using Microsoft Query

I use Excel as front end to display data from a database. Concretely, I have customer information in a MariaDB database and with sql I extract for each customer information from the database using Microsoft Query/ODBC:

SELECT name, n_consultation
FROM consultation
WHERE customer_id = 1

So, for each customer I have one Excel workbook with five sheets where each sheet has another sql-query with the same customer.

To create a new Excel workbook I make a copy of the Excel workbook of customer 1, change name of the workbook and in each sheet I change the WHERE part by changing the customer_id = 1 to customer_id = 2. This I can do quite fast by short cuts (ALT+V, J, etc.).

Having 10 customer and 5 sheets for each workbook I have to do 50 changes and this is feasible. But in case of 100 customer this is not manageable. Is there a way to use a parameter with Microsoft Query/ODBC? For example, the queries could be written with:

SELECT name, n_consultation
FROM consultation
WHERE customer_id = @id

and @id can be defined in the first sheet.

If this is not possible I suppose that the only alternative is using VBA.

Upvotes: 0

Views: 119

Answers (1)

Nadun
Nadun

Reputation: 1

I propose to change the SQL syntax at Queries & Connection--> Properties --> Definition tab like follows WHERE customer_id = ?.

Upvotes: 0

Related Questions