Results 1 to 3 of 3
  1. #1
    Join Date
    May 2003
    Location
    Dallas
    Posts
    817
    Provided Answers: 5

    Unanswered: Reverse engineering SQL to Access SQL!

    i GET A SYNTAX ERROR AND i CANNOT SEE WHERE IT IS PLEASE HELP

    SELECT dbo_vMFI_TICKETS_SHIPPED.FISCAL_CD, dbo_MFI_MASTER_STORES.MARKET AS DISTRICT, dbo_MFI_MASTER_STORES.GERS_CODE, dbo_MFI_MASTER_STORES.STORE,
    Sum([EXT_PRICE]) AS FSALES,
    Sum([Gross]) AS FGross,
    NZ(Min([Bud_AMT]),0) AS BUDGET,
    NZ(Min([Bud_GM]),0) AS GM_BUDGET

    FROM dbo_vMFI_TICKETS_SHIPPED
    Inner JOIN dbo_MFI_MASTER_STORES ON
    dbo_vMFI_TICKETS_SHIPPED.STORE = dbo_MFI_MASTER_STORES.STORE
    and dbo_vMFI_TICKETS_SHIPPED.SO_STORE_CD = dbo_MFI_MASTER_STORES.GERS_CODe

    Left Join MFI_BUD_STORE_PER
    on vMFI_TICKETS_SHIPPED.SO_STORE_CD = dbo_MFI_BUD_STORE_PER.store_cd
    AND dbo_vMFI_TICKETS_SHIPPED.FISCAL_CD = dbo_MFI_BUD_STORE_PER.FISCAL_CD
    AND dbo_MFI_MASTER_STOREs.GERS_CODE = dbo_MFI_BUD_STORE_PER.STORE_CD

    WHERE (((dbo_vMFI_TICKETS_SHIPPED.FISCAL_CD)=201106)
    AND ((dbo_MFI_MASTER_STORES.WAREHOUSE)=0)
    AND ((dbo_MFI_MASTER_STORES.MARKET_CONSULTANT) Is Null));
    Dale Houston, TX

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    What happens when you switch (or try to) from SQL View to Datasheet view in the Query designer? Usually it delivers a (rather) detailed error message and highlight the place where it founds something wrong.
    Have a nice day!

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT dbo_vmfi_tickets_shipped.fiscal_cd
         , dbo_mfi_master_stores.market as district
         , dbo_mfi_master_stores.gers_code
         , dbo_mfi_master_stores.store
         , sum([ext_price]) as fsales
         , sum([gross]) as fgross
         , nz(min([bud_amt]),0) as budget
         , nz(min([bud_gm]),0) as gm_budget
      FROM (
           dbo_vmfi_tickets_shipped
    INNER 
      JOIN dbo_mfi_master_stores 
        ON dbo_mfi_master_stores.store     = dbo_vmfi_tickets_shipped.store
       AND dbo_mfi_master_stores.gers_code = dbo_vmfi_tickets_shipped.so_store_cd
       AND dbo_mfi_master_stores.warehouse = 0  
           ) 
    LEFT OUTER
      JOIN dbo_mfi_bud_store_per
        ON dbo_mfi_bud_store_per.store_cd  = dbo_vmfi_tickets_shipped.so_store_cd
       AND dbo_mfi_bud_store_per.fiscal_cd = dbo_vmfi_tickets_shipped.fiscal_cd
     WHERE dbo_vmfi_tickets_shipped.fiscal_cd = 201106 
       AND dbo_mfi_master_stores.market_consultant IS NULL
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •