Welcome to SPN

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

Sign Up Now!

query wierdness

Discussion in 'Information Technology' started by Brian, Jul 28, 2006.

  1. Brian

    Brian
    Expand Collapse
    Guest

    I am setting up a query through the QBE grid using 3 tables. Each table has
    a 1-many relationship to one of the other table fields:

    customer_tbl
    custID (PK)
    name ..... etc

    invoice_tbl
    invoiceNum (PK)
    custID (1-many from customer_tbl)
    yada yada yada

    tblProdDates
    prodDateID (PK)
    invoiceNum (1-many from invoices_tbl)
    date ...... etc

    When I place the customer_tbl and invoice_tbl tables in the QBE, and query
    on the name and invoiceNum Fields I return 77 records. If I add the
    ProdDates_tbl table to the grid - even if I don't add any table fields to
    the query, my results drop to 66 records - even though I know there are
    records that should be picked up by the query. If i am not adding any
    criteria from the ProdDates_tbl table yet, why will that change my query
    results, and what can I do to include all the tables needed?

    The SQL statements

    Just the customer_tbl and invoice_tbl
    ---------------------------------------
    SELECT customer_tbl.name_last, invoice_tbl.[invoice#], invoice_tbl.jobType,
    invoice_tbl.orderDate, invoice_tbl.promDate, invoice_tbl.OrderStatus,
    invoice_tbl.style
    FROM customer_tbl INNER JOIN invoice_tbl ON customer_tbl.custID =
    invoice_tbl.customerID
    ORDER BY customer_tbl.name_last;

    Adding the ProdDates_tbl with no criteria added
    -----------------------------------------------
    SELECT customer_tbl.name_last, invoice_tbl.[invoice#], invoice_tbl.jobType,
    invoice_tbl.orderDate, invoice_tbl.promDate, invoice_tbl.OrderStatus,
    invoice_tbl.style
    FROM (customer_tbl INNER JOIN invoice_tbl ON customer_tbl.custID =
    invoice_tbl.customerID) INNER JOIN ProdDates_tbl ON invoice_tbl.[invoice#] =
    ProdDates_tbl.[invoice#]
    ORDER BY customer_tbl.name_last;

    I see the difference in the SQL statement - but what does it mean?
     
  2. Loading...

    Similar Threads Forum Date
    Query about Jhatka Meat by Shooting in Head Sikh Sikhi Sikhism Aug 26, 2011
    Who is a sikh? A non sikh friend's query!! Sikh Sikhi Sikhism Apr 30, 2010
    General Query Hard Talk Sep 4, 2008
    Power of pauri's in Japji Sahib query Sikh Sikhi Sikhism Aug 17, 2006
    Sikhism a query Book Reviews & Editorials Aug 2, 2005

  3. strive4peace

    strive4peace
    Expand Collapse
    Guest

    Hi Brian,

    that happens because you do not have a record for every
    invoiceNum in your tblProdDates table.

    Righ-click on the Join line connecting invoiceNum between
    the 2 tables. Choose to show all records in tblProdDates
    and only those records in tblProdDates where the records match.

    You may need to do the same between customer_tbl and
    invoice_tbl if Access compalins about ambiguous outler joins.

    Warm Regards,
    Crystal
    MVP Microsoft Access

    remote programming and training
    strive4peace2006 at yahoo.com
    *
    Have an awesome day ;)


    Brian wrote:
    > I am setting up a query through the QBE grid using 3 tables. Each table has
    > a 1-many relationship to one of the other table fields:
    >
    > customer_tbl
    > custID (PK)
    > name ..... etc
    >
    > invoice_tbl
    > invoiceNum (PK)
    > custID (1-many from customer_tbl)
    > yada yada yada
    >
    > tblProdDates
    > prodDateID (PK)
    > invoiceNum (1-many from invoices_tbl)
    > date ...... etc
    >
    > When I place the customer_tbl and invoice_tbl tables in the QBE, and query
    > on the name and invoiceNum Fields I return 77 records. If I add the
    > ProdDates_tbl table to the grid - even if I don't add any table fields to
    > the query, my results drop to 66 records - even though I know there are
    > records that should be picked up by the query. If i am not adding any
    > criteria from the ProdDates_tbl table yet, why will that change my query
    > results, and what can I do to include all the tables needed?
    >
    > The SQL statements
    >
    > Just the customer_tbl and invoice_tbl
    > ---------------------------------------
    > SELECT customer_tbl.name_last, invoice_tbl.[invoice#], invoice_tbl.jobType,
    > invoice_tbl.orderDate, invoice_tbl.promDate, invoice_tbl.OrderStatus,
    > invoice_tbl.style
    > FROM customer_tbl INNER JOIN invoice_tbl ON customer_tbl.custID =
    > invoice_tbl.customerID
    > ORDER BY customer_tbl.name_last;
    >
    > Adding the ProdDates_tbl with no criteria added
    > -----------------------------------------------
    > SELECT customer_tbl.name_last, invoice_tbl.[invoice#], invoice_tbl.jobType,
    > invoice_tbl.orderDate, invoice_tbl.promDate, invoice_tbl.OrderStatus,
    > invoice_tbl.style
    > FROM (customer_tbl INNER JOIN invoice_tbl ON customer_tbl.custID =
    > invoice_tbl.customerID) INNER JOIN ProdDates_tbl ON invoice_tbl.[invoice#] =
    > ProdDates_tbl.[invoice#]
    > ORDER BY customer_tbl.name_last;
    >
    > I see the difference in the SQL statement - but what does it mean?
    >
    >
     

Share This Page