query wierdness

  • Thread starter Thread starter Brian
  • Start date Start date
B

Brian

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?
 
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 ;)
 
Back
Top