1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Select some listings based on one column - need VBA

Discussion in 'Business Applications' started by slurpee55, Nov 11, 2009.

Thread Status:
Not open for further replies.
Advertisement
  1. slurpee55

    slurpee55 Thread Starter

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    Currently I have 83 products listed in one table in Access and a huge listing of people who use those products in another table. When I run a query to find all the ones who use the products listed I get 58113 connections.
    What I would like would be to randomly select 10 to 20 users of each product (the minimum number of users for any one product is 10, but the maximum is over 3000 for the most used product. I would then like to export these randomly selected names to different Excel worksheets in the same workbook - I suppose one could export them all and then break up the data into new worksheets based on the product code....

    Oh, and there are 10 products for whom there are no listed users, so I will have to match them up by their reporting districts instead and then select 10-20 as well...(I have run a query for this and the smallest group this way is 15 people.)

    Thanks in advance!
     
  2. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,836
    Must the users be Randomly selected?
    I think that you will need to use VBA combined with queries to do this. I would suggest that you you use Append Queries to dump the 10 to 20 results in to a Temporary table and then export that to Excel, although you could export the results directly to Excel in the VBA.
    Have you got an Excel sheet with the Sheets for the Parts set up?
     
  3. slurpee55

    slurpee55 Thread Starter

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    OBP, I apparently had an old email for you.
    However, Computerman is forwarding the Access file to you.
    Oh, and you are absolutely right - this is a VBA job and I probably should have stuck with Access all the way.
    What really is annoying, is that I got this data from a really major corporation and it is in Excel, rather than in a database, like it should be.

    The users are to be random - but - not if they are the only purchasers or part of a very small group.
    We need 20 names to call for this (we need 3 interviews [or so] per product sold.) So if there are only 5 people who bought the product, well, we need to call them all. But if 1 of them was also part of a group of 10 people who bought another product, well, that 1 can only be interviewed once, so that second group just became a group of 9 - they need to be made ineligible based on the fact that they may be in a smaller group.
     
  4. slurpee55

    slurpee55 Thread Starter

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    Oh, and to tell the people apart, I suggest you use the field named ID (not ID1.)
     
  5. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,836
    You fooled me by sending it to that email address as I only read it once a day in the mornings, whereas the Google one I check all the time.

    What you are asking is quite involved, especially the randomising part.
    So I will attack it one part at a time.
     
  6. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,836
    I have emailed you a your database with some test VBA.
     
  7. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Similar Threads - Solved Select listings
  1. SBaird
    Replies:
    6
    Views:
    522
  2. kimaso
    Replies:
    1
    Views:
    224
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/876498

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice