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