Results 1 to 7 of 7
  1. #1
    Join Date
    May 2009
    Posts
    5

    Unanswered: query assistance please please please

    Hi All,

    A new member to this forum so please be gentle.

    I have a access 2007 database with a table called VWTL

    In the table I have certain fields which capture a rating and a percentage.

    I am not sure which way to tackle this, but what I need is a query that produces an output that captures a certain percentage of each customers records.

    Fields include

    Customer, Order No, Rating & Percentage Driver

    I believe that a nested sql query could do the trick but I am struggling with this.
    P0508

  2. #2
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Design one query which sums up the total for your first percentage (if it's a 1 line total-great - ie. CC: 1 and then sum that field). Then just sum up all the records in a query where it just shows the total overall count (ie. use an expression CC: 1 and then set that field to sum using the Summarization button (backwards E.) - same as the first query. Then nest both in 1 query (or use dlookup) and do your calculation.

    If you're summing multiple values, you'll need to make sure you can link both tables to do the summing correctly. Find/create a common value in both summing queries to match on in the final nested query.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    Apr 2009
    Posts
    14
    Hi p0508

    Open the new query in design view and copy all the fields from your table into THE design grid.

    Next –put the criteria for the records to be selected in the criteria part of the grid for THE field(s) you’re selecting on.

    Next – run the query. The displayed records should satisfy the query. It’s usually a simple as that.

    If you still have difficulty package the whole MDB and send it up and I’ll fix it for you once you let me know the criteria you want to use.

    Mike

  4. #4
    Join Date
    May 2009
    Posts
    5
    Thanks pkstormy for replying but I dont think this will work..

    Customer Order No Rating Percetnage Driver
    Pete 2345 1 10%
    Paul 1254 10 100%
    Paul 0001 10 100%

    Is the sample data, each customer and order no can have multiple records. The rating and percentage is the same for all records that relate to that customer. So in the above example all of customer paul's records will be rating 10 percentage driver 100%. i need a query that will group via each customer and product the percentage driver amount of their records. So if the percentage driver is 10% show only 10% of that customers records. Random would be awesome

  5. #5
    Join Date
    May 2009
    Posts
    5
    Can someone please help

  6. #6
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580
    Please post 2 things only:

    Sample data
    Required output
    Windows Server 2003-8 / Terminal Services / SQL 2000 / Access 2003 / Office 2003-7 / Exchange 2003-7 / Blackberry Enterprise Server / AutoCAD / Lambert And Butler / Red Bull

  7. #7
    Join Date
    May 2009
    Posts
    5
    Hi garethdart,

    The required output is the same as the sample data but I require the query to look at the percentage driver and return that percentage of records per customer and random would be nice.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •