Compare records in a query then write equation??

[Samer]

Hi all, i have a problem and i need help, the case is as follows: the
default rule is that i have 4 fields, (Date, Team, Score). each team
is allowed to have one score per day but sometimes it can have 2
scores per day, when this is the case i wanna multiply each score by a
certain number and have one score instead of 2 scores (similar to
average but not average). So, i need to have a condition which
compares records, and if this is the case, formulates this equation
and gives me one score instead of 2 scores. Please help

 

[John Spencer]

SELECT Team, [Date],
Sum(Score)/Count(Score) * IIF(Count(Score) =1,1,SomeFactor) as CalcScore
FROM SomeTable
GROUP BY Team, [Date]

Interesting fact is that you only listed 3 of your 4 fields.

IF you don't know how to do this in the SQL window, then post back and I
will try to describe how to build the query in the query view.


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Samer]

SELECT Team, [Date],
Sum(Score)/Count(Score) * IIF(Count(Score) =1,1,SomeFactor) as CalcScore
FROM SomeTable
GROUP BY Team, [Date]

Interesting fact is that you only listed 3 of your 4 fields.

IF you don't know how to do this in the SQL window, then post back and I
will try to describe how to build the query in the query view.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.

Hi all, i have a problem and i need help, the case is as follows: the
default rule is that i have 4 fields, (Date, Team, Score). each team
is allowed to have one score per day but sometimes it can have 2
scores per day, when this is the case i wanna multiply each score by a
certain number and have one score instead of 2 scores (similar to
average but not average). So, i need to have a condition which
compares records, and if this is the case, formulates this equation
and gives me one score instead of 2 scores. Please help- Hide quoted text -

- Show quoted text -

Sorry for this mistake, the 4 fields are (Date,Team,Score,Volume),
basically if a team have more than one score per day i want to
multiply each score by its corresponding volume, then divide all by
the total volume of this team. This is specifically my problem, i hope
it is becoming clear now, thanks for the assistance. i am aware with
sql and i can write sql code, so go ahead in sql. it is urgent, so
please help

 

[John Spencer]

If I have understood you correctly, then perhaps the following will work for
you.

SELECT Team, [Date],
IIF(Count(Score) = 1, Sum(Score), Sum(Score * Volume)/Sum(Volume)) as
CalcScore
FROM SomeTable
GROUP BY Team, [Date]

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

SELECT Team, [Date],
Sum(Score)/Count(Score) * IIF(Count(Score) =1,1,SomeFactor) as CalcScore
FROM SomeTable
GROUP BY Team, [Date]

Interesting fact is that you only listed 3 of your 4 fields.

IF you don't know how to do this in the SQL window, then post back and I
will try to describe how to build the query in the query view.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.

Hi all, i have a problem and i need help, the case is as follows: the
default rule is that i have 4 fields, (Date, Team, Score). each team
is allowed to have one score per day but sometimes it can have 2
scores per day, when this is the case i wanna multiply each score by a
certain number and have one score instead of 2 scores (similar to
average but not average). So, i need to have a condition which
compares records, and if this is the case, formulates this equation
and gives me one score instead of 2 scores. Please help- Hide quoted
text -

- Show quoted text -

Sorry for this mistake, the 4 fields are (Date,Team,Score,Volume),
basically if a team have more than one score per day i want to
multiply each score by its corresponding volume, then divide all by
the total volume of this team. This is specifically my problem, i hope
it is becoming clear now, thanks for the assistance. i am aware with
sql and i can write sql code, so go ahead in sql. it is urgent, so
please help