S
spiderq
I have a database which helps me calculate total monthly scores and
produce reports with running sums for an archery league with 10
different teams,over 5 months and team members who change each month
(depending on their scores). Each team is in a different league with a
different threshold score- which cannot be exceeded.
I realise that I would not ideally want to store a value I have
calculated from a query but it would make life easier. I have a query
which finds the monthly total score for each team using the parameters
"HomeTeamID" and "CompMonth" - this works fine but to produce the final
monthly printed report I copy the result to another table, then
manually work out if the score is within the threshold.
This is the SQL of this query:
SELECT tblScores.HomeTeamID, tblCompDates.CompMonth,
Sum(tblScores.Score) AS Result
FROM tblCompDates INNER JOIN tblScores ON tblCompDates.CompDatesID =
tblScores.Month
GROUP BY tblScores.HomeTeamID, tblCompDates.CompMonth
HAVING (((tblScores.HomeTeamID)=[Enter Team Code]) AND
((tblCompDates.CompMonth)=[Enter Date]));
I have tried to use an Append query but I am adding the "Result" to
whatever CompMonth(i.e. Nov, Dec etc) and HomeTeamID I have used as
parameters in another table. Is there a way in VB to do this?
produce reports with running sums for an archery league with 10
different teams,over 5 months and team members who change each month
(depending on their scores). Each team is in a different league with a
different threshold score- which cannot be exceeded.
I realise that I would not ideally want to store a value I have
calculated from a query but it would make life easier. I have a query
which finds the monthly total score for each team using the parameters
"HomeTeamID" and "CompMonth" - this works fine but to produce the final
monthly printed report I copy the result to another table, then
manually work out if the score is within the threshold.
This is the SQL of this query:
SELECT tblScores.HomeTeamID, tblCompDates.CompMonth,
Sum(tblScores.Score) AS Result
FROM tblCompDates INNER JOIN tblScores ON tblCompDates.CompDatesID =
tblScores.Month
GROUP BY tblScores.HomeTeamID, tblCompDates.CompMonth
HAVING (((tblScores.HomeTeamID)=[Enter Team Code]) AND
((tblCompDates.CompMonth)=[Enter Date]));
I have tried to use an Append query but I am adding the "Result" to
whatever CompMonth(i.e. Nov, Dec etc) and HomeTeamID I have used as
parameters in another table. Is there a way in VB to do this?