Welcome to SPN

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

Sign Up Now!
  1. Guest ji, please consider donating today!   Become a Supporter    ::   Make a Contribution   
    Monthly (Recurring) Target: $300 :: Achieved: $95

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
    >
     
Since you're here... we have a small favor to ask...

More people are visiting & reading SPN than ever but far fewer are paying to sustain it. Advertising revenues across the online media have fallen fast. So you can see why we need to ask for your help. Donating to SPN's is vote for free speech, for diversity of opinions, for the right of the people to stand up to religious bigotry. Without any affiliation to any organization, this constant struggle takes a lot of hard work to sustain as we entirely depend on the contributions of our esteemed writers/readers. We do it because we believe our perspective matters – because it might well be your perspective, too... Fund our efforts and together we can keep the world informed about the real Sikh Sikhi Sikhism. If everyone who writes or reads our content, who likes it, helps us to pay for it, our future would be much more secure. Every Contribution Matters, Contribute Generously!

    Become a Supporter      ::     Make a Contribution     



Share This Page