Best way to pull multiple SQL data queries into Excel

[Jon_London]

Hi there everyone

I have a spreadsheet that has each month of the year as a column and each of our products as a row.

I need to use SQL data queries to pull through the total amount of sales per product each month. I have the SQL script for each month/product, but I'm unsure of the best way to pull this into Excel using a Data Connection.

An example of my spreadsheet is shown below (shown as a table here). So, I would want to pull down data for e.g. Product1 for Jan, Product2 for Jan and so on. My method of creating a query for each and then "Loading" the output of the query into the relevant cell as a table, then removing the table header, seems very long winded. I'm sure there must be an easier way!

Any help would be much appreciated.

Many thanks
Jon

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Jan[/TD]
[TD]Feb[/TD]
[TD]Mar[/TD]
[TD]Apr[/TD]
[TD]May[/TD]
[TD]Jun[/TD]
[TD]Jul[/TD]
[TD]Aug[/TD]
[TD]Sep[/TD]
[TD]etc...[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Product1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Product2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Product3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Product4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[jmacleary]

Hi there.

Why not have a single query that pulls down all the raw data in one go, and then build up your table with either formulae (maybe countifs) or create a pivot table on the results. You could also consider using a more complex query that does the summing for you.

 

[Jon_London]

Thanks so much. I'll try that and see if it works for me.