Hello,
My question is about using data from an Access database and using it in Excel. I would like to link to a single query, but be able to separate this data in Excel while keeping the data link.
For example, a query that selects all products associated with a single customer:
I would like to use this information in Excel, but with the ability to separate the query by product category so I can add subtotals and format them the way I like.
I can get the data into Excel just fine (it comes in as an Excel Table (formerly Excel List, pre-2007)). I can remove the customer ID column and hide the product category column OK because they have redundant information. My question is: Is there a way to add a blank row between the categories that will be independent of the Access data, but keep the link to the data in general? The goal is to get it to into something like this:
The problem is that an inserted row will be overwritten if the data is refreshed. If the Excel table with the data is copied twice or more and the data is filtered in each copy to show only one category, only the bottom-most table can update because Excel says it is not able to shift filtered cells (in the tables above if records are added or removed). I would like to keep the queries specific to only a customer, rather than a specific to both a customer and a category. Is there a way to do this?
Thank you very much for your help!
Ryan
My question is about using data from an Access database and using it in Excel. I would like to link to a single query, but be able to separate this data in Excel while keeping the data link.
For example, a query that selects all products associated with a single customer:
Code:
CustomerID ProductCategory Product Price
--------------------------------------------------------
0 Shoes Air Max $100
0 Shoes Snow boot $120
0 Shoes Flip-flop $30
0 Clothes T-shirt $15
0 Clothes Sweater $40
0 Clothes Pants $30
I would like to use this information in Excel, but with the ability to separate the query by product category so I can add subtotals and format them the way I like.
I can get the data into Excel just fine (it comes in as an Excel Table (formerly Excel List, pre-2007)). I can remove the customer ID column and hide the product category column OK because they have redundant information. My question is: Is there a way to add a blank row between the categories that will be independent of the Access data, but keep the link to the data in general? The goal is to get it to into something like this:
Code:
Shoes
----------------
Air Max $100
Snow boot $120
Flip-flop $ 30
----
$250
Clothes
----------------
T-shirt $ 15
Sweater $ 40
Pants $ 30
----
$ 85
================
Total: $385
The problem is that an inserted row will be overwritten if the data is refreshed. If the Excel table with the data is copied twice or more and the data is filtered in each copy to show only one category, only the bottom-most table can update because Excel says it is not able to shift filtered cells (in the tables above if records are added or removed). I would like to keep the queries specific to only a customer, rather than a specific to both a customer and a category. Is there a way to do this?
Thank you very much for your help!
Ryan