Report Question

Topic

New Reply

Hello Everyone,

I need HELP resolving this issue and was hoping someone would offer some ideas.

I have 3 tables…1)tTime, 2)Projects and 3)Budgets. In tTime, there are many fields one of which WeekEndingDate another one is Department, etc… In Department there is only 1 field … that field contains a list of 15 different departments.

My query joins all 3 tables with the critera being specified projects with specified WEDates (all Friday dates) and returns:

the projects which had time spent for the specified period grouped by the department.

My problem is that if a project had time spent on it say, Department1 didn’t spend any time, Department2 didn’t spend any time, but Department3-15 did, it will show Department 3-15, I need it to show 1 and 2 as well but since these departments didn’t spend any time, I would like it to show 0. Can this be done?

Thanks,

Reply | Quote

Replies

Hi Roberta,

It sounds like you need to modify the join properties of the Department table in your query. Just double-click on the Relationship line between your Department table and the other table that it’s linked to. You’ll see the “Join Properties” dialog box. From there you can choose (1,2, or 3) to Show ALL records from the Departments table. Access 2000 gives you a few more options to play with than 97, but basically the concept is the same. Once you’ve told it to show ALL records in the Department table you’ll see all departments.

If the value for the unused Departments shows as Null, you may want to use the NZ() function as an expression on the Department column in the query: =NZ([DepartmentID])

HTH

Reply | Quote