Group records and counting

[Graham]

Access 2002
It is such a long time since I had to create reports and despite searching
this Group and a lot of trial and error I just have not got a start.

My requirement is to generate a report with one line for each ProgramName
and to display the ProgramName, Count of the number of times the Program has
been run (within a date window) and Count of the total number of enrolments
for the ProgramName. I have already setup a front end form to provide the
date selection and then open the report.

Database structue (abbreviated) is
tProgram
ProgramID
ProgNameID
StartDate
EndDate

tProgName
ProgNameID
ProgramName

tEnrolments
EnrolmentID
ProgramID

 

[Marshall Barton]

Access 2002
It is such a long time since I had to create reports and despite searching
this Group and a lot of trial and error I just have not got a start.

My requirement is to generate a report with one line for each ProgramName
and to display the ProgramName, Count of the number of times the Program has
been run (within a date window) and Count of the total number of enrolments
for the ProgramName. I have already setup a front end form to provide the
date selection and then open the report.

Database structue (abbreviated) is
tProgram
ProgramID
ProgNameID
StartDate
EndDate

tProgName
ProgNameID
ProgramName

tEnrolments
EnrolmentID
ProgramID

Always start a report design by fiuring out the data that
will be needed. Then create a query to to retrieve that
data. After that is working, the report should be
relatively easy.

I think this may get the data you want:

SELECT ProgramName,
Count(P.ProgramID) As CountOfProgram,
Count(EnrolmentID) As CountOfEnrollments
FROM (tProgName As N
LEFT JOIN tProgram As P
ON N.ProgNameID = P.ProgNameID)
LEFT JOIN tEnrolments As E
ON P.ProgramID = E.ProgramID

 

[Graham]

Thanks Marshall

I pasted the code into a new query and running it resulted in the error
You tried to execute a query that does not include the specified expression
'ProgramName' as part of an aggregate function.

Unfortunately, my SQL is extremely basic and I am at a loss as to how to
debug.

 

[Marshall Barton]

I pasted the code into a new query and running it resulted in the error
You tried to execute a query that does not include the specified expression
'ProgramName' as part of an aggregate function.

Unfortunately, my SQL is extremely basic and I am at a loss as to how to
debug.

Sorry, I forgot the last line. Add this to the end of the
query:
. . .
GROUP BY ProgramName

 

[Graham]

Hi Marshall

That got rid of the error - interesting technique creating an "alias" for
table name.

The results included a record for ProgramID's for which there were not any
enrolments - but a change of the join type to INNER fixed that.

However, the result is not yet quite what I am looking for - if there are 2
enrolments for the same ProgramID the results are
CountOfProgram = 2 CountOfEnrolment = 2

Whereas, what I am looking for is
CountOfProgram = 1 CountOfEnrolment = 2

 

[Marshall Barton]

That got rid of the error - interesting technique creating an "alias" for
table name.

The results included a record for ProgramID's for which there were not any
enrolments - but a change of the join type to INNER fixed that.

However, the result is not yet quite what I am looking for - if there are 2
enrolments for the same ProgramID the results are
CountOfProgram = 2 CountOfEnrolment = 2

Whereas, what I am looking for is
CountOfProgram = 1 CountOfEnrolment = 2

I should have figured that out. I think I was trying to
avoid a subquery so the date criteria would be easy, but
what good is that if it doesn't do the job.

SELECT ProgramName,
(SELECT Count(*) FROM tProgram As X
WHERE X.ProgNameID = P.ProgNameID
And X.StartDate < ???
And X.EndDate < ???
) As CountOfProgram,
Count(EnrolmentID) As CountOfEnrollments
FROM (tProgName As N
INNER JOIN tProgram As P
ON N.ProgNameID = P.ProgNameID)
INNER JOIN tEnrolments As E
ON P.ProgramID = E.ProgramID
WHERE P.StartDate < ???
And P.EndDate < ???
GROUP BY ProgramName,
(SELECT Count(*) FROM tProgram As X
WHERE X.ProgNameID = P.ProgNameID
And X.StartDate < ???
And X.EndDate < ???)

I still didn't set up all the tables, etc. to test this. I
hope you don't have to split the subquery off to a separate
query to handle the grouping and the criteria.

 

[Graham]

Hi Marshall

Now getting error
Syntax error in query expression '(SELECT Count(*) FROM tProgram As X
WHERE X.ProgNameID = P.ProgNameID
And X.StartDate >= #1,1,2007#
And X.EndDate <= #4,30.2007)'

I have studied and the untrained eye cannot see the problem - I feel so
helpless (and useless too).

 

[John Spencer]

Try using slashes or dashes as the delimiter for the dates. But not a
mix of periods and commas. Also your posted SQL did not have a closing
# after the second date.

'(SELECT Count(*) FROM tProgram As X
WHERE X.ProgNameID = P.ProgNameID
And X.StartDate >= #1/1/2007#
And X.EndDate <= #4/30/2007#)'



'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[Graham]

Hi John

The missing # was my typing - it was there in the SQL

The change of delimiter did not help.

Thanks

 

[Marshall Barton]

The missing # was my typing - it was there in the SQL

The change of delimiter did not help.

Please do not ask us to debug your typing. Us Copy/Paste to
post troublesome expressions, code or SQL statements.

 

[Graham]

Hi Marshall

Absolutely fair comment and I apologise for wasting time that is given so
graciously but, I could not copy from the dialog.

The problem still exists and I am at a loss to understand why.

 

[Marshall Barton]

Hi Marshall

Absolutely fair comment and I apologise for wasting time that is given so
graciously but, I could not copy from the dialog.

The problem still exists and I am at a loss to understand why.

Yes, but we are trying to debug a query that we can not see.
Please just open the query in design view, change to SQL
view and Copy/Paste it into a reply post.