Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Help with SQL view

Discussion in 'Information Technology' started by Ian, Jul 28, 2006.

Tags:
  1. Ian

    Ian
    Expand Collapse
    Guest

    Need some help with SQL view. I can't write sql queries and I have a
    spreadsheet that is linked to our sql db. So what I do is design the queries
    in access with linked tables then choose sql view and plunk it into the query
    builder in excel to link the pivot table to the sql db. I've run into a
    problem with an IsLike statement -- the query bulder won't let me put the
    field 'apptnote' (see below) in and create the islike clause. I'm not sure
    how it's supposed to be written. I've attached the sql code form access and
    the one from the query builder. The query is supposed to return any records
    where the 'apptnote' contains the word "implant" OR the 'appttype' is 6 OR
    the 'apptype' is 16 (in all cases the pendingsource=0). Ignore the FROM
    statement because the datasource is slighly different (linked table in first,
    SQL db in second). Thanks for any help.

    SQL View from Access for Query that works:

    SELECT dbo_ApptPending.ApptPendingID, dbo_ApptPending.OfficeID,
    dbo_ApptPending.ProviderID
    FROM dbo_ApptPending
    WHERE (((dbo_ApptPending.PendingSource)=0) AND
    ((dbo_ApptPending.ApptTypeID)=6 Or (dbo_ApptPending.ApptTypeID)=16)) OR
    (((dbo_ApptPending.ApptNote) Like "*implant*"))
    GROUP BY dbo_ApptPending.ApptPendingID, dbo_ApptPending.OfficeID,
    dbo_ApptPending.ProviderID;

    SQL View from Excel Query Builder that works except for ApptNote portion

    SELECT ApptPending.CreateDate, ApptPending.ApptPendingID,
    ApptPending.OfficeID, ApptPending.ProviderID
    FROM DWare.dbo.ApptPending ApptPending
    WHERE (ApptPending.PendingSource=0) AND (ApptPending.ApptTypeID=6) OR
    (ApptPending.ApptTypeID=16)
    GROUP BY ApptPending.CreateDate, ApptPending.ApptPendingID,
    ApptPending.OfficeID, ApptPending.ProviderID
     
  2. Loading...


  3. Sylvain Lafontaine

    Sylvain Lafontaine
    Expand Collapse
    Guest

    I don't know the query builder in Excel so I'm not sure of the problem here.
    However, the real string separator in SQL-Server is the single quote ' and
    not the double quote ". With the right configuration parameter, you can use
    the double quote as the string separator on SQL-Server but many query engine
    won't support it. Maybe this is your problem.

    Also, for SQL-Server, you should replace the * with % for your LIKE
    parameters.

    Also, the order of parenthesis around the AND and the OR are not identical
    on your two examples.

    If you are using SQL-Server 2000 but you don't have access to its client
    tools like SQL Manager, a good idea would be to use an Access ADP project
    instead of a MDB file or the Excel query builder for creating your views on
    the SQL-Server.

    --
    Sylvain Lafontaine, ing.
    MVP - Technologies Virtual-PC
    E-mail: http://cerbermail.com/?QugbLEWINF


    "Ian" <Ian@discussions.microsoft.com> wrote in message
    news:87583337-7654-4D40-A96F-16E39EBEE16F@microsoft.com...
    > Need some help with SQL view. I can't write sql queries and I have a
    > spreadsheet that is linked to our sql db. So what I do is design the
    > queries
    > in access with linked tables then choose sql view and plunk it into the
    > query
    > builder in excel to link the pivot table to the sql db. I've run into a
    > problem with an IsLike statement -- the query bulder won't let me put the
    > field 'apptnote' (see below) in and create the islike clause. I'm not
    > sure
    > how it's supposed to be written. I've attached the sql code form access
    > and
    > the one from the query builder. The query is supposed to return any
    > records
    > where the 'apptnote' contains the word "implant" OR the 'appttype' is 6 OR
    > the 'apptype' is 16 (in all cases the pendingsource=0). Ignore the FROM
    > statement because the datasource is slighly different (linked table in
    > first,
    > SQL db in second). Thanks for any help.
    >
    > SQL View from Access for Query that works:
    >
    > SELECT dbo_ApptPending.ApptPendingID, dbo_ApptPending.OfficeID,
    > dbo_ApptPending.ProviderID
    > FROM dbo_ApptPending
    > WHERE (((dbo_ApptPending.PendingSource)=0) AND
    > ((dbo_ApptPending.ApptTypeID)=6 Or (dbo_ApptPending.ApptTypeID)=16)) OR
    > (((dbo_ApptPending.ApptNote) Like "*implant*"))
    > GROUP BY dbo_ApptPending.ApptPendingID, dbo_ApptPending.OfficeID,
    > dbo_ApptPending.ProviderID;
    >
    > SQL View from Excel Query Builder that works except for ApptNote portion
    >
    > SELECT ApptPending.CreateDate, ApptPending.ApptPendingID,
    > ApptPending.OfficeID, ApptPending.ProviderID
    > FROM DWare.dbo.ApptPending ApptPending
    > WHERE (ApptPending.PendingSource=0) AND (ApptPending.ApptTypeID=6) OR
    > (ApptPending.ApptTypeID=16)
    > GROUP BY ApptPending.CreateDate, ApptPending.ApptPendingID,
    > ApptPending.OfficeID, ApptPending.ProviderID
    >
     
  4. xingxing

    xingxing
    Expand Collapse
    Guest

    ŬÁ¦µã°É!
    "Ian" <Ian@discussions.microsoft.com> дÈëÓʼþ
    news:87583337-7654-4D40-A96F-16E39EBEE16F@microsoft.com...
    > Need some help with SQL view. I can't write sql queries and I have a
    > spreadsheet that is linked to our sql db. So what I do is design the

    queries
    > in access with linked tables then choose sql view and plunk it into the

    query
    > builder in excel to link the pivot table to the sql db. I've run into a
    > problem with an IsLike statement -- the query bulder won't let me put the
    > field 'apptnote' (see below) in and create the islike clause. I'm not

    sure
    > how it's supposed to be written. I've attached the sql code form access

    and
    > the one from the query builder. The query is supposed to return any

    records
    > where the 'apptnote' contains the word "implant" OR the 'appttype' is 6 OR
    > the 'apptype' is 16 (in all cases the pendingsource=0). Ignore the FROM
    > statement because the datasource is slighly different (linked table in

    first,
    > SQL db in second). Thanks for any help.
    >
    > SQL View from Access for Query that works:
    >
    > SELECT dbo_ApptPending.ApptPendingID, dbo_ApptPending.OfficeID,
    > dbo_ApptPending.ProviderID
    > FROM dbo_ApptPending
    > WHERE (((dbo_ApptPending.PendingSource)=0) AND
    > ((dbo_ApptPending.ApptTypeID)=6 Or (dbo_ApptPending.ApptTypeID)=16)) OR
    > (((dbo_ApptPending.ApptNote) Like "*implant*"))
    > GROUP BY dbo_ApptPending.ApptPendingID, dbo_ApptPending.OfficeID,
    > dbo_ApptPending.ProviderID;
    >
    > SQL View from Excel Query Builder that works except for ApptNote portion
    >
    > SELECT ApptPending.CreateDate, ApptPending.ApptPendingID,
    > ApptPending.OfficeID, ApptPending.ProviderID
    > FROM DWare.dbo.ApptPending ApptPending
    > WHERE (ApptPending.PendingSource=0) AND (ApptPending.ApptTypeID=6) OR
    > (ApptPending.ApptTypeID=16)
    > GROUP BY ApptPending.CreateDate, ApptPending.ApptPendingID,
    > ApptPending.OfficeID, ApptPending.ProviderID
    >
     

Share This Page