Query Question

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;
 
G

Guest

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.
 
G

Guest

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...
 
G

Guest

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...
 
G

Guest

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...
 
G

Guest

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

REP: Changing Text to Number 1
Cross-Tab Query 2
Query not showing certain records 5
The query cannot be completed. 1
Another query too complex problem 3
Query Too Complex with Total line 8
Combining 3 queries 2
Help with query! 3

Top