join query problem

  • Thread starter Thread starter Alex
  • Start date Start date
A

Alex

This query comes up with emty Territory_ID and Description
columns.

SELECT qrySales.Product_ID, qrySales.Distributor_ID,
qryTerritory.Territory_ID, qryTerritory.Description
FROM qrySales LEFT JOIN qryTerritory ON
qrySales.Distributor_ID = qryTerritory.Distributor_ID;

Separately, the queries are working. I even added them to
relationship scheme creating one to many relationships
there. Anyway, these columns are empty.

Can anybody clarify what's wrong with this query.

Thanks in advance.
 
The rows that are returned with blank territory_Id and
description columns are because its distributor_Id returned
by qrySales does not exist in the output returned by
qryTerritory. If you don't want those rows, then replace
LEFT JOIN with INNER JOIN.

Hope This Helps
Gerald Stanley MCSD
 
Thanks, but when I changed it to INNER JOIN, the whole
query is empty. The both queries, as I said, are working
separately and the both of them have distributors. The
qrySales has repeated (many) Distributors (it goes
together with Product_ID, Product_ID and Distributors are
grouped - non-repeated in qrySales). The qryTerritory has
non-repeated (one) Distributors. May be something wrong
with these repeated and non-repeated ones.
But, I need them all from the qrySales.

SELECT qrySales.Product_ID, qrySales.Distributor_ID,
qryTerritory.Teritory_ID, qryTerritory.Description
FROM qrySales INNER JOIN qryTerritory ON
qrySales.Distributor_ID = qryTerritory.Distributor_ID;

I did copmact and repair DB but it's still not working.

Thanks
 
Thanks everybody!!!
I've done it. It was format mismatching. I've fixed it but
forgot to transfer the new data into Temporary table. So,
it was working with old data giving just 3 digits from
Temporary table but the original table was with 5 digits.
This is why it was empty.
 
Back
Top