Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2004
    Posts
    31

    Unanswered: Query help needed...

    Hi all,

    I have the following table setup and am trying to figure out how to get certain info from it...all data is entered into table via a form..[DATE] is from date=() function and [OVERALL] is a blank field that I was trying to pull from the form but couldn't (average of Q1 through Q6 calculated in the form)

    [NAME] [DATE] [Q1] [Q2] [Q3] [Q4] [Q5] [Q6] [OVERALL]

    these are the types of reports that I would like to generate from this table but I am not sure on the SQL portion of things:

    report that pulls any values below a 3 in [Q1]-[Q6]

    SELECT [NAME], [DATE], [Q1], [Q2], [Q3], [Q4], [Q5], [Q6], [OVERALL]
    FROM [TABLE1]
    WHERE ([Q1]<3 OR [Q2]<3 OR [Q3]<3 OR [Q4]<3 OR [Q5]<3 OR [Q6]<3)

    report that gives max and min [OVERALL] for all [NAME]

    SELECT [NAME], [DATE], [Q1], [Q2], [Q3], [Q4], [Q5], [Q6], [OVERALL]
    FROM [TABLE1]
    SET [OVERALL] =(([Q1]+[Q2]+[Q3]+[Q4]+[Q5]+[Q6])/6)
    WHERE [NAME]="AL"; MAX [OVERALL] AND MIN [OVERALL]
    WHERE [NAME]="SUE"; MAX [OVERALL] AND MIN [OVERALL]

  2. #2
    Join Date
    Jan 2004
    Posts
    31
    I must have hit enter instead of shift, here is the other report that I would like to generate from this same table:

    report that gives the average [OVERALL] for all entries

    SELECT [NAME], [DATE], [Q1], [Q2], [Q3], [Q4], [Q5], [Q6], [OVERALL]
    FROM [TABLE1]
    SET [OVERALL] =(([Q1]+[Q2]+[Q3]+[Q4]+[Q5]+[Q6])/6)
    CREATE [OVERALLTOT]
    SET [OVERALLTOT] = SUM [OVERALL]
    CREATE [OVERALLCT]
    SET [OVERALLCT] = COUNT [OVERALL]
    CREATE [ OVERALLAVG]
    SET [OVERALLAVG] = ([OVERALLTOT]/[OVERALLCT])
    LAST [OVERALLAVG]

    only need the most recent overallavg

    I hope that someone can make sense out of what I am trying to accomplish...this is my first attempt at setting up a DB and I am having problems pulling the data that I want.

    thanks for any help that you may be able to give me,
    Frank

  3. #3
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    Given the table

    name | Q1 | Q2 | overall

    update table
    set overall = ((Q1+Q2)/3)

    SQL> select name, count(overall) as count, sum(overall) as sum, avg(overall) as avg1,
    2 sum(overall)/count(overall) as avg2
    3 from a
    4 group by name;

    NAME COUNT SUM AVG1 AVG2
    ---------- ---------- ---------- ---------- ----------
    A 2 9 4.5 4.5
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  4. #4
    Join Date
    Jan 2004
    Posts
    31
    r123456,

    so i should update the table by computing the overall field in a query?

    how do i get the info to save back into the table?

  5. #5
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    [OVERALL] is a blank field that I was trying to pull from the form but couldn't (average of Q1 through Q6 calculated in the form)
    If [overall] is a field of the table then the value for overall would be calculated by the inserting application. Alternatively a trigger can be used to set this value if supported.

    If however, [overall] is a dynamically calculated value that is specific to the application and independent of the database then,

    select q1, q2, ((q1+q2)/2) from table;

    Further, you could create a view to dynamically calculate the expressions,

    create view v
    as select a, b, ((a+b)/2) as Overall
    from table;
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  6. #6
    Join Date
    Jan 2004
    Posts
    31
    I still can't get the value to go into the column [Overall]...I've tried a query and tried using a macro(Access) to no avail..I realize that this is probably very easily accomplished but I just am not getting it..this is what I tried in a query but am getting nowhere with it:

    SELECT [Call log].[Analyst Name], [Call log].Date, [Call log].Q1, [Call log].Q2, [Call log].Q3, [Call log].Q4, [Call log].Q5, [Call log].Q6, [Call log].[Overall Rating]
    FROM [Call log];
    UPDATE TABLE [Call log]
    SET [Overall Rating] = ((Q1+Q2+Q3+Q4+Q5+Q6)/6)

    I am getting prompts about characters after update and set functions..

    thank you for your input.

  7. #7
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    From the error you described it apears that 2 SQL statements are passed as a single argument to a macro action. One solution is to create individual OpenQuery Action's for each SQL statement.
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

Posting Permissions

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