Queries based on counting records

[Guest]

Hello,

I am trying to build a report based on a query. All the information is in
the one table. There are two work programs, and what I need to do is create
fields which count all the jobs done in a financial year under each work
program,

So for example, I want the report to return

1999/2000
Program 1 4 (jobs)
Program 2 3 (jobs)

2000/2001
Program 1 5(jobs)
Program 2 6(jobs)

and so on

My table has the fields with the program (program) and what the job
(upgrade) was and the completion date.
I am trying to write something with the syntax in a query that counts these
fields based upon the program and the date.
Any suggestions? I am doing this as a query, because I believe I need to
create calculated fields in queries?

 

[pietlinden]

Hello,

I am trying to build a report based on a query. All the information is in
the one table. There are two work programs, and what I need to do is create
fields which count all the jobs done in a financial year under each work
program,

So for example, I want the report to return

1999/2000
Program 1 4 (jobs)
Program 2 3 (jobs)

2000/2001
Program 1 5(jobs)
Program 2 6(jobs)

and so on

My table has the fields with the program (program) and what the job
(upgrade) was and the completion date.
I am trying to write something with the syntax in a query that counts these
fields based upon the program and the date.
Any suggestions? I am doing this as a query, because I believe I need to
create calculated fields in queries?

You don't do this with a query. You do it in the report. Just create
a group header on Year, then Program and put counts inside.

 

[John Spencer]

SELECT Year(CompletionDate) as TheYear
, ProgramType
, Count(Job) as NumberOfJobs
FROM YOURTable
GROUP BY
Year(CompletionDate)
, ProgramType

If you need to you can adjust CompletionDate to match a financial year by
using the date add function. For instance if the financial year runs from
July 1 to June 30

Year(DateAdd("m",6, CompletionDate)) would return 2007 for dates from July
1, 2006 to June 30, 2007

You can do the above query in the query grid.
-- Select the fields you want
-- Select View: Totals from the menu
-- Change Group By to Count under the Job field.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Guest]

Thanks, I have got the query to work, apart from the DateAddFunction.
I am putting the syntax
Year(DateAdd("m",6,"tblJobDescriptor.datActualCompletionDate"))
in the criteria within the query. Am I putting it in the right place?

 

[John W. Vinson]

I am putting the syntax
Year(DateAdd("m",6,"tblJobDescriptor.datActualCompletionDate"))
in the criteria within the query. Am I putting it in the right place?

Right place but wrong syntax. You're asking it to treat the *literal text
string* tblJobDescriptor.datActualCompletionDate as a date - instead, use
square brackets so that it looks at the field in the table.

Year(DateAdd("m",6,[tblJobDescriptor].[datActualCompletionDate]))

John W. Vinson [MVP]

 

[John W. Vinson]

Am I putting it in the right place?

OOPS... no. My previous message was in error about that!

Put it in a vacant Field cell in the query instead. You're calculating a value
(a fiscal year) to be included in the query as a new field - not constructing
a criterion to filter the records.

John W. Vinson [MVP]

 

[John Spencer]

First, No quotes around the table and field name

Year(DateAdd("m",6,tblJobDescriptor.datActualCompletionDate))

Second
Replace both instances of Year(datActualCompletionDate) with the above.
Your SQL should look something like


SELECT Year(DateAdd("m",6,tblJobDescriptor.datActualCompletionDate)) as
TheYear
, ProgramType
, Count(Job) as NumberOfJobs
FROM tblJobDescriptor
GROUP BY
Year(DateAdd("m",6,tblJobDescriptor.datActualCompletionDate))
, ProgramType


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