include zero data where no record

[Sandy Gomez]

I have a table with budget and forecast information by task joined to another table with detailed actual info by task. On a report, I want to show the budget and forecast information even if there is no detailed actual information. How do I do this when there is no Task info in the detail table?



Sample

Task 1 John Doe 20 actual (detail table), 10 budgeted (b&f table), 20 forecast (b&f table)
Task 2 (no record in detail table), 15 budgeted (b&f table), 30 forecast (b&f table)

I tried a join to include all records from the b&f table and only those records from the detail table where the fields are equal, but it still leaves out the b&f info.

[TheShabz]

Other way around. You want everything in the b&f table and only what matches in the detail table.

Also, look up the Nz() function to replace nulls with 0s.

[ajetrumpet]

IMO, the NZ function will not help you (unless I'm understanding the question incorrectly). If there is no record, you can't pull it. I'm having the same problem, but I have my own function to fix it. I use it to manipulate the source tables because I have found no other interface solution.

Your situation is easier though I think. At this point, you can certainly write a small function that adds new recs to table 2 based on whether the identifier from table 1 is in there or not.

I would suggest doing that first. To fix this in the future, consider adding some code or something that automatically inserts a new record in table 2 when you create a new rec for table 1. If you use that sort of preventive measure today, you won't ever have to ask this question again.

[TheShabz]

What I understood was that she is getting

20 10 20
null 15 30

and wants to replace the null with a 0. That's why I recommended the Nz() function

[NoellaG]

Hi,

use an outer join in your query

grNG