Dec 3, 2004
Hey all!
I'm having issues trying to create a query that will take data from two tables and summarize it based on ID, Count(ID), Sum(Score), Sum(Xs). I don't have an issue doing the combining, just the summing and counting (actually, just the counting). I've tried playing directly with the SQL statement, but I keep getting errors.
This is what I have:
tblRoster
ID, Name, Sex, Youth
tblScores
ID, WeekNo, A1, A2, A3, A2X, A3X, B1, B2, B3, B2X, B3X
Sample Data for tblScores would look like this (scores actually are not the same, but you get the idea)
10, 1, 90, 89, 87, 3, 7, 87, 92, 99, 4, 7
10, 2, 90, 89, 87, 3, 7, 87, 92, 99, 4, 7
10, 3, 90, 89, 87, 3, 7, 87, 92, 99, 4, 7
10, 4, 90, 89, 87, 3, 7, 87, 92, 99, 4, 7
23, 1, 90, 89, 87, 3, 7, 87, 92, 99, 4, 7
23, 2, 90, 89, 87, 3, 7, 87, 92, 99, 4, 7
23, 3, 90, 89, 87, 3, 7, 87, 92, 99, 4, 7
23, 4, 90, 89, 87, 3, 7, 87, 92, 99, 4, 7
23, 5, 90, 89, 87, 3, 7, 87, 92, 99, 4, 7
67, 1, 90, 89, 87, 3, 7, 87, 92, 99, 4, 7
67, 2, 90, 89, 87, 3, 7, 87, 92, 99, 4, 7
67, 3, 90, 89, 87, 3, 7, 87, 92, 99, 4, 7
What I would like to do is summarize the info in the following manner:
ID, Name, Sex, Youth, Count(WeekNo), Sum(A1+A2+A3+B1+B2+B3), Sum(A2X+A3X+B2X+B3X) such that I would get the following results:
10, John Smith, M, , 4, 2176, 84
23, Jane Doe, F, , 5, 2720, 105
67, Johnny Jones, M, Y, 3, 1632, 63
I have been able to get the summed part done, but my attempts to do the count have failed. I know that the resulting query will be straight forward, but right now I'm cross-eyed.
Thanks.
-BT.
View 5 Replies
View Related
Mar 26, 2006
Hello,
I am using Access 97 and I am getting very stuck when I am making a group by query.
Basically I have a table which has for engineering jobs (and a few other collums):
[id], [Site Code], [Ref Number], [Job Size], [Date Origionated] and [Date Completed]
I then have made a query (called [MG - CWD SLA]) which works out for all jobs between 2 dates, how long they have been running for and if they have met the Service Level Agreeement:
SELECT Sheet1.[SITE CODE], Sheet1.[Ref Number], Sheet1.[Job Size], Sheet1.[DATE ORIGINATED], Sheet1.[JOB COMPLETE], (CalcWorkdays([DATE ORIGINATED],[JOB COMPLETE])) AS Expr1, IIf(IsNull(Sheet1.[JOB COMPLETE]),"Not finished",IIf([Job Size] Like "M*",IIf(Expr1<=10,"Y","N"),IIf([Job Size] Like "S*",IIf(Expr1<=5,"Y","N"),iif([Ref Number] LIKE "ID*",iif(Expr1 <= 1,"Y","N"),"N/A")))) AS Expr2
FROM Sheet1
WHERE (((Sheet1.[Help Desk Type])="CWD") AND ((Sheet1.[DATE ORIGINATED]) Between [Type the beginning date:] And [Type the ending date:]))
ORDER BY [DATE ORIGINATED];
I then have made a summary query based on the above query which gives me for every site, between 2 dates; the number of jobs who have met the SLA or not as the case may be.
SELECT [MG - CWD SLA].[SITE CODE], [MG - CWD SLA].Expr2, count(*)
FROM [MG - CWD SLA]
GROUP BY [MG - CWD SLA].[SITE CODE], [MG - CWD SLA].Expr2;
What I would like to do is for every like outputted by this query, give the number of jobs for this site between those 2 dates, so then for every line of the query give the percent of jobs which met the sla/not met the sla or not finished.
I have been messing around with distinct and I cannot get this to work! Also been trying to do a join or a subquery, but not sure how to integrate this with working with a query based on a query.
Any help would be great!
Thanks,
Steve
View 2 Replies
View Related
