Re: FN-FORUM: SQL help

date posted 11th January 2007 19:44

Hi Paul,
There are a few ways to do this, but you could try something like this SQL
query:

SELECT L.Company, Count(ScoreID)
FROM tblScores S
JOIN tblGroups G ON G.GroupID = S.GroupID
JOIN tblLicensees L ON L.LicenseeID = G.LicenseeID
WHERE S.GeneratedDate BETWEEN '2007-01-01' AND '2007-01-11'
GROUP BY L.Company

Basically, you'll want to count the number of scores in the tblScores table
for each Company. If you add any other fields to the SELECT statement then
you'll need to add them to the GROUP BY also.

I haven't tested it so it may not be perfect, but I hope it helps.

regards
Dudley

Dudley Trueman
True Creations Website Design & Development
www.truecreations.co.uk

----- Original Message -----
> I want to run a SQL statement so that I can see how many scores have
> been generated between specific dates and by whom. So essentially my
> form will just allow a user to input a start date and end date. The
> query will then display a list of all Licensees that have scores
> generated between those dates (I don't want to display Licensees in
> this list who have not generated scores in the period). Alongside the
> list of each Licensee displayed will be the total number of scores
> they've generated.
>