Query Question

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have this query that works great. It shows me which stores are buy a
certain product based on the product number.
My Problem is same reasoning however I want to know which stores are NOT
buying the product. I have tried using Null and I get nothing. Here is a copy
of the SQL of the query that is working Properly.

SELECT tblStores.StoreName, tblSumOfCasesByStore.[Customer Number],
tblSumOfCasesByStore.District, tblSumOfCasesByStore.[Sum Of Cases Shipped]
FROM tblStores INNER JOIN (tblSumOfCasesByStore INNER JOIN
tblStoresUSCustNos ON tblSumOfCasesByStore.[Customer Number] =
tblStoresUSCustNos.USCustNo) ON tblStores.StoreNo = tblStoresUSCustNos.StoreNo
WHERE (((tblSumOfCasesByStore.[USF Product
Number])=[Forms]![frmItemListVendor]![txtUSFSItemNo]))
ORDER BY tblStores.StoreName;
 
The null was a step in the right direction. However you have Inner Joins
which means the tables joined both need a matching record to return something.

Double-click on the line between the two tables and select one of the other
two options presented. See if that works. If not try the other option OR the
join on the other set of tables.
 
that did not work. Any other ides?

--
Lisa S.


Jerry Whittle said:
The null was a step in the right direction. However you have Inner Joins
which means the tables joined both need a matching record to return something.

Double-click on the line between the two tables and select one of the other
two options presented. See if that works. If not try the other option OR the
join on the other set of tables.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


Lisa said:
I have this query that works great. It shows me which stores are buy a
certain product based on the product number.
My Problem is same reasoning however I want to know which stores are NOT
buying the product. I have tried using Null and I get nothing. Here is a copy
of the SQL of the query that is working Properly.

SELECT tblStores.StoreName, tblSumOfCasesByStore.[Customer Number],
tblSumOfCasesByStore.District, tblSumOfCasesByStore.[Sum Of Cases Shipped]
FROM tblStores INNER JOIN (tblSumOfCasesByStore INNER JOIN
tblStoresUSCustNos ON tblSumOfCasesByStore.[Customer Number] =
tblStoresUSCustNos.USCustNo) ON tblStores.StoreNo = tblStoresUSCustNos.StoreNo
WHERE (((tblSumOfCasesByStore.[USF Product
Number])=[Forms]![frmItemListVendor]![txtUSFSItemNo]))
ORDER BY tblStores.StoreName;
Click to expand...
Click to expand...
 
Use you query as a SubQuery, like:

SELECT tblStores.StoreName, tblSumOfCasesByStore.[Customer Number],
tblSumOfCasesByStore.District, tblSumOfCasesByStore.[Sum Of Cases Shipped]
FROM tblStores INNER JOIN (tblSumOfCasesByStore INNER JOIN
tblStoresUSCustNos ON tblSumOfCasesByStore.[Customer Number] =
tblStoresUSCustNos.USCustNo) ON tblStores.StoreNo = tblStoresUSCustNos.StoreNo
WHERE tblStores.StoreNo Not In
(SELECT tblStores.StoreNo
FROM tblStores INNER JOIN (tblSumOfCasesByStore INNER JOIN
tblStoresUSCustNos ON tblSumOfCasesByStore.[Customer Number] =
tblStoresUSCustNos.USCustNo) ON tblStores.StoreNo = tblStoresUSCustNos.StoreNo
WHERE tblSumOfCasesByStore.[USF Product
Number]=[Forms]![frmItemListVendor]![txtUSFSItemNo])
ORDER BY tblStores.StoreName

--
Good Luck
BS"D


Lisa said:
that did not work. Any other ides?

--
Lisa S.


Jerry Whittle said:
The null was a step in the right direction. However you have Inner Joins
which means the tables joined both need a matching record to return something.

Double-click on the line between the two tables and select one of the other
two options presented. See if that works. If not try the other option OR the
join on the other set of tables.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


Lisa said:
I have this query that works great. It shows me which stores are buy a
certain product based on the product number.
My Problem is same reasoning however I want to know which stores are NOT
buying the product. I have tried using Null and I get nothing. Here is a copy
of the SQL of the query that is working Properly.

SELECT tblStores.StoreName, tblSumOfCasesByStore.[Customer Number],
tblSumOfCasesByStore.District, tblSumOfCasesByStore.[Sum Of Cases Shipped]
FROM tblStores INNER JOIN (tblSumOfCasesByStore INNER JOIN
tblStoresUSCustNos ON tblSumOfCasesByStore.[Customer Number] =
tblStoresUSCustNos.USCustNo) ON tblStores.StoreNo = tblStoresUSCustNos.StoreNo
WHERE (((tblSumOfCasesByStore.[USF Product
Number])=[Forms]![frmItemListVendor]![txtUSFSItemNo]))
ORDER BY tblStores.StoreName;
Click to expand...
Click to expand...
Click to expand...
 
Thanks however that did not work I still get nothing
--
Lisa S.


Ofer Cohen said:
Use you query as a SubQuery, like:

SELECT tblStores.StoreName, tblSumOfCasesByStore.[Customer Number],
tblSumOfCasesByStore.District, tblSumOfCasesByStore.[Sum Of Cases Shipped]
FROM tblStores INNER JOIN (tblSumOfCasesByStore INNER JOIN
tblStoresUSCustNos ON tblSumOfCasesByStore.[Customer Number] =
tblStoresUSCustNos.USCustNo) ON tblStores.StoreNo = tblStoresUSCustNos.StoreNo
WHERE tblStores.StoreNo Not In
(SELECT tblStores.StoreNo
FROM tblStores INNER JOIN (tblSumOfCasesByStore INNER JOIN
tblStoresUSCustNos ON tblSumOfCasesByStore.[Customer Number] =
tblStoresUSCustNos.USCustNo) ON tblStores.StoreNo = tblStoresUSCustNos.StoreNo
WHERE tblSumOfCasesByStore.[USF Product
Number]=[Forms]![frmItemListVendor]![txtUSFSItemNo])
ORDER BY tblStores.StoreName

--
Good Luck
BS"D


Lisa said:
that did not work. Any other ides?

--
Lisa S.


Jerry Whittle said:
The null was a step in the right direction. However you have Inner Joins
which means the tables joined both need a matching record to return something.

Double-click on the line between the two tables and select one of the other
two options presented. See if that works. If not try the other option OR the
join on the other set of tables.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


:

I have this query that works great. It shows me which stores are buy a
certain product based on the product number.
My Problem is same reasoning however I want to know which stores are NOT
buying the product. I have tried using Null and I get nothing. Here is a copy
of the SQL of the query that is working Properly.

SELECT tblStores.StoreName, tblSumOfCasesByStore.[Customer Number],
tblSumOfCasesByStore.District, tblSumOfCasesByStore.[Sum Of Cases Shipped]
FROM tblStores INNER JOIN (tblSumOfCasesByStore INNER JOIN
tblStoresUSCustNos ON tblSumOfCasesByStore.[Customer Number] =
tblStoresUSCustNos.USCustNo) ON tblStores.StoreNo = tblStoresUSCustNos.StoreNo
WHERE (((tblSumOfCasesByStore.[USF Product
Number])=[Forms]![frmItemListVendor]![txtUSFSItemNo]))
ORDER BY tblStores.StoreName;
Click to expand...
Click to expand...
Click to expand...
 
Hi Lisa
Try this

SELECT tblStores.*
FROM tblStores
WHERE (((tblStores.StoreNo) Not In (SELECT tblStores.StoreNo
FROM (tblSumOfCasesByStore INNER JOIN tblStoresUSCustNos ON
tblSumOfCasesByStore.[Customer Number] = tblStoresUSCustNos.USCustNo) INNER
JOIN tblStores ON tblStoresUSCustNos.StoreNo = tblStores.StoreNo
WHERE tblSumOfCasesByStore.[USF Product
Number]=[Forms]![frmItemListVendor]![txtUSFSItemNo])))

--
Good Luck
BS"D


Lisa said:
Thanks however that did not work I still get nothing
--
Lisa S.


Ofer Cohen said:
Use you query as a SubQuery, like:

SELECT tblStores.StoreName, tblSumOfCasesByStore.[Customer Number],
tblSumOfCasesByStore.District, tblSumOfCasesByStore.[Sum Of Cases Shipped]
FROM tblStores INNER JOIN (tblSumOfCasesByStore INNER JOIN
tblStoresUSCustNos ON tblSumOfCasesByStore.[Customer Number] =
tblStoresUSCustNos.USCustNo) ON tblStores.StoreNo = tblStoresUSCustNos.StoreNo
WHERE tblStores.StoreNo Not In
(SELECT tblStores.StoreNo
FROM tblStores INNER JOIN (tblSumOfCasesByStore INNER JOIN
tblStoresUSCustNos ON tblSumOfCasesByStore.[Customer Number] =
tblStoresUSCustNos.USCustNo) ON tblStores.StoreNo = tblStoresUSCustNos.StoreNo
WHERE tblSumOfCasesByStore.[USF Product
Number]=[Forms]![frmItemListVendor]![txtUSFSItemNo])
ORDER BY tblStores.StoreName

--
Good Luck
BS"D


Lisa said:
that did not work. Any other ides?

--
Lisa S.


:

The null was a step in the right direction. However you have Inner Joins
which means the tables joined both need a matching record to return something.

Double-click on the line between the two tables and select one of the other
two options presented. See if that works. If not try the other option OR the
join on the other set of tables.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


:

I have this query that works great. It shows me which stores are buy a
certain product based on the product number.
My Problem is same reasoning however I want to know which stores are NOT
buying the product. I have tried using Null and I get nothing. Here is a copy
of the SQL of the query that is working Properly.

SELECT tblStores.StoreName, tblSumOfCasesByStore.[Customer Number],
tblSumOfCasesByStore.District, tblSumOfCasesByStore.[Sum Of Cases Shipped]
FROM tblStores INNER JOIN (tblSumOfCasesByStore INNER JOIN
tblStoresUSCustNos ON tblSumOfCasesByStore.[Customer Number] =
tblStoresUSCustNos.USCustNo) ON tblStores.StoreNo = tblStoresUSCustNos.StoreNo
WHERE (((tblSumOfCasesByStore.[USF Product
Number])=[Forms]![frmItemListVendor]![txtUSFSItemNo]))
ORDER BY tblStores.StoreName;
Click to expand...
Click to expand...
Click to expand...
 
Back
Top