Data Not Displaying in Report

[roxtonsgirl]

I have a database for tracking materials purchases. One of the reports is a general summary that shows the Departments, Budgeted Amount, Encumbered Amount, Expended Amount and Percent Expended. It is based off of two queries (one that totals expenditures and one that totals encumbered amounts). It works fine so long as there has been spending. However, if a department hasn’t spent anything it doesn’t show up on the report at all. I would like the report to display the department, regardless of spending as their total budget and encumbered amounts would still be there.

I’ve searched through the forums and I think I’m using the wrong keywords because I’m not finding what I’m looking for. The closest I’ve found is information on the “On No Data” property which isn’t quite what I’m looking for. I think perhaps I have a flaw in my report design, but I'm not quite sure what I did wrong that the fields won't all display. Any assistance would be much appreciated.

Thank you,
Steph

 

[shadow9449]

Steph:

Without seeing how the report draws the data, it's hard to give specific advice but in general, your best bet would probably be to change the fields' control sources to =NZ([data], 0) or something along that line, depending on how the data is being drawn.

Unless someone else can help you, you may be best off uploading the database or a functioning mockup of the section that's troubling you (the latter is probably the best approach).

SHADOW

 

[roxtonsgirl]

Steph:

you may be best off uploading the database or a functioning mockup of the section that's troubling you (the latter is probably the best approach).

SHADOW

Shadow,

Thanks for your suggestion. I wasn't able to get it to work, but I'm also not great with Access especially coding. It might have just been the way I entered the information in the control source. I would like to upload a functioning mockup, but I'm not quite sure how to do that. Do most people just create another build, or is there a simple way to export the existing report? I did mention that I'm not great with Access, yeah?

Thanks,
Steph

 

[pbaldy]

If the database doesn't contain anything sensitive, you can just attach the whole thing. Otherwise, you can export the report and any objects it depends on into a new, empty db. The report alone is probably not enough, as without data we wouldn't be able to replicate your issue. More here:

http://www.access-programmers.co.uk/forums/showthread.php?t=140587

 

[roxtonsgirl]

Thanks for the hyperlink, that helped. The information in the database at this point is all dummy stuff for testing puposes, so it's not too sensitive right now. The report I'm having difficulty with is the "General Summary". It can be found from the "Materials Management" form that opens at launch or under the "Budget" heading in the Navigation Pane.

Thanks again,

Steph

 

[pbaldy]

Does this get you what you want?

SELECT Budget.Department, Budget.Amount, [Encumbered by Department].[SumOfEncumbered Amount], [Department Summary].SumOfExpended, [Department Summary].Percentage FROM [Encumbered by Department] RIGHT JOIN ([Department Summary] RIGHT JOIN Budget ON [Department Summary].Department=Budget.Department) ON [Encumbered by Department].Department=Budget.Department;

 

[roxtonsgirl]

Thank you very much. That worked like a charm! Obviously I need to do some reading on Joins and SQL. I appreciate your figuring out the SQL statement for me, I don't think I could have devised that one in this stage of my Access and SQL knowledge.

 

[pbaldy]

My first thought was joins, but it helped to see the db. One way to have it make more sense is to look at your original query in design view. Right click on one of the join lines between tables and select Join Properties. The three choices give a nice description of what each will do. Choose one and then look in SQL view to see what it did.

 

[roxtonsgirl]

Thanks, I will try that out. It always helps to have a somewhat hands on example like that.