Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Parameter- not append

Discussion in 'Information Technology' started by salmonella, Nov 1, 2005.

  1. salmonella

    salmonella
    Expand Collapse
    Guest

    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!
     
  2. '69 Camaro

    '69 Camaro
    Expand Collapse
    Guest

    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" wrote:

    > 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!
    >
    >
     
  3. salmonella

    salmonella
    Expand Collapse
    Guest

    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






    "'69 Camaro" wrote:

    > 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" wrote:
    >
    > > 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!
    > >
    > >
     
  4. MtnWindow@hotmail.com

    MtnWindow@hotmail.com
    Expand Collapse
    Guest

    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.
     
  5. salmonella

    salmonella
    Expand Collapse
    Guest

    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!

    "MtnWindow@hotmail.com" wrote:

    > 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.
    >
    >
     

Share This Page