Parameter- not append

[salmonella]

Please ignore the other message about an append query- don't know what I was
thinking. I hope this now makes sense!!!

This is different but similar to another problem I sought advice on.

If you have two tables, A & B, and they are joined with a left join in a
select query, it will return all records in A even if there is not a matching
record in B.

HOWEVER, if I change the query to an parameter query (for fields in tables A
and B) and there are no matching
records in table B, then the record in Table A will not be returned. How can
I get around this so that a record that matches the parameter in A will be
returned when there is not a matching record in table B? Thus it is not a
matter of the field in table B being null, but rather that there is no
matching record at all.

In actuality there are about 5 tables joined and I am running a Parameter
query across them all and I would like to still return a record if it
matches, for example, the parameters for tables A,B and C even if there is
not a matching record in table D.

Any ideas?


Thanks!

 

['69 Camaro]

Check the joins in the chain of five tables. One of them is likely
interfering. Look at the SQL. Do you have any INNER JOIN's or OUTER JOIN's
in the wrong direction limiting the "matches"? Are the joins on the proper
columns?

If nothing catches your eye, then create a new, simpler query with just two
of the tables, a few fields, and the OUTER JOIN. If that works okay, then
join the third table. If that works okay, then join the fourth table. If
that works okay, then join the fifth table. If that works okay, then start
adding more columns. Build up your simpler query by adding tables and
columns until it matches your query that doesn't work. At some point it will
break, and you'll find the cause.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.

 

[salmonella]

I have already done what you said but that is not the problem.

Instead of describing it let me give an example. A have a 3 tables called 1,
2,3. If table one has a field called technician, table 2 a field called
method, and table 3 a field called bacteria then with left joins on a query i
could get all records which show the technician, the method they used and the
bacteria they isolated using the method. This works fine. However, if i put
parameters on the query for each field that look up their values from combo
boxes, then it works fine, IF there is a matching record in all 3 tables.
However, if I have entered only the technician and the method but do not yet
have data on the bacteria found, and I run the query, this record will not be
returned because there is not a matching record for field bacteria in table 3
so the query will not return. Again, without the parameter and using a left
join it works fine. It is the where clause that is messing it up.

I honestly do not think that there is a simple way of doing this. I assume
that something like a select query must be used first to get all the records,
the null fields changed to zero length or something and then the parameter
query would work using a wildcard as a default for the parameter when no
value is chosen. I am sure that was very confusing.

Another way to see it is: how do people generate a report using a parameter
query that has parameters in many fields and many tables where not all
records in some tables have matching records in others?

Any other ideas?

thanks

 

[MtnWindow]

In the criteria box in design view, it can say something like:
[Enter bacteria] Or Is Null

That will work if there are matches on technician and method, but no
matching records at all for the bacteria. But if there are matching
records for a bacteria different than what was entered as the
parameter, then no records will be returned, (which is correct since no
records matched all criteria).

You might want to try using the built-in Filter by Form menu option
instead of a parameter query. Or using the kind of form described in
this article:
http://support.microsoft.com/default.aspx?scid=kb;en-us;304302. Using a
form or filter by form might be better because the results could be
seen after applying one parameter at a time.

 

[salmonella]

Thanks for the ideas. However, they won't work. I have a NZ function in the
criteria box that returns either the value or "*" however, if I add a is null
and the person sets criteria only for the type of bacteria (and the field is
null) then it will return all records with null values (because other two
fields in person and method will also have a is null and the nz function. I
really think that the only way around this is to creat a record set or
somthing off a query with no criteria set (this will return all values) then
change null to some string then but the nz function on it and then it will do
want i want and not return null records as above..... it just seems like
there should be something simpler for something so basic as parameter queries
across multiple tables!!!!

Many thanks!