Counting # of jobs per month 1

[Brlee1crv]

Hi,
How can I create a report which will show me a graph displaying how many jobs were due each month? For example the x axis has the months while the y axis contains the job count.

I have a table which contains a due date(DueDate). I'd like the report to count the number of jobs for any given year. Thanks.

 

[dhookom]

I would first create a totals query that totals by month and counts jobs. You can filter it however you want but its sort order should be how you would want your data to appear in the graph.

Then, create a new report using the chart wizard.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[Brlee1crv]

Sorry I'm new to Access. I know how to create basic queries but how can I create one that totals jobs broken down by month?

Thanks for the reply.

 

[dhookom]

Check Help on totals queries. To get you started, create a new query and select your table. Add the proper fields to the query grid and then select View|Totals. You can change the date expression to:
Mth:Month([YourDateField])

Set the JobNumber totals to Count.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[Brlee1crv]

OK cool. The query works fine but I'd like to narrow the query down a bit further. I have dates going back to 1999 so how can I have a prompt appear so I can choose a date range?

 

[dhookom]

Don't use parameter prompts. I recommend references to controls on forms. For instance have a form "frmRptSlct" and controls "txtStart" and "txtEnd". Then make sure the form is open and set the criteria in the query to
Between Forms!frmRptSlct!txtStart And Forms!frmRptSlct!txtEnd

Then set the Query|Parameters to:
Forms!frmRptSlct!txtStart DateTime
Forms!frmRptSlct!txtEnd DateTime

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[cjsorel]

Duane,
I have been trying to do a reference to controls on another form and when I try like you mention I get this error.
Invalid bracketing of name '[Forms!Chart_OverTime!txtStartDate]'.

I have tried putting in the brakets but no help. Here is the SQL statement:
PARAMETERS [[Forms]!Chart_OverTime![txtStartDate]] DateTime;
SELECT qryrptOver_time.ContractTypeDesc, Count(qryrptOver_time.ExpiredDate) AS ExpiredDate, Count(qryrptOver_time.TerminatedDate) AS TerminatedDate, Count(qryrptOver_time.CanceledDate) AS CanceledDate, Count(qryrptOver_time.ExecutedDate) AS ExecutedDate
FROM qryrptOver_time
GROUP BY qryrptOver_time.ContractTypeDesc
HAVING (((Count(qryrptOver_time.ExpiredDate)) Between [Forms]![Chart_OverTime]![txtStartDate] And [Forms]![Chart_OverTime]![txtEndDate]) AND ((Count(qryrptOver_time.TerminatedDate)) Between [Forms]![Chart_OverTime]![txtStartDate] And [Forms]![Chart_OverTime]![txtEndDate]) AND ((Count(qryrptOver_time.CanceledDate)) Between [Forms]![Chart_OverTime]![txtStartDate] And [Forms]![Chart_OverTime]![txtEndDate]) AND ((Count(qryrptOver_time.ExecutedDate)) Between [Forms]![Chart_OverTime]![txtStartDate] And [Forms]![Chart_OverTime]![txtEndDate]));

Chris

 

[dhookom]

Access queries sometimes mess up the []s in the parameters. I think the following query sql is closer but I don't quite think it will provide the results you want even though I don't know what results you expect...

PARAMETERS [Forms]!Chart_OverTime![txtStartDate] DateTime;
SELECT qryrptOver_time.ContractTypeDesc,
Count(qryrptOver_time.ExpiredDate) AS ExpiredDate,
Count(qryrptOver_time.TerminatedDate) AS TerminatedDate,
Count(qryrptOver_time.CanceledDate) AS CanceledDate,
Count(qryrptOver_time.ExecutedDate) AS ExecutedDate
FROM qryrptOver_time
GROUP BY qryrptOver_time.ContractTypeDesc
WHERE ExpiredDate Between [Forms]![Chart_OverTime]![txtStartDate] And [Forms]![Chart_OverTime]![txtEndDate]
AND TerminatedDate Between [Forms]![Chart_OverTime]![txtStartDate] And [Forms]![Chart_OverTime]![txtEndDate]
AND CanceledDate Between [Forms]![Chart_OverTime]![txtStartDate] And [Forms]![Chart_OverTime]![txtEndDate]
AND ExecutedDate Between [Forms]![Chart_OverTime]![txtStartDate] And [Forms]![Chart_OverTime]![txtEndDate];


Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[cjsorel]

I had to move the group by to the end for it to work but still not giving me any results. This is the sql that I started with
SELECT qryrptOver_time.ContractTypeDesc, Count(qryrptOver_time.ExpiredDate) AS ExpiredDate, Count(qryrptOver_time.TerminatedDate) AS TerminatedDate, Count(qryrptOver_time.CanceledDate) AS CanceledDate, Count(qryrptOver_time.ExecutedDate) AS ExecutedDate
FROM qryrptOver_time
GROUP BY qryrptOver_time.ContractTypeDesc;

What I want to do is filter the results based on a start and end date that is entered into a form. To limit a bar chart report. I tried to use the link child fields in the chart but I get a Can't build a link between unbound forms error.

 

[dhookom]

It is difficult to determine what you want. To count the number of ExpiredDate values between the start and end dates by ContractTypeDesc, you could use:

PARAMETERS [Forms]!Chart_OverTime![txtStartDate] DateTime, [Forms]![Chart_OverTime]![txtEndDate] DateTime;
SELECT ContractTypeDesc,
Sum(Abs(ExpiredDate>=Forms]![Chart_OverTime]![txtStartDate] and ExpiredDate<=[Forms]![Chart_OverTime]![txtEndDate])) AS Expired
FROM qryrptOver_time
GROUP BY qryrptOver_time.ContractTypeDesc;

Duplicate the same syntax for your other dates.


Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[cjsorel]

Duane,
Works great after placing in a [ your missed on the sum line Sum(Abs(ExpiredDate>=[For...

Thank you very much and a star for this help. No I can stop pulling out my hair
Chris