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]

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

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

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?

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;

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.

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.