Help with SQL view

G

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
 
S

Sylvain Lafontaine

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.
 
S

Sylvain Lafontaine

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Help with SQL Statement for pivot table 2
SQL View Help 2
Help with INSERT query 1
TRIGGER a VIEW with SQL for a LINKED SERVER (ACCESS DB) 6
Need help with update sql plus filter 3
Access to SQL - data differences when importing? 7
Help w/ SQL View and Form Reference 5
HELP!! can I creat a database in a query in SQL view? 1

Top