YTD and Monthly Summary 1

[jeffshex]

Hello all! I'm looking for any ideas on the following:
I have a query that pulls in Date, Wage, and Staff from a query. There is input parameters, [Starting Date] and [Ending Date], on the Date field.
This report will be pulled usually by the first of the year, ie 1/1/2008 to the end of whatever month 9/30/2008.

The report grouped by staff doing a count on that field and an average for the wage. That gives me the YTD that I need. I'm having trouble conceptualizing a more automated way to get just that last month's numbers. Using the dates I provided above, how could I get the YTD info along with just September's info?
Anyone have any good ideas/examples/etc...?

Thanks!

 

[dhookom]

First of all IMHO parameter prompts are never acceptable faq701-6763.

Can you share the SQL view of your report's record source? Is the query a totals query?

Generally you can get a MTD for the ending date with and expression like:

=Sum(Abs(Format([datefield],"mmyy") =Format(Forms!frmYourFrom!txtEndDate,"mmyy")) * [Field To Sum])

Duane
Hook'D on Access
MS Access MVP

 

[jeffshex]

Here is the SQL view for the report:

SELECT tblPlacementCreditData.Staff, tblPlacementCreditData.DateOfEntry, tblPlacementData.Wage
FROM tblPlacementData INNER JOIN tblPlacementCreditData ON tblPlacementData.PlacementID = tblPlacementCreditData.PlacementID
WHERE (((tblPlacementCreditData.DateOfEntry) Between [Starting Date] And [Ending Date]));

It just brings in all the details. I have the detail section hidden and have the controls in the footer.
I did find a way to do a nasty DCount for the monthly count, but I am having difficulty finding the Average wage for the month. I almost need an Aveage Where or something

 

[dhookom]

If you want to count the number of records for the ending month you can use a text box in a group or report footer with a control source of:

=Sum(Abs(Format([DateOfEntry],"mmyy") = Format([Ending Date],"mmyy")))

Again, I don't care for parameter prompts ;-)

Duane
Hook'D on Access
MS Access MVP

 

[jeffshex]

I plan on converting over those parameter prompts. Right now it's just quicker for me to develop using that. I move that over to a form dialog once i get the kinks worked out.

Any ideas for the Average on the wage for just the last month?

 

[dhookom]

I assume average on the wage would be the sum of all wages divided by the number of records. If so, try:

=Sum(Abs(Format([DateOfEntry],"mmyy") = Format([Ending Date],"mmyy")) * [Wage])/Sum(Abs(Format([DateOfEntry],"mmyy") = Format([Ending Date],"mmyy")))

Duane
Hook'D on Access
MS Access MVP