Results 1 to 10 of 10
  1. #1
    Join Date
    Jan 2004
    Location
    Preston, Lancashire, UK
    Posts
    26

    Unanswered: Running sub total in query

    Hi all

    Iím trying to run a query that will only output suppliers, where their accumulative spend is greater than 80% of the overall spend. Easy to do in excel (Book1.xls) but Iím struggling to sub total a query row by row (db1.mdb Ė Query2). Iím guessing Iíll have to do something with recordsets, unless thereís a way in sql

    Thanks
    Attached Files Attached Files

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Hi Darren
    you can achieve this either using queries or VBA or a mix of both.

    Lets assuem you are going don the route of queries
    I'd suggets you break down yourproblem into manageable
    steps

    1) create a query which identifies your 80% spend threshold, based on the business logic you are trying to represent. depends on you data but probably includes a sum, avg or other form of SQL aggregate function as well as a group by

    2) create another query which the compares that threshold value against individual customers (the first query could be a subselect in this query), or just dragged in as a value from that query.

    HTH

  3. #3
    Join Date
    Jan 2004
    Location
    Preston, Lancashire, UK
    Posts
    26
    Quote Originally Posted by healdem
    Hi Darren
    you can achieve this either using queries or VBA or a mix of both.

    Lets assuem you are going don the route of queries
    I'd suggets you break down yourproblem into manageable
    steps

    1) create a query which identifies your 80% spend threshold, based on the business logic you are trying to represent. depends on you data but probably includes a sum, avg or other form of SQL aggregate function as well as a group by

    2) create another query which the compares that threshold value against individual customers (the first query could be a subselect in this query), or just dragged in as a value from that query.

    HTH
    Yeah, itís the second bit Iím struggling with, how do I total each suppliers spend until it reaches the threshold value (80% of total spend)?

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    So how have you calulcated your threshold value?

    Effectively its the same query as the first one, except you are comparing against the threshold.

    if your first query calucaltes a single value then you could incorporate the value of that query in the second one.

    Perhaps if you cna supply more details (examples, or possibly where you have got up to.

    HTH

  5. #5
    Join Date
    Jan 2004
    Location
    Preston, Lancashire, UK
    Posts
    26
    Quote Originally Posted by healdem
    So how have you calulcated your threshold value?

    Effectively its the same query as the first one, except you are comparing against the threshold.

    if your first query calucaltes a single value then you could incorporate the value of that query in the second one.

    Perhaps if you cna supply more details (examples, or possibly where you have got up to.

    HTH
    Hi, is my attachment visible?

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Darren
    place your threshold value in the relevant column of the query

    ie
    % of Cate Spend: Round((([Supplier Spend]/[Category Spend])*100))

    criteria: >=80

  7. #7
    Join Date
    Jan 2004
    Location
    Preston, Lancashire, UK
    Posts
    26
    Quote Originally Posted by healdem
    Darren
    place your threshold value in the relevant column of the query

    ie
    % of Cate Spend: Round((([Supplier Spend]/[Category Spend])*100))

    criteria: >=80
    Yep, thats what i have but i only want to output the suppliers that make up 80% of the category spend

  8. #8
    Join Date
    Jan 2004
    Location
    Preston, Lancashire, UK
    Posts
    26
    All done, thanks for your time / help. I went down the road of creating a new table and running a query on it.
    Cheers

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    darren

    basically you were there, had you added to the criteria row for the relevant column (png attached) it would have worked, without using anouther query or table.
    Incidentally I'd suggest a couple of chanegs to your table design

    1) stick clear of using spaces (or other reserved letters) in column names eg "Supplier Spend" - how you get round the issue is up to you "SupplierSpend" or "Supplier_Spend" are used by practioners (PCent for %). Reason - you are making problems for yourself in how you name caolumns which could cause serious issues with other SQL engines.

    2) consider using a consistent naming convetion to reduce space in column names. eg Sup in place of supplier, Cat in place of Category. Reason some SQL engines have problems with column names that are overly long. It also can become a pian when referrring to column names when designing forms or queries.

    3) consider using a naming convention for your tables, queries, forms and controls etc. Reason it makes it easier to read your code, and makes it self documenting. - this especailly applies to controls in reports
    eg Query2 - becomes qrySupOverTarget
    eg Combo1 - becomes cmbSelCategory

    its usually easier to find the table / query / control you want, and having found the right query it makes you less prone to making mistakes
    Attached Thumbnails Attached Thumbnails darrentaylorquery.gif  

  10. #10
    Join Date
    Jan 2004
    Location
    Preston, Lancashire, UK
    Posts
    26
    Cheers and thanks for the design tips

Posting Permissions

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