Complicated Queries

[Murray]

G'day all
I have been asked to set up the following from our Database and am not sure
how to proceed.
We have a database with the fields , TenderID, Employee, Winlose,
reveivedDate, category. The Tender ID is just a number, employee a name,
winlose is weather the tender was won, lost or still alive, received date is
the date the tender was received and the category is who did the tender
(workshop, engineering etc)
I have been asked to provide a graph showing the monthly strike rate for
each category or employee. Strike rate = wins / total number of tenders).
Whislt I can get the strike rate for an individual or category using Dcount I
don't know how to get a monthly running total. Can anyone give me a good
example of explain how I should progress
Thanks

 

[KARL DEWEY]

Try this --
SELECT Murray.Employee AS Submitter_Dept, Format([reveivedDate],"yyyy mm")
AS Year_mon, Sum(IIf([Winlose]="won",1,0))/Sum([TenderID]) AS [Strike Rate]
FROM Murray
GROUP BY Murray.Employee, Format([reveivedDate],"yyyy mm")
UNION ALL SELECT Murray.category AS Submitter_Dept,
Format([reveivedDate],"yyyy mm") AS Year_mon,
Sum(IIf([Winlose]="won",1,0))/Sum([TenderID]) AS [Strike Rate]
FROM Murray
GROUP BY Murray.category, Format([reveivedDate],"yyyy mm");

 

[Murray]

karl
Thanks for the information but I think it is a bit too complex for me. I
need a monthly running total and then I need to find out how to show it as a
graph.

Try this --
SELECT Murray.Employee AS Submitter_Dept, Format([reveivedDate],"yyyy mm")
AS Year_mon, Sum(IIf([Winlose]="won",1,0))/Sum([TenderID]) AS [Strike Rate]
FROM Murray
GROUP BY Murray.Employee, Format([reveivedDate],"yyyy mm")
UNION ALL SELECT Murray.category AS Submitter_Dept,
Format([reveivedDate],"yyyy mm") AS Year_mon,
Sum(IIf([Winlose]="won",1,0))/Sum([TenderID]) AS [Strike Rate]
FROM Murray
GROUP BY Murray.category, Format([reveivedDate],"yyyy mm");

--
KARL DEWEY
Build a little - Test a little

G'day all
I have been asked to set up the following from our Database and am not sure
how to proceed.
We have a database with the fields , TenderID, Employee, Winlose,
reveivedDate, category. The Tender ID is just a number, employee a name,
winlose is weather the tender was won, lost or still alive, received date is
the date the tender was received and the category is who did the tender
(workshop, engineering etc)
I have been asked to provide a graph showing the monthly strike rate for
each category or employee. Strike rate = wins / total number of tenders).
Whislt I can get the strike rate for an individual or category using Dcount I
don't know how to get a monthly running total. Can anyone give me a good
example of explain how I should progress
Thanks