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