Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    crobaseball is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2014
    Posts
    128

    Grouping queries in Navigation pane . . . can I create further subgroups?

    Hello, I have a LOT of queries I have to build, which I then import into Excel (Yes, they HAVE to go into Excel for our customers). To organize them, I know I can group them as seen below, but is there any way to be able to then create further sub-groups to organize everything? e.g. - Under American Olean, I'd like to further sub-group these by Amber Valley.





    Thanks!

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Not as far as I tell, but then I've never done any custom grouping of objects.

    I wonder why you need so many queries as opposed to maybe dynamic filtering of a single query. Would have to know more about your data structure.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    crobaseball is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2014
    Posts
    128
    I'd love to do dynamic filtering, but here's the issue: I have the various queries which you see above populating MANY different Excel spreadsheets. Essentially, the spreadsheets are a pricebook which we email to customers. So each query is imported and linked to the database, which we can then update with various changes, which then automatically update the Excel spreadsheets/pricebooks. It looks like this:



    But because I need each query "active" at all times to populate the spreadsheets, I don't know how I could create a dynamic filter for each spreadsheet.

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Understood.

    Next question, why spreadsheets and not maybe a PDF of a report?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    crobaseball is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2014
    Posts
    128
    Quote Originally Posted by June7 View Post
    Understood.

    Next question, why spreadsheets and not maybe a PDF of a report?
    Again, I HAVE TO USE EXCEL. There are interactive numbers which are used. Discounts which are distinct for each client. They type their discount in, which then gets applied to the data I supply from Access. Most don't have Access and wouldn't know how to use it. We also use excel as a calculator with the data to set margins for pricing.

  6. #6
    crobaseball is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2014
    Posts
    128
    Follow up q: with the situation I have, am I correct in the conclusion I have that a dynamic filter isn't feasible?

  7. #7
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Maybe. Depends on how you 'import'. Are you using Excel wizard to set links to the Access query objects?

    Alternative could be lots of code in VBA to fill cells of spreadsheet.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  8. #8
    crobaseball is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2014
    Posts
    128
    I am indeed using the Excel import on the Excel ribbon.

    I'm open to using VBA, but I'm a novice, and I'm wondering if the code might be more than I want to jump into at this point. I am very much open to learning, and will, in fact, be looking to dive into using VBA more and more, but I do have a deadline with this particular project

  9. #9
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Then save the VBA for a boring, rainy day.

    I haven't used the Excel import/linking much. Are you creating an Excel workbook for each client? Is this a dynamic link?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  10. #10
    crobaseball is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2014
    Posts
    128
    lol. But I'm a nerd and I like this kind of stuff. I need it for a sunny day . . .

    No, there isn't a workbook for each client, there's a workbook/worksheet for each series of items. So if you look at the following example:

    This is one worksheet for the "Amber Valley" series. I then have many more worksheets, for different series (plural), all of which will be linked to 4-6 queries filtering the information. The tables which you see represent 3 different links to the database.

    I'm getting the sense that writing VBA for this will be easier, but here's another major catch: this is a one off project for these guys. They have to be able to add new series/vendors in the future. I can show someone how to copy/paste a query, then make changes to the filters to create new links, but asking them to copy/paste new vba? forget about it.

  11. #11
    crobaseball is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2014
    Posts
    128
    Let me ask you this: what if I started really small with the VBA code. On these worksheets, I need to show the most recent pricing update "time". So my query looks like this:

    I then have would have one cell in my worksheet which would have the effective date information. I could just as easily use the primary key to pull the value in. What would the VBA code look like to populate this single cell in an excel worksheet? Here's the connection string for this link (if it helps): Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=C:\Users\Ro Family\Desktop\Charles\Consulting\Design Materials\_DMI Test - master supplier price database - 2copy.accdb;Mode=Read;Extended Properties="";Jet OLEDB:System database="";Jet OLEDB:Registry Path="";Jet OLEDB:Engine Type=6;Jet OLEDBdatabase Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDBdon't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False;Jet OLEDB:Support Complex Data=False;Jet OLEDB:Bypass UserInfo Validation=False

  12. #12
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    How does each import/link not overwrite the one below it if there are more than 3 records?

    Ideally, users would never go near tables and queries and interact with data only through forms and reports.

    If the VBA were done properly, the users would never know it's there and they could get spreadsheets that show whatever they need. The code would be quite complicated. This is always the case with 'user-friendly' - the more friendly the more code. How much 'friendly' are they willing to pay for?

    There are many examples on web of code manipulating Excel objects. For a start, review the links found in http://forums.aspfree.com/microsoft-...el-414974.html

    An Access report open in Print Preview can be exported directly to Excel but the output is not always satisfactory.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  13. #13
    crobaseball is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2014
    Posts
    128
    The link can be set to include or delete rows as needed by the size of the data. So if, e.g. - a color is discontinued, I can have it marked as such in the db, and the row auto disappears in the excel worksheet.

    And yes, ideally users would never go near anything data related, but we're talking about a level of programming and sophistication which this budget does not warrant. I have to balance out keeping a simple way of going about doing things without going through a lot of lengthy programming.

    Thanks for the link to get started! Really appreciate it. I'll look at it tonight

  14. #14
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Educate me some more. If this is a live link to query in Access, what happens when you send the Excel file to client? They don't have the Access file available for the link but the Excel will show the data retrieved from the last 'update'? The 'update' is a manually triggered action on the Excel sheet? If this is the case, I don't see why a dynamic query in Access won't serve. The Excel 'update' should retrieve only the records allowed by the filter parameter. You are already doing this with a static parameter. The question is whether or not the dynamic parameter can be a reference to a form control where user inputs the filter criteria. The alternative is a popup input within the query. I have a vague recollection of addressing this very question in another thread.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  15. #15
    crobaseball is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2014
    Posts
    128
    Quote Originally Posted by June7 View Post
    Educate me some more. If this is a live link to query in Access, what happens when you send the Excel file to client? They don't have the Access file available for the link but the Excel will show the data retrieved from the last 'update'? The 'update' is a manually triggered action on the Excel sheet? If this is the case, I don't see why a dynamic query in Access won't serve. The Excel 'update' should retrieve only the records allowed by the filter parameter. You are already doing this with a static parameter. The question is whether or not the dynamic parameter can be a reference to a form control where user inputs the filter criteria. The alternative is a popup input within the query. I have a vague recollection of addressing this very question in another thread.
    Shoot, I thought I posted this last night but apparently got interrupted:
    We post the spreadsheet to our website which can then be downloaded, so yes, when info is updated, the spreadsheet/s have to be opened initially to get updated information, then uploaded to the website. The changes aren't constant, more like a few times a year. But when you have 30+ vendors, it's a lot of updating of the actual database. I was thinking about this myself. I can't have a "general" pricebook where the user inputs the series they want to then download the data. If it was a web-based pricebook, this would of course be perfect. But since they download the spreadsheet, and the links would then be "cut", they will use the data which has already populated. But each pricebook has to have the data already in it. i.e. - no, I can't make it dynamic in the sense of having a general pricebook with customer input to then be the dynamic filter. That's a much cleaner solution of course. And eventually, the tile business will get to the point where everyone will do this, but we're 5-10 years out from that.

    I'm also dealing with some VERY old school businesses (as in they have Excel 1997, or have us mail/fax the pricebooks to them) with buyers who are NOT terribly sophisticated. They can build you an awesome looking house, but playing with a spreadsheet isn't their game. Some are quite sophisticated, but I have to plan for the simplest user (as you well know).

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. How to create a new Group in the Navigation Pane?
    By JohnEMitchell in forum Access
    Replies: 16
    Last Post: 11-08-2013, 05:55 PM
  2. Replies: 2
    Last Post: 04-21-2013, 08:03 AM
  3. Navigation Pane
    By Patrick.Grant01 in forum Programming
    Replies: 11
    Last Post: 01-08-2013, 04:55 PM
  4. Navigation Pane Help Please...
    By Kristena in forum Programming
    Replies: 2
    Last Post: 01-13-2012, 03:03 PM
  5. Control navigation pane
    By ohporter in forum Access
    Replies: 0
    Last Post: 05-27-2008, 12:45 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums