simple query question

[Guest]

I pull the results of multiable queries into one, as long as everyone of them
has a value the master query works fine, but if one of them has zero/no
count/no value (whatever you call it) then the entire master query has no
values.
I cannot change the table, but is there something I can do in the queries to
give me the values of the sub queries with values???

 

[Allen Browne]

In the upper pane of the query design window, double-click the line joining
your 2 tables. Access responds with a dialog that gives you 3 options.
Choose the one that returns all records from the table you want.

Technically, this is known as an outer join. More info in the 2nd part of
this article:
The Query Lost My Records! (Nulls)
at:
http://members.iinet.net.au/~allenbrowne/casu-02.html

 

[Guest]

That's the problem there is nothing to join them with!!! Let me explain a
little more in depth.

1. I have a buch of queries that calculate a score.
2. I need to put all those scors on one query (call it Master Query).
ie: Q1=80% Q2=75% etc.

3. Now, using a new query (call it Grand Master) I will put Master query and
another similur query.

On Grand Master I can now put >80% for each score and 90% for each score on
the simular query.

(Simular query is Physicians Scores)
So, all Physicians scoring 90% on questions >80%...

Help!!!
Thanks

 

[Allen Browne]

Well, I didn't really follow that, Dan, but I think you are saying that the
problem is that the data structure is inadequate.

You need a question table (one record for each question). By putting this
table as the main table in the outer join, all questions are represented in
the query.

Person table: one record for each doctor who takes the test.

Test table: one record for each time a person takes a test.

TestDetail: one record for each answer given in a Test.

Now you can create a query to average answers or perform whatever stats you
need. As long as the original Question table is included with an outer join,
the question will be present, even if the statistical results are null (e.g.
if no-one answered the question).

If you are trying to perform an actual verses a statistical score, a
subquery might be worthwhile. If that's new, see:
How to Create and Use Subqueries
at:
http://support.microsoft.com/?id=209066

 

[Guest]

Dan, Don't know if the response you got to this was helpful or not. I
happened to see your post when I was looking for the same type of answer. I
think you might have been having the same problem I was, so I am posting the
URL for my question and the wonderful answer that I got, which worked.

http://support.microsoft.com/newsgr...9d6569-d600-4547-9026-c99a6095573e&sloc=en-us