required field

[Geel]

Hello,

I will try to explain this as understanding as possible.

I've build up query's on where i base reports on. In these query's a
lot of tables and other query's are linked to gether.

levels:

1st: projects
2th: category
3th: department
4th: total


2 of the fields i added to the table represent a Budget wich are on the
department level.

Now what i would like to happen but where i havn't got any solution for
is that i want the Budget to always show up when it is there. And thus
always making the department level of the budget wich is entered show
up, ofcourse when there is a budget entered. (maybe 2de option would
be, always showing all the departments?).

Problem: when i look at the big query where the report is based on, the
budgets won't appear when they are only entered and the department
hasn't got any projects in it.

Possible 2de solution: entering blank projects, problem is that when i
would do this some numbers are used for calculating percentages and
that would cause 0/0 calucations and would give me another problem.

I really thought about this well but i havn't got a solution yet, and
that's why i came here, i'm hoping for some good tips or answers wich
would be all greatly apreciated.

Thx already,

Tim

 

[Geel]

Jerry Whittle schreef:

You have an inner join between the various tables. That means there needs to
be a matching record in all tables for the record to show. What you need is a
Left join.

Open up the query in design view. Double click on the line between the two
tables that you're having troubles. It's a little tricky to do. A dialog box
should show. Once it does, select the second option and see if that works.
If not try the third option. If that still doesn't work, you have a join
between other tables causing the problems. Don't be surprised if you get an
Unambigous Outer Join error. That means you may need to join the tables in a
different order or go to sub-queries.

It really helped me think, and espacially remind me again of the right
and left joins. But ofcourse the ambigious error you were talking about
showed up. So i suppose fixing this isn't gonna be very easy.

At this moment i've got some calculation query's and tables with
entered values, these are all joined together in 1 big query, this
means this final query where my report is based on has a lot of joins.
What i now think of a possible solution is leaving the budget out and
doing all the inner joins in a seperate query. And then adding the
budget query in the final query.

What do you think ?