Access / sql question

Discussion in 'OT Technology' started by 2000GT, Dec 15, 2003.

  1. 2000GT

    2000GT Active Member

    Joined:
    Jan 21, 2001
    Messages:
    5,773
    Likes Received:
    8
    Location:
    Vancouver
    I am trying to create an html report (ASP) via SQL connecting to an Access DB.

    I have a table with names of people and the sales that they have put through into the table - eg.

    Name | Sale DATE | TYPE OF SALE
    ----------------------------------------------
    John Doe | 12/08/2003 | TYPE A
    John Doe | 12/08/2003 | TYPE C
    John Doe | 12/08/2003 | TYPE C
    John Doe | 12/08/2003 | TYPE B
    John Doe | 12/08/2003 | TYPE A
    John Doe | 12/08/2003 | TYPE A
    John Doe | 12/08/2003 | TYPE C
    Jane Doe | 12/08/2003 | TYPE C
    Jane Doe | 12/08/2003 | TYPE B
    Jane Doe | 12/08/2003 | TYPE A


    Anyway, I am trying to create a report that will show soething like this:

    NAME | TOTAL | PERCENTAGE
    ---------------------------------------
    John Doe | 7 | 70%
    Jane Doe | 3 | 30%

    its a basic example of a complex problem for me... if I run a select statement to get a distinct recordset of names I can't run a sum or count?

    What would you suggest?
     
  2. SLED

    SLED build an idiot proof device and someone else will

    Joined:
    Sep 20, 2001
    Messages:
    28,118
    Likes Received:
    0
    Location:
    AZ, like a bauce!
    what's the percentage coming from?

    i think you may be looking for a GROUP BY query. Something like this maybe:
    Code:
    select name, count(*)
    from yourtable
    group by name
    
    i'm not sure what the percent is all about, but this will get you counts.
     
  3. 2000GT

    2000GT Active Member

    Joined:
    Jan 21, 2001
    Messages:
    5,773
    Likes Received:
    8
    Location:
    Vancouver
    The percentage is just the total amount of a persons sales divided by everyones sales....

    Thanks for your input!
     
  4. SLED

    SLED build an idiot proof device and someone else will

    Joined:
    Sep 20, 2001
    Messages:
    28,118
    Likes Received:
    0
    Location:
    AZ, like a bauce!
    that should be pretty trivial to figure out as well. Let me know if you need help with it
     
  5. 2000GT

    2000GT Active Member

    Joined:
    Jan 21, 2001
    Messages:
    5,773
    Likes Received:
    8
    Location:
    Vancouver
    Thanks Sled!!

    Last question for ya... can I do multiple group by's?

    For example, to sort out the of a specific type of sale percentage, I need to take a sale type divided by total sales by the salesperson. In which case I need to do another group by with differant criteria...

    example 1:

    SELECT agent, count(") AS SALES FROM table WHERE sale_date = date GROUP BY agent

    example 2:

    SELECT agent, count(") AS SALES FROM table WHERE sale_type = type_A and sale_date = date GROUP BY agent

    so now I can take count(example2) / count(example1) * 100 = percentage of type_A sales

    I need to have those totals togethor? Not sure if I make any sense... what are your thoughts?
     
  6. you can do multiple group bys, but it becomes very convoluted very quickly ... I'm trying to figure out your last question ... ??? you want to divide the count of all records in example 2 by the count of all records in example 1? count's not a record-by-record function ...
     
  7. 2000GT

    2000GT Active Member

    Joined:
    Jan 21, 2001
    Messages:
    5,773
    Likes Received:
    8
    Location:
    Vancouver

    Hard to explain, let me paint a bigger picture:

    Need a dynamic report as follows:

    Sales Report:
    Name | Date | Sale Type | Sale Percentage | Total Sales
    ------------------------------------------------
    John Doe 12/08/2003 | Type_A | 25% | 4


    Access table:
    Name | Date | Sale Type
    -------------------------------
    John Doe 12/08/2003 | Type_A
    John Doe 12/08/2003 | Type_B
    John Doe 12/08/2003 | Type_B
    John Doe 12/08/2003 | Type_B


    So Sale Percentage is total sales for that person (4) divided by the type of sale (Type_A)... 1 of the 4 sales he has is type_A hence the 25%... Make more sense?
     
  8. 5Gen_Prelude

    5Gen_Prelude There might not be an "I" in the word "Team", but

    Joined:
    Mar 14, 2000
    Messages:
    14,519
    Likes Received:
    1
    Location:
    Vancouver, BC, CANADA
    Personally I would make one query that counted sales by person and date. Then make your second one sort by person, date and type. Then add the other query in linking the table and query by sales name and date:

    Code:
    Sales 
    -----            SalesQuery
    SalesID          ---------- 
    Name   --------> Name
    Date   --------> Date
    Type   --------> CountOfSalesID
    
    Then Group by Name, Date, Type, Count(SalesID)/CountofSalesID*100, CountOfSalesID

    The trick is to split your problem into two distinct queries, one that groups by name and date, and another that groups by name, date and type. The latter one uses info from the former to complete
     
  9. 2000GT

    2000GT Active Member

    Joined:
    Jan 21, 2001
    Messages:
    5,773
    Likes Received:
    8
    Location:
    Vancouver

    That all makes sense, but how do I combine the 2 queries so that when the user clicks the page and chooses a date, both queries run? Would you do a nested select statement or a join, this is sort of where I am stumped?
     
  10. 2000GT

    2000GT Active Member

    Joined:
    Jan 21, 2001
    Messages:
    5,773
    Likes Received:
    8
    Location:
    Vancouver
    It doesn't look like what I am trying to do is possible.... Can anyone confirm that? I have googled all day yesterday and today trying numerous things, but I seem to get stuck at the same point everytime - can't count specific criteria when I have grouped on other criteria....?!!? HELP?
     
  11. picabu

    picabu I like pie

    Joined:
    Oct 4, 2001
    Messages:
    916
    Likes Received:
    0
    Location:
    Houston
    Since you're doing a script... Can't you first do a select to determine the total number of records. Assign that value to a variable. Then do your grouped select. When outputting your data, the percentage would be (groupcount/totalvar)*100. :dunno:
     
  12. 5Gen_Prelude

    5Gen_Prelude There might not be an "I" in the word "Team", but

    Joined:
    Mar 14, 2000
    Messages:
    14,519
    Likes Received:
    1
    Location:
    Vancouver, BC, CANADA
    A query can be based on table or query data. So one query has it built in:


    Code:
    Table1:
    SalesID Autunumber
    Name Text
    Date Date/Time
    Type Text
    
    Query1:
    
    SELECT Table1.Name, Table1.Date, Count(Table1.SalesID) AS CountOfSalesID
    FROM Table1
    GROUP BY Table1.Name, Table1.Date;
    
    Query2:
    
    SELECT Table1.Name, Table1.Date, Table1.Type, Count([SalesID])/[countofsalesid] AS PercentOfSales, Query1.CountOfSalesID AS TotalSales
    FROM Table1 INNER JOIN Query1 ON (Table1.Name = Query1.Name) AND (Table1.Date = Query1.Date)
    GROUP BY Table1.Name, Table1.Date, Table1.Type, Query1.CountOfSalesID;
    
    Simply run Query2. It will first have to run query1, but it knows that. I just threw some test data in there and it works fine. Trust me, I use this app EVERY day, I know that it works.

    Table data:

    Code:
    
    SalesID	Name	Date	Type
    1	test	12/18/2003	a
    2	test	12/18/2003	a
    3	test	12/18/2003	b
    4	test	12/18/2003	b
    5	test	12/18/2003	c
    6	test	12/19/2003	a
    7	test	12/19/2003	a
    8	test	12/19/2003	b
    9	test2	12/18/2003	a
    10	test2	12/18/2003	a
    11	test2	12/18/2003	b
    
    Output of Query2:


    Code:
    Name	Date	Type	PercentOfSales	TotalSales
    test	12/18/2003	a	40.00%	5
    test	12/18/2003	b	40.00%	5
    test	12/18/2003	c	20.00%	5
    test	12/19/2003	a	66.67%	3
    test	12/19/2003	b	33.33%	3
    test2	12/18/2003	a	66.67%	3
    test2	12/18/2003	b	33.33%	3
    
     
    Last edited: Dec 19, 2003

Share This Page