FN-FORUM: SQL help

date posted 11th January 2007 17:13

Hi

I've got three tables as follows.

tblScores that stores the results of a questionnaire. The relevant
fields it contains to my query are:
ScoreID
GroupID
GeneratedDate

tblGroups that stores information about a group (defining what
questionnaire to use and who set up the Group). The relevant fields
it contains to my query are:
GroupID
LicenseeID

tblLicensees that stores information about users that set up the
group. The relevant fields to my query are:
LicenseeID
Company

So the GroupID field in tblScores is the foreign key to the GroupID in
tblGroups. So I know what Group a record in tblScores belongs to.
The LicenseeID field in tblGroups is the foreign key to the LicenseeID
in tblLicensees so I know who the Licensee is.

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.

TIA
Paul