Reporting the details of one criteria and using sums of another criteria

[mittra]

First, thank you for your help in getting DSUM to work. It was pretty awesome being able to teach Access to do something that was a given in SQL. Now I'm trying to make use of the data.

I used DSUM to identify a number of work items that can be done in a given year with a given amount of funding. This is working perfectly. I now have a query of my original table that shows the work item, the cost, and whether it is "Budgeted" or "Deferred" for the fiscal year.

I need to now generate a report that shows the work items that are budgeted for in the details, but in the footers, I need a summary of the cost of the budgeted items and a summary of the cost of the deferred items (i.e., I don't need to see the details of the deferred items, only how much was deferred).

My report is currently grouped by:
Organization

Site

Building

I know how to generate the report for ONLY the budgeted items and by grouping on the status (budgeted or deferred) I can separate the budgeted and deferred items and show subtotals for each, but I can't figure out how to show details for only budgeted items, but summary totals for each.

[rpeare]

I would suggest that dsums do not work well in queries, they are very memory intensive and you can almost always (at least in my experience) work around them with either sub queries or sub forms/reports.

That being said, however, can you provide a sample of your database, suggestions are really going to range widely because the structure of the report is what is really driving your question.

It might be as simple as making a report based on your dsumm'ed query and summing all the deferred and budgeted items on two different lines. Then putting that subreport in your report footer.

[mittra]

SampleWorkingData.zip

The relevant parts of my database are attached. When you run the report for Constrained Cost or the query that report is based on (qryFinalWorkItemsbyAvailFunding) and it requests a criteria for the Available Funds, use 65000 as it will give a list of budgeted items that satisfies all cases (must do items and projects).

I'm not particularly worried about the DSUM query being a resource hog. This particular project is only a proof of concept and the number of records involved shouldn't be more than 500-1000 and so far it hasn't appeared to slow things down that much. If you can give me another way to tell the query how much money I can spend and have it identify which items can be done and which can't be done based on the priority score, I'd be interested in hearing it after we get the report thing worked out.

So, now that you have a copy of the report to look at, what I need is to show in the details ONLY the items that are "Budgeted" and in the Facility, Complex, and Site totals, I need to see how much the Budgeted Items will cost and how much the "Deferred" items would cost (without showing the individual Work Items).

If I put LIStatus as another Grouping in the report, I can get the report to separate the Budgeted and Deferred Items and show me the totals for each at the Facility Level, but it still shows the Deferred Work Items. I tried putting an IIF statement in the WORK ITEM DESCRIPTION textbox in the detail area that said if the Status was Budgeted to show the Item, but if it was Deferred to put "" and that put a blank for the Work Item Description for the Deferred Items, but gave me "#Error" for the Budgeted Items. Changing the IIF statement to make the textbox visible control True for Budgeted and False for Deferred resulted in "-1" for all the Budgeted Items and "0" for the Deferred Items.

I've included the Unconstrained reports and queries to use as a comparison.

I appreciate anything you can suggest that will push me in the right direction to get this report output correct. If there's anything else I can provide, please let me know.

[rpeare]

These queries are a mess of aliases, is there a reason you set it up this way?

On your report I'm able to put in a filter of
[LIStatus] = "Budgeted" and get a list of budgeted items only, however when I use something like dsum("[Actual Cost]", "qryFinalWorkItemsbyAvailFunding", "
[LIStatus] = 'Deferred'") in the report footer I get an error and I think it's likely because of the really convoluted way you've done your queries but I don't use aliases very often (maybe 3 times in the last 10 years) so I am not really familiar with the problems that can arise from them.

Is it safe to assume that you want to put in a potential budget for a year and find out which items would be 'payable' for a specific customer? And that determining which items should be done first you go by 'must do' first, then by score in ascending or descending order?

Trying to trudge through the aliasing is really not my idea of fun

[mittra]

There are indeed a number of aliases in the queries. They are intended to help keep track of the various subqueries necessary to make the end result possible.

Your assumption is essentially correct. The client is supposed to import site plans with the work items for multiple sites and be able to enter how much they have to spend for the fiscal year. The report is supposed to then identify which work items can be completed based on that budget. It is supposed to make certain that the items identified as Must Do are done first, and then it needs to keep Project Items grouped together and apply the funding based on the Score in descending order.

I wouldn't think you would need DSUM at the report level. Your error is coming from your DSUM statement. The criteria portion of the statement needs a number that increments in a predictable way without duplicates (I just learned this myself).

[rpeare]

I was working with the query that runs the report. I was able to filter so that only the budgeted items showed, the problem was with the dsum using a syntax which I know is correct but still getting an error. The only thing I could think of was that the aliasing was the cause of the problem. This was the easiest solution I could think of when I looked at your db without having to unravel the aliasing to find out where along the way the problem was occurring.

Here's what I did to get it working
1. put a criteria of 'budgeted' in your query qryFinalWorkItemsbyAvailFunding
2. Save it as 'qryFinalWorkItemsbyAvailFunding_Budgeted'
3. Do the same thing with 'deferred'
4. Create a sub report that just sums all the values in the 'deferred' query
5. Put the sub report on the 'main' report

Dollar totals on both in the report footer appear to be correct.

I've tried any number of formulas without changing anything about your current setup and none of them work this was the only solution I could find that did without changing anything major about your setup.

[mittra]

So there is a way to put the results of multiple queries into one report? I'm unfamiliar with subreports.

Would you mind posting my sample back with the steps you've outlined so I can see how it works?

[rpeare]

SampleWorkingData.zip

to avoid multiple prompts for the same information open up my form frmmain and fill in the number value

[mittra]

Okay. Using a subreport, I can get the "Deferred" total for the building, complex, and site levels, but can't get the Grand Total of deferred items.....

Also, if a building or site is excluded from the report because it isn't budgeted for at all, I won't get a deferred cost either.

[mittra]

I wish I had shown you guys how I had done whatever it was I did on Friday to get the deferred cost into the various footers, because now I have exactly the opposite problem.

I have configured my sub report and report to show the budgeted items correctly and to give me the grand total of the deferred items cost, but I can't get the deferred cost totals for each footer (facility, complex, and/or site).

In the subreport, I renamed the textboxes "subDetailCost", "subFacilityCost", "subComplexCost", "subSiteCost", "subDeferredCost". If I put a textbox in the Facility Footer of the main report and assign the control source to "subFacilityCost", it shows me the deferred cost of the last facility in all of the other facility footers as well.

How would I have gotten the subtotals for each group before?

[mittra]

SampleWorkingData.zip

SOOOOO CLOSE! Yet so far....

Attached is my database with sample data in it. Open the form and select "Determine work plan items based on available funding" and put in 65000. The report opens up and shows the work items that can be done for each building based on $65000 and gives a sum of the work items that were deferred. Looks great. If there isn't a Complex, it hides the Complex Total lines beautifully. Now enter 925000 (enough funding to cover everything) and now I get a new error ("#Size!") for the deferred total. If you view the report in Report View, it correctly displays $0.00, but when it prints is has this "#Size!" error.

I have tried using nz() in combination with an iif statement about it being Null, but that doesn't seem to be working. I'm sure a simple line in my VBA code or changing how the Control Source is formatted will fix it, but I'm stuck.

Help?

[rpeare]

use a dcount function on the query deriving the deferred cost to find out if there are any records if you're going to do it this way:

=IIf(DCount("*","qryconstrainedworkitems_deferred" )=0,0,Nz([subSumFacilityCost_Deferred].[Report]![subSumTotalDeferred],0))

use the same dcount in each section formula