Current Month and YTD dollars

[hende10]

I'm hoping someone can point me in the right direction on this, I have browsed through a lot of posts, and still can't figure out a solution.

I have a table with the following:
TicketNo
Date
Super
WorkCent
Emp
MyMonth (pulls the month from 'Date')

And I have this subform table (each ticketno can have multiple lines):
Qty
Part
ScrapDollars

I need a report that will show me the total scrap dollars for each employee, work center, and supervisor, for both the current month and ytd. The current report I have works perfectly for total dollars for all ticketno's, but I can't figure out how to break the dollars out by what I need. Will this require a SQL query, or can it be done simplier than that? The general grouping of the report is:

Supervisor
WorkCenter
Employee
TicketNo Detail
Subtotal $s for Employee
Subtotal $s for WorkCenter
Subtotal $s for Supervisor

I need to get all of the subtotal dollars shown for both the current month, and the year to date.

Thanks!

 

[hende10]

I really over-worded the first post I had, so here's the quick and dirty version.

I can run a report that will either subtotal dollars by month, or by year to date, depending on what the base query selects. However, I need to show both on one report, and I can't get past the two being mutually exclusive. Can I have the query select and subtotal the dollars based on the current month, and have it do the subtotals for year to date? I only need the dollars for year to date, not any of the detail parts.

I hope this makes more sense, and someone can point me in the right direction.

Thanks.

 

[dhookom]

Total dollars year to date for any of the groups should be:
=Sum(Abs(Year([date])=Year(Date())) * [ScrapDollars])

There is no need to store the month if you are storing the date. Also, date is a poor name for a field since it is also the name of a function.

Duane
MS Access MVP
[green]Ask a great question, get a great answer.[/green]
[red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
[blue]Ask me about my grandson, get a grand answer.[/blue]

 

[hende10]

Duane,

Thanks for the reply, the formula works well, but I've ran into another problem.

The Detail portion of the report needs to only list the individual scrap tickets for the current month, not for the year. However, when the base query is set to only select records for the current month, the formula you gave me just totals on those records selected, not all of them for a full yearly total.

Is there a way to run this formula directly on the table, bypassing the query?

Thanks,

Mike

 

[dhookom]

I would then create a totals query that groups by your "report group" fields and sums the ScrapDollars based on only the current year. Then add this query to your report's record source and join the group fields. This should make the SumOfScrapDollars for the year available in the report.

Duane
MS Access MVP
[green]Ask a great question, get a great answer.[/green]
[red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
[blue]Ask me about my grandson, get a grand answer.[/blue]