What will be the function?

[D]

Hi:

I have the query below and it woks fine; the result shows the stores that
are selling invalid menu items.
***
SELECT DISTINCT ODSDBA_WATP1MIT.MENU_ITEM_CD, ODSDBA_WATP1MIT.MENU_ITEM_DSC,
ODSDBA_WATS5MIX.DLY_UNT_SLS_QTY, ODSDBA_WATS5MIX.NATL_STR_NBR,
ODSDBA_WATS5MIX.DLY_SLS_DT, [DS Complete].[Site Description], [DS
Complete].City, [DS Complete].Prov, IIf([Owner Type]="M",[Business/Ops
Consultant],[Franchisee]) AS Expr1, IIf([Owner Type]="M","Ops.
Consultant","Franchisee") AS Expr2, [DS Complete].[BO Mobil], [DS
Complete].[BO Office], [DS Complete].[Fran Mobil], [DS Complete].[Owner
Type], [DS Complete].[Fran Office], [DS Complete].[Restaurant Phone #], [DS
Complete].[McOpCo#], [DS Complete].Comp, ODSDBA_WATP1MIT.AMIL_FG,
ODSDBA_WATP1MIT.MENU_ITEM_STS, IIf([dly_sls_dt]>[comp],"Contact
Store?","Ignore") AS Expr3
FROM (ODSDBA_WATP1MIT INNER JOIN ODSDBA_WATS5MIX ON
ODSDBA_WATP1MIT.MENU_ITEM_CD=ODSDBA_WATS5MIX.MENU_ITM_NBR) INNER JOIN [DS
Complete] ON ODSDBA_WATS5MIX.NATL_STR_NBR=[DS Complete].[Natl#]
WHERE (((ODSDBA_WATP1MIT.MENU_ITEM_CD) Not In (328)) AND
((ODSDBA_WATS5MIX.DLY_SLS_DT) Between [Please enter the Start Date] And
Date()) AND ((ODSDBA_WATP1MIT.AMIL_FG)="N") AND
((ODSDBA_WATP1MIT.MENU_ITEM_STS) Not Like "T") AND
((IIf([dly_sls_dt]>[comp],"Contact Store?","Ignore")) Like "Contact Store?"))
ORDER BY ODSDBA_WATS5MIX.DLY_SLS_DT DESC;
***

But now here is the issue I want to solve:

I have a table with ‘test’ menu items and another table with stores that are
selling the ‘test menu items’; these stores can sell also, other menu items;
these two tables are just one field tables; one with test mi and the other
one with storetest.

I hope I was clear so far?

My question is how do I get the stores that are selling the ‘test menu
items’ but are not allowed to do it (are not in the test stores table?

Thanks,

Dan

 

[[MVP] S.Clark]

The first problem I would have would be the definition of what an invalid
product for a store is.

Can a product be valid for one store, but not another? Is so, then a single
query may not find what you need. You may need to query one time for each
store, after ascertain which products are not valid for that store.

Once the invalid productID's for a store is known, then a simple query can
be ran to find if that storeID has any one of those productID's

IMHO, having an IIF in a query is a red flag that there is something
inherently wrong with the rest of the database.

 

[D]

Thanks Steve!

Dan

The first problem I would have would be the definition of what an invalid
product for a store is.

Can a product be valid for one store, but not another? Is so, then a single
query may not find what you need. You may need to query one time for each
store, after ascertain which products are not valid for that store.

Once the invalid productID's for a store is known, then a simple query can
be ran to find if that storeID has any one of those productID's

IMHO, having an IIF in a query is a red flag that there is something
inherently wrong with the rest of the database.

--
Steve Clark, Access MVP
FMS, Inc.
www.fmsinc.com/consulting

Hi:

I have the query below and it woks fine; the result shows the stores that
are selling invalid menu items.
***
SELECT DISTINCT ODSDBA_WATP1MIT.MENU_ITEM_CD,
ODSDBA_WATP1MIT.MENU_ITEM_DSC,
ODSDBA_WATS5MIX.DLY_UNT_SLS_QTY, ODSDBA_WATS5MIX.NATL_STR_NBR,
ODSDBA_WATS5MIX.DLY_SLS_DT, [DS Complete].[Site Description], [DS
Complete].City, [DS Complete].Prov, IIf([Owner Type]="M",[Business/Ops
Consultant],[Franchisee]) AS Expr1, IIf([Owner Type]="M","Ops.
Consultant","Franchisee") AS Expr2, [DS Complete].[BO Mobil], [DS
Complete].[BO Office], [DS Complete].[Fran Mobil], [DS Complete].[Owner
Type], [DS Complete].[Fran Office], [DS Complete].[Restaurant Phone #],
[DS
Complete].[McOpCo#], [DS Complete].Comp, ODSDBA_WATP1MIT.AMIL_FG,
ODSDBA_WATP1MIT.MENU_ITEM_STS, IIf([dly_sls_dt]>[comp],"Contact
Store?","Ignore") AS Expr3
FROM (ODSDBA_WATP1MIT INNER JOIN ODSDBA_WATS5MIX ON
ODSDBA_WATP1MIT.MENU_ITEM_CD=ODSDBA_WATS5MIX.MENU_ITM_NBR) INNER JOIN [DS
Complete] ON ODSDBA_WATS5MIX.NATL_STR_NBR=[DS Complete].[Natl#]
WHERE (((ODSDBA_WATP1MIT.MENU_ITEM_CD) Not In (328)) AND
((ODSDBA_WATS5MIX.DLY_SLS_DT) Between [Please enter the Start Date] And
Date()) AND ((ODSDBA_WATP1MIT.AMIL_FG)="N") AND
((ODSDBA_WATP1MIT.MENU_ITEM_STS) Not Like "T") AND
((IIf([dly_sls_dt]>[comp],"Contact Store?","Ignore")) Like "Contact
Store?"))
ORDER BY ODSDBA_WATS5MIX.DLY_SLS_DT DESC;
***

But now here is the issue I want to solve:

I have a table with 'test' menu items and another table with stores that
are
selling the 'test menu items'; these stores can sell also, other menu
items;
these two tables are just one field tables; one with test mi and the other
one with storetest.

I hope I was clear so far?

My question is how do I get the stores that are selling the 'test menu
items' but are not allowed to do it (are not in the test stores table?

Thanks,

Dan