MS Access data in Excel: Divide a single query but keep data link (1 Viewer)

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

Howdy, and welcome to the board. I tend to add another XL worksheet, that pulls the data from this original one linked to Access. That way I can set it up anyway I want. The best thing to do is (dynamically) name the range of the worksheet that has the link to Access, which makes it easier to identify.
________
Ferrari 625 history

Thank you for the welcome and quick reply. Making a sheet with just the plain query is a good idea—I didn't think of that. I wanted to avoid having a worksheet that is only source data like that, but maybe that is the only way.

I haven't used dynamic named ranges before, but I looked into it, and it seems very useful. Do you mean to define ranges by using the OFFSET and COUNTIF functions? I saw some articles mentioning COUNTA instead, but in reading about the two in the Excel help files (topic ID HP10070480), it looks like this might be easier. What do you think?

• The data/query would be in its own worksheet.
• Another worksheet would reference this data and could then format in any way.
• The referencing would be through dynamic named ranges that would expand or contract as records are added or removed.

I started writing a try formula to define a dynamic named range, but it quickly started getting complicated. I had OFFSET, and another OFFSET nested in MATCH, and then one or two COUNTIFs. Do this sound like I'm going in the right direction?

Thanks again!

Dynamic Named range: Let's say it starts at A1, then MyRng would refer to:

=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),COUNTA(Sheet1!$1:$1))

The first COUNTA counts the number of used rows and the second counts the number of columns. As long as you never delete A!, this will always include all data in the range, no matter how much it shrinks or expands.
________
Xk6 Engine

Hmm, I see how the COUNTA counts the number of used rows or columns, but what about a range/table that looks like the top query in my first post? That is, to have the dynamic range not depend on whether the cell (row) has content or not, but depend on what the content actually is.

To make a value-dependent dynamic range was my thought in trying to use the COUNTIF function: The COUNTIF would count the number of rows in a product category even though there are other rows with values in the same column. All rows of a like category would be contiguous because the table would be sorted by category. But it would be tricky because the start of a category might be in the middle of the table, so the starting position and the ending position of the dynamic range would need to be flexible. The range formula would need to find the first occurrence of the value (e.g., "Clothes") and then count how many of them there are from the position of first occurrence. So would VLOOKUP be the best thing to use for that?


No, wait, VLOOKUP won't work because it doesn't return a reference... What about INDEX? Or maybe MATCH? I keep on thinking in programming-like terms, like using a DO WHILE loop to add rows to the dynamic range while the category is still the same. Is this getting in to the Visual Basic territory? Either way, I wish Excel had a cleaner way to enter long formulas!

If it isn't the left most column for criteria, then VLOOKUP won't work. Far more flexible is INDEX/MATCH for finding specific items because it can use any column for reference. But if you want subtotals, then with one criteria, use SUMIF, and if two criteria, use SUMPRODUCT. Each of them can use the dynamic ranges.

If you post a sample Excel file, I will set it up and show you how to do each.
________
Honda Z50A

Okay, using the data in your OP, I set up a Data worksheet (which will draw from the linked sheet in Access), with several dynamic range names. I also included an extra column for lookup purposes (Col A).

Data

=OFFSET(Data!$A$1,0,0,COUNTA(Data!$A:$A),COUNTA(Data!$1:$1))

DataLU

=OFFSET(Data!$A$1,0,0,COUNTA(Data!$A:$A),1)

ProdCat

=OFFSET(DataLU,0,2)

Prod

=OFFSET(DataLU,0,3)

Price

=OFFSET(DataLU,0,4)

Each will automatically update as you add more rows.

On Display worksheet, I set up the examples you had for summaries, and used INDEX/MATCH. In cell C3, I put this formula:

=INDEX(Data,MATCH($B$2&$B3,DataLU,FALSE),MATCH($C$2,DataHead,FALSE))

and copied down. then in cell C9, I put this:

=INDEX(Data,MATCH($B$8&$B9,DataLU,FALSE),MATCH(C$8,DataHead,FALSE))

Now, I suspect that you will want more, but this at least gives you an idea.

(The Lists worksheet is for reference, and could be used for dependent dropdown lists.)
________
Vaporizer Manufacturer

Wow, thank you very much! I will have a look at this and get back to you as soon as I can.

Your workbook really helped me a lot; it was so much easier to understand with an example. Thank you! I added ID number columns on the Data worksheet to make it more database-like and used those as look-up values instead of values concatenated from names. (I made dynamic named ranges for them.)

The biggest change I started to make—but got stuck—was to make dynamic ranges by product category that are next to each other in the data table. Using MATCH and COUNTIF in OFFSET, the ranges search for where a category begins and includes all of the records for that category until the category ends:

1. "Data" is the array to start the OFFSET from (upper left-hand corner).
2. The MATCH looks for the first occurrence of category ID within the ProductCategoryID column/field. The "-1" shifts the starting position of the range to compensate for the heading row.
3. "4" is the position (the first column is #0) in the array of the column where you want to start the range.
4. The COUNTIF counts the number of occurrences of the product ID. This value is used to set the height (number of rows) of the range. The data needs to be sorted by category for this to work.
5. "3" is the number of columns to extend the range.

After this is where I got stuck: I would like to replicate these new ranges on the "Display (revised)" sheet in place of the like values (product ID, product, and price) that are there now. That is, to have it the way the product name and price works now (which is great) on a range scale instead of a cell scale.

I could not figure out a way to kind of say, "Insert range AllShoes" here, so that the place it is interested would also update when the named range is updated, namely adding or removing records. Then the three columns would also be linked together in the display sheet (because they would be in an Excel table in the data sheet). Instead of saying, "=Data!E2", "=Data!F2", "=Data!G2", [new row] "=Data!E3" and so on, I want to say, "=AllShoes", where "AllShoes" is a named range. Is there a way to work around this?


Aside: Although they didn't appear to be useful for this particular situation, I came across "structured references" for Excel tables (Help topic ID # HA10155686), which seemed pretty powerful.

Howdy. From reading your last post, it seems that you are trying to use the Data tab as a display. Am I understanding that correctly? If so, it seems to be the wrong way to approach. This is why in your data layout, having another ID number doesn't really seem help. Does ProductID uniquely identify the Product? And will it be used elsewhere appart from Pdocut Name? If not, it might be redundant to have it.

Can you attach the file that shows what you want to display apart (another Display worksheet) from what is on the revised Display?
________
TOYOTA AYGO HISTORY

Oh, sorry for not being more specific: The "Display (revised)" worksheet/tab is more-or-less how I would like the data displayed. I have included the ProductCategoryID and ProductID numbers in columns A and B; I don't want them to show, so those two columns can be hidden.

The ID numbers uniquely identify a product category or product. They would be the primary keys in the ProductCategory and Products tables in the database where they come from. I added the ID numbers to make it more database-like since the "Data" tab in the test file is static and unlinked. In the "Display (revised)" worksheet, they are used as the look-up values instead of the Category&Product concatenations used in the original "Display" sheet. (So if "Display" is removed, the entire DataLU column in "Data" can be removed, but right now it is still needed because the OFFSETs/named ranges depend on it—or at least a blank column—being there.)

I intend the use the "Data" sheet to store the unformatted query and use that data in a display sheet since the query can't be formatted the way I want. I think I could get format I want (like in "Display (revised)") by using a query for each product category, for each customer, but that would make a whole lot of queries. For example, to get the current data as it is now, I only need to make one query, like:
To avoid my whole question to begin with, I could do three (I added a Hats category) separate queries:
because the category sections need to be separate. But this would add-up to a lot of queries a lot faster.

The first single query (by customer) would work if Excel tables could be both filtered and shifted (when the data updates), but Excel can't do it (original post, last paragraph).

The file attached with this post has the product lists in "Display (revised)" put into tables* to make them more database-like/more similar to how it would be when using queries by customer and category. If a new product is added to the customer (automatically through the linked data in "Data"), a new row needs to be added to a product category table in "Display (revised)" and the ProductID needs to be entered; with that, the product name and price fill-in automatically.

Once I use this workbook for real, the product additions and deletions won't happen too often, but it would be really nice if the adding and deleting of rows in the display sheet would happen automatically (because if the products did change a lot, it would be a pain to keep-up with!). That is why, in my last post, I was hoping to find a way to mirror the dynamic named ranges I made in "Data" (e.g., AllClothes) onto the display sheet because the ranges happen to have the format I want and they adjust automatically. After typing the above, I realize what you mean: you're right, relying on the format of the plain data is not a good idea.


* What version of Excel are you using? I have 2007, which allows the header row to be hid. The Compatibility Checker for (for .xls) says that the tables (with hidden header rows on "Display (revised)") will be un-tabled in earlier versions of Excel. I don't think it should be a big deal, though.

I have been out of town on work, so haven't looked at it. I will download and take a look today.
________
Suzuki Rm85

I think I am going to go with the method described in my last post/attachment. This should be pretty good since the products chosen for the display sheet won't change very often. It also avoids the problem of having to pre-format the query/data (sorted by product category) so that the dynamic named ranges function correctly.

I also looked into PivotTables, which are very close to what I want, but they are a little to confining for me. I also tried using a single, filtered PivotTable for each product category (instead of one PivotTable for everything), but that has the problem that when a new record is added, the table overwrites the rows underneath it instead of inserting a row.

Reports in Access are a possible solution, but I want to use Excel because it will be easier for others who will use this set-up; maybe in the future!

This should be a good start. Thank you for your help!

If you ever feel up to it, you might consider using VBA for Pivot Tables, and then continuing on with VBA using that data. Very fast, flexible, and uses power of Pivots with much greater options. Check out chapter 12 of VBA and Macros for MS Excel by Bill Jelen and Tracy Syrstad. That chapter alone is worth the price of the book.
________
Penny Stock Pick