display non match records

[jmnekrkt]

I'm trying to create a report that will show expenditures by section for several different categories. Each section may not have an expenditure in each category but I still want each category to print for each section. Any ideas?

Thank you

 

[SgtJarrow]

You will need to rely on your underlying data source for this one.....

I am going to assume you have two tables...one with the sections and one with the categories. I am also assuming that you have the two related similar to

tblSections
Section (PK)

tblCategories
Section (FK)
Category (PK)

And I am assuming you have set up a one to may relationship between the two tables.

Create a query displaying the fields that you want to view...in this case Section from tblSections and Category from tblCategories. In the top portion of the design view, right click on the join line between the two tables and select join properties. In this window, look for the option that say show all records from the Sections table and matching records from the categories table.

This should provide the data you want to see. It's not important that someone else can do in one step what it took you ten to do...the important thing is that you found a solution.

Robert L. Johnson III, A+, Network+, MCP
Access Developer/Programmer

 

[jmnekrkt]

All sections can use each category so the category table does not have a section number. Each section has many orders. Each order relates to a category. The problem is that the categories ALL have to repeat for each section whether there is a value or not:
Section 1
Allotment 1 $20
Allotment 2 0
Allotment 3 $10
Section 2
Allotment 1 0
Allotment 2 0
Allotment 3 $100
Section 3
Allotment 1 $5
Allotment 2 $10
Allotment 3 $50

The one to many only seems to work if there is only one section.

 

[SgtJarrow]

Okay, I guess I am not understanding how your data is set up....If you can list the table structures like I did above, maybe it will help me help you...

That being said, from the sample you just provided, it seems you merely want to show a 0 in any allotment that doesn't have data.... If this is the case, you can try the following...

You should have a report with grouping per section already set up. You should also have the "Allotments" listed as above. Then, in the control source property for each of the "Allotment" boxes, try

IIf(IsNull([fieldnameofdata]),0,[fieldnameofdata])

This says

If the field name is blank, put a 0 here, else list the field data....

Let me know. It's not important that someone else can do in one step what it took you ten to do...the important thing is that you found a solution.

Robert L. Johnson III, A+, Network+, MCP
Access Developer/Programmer

 

[jmnekrkt]

I sort of came to the same conclusion. I used a crosstab query to group & total.

I had hoped to not have to hard code each allotment in the report. I wanted the Allotment table to be the source so that if I add a new allotment to the table, I won't have to add a new field to the report.

Thank you for your assistance & quick responses.

Debby