(Msg. 1) Posted: Wed May 16, 2007 2:39 pm
Post subject: Excel SQL syntax question Archived from groups: microsoft>public>excel>querydao (more info?)
Hello all,
I am querying data from a SQL server and would like the query to
reference data in the spreadsheet. I'm not sure how to compose the SQL
query. Here is what I have so far which, of course, does not work. I get
syntax errors.
Select Count(ReferralNumber)
From Conversion
Where YEAR(ReferralDate) = 'Sheet1'!$B$1
(Msg. 2) Posted: Wed May 16, 2007 6:02 pm
Post subject: Re: Excel SQL syntax question [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
nick_hodgeTAKETHISOUT.DeleteThis@zen.co.ukANDTHIS
web: www.nickhodge.co.uk
blog (non-tech): www.nickhodge.co.uk/blog/
"Don S" <NoSpamForMe.DeleteThis@either.com> wrote in message
news:u9qd$l%23lHHA.960@TK2MSFTNGP03.phx.gbl...
> Hello all,
> I am querying data from a SQL server and would like the query to
> reference data in the spreadsheet. I'm not sure how to compose the SQL
> query. Here is what I have so far which, of course, does not work. I get
> syntax errors.
>
> Select Count(ReferralNumber)
> From Conversion
> Where YEAR(ReferralDate) = 'Sheet1'!$B$1
>
(Msg. 3) Posted: Mon May 21, 2007 1:14 pm
Post subject: Re: Excel SQL syntax question [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
Thanks a million Nick!
Do you know how to get MS Query editor to stop reformatting my queries. I
have a 3-parameter query. It gets reformatted so that I have to enter the
value 12+ times for EACH parameter. It changes this:
elect Count(*)
From Conversion
Where Year(ReferralDate) = ?
and Month(ReferralDate) = ?
and Approachedby = ?
and (ApproachSV = 2
or ApproachFV = 2
or ApproachSkin = 2
or ApproachUB = 2
or ApproachLB = 2
or ApproachMand = 2)
AND (DonorReg = 'Yes' or DonorReg = 'Yes, form on file')
. . . to this:
SELECT Count(*)
FROM Compstat.dbo.Conversion Conversion
WHERE (Conversion.ApproachSV=2) AND (Year(ReferralDate)=?) AND
(Month(ReferralDate)=?) AND (Conversion.ApproachedBy=?) AND
(Conversion.DonorReg='Yes') OR (Year(ReferralDate)=?) AND
(Month(ReferralDate)=?) AND (Conversion.ApproachedBy=?) AND
(Conversion.DonorReg='Yes') AND (Conversion.ApproachFV=2) OR
(Year(ReferralDate)=?) AND (Month(ReferralDate)=?) AND
(Conversion.ApproachedBy=?) AND (Conversion.DonorReg='Yes') AND
(Conversion.ApproachSkin=2) OR (Year(ReferralDate)=?) AND
(Month(ReferralDate)=?) AND (Conversion.ApproachedBy=?) AND
(Conversion.DonorReg='Yes') AND (Conversion.ApproachUB=2) OR
(Year(ReferralDate)=?) AND (Month(ReferralDate)=?) AND
(Conversion.ApproachedBy=?) AND (Conversion.DonorReg='Yes') AND
(Conversion.ApproachLB=2) OR (Year(ReferralDate)=?) AND
(Month(ReferralDate)=?) AND (Conversion.ApproachedBy=?) AND
(Conversion.DonorReg='Yes') AND (Conversion.ApproachMand=2) OR
(Conversion.ApproachSV=2) AND (Year(ReferralDate)=?) AND
(Month(ReferralDate)=?) AND (Conversion.ApproachedBy=?) AND
(Conversion.DonorReg='Yes, form on file') OR (Year(ReferralDate)=?) AND
(Month(ReferralDate)=?) AND (Conversion.ApproachedBy=?) AND
(Conversion.DonorReg='Yes, form on file') AND (Conversion.ApproachFV=2) OR
(Year(ReferralDate)=?) AND (Month(ReferralDate)=?) AND
(Conversion.ApproachedBy=?) AND (Conversion.DonorReg='Yes, form on file')
AND (Conversion.ApproachSkin=2) OR (Year(ReferralDate)=?) AND
(Month(ReferralDate)=?) AND (Conversion.ApproachedBy=?) AND
(Conversion.DonorReg='Yes, form on file') AND (Conversion.ApproachUB=2) OR
(Year(ReferralDate)=?) AND (Month(ReferralDate)=?) AND
(Conversion.ApproachedBy=?) AND (Conversion.DonorReg='Yes, form on file')
AND (Conversion.ApproachLB=2) OR (Year(ReferralDate)=?) AND
(Month(ReferralDate)=?) AND (Conversion.ApproachedBy=?) AND
(Conversion.DonorReg='Yes, form on file') AND (Conversion.ApproachMand=2)
"Nick Hodge" <nick_hodgeTAKETHISOUT.TakeThisOut@zen.co.uk.ANDTHIS> wrote in message
news:63EA0E03-0BA3-4A71-9ECF-BE6327899DEB@microsoft.com...
> Don
>
> Check here
>
> http://www.nickhodge.co.uk/gui/datamenu/dataexamples/externaldataexamples.htm >
> (Under using parameters.......)
>
> --
> HTH
> Nick Hodge
> Microsoft MVP - Excel
> Southampton, England
> nick_hodgeTAKETHISOUT.TakeThisOut@zen.co.ukANDTHIS
> web: www.nickhodge.co.uk > blog (non-tech): www.nickhodge.co.uk/blog/ > "Don S" <NoSpamForMe.TakeThisOut@either.com> wrote in message
> news:u9qd$l%23lHHA.960@TK2MSFTNGP03.phx.gbl...
>> Hello all,
>> I am querying data from a SQL server and would like the query to
>> reference data in the spreadsheet. I'm not sure how to compose the SQL
>> query. Here is what I have so far which, of course, does not work. I
>> get syntax errors.
>>
>> Select Count(ReferralNumber)
>> From Conversion
>> Where YEAR(ReferralDate) = 'Sheet1'!$B$1
>>
>
(Msg. 4) Posted: Mon May 21, 2007 7:02 pm
Post subject: Re: Excel SQL syntax question [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
Don
Wow, even if you hand-type the query rather then building it in the grid?
--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
nick_hodgeTAKETHISOUT.RemoveThis@zen.co.ukANDTHIS
web: www.nickhodge.co.uk
blog (non-tech): www.nickhodge.co.uk/blog/
"Don S" <NoSpamForMe.RemoveThis@either.com> wrote in message
news:uWGORu8mHHA.3888@TK2MSFTNGP03.phx.gbl...
> Thanks a million Nick!
> Do you know how to get MS Query editor to stop reformatting my queries. I
> have a 3-parameter query. It gets reformatted so that I have to enter the
> value 12+ times for EACH parameter. It changes this:
>
> elect Count(*)
> From Conversion
> Where Year(ReferralDate) = ?
> and Month(ReferralDate) = ?
> and Approachedby = ?
> and (ApproachSV = 2
> or ApproachFV = 2
> or ApproachSkin = 2
> or ApproachUB = 2
> or ApproachLB = 2
> or ApproachMand = 2)
> AND (DonorReg = 'Yes' or DonorReg = 'Yes, form on file')
>
> . . . to this:
>
> SELECT Count(*)
> FROM Compstat.dbo.Conversion Conversion
> WHERE (Conversion.ApproachSV=2) AND (Year(ReferralDate)=?) AND
> (Month(ReferralDate)=?) AND (Conversion.ApproachedBy=?) AND
> (Conversion.DonorReg='Yes') OR (Year(ReferralDate)=?) AND
> (Month(ReferralDate)=?) AND (Conversion.ApproachedBy=?) AND
> (Conversion.DonorReg='Yes') AND (Conversion.ApproachFV=2) OR
> (Year(ReferralDate)=?) AND (Month(ReferralDate)=?) AND
> (Conversion.ApproachedBy=?) AND (Conversion.DonorReg='Yes') AND
> (Conversion.ApproachSkin=2) OR (Year(ReferralDate)=?) AND
> (Month(ReferralDate)=?) AND (Conversion.ApproachedBy=?) AND
> (Conversion.DonorReg='Yes') AND (Conversion.ApproachUB=2) OR
> (Year(ReferralDate)=?) AND (Month(ReferralDate)=?) AND
> (Conversion.ApproachedBy=?) AND (Conversion.DonorReg='Yes') AND
> (Conversion.ApproachLB=2) OR (Year(ReferralDate)=?) AND
> (Month(ReferralDate)=?) AND (Conversion.ApproachedBy=?) AND
> (Conversion.DonorReg='Yes') AND (Conversion.ApproachMand=2) OR
> (Conversion.ApproachSV=2) AND (Year(ReferralDate)=?) AND
> (Month(ReferralDate)=?) AND (Conversion.ApproachedBy=?) AND
> (Conversion.DonorReg='Yes, form on file') OR (Year(ReferralDate)=?) AND
> (Month(ReferralDate)=?) AND (Conversion.ApproachedBy=?) AND
> (Conversion.DonorReg='Yes, form on file') AND (Conversion.ApproachFV=2) OR
> (Year(ReferralDate)=?) AND (Month(ReferralDate)=?) AND
> (Conversion.ApproachedBy=?) AND (Conversion.DonorReg='Yes, form on file')
> AND (Conversion.ApproachSkin=2) OR (Year(ReferralDate)=?) AND
> (Month(ReferralDate)=?) AND (Conversion.ApproachedBy=?) AND
> (Conversion.DonorReg='Yes, form on file') AND (Conversion.ApproachUB=2) OR
> (Year(ReferralDate)=?) AND (Month(ReferralDate)=?) AND
> (Conversion.ApproachedBy=?) AND (Conversion.DonorReg='Yes, form on file')
> AND (Conversion.ApproachLB=2) OR (Year(ReferralDate)=?) AND
> (Month(ReferralDate)=?) AND (Conversion.ApproachedBy=?) AND
> (Conversion.DonorReg='Yes, form on file') AND (Conversion.ApproachMand=2)
>
>
>
> "Nick Hodge" <nick_hodgeTAKETHISOUT.RemoveThis@zen.co.uk.ANDTHIS> wrote in message
> news:63EA0E03-0BA3-4A71-9ECF-BE6327899DEB@microsoft.com...
>> Don
>>
>> Check here
>>
>> http://www.nickhodge.co.uk/gui/datamenu/dataexamples/externaldataexamples.htm >>
>> (Under using parameters.......)
>>
>> --
>> HTH
>> Nick Hodge
>> Microsoft MVP - Excel
>> Southampton, England
>> nick_hodgeTAKETHISOUT.RemoveThis@zen.co.ukANDTHIS
>> web: www.nickhodge.co.uk >> blog (non-tech): www.nickhodge.co.uk/blog/ >> "Don S" <NoSpamForMe.RemoveThis@either.com> wrote in message
>> news:u9qd$l%23lHHA.960@TK2MSFTNGP03.phx.gbl...
>>> Hello all,
>>> I am querying data from a SQL server and would like the query to
>>> reference data in the spreadsheet. I'm not sure how to compose the SQL
>>> query. Here is what I have so far which, of course, does not work. I
>>> get syntax errors.
>>>
>>> Select Count(ReferralNumber)
>>> From Conversion
>>> Where YEAR(ReferralDate) = 'Sheet1'!$B$1
>>>
>>
>
>
(Msg. 5) Posted: Tue May 22, 2007 10:55 am
Post subject: Re: Excel SQL syntax question [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
Yes. I seldom use the grid anymore. I'd REALLY love to turn this "feature"
off.
Don.
"Nick Hodge" <nick_hodgeTAKETHISOUT.RemoveThis@zen.co.uk.ANDTHIS> wrote in message
news:6ACEE03F-D175-404E-A61C-ADDD73CA8FB7@microsoft.com...
> Don
>
> Wow, even if you hand-type the query rather then building it in the grid?
>
> --
> HTH
> Nick Hodge
> Microsoft MVP - Excel
> Southampton, England
> nick_hodgeTAKETHISOUT.RemoveThis@zen.co.ukANDTHIS
> web: www.nickhodge.co.uk > blog (non-tech): www.nickhodge.co.uk/blog/ > "Don S" <NoSpamForMe.RemoveThis@either.com> wrote in message
> news:uWGORu8mHHA.3888@TK2MSFTNGP03.phx.gbl...
>> Thanks a million Nick!
>> Do you know how to get MS Query editor to stop reformatting my queries.
>> I have a 3-parameter query. It gets reformatted so that I have to enter
>> the value 12+ times for EACH parameter. It changes this:
>>
>> elect Count(*)
>> From Conversion
>> Where Year(ReferralDate) = ?
>> and Month(ReferralDate) = ?
>> and Approachedby = ?
>> and (ApproachSV = 2
>> or ApproachFV = 2
>> or ApproachSkin = 2
>> or ApproachUB = 2
>> or ApproachLB = 2
>> or ApproachMand = 2)
>> AND (DonorReg = 'Yes' or DonorReg = 'Yes, form on file')
>>
>> . . . to this:
>>
>> SELECT Count(*)
>> FROM Compstat.dbo.Conversion Conversion
>> WHERE (Conversion.ApproachSV=2) AND (Year(ReferralDate)=?) AND
>> (Month(ReferralDate)=?) AND (Conversion.ApproachedBy=?) AND
>> (Conversion.DonorReg='Yes') OR (Year(ReferralDate)=?) AND
>> (Month(ReferralDate)=?) AND (Conversion.ApproachedBy=?) AND
>> (Conversion.DonorReg='Yes') AND (Conversion.ApproachFV=2) OR
>> (Year(ReferralDate)=?) AND (Month(ReferralDate)=?) AND
>> (Conversion.ApproachedBy=?) AND (Conversion.DonorReg='Yes') AND
>> (Conversion.ApproachSkin=2) OR (Year(ReferralDate)=?) AND
>> (Month(ReferralDate)=?) AND (Conversion.ApproachedBy=?) AND
>> (Conversion.DonorReg='Yes') AND (Conversion.ApproachUB=2) OR
>> (Year(ReferralDate)=?) AND (Month(ReferralDate)=?) AND
>> (Conversion.ApproachedBy=?) AND (Conversion.DonorReg='Yes') AND
>> (Conversion.ApproachLB=2) OR (Year(ReferralDate)=?) AND
>> (Month(ReferralDate)=?) AND (Conversion.ApproachedBy=?) AND
>> (Conversion.DonorReg='Yes') AND (Conversion.ApproachMand=2) OR
>> (Conversion.ApproachSV=2) AND (Year(ReferralDate)=?) AND
>> (Month(ReferralDate)=?) AND (Conversion.ApproachedBy=?) AND
>> (Conversion.DonorReg='Yes, form on file') OR (Year(ReferralDate)=?) AND
>> (Month(ReferralDate)=?) AND (Conversion.ApproachedBy=?) AND
>> (Conversion.DonorReg='Yes, form on file') AND (Conversion.ApproachFV=2)
>> OR (Year(ReferralDate)=?) AND (Month(ReferralDate)=?) AND
>> (Conversion.ApproachedBy=?) AND (Conversion.DonorReg='Yes, form on file')
>> AND (Conversion.ApproachSkin=2) OR (Year(ReferralDate)=?) AND
>> (Month(ReferralDate)=?) AND (Conversion.ApproachedBy=?) AND
>> (Conversion.DonorReg='Yes, form on file') AND (Conversion.ApproachUB=2)
>> OR (Year(ReferralDate)=?) AND (Month(ReferralDate)=?) AND
>> (Conversion.ApproachedBy=?) AND (Conversion.DonorReg='Yes, form on file')
>> AND (Conversion.ApproachLB=2) OR (Year(ReferralDate)=?) AND
>> (Month(ReferralDate)=?) AND (Conversion.ApproachedBy=?) AND
>> (Conversion.DonorReg='Yes, form on file') AND (Conversion.ApproachMand=2)
>>
>>
>>
>> "Nick Hodge" <nick_hodgeTAKETHISOUT.RemoveThis@zen.co.uk.ANDTHIS> wrote in message
>> news:63EA0E03-0BA3-4A71-9ECF-BE6327899DEB@microsoft.com...
>>> Don
>>>
>>> Check here
>>>
>>> http://www.nickhodge.co.uk/gui/datamenu/dataexamples/externaldataexamples.htm >>>
>>> (Under using parameters.......)
>>>
>>> --
>>> HTH
>>> Nick Hodge
>>> Microsoft MVP - Excel
>>> Southampton, England
>>> nick_hodgeTAKETHISOUT.RemoveThis@zen.co.ukANDTHIS
>>> web: www.nickhodge.co.uk >>> blog (non-tech): www.nickhodge.co.uk/blog/ >>> "Don S" <NoSpamForMe.RemoveThis@either.com> wrote in message
>>> news:u9qd$l%23lHHA.960@TK2MSFTNGP03.phx.gbl...
>>>> Hello all,
>>>> I am querying data from a SQL server and would like the query to
>>>> reference data in the spreadsheet. I'm not sure how to compose the SQL
>>>> query. Here is what I have so far which, of course, does not work. I
>>>> get syntax errors.
>>>>
>>>> Select Count(ReferralNumber)
>>>> From Conversion
>>>> Where YEAR(ReferralDate) = 'Sheet1'!$B$1
>>>>
>>>
>>
>>
>
(Msg. 6) Posted: Tue May 22, 2007 4:03 pm
Post subject: Re: Excel SQL syntax question [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
Don
Does re-arranging the ANDs and ORs to run in seq make any odds?
--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
nick_hodgeTAKETHISOUT DeleteThis @zen.co.ukANDTHIS
web: www.nickhodge.co.uk
blog (non-tech): www.nickhodge.co.uk/blog/
"Don S" <NoSpamForMe DeleteThis @either.com> wrote in message
news:OHkJQFInHHA.1240@TK2MSFTNGP04.phx.gbl...
> Yes. I seldom use the grid anymore. I'd REALLY love to turn this
> "feature" off.
>
> Don.
>
> "Nick Hodge" <nick_hodgeTAKETHISOUT DeleteThis @zen.co.uk.ANDTHIS> wrote in message
> news:6ACEE03F-D175-404E-A61C-ADDD73CA8FB7@microsoft.com...
>> Don
>>
>> Wow, even if you hand-type the query rather then building it in the grid?
>>
>> --
>> HTH
>> Nick Hodge
>> Microsoft MVP - Excel
>> Southampton, England
>> nick_hodgeTAKETHISOUT DeleteThis @zen.co.ukANDTHIS
>> web: www.nickhodge.co.uk >> blog (non-tech): www.nickhodge.co.uk/blog/ >> "Don S" <NoSpamForMe DeleteThis @either.com> wrote in message
>> news:uWGORu8mHHA.3888@TK2MSFTNGP03.phx.gbl...
>>> Thanks a million Nick!
>>> Do you know how to get MS Query editor to stop reformatting my queries.
>>> I have a 3-parameter query. It gets reformatted so that I have to enter
>>> the value 12+ times for EACH parameter. It changes this:
>>>
>>> elect Count(*)
>>> From Conversion
>>> Where Year(ReferralDate) = ?
>>> and Month(ReferralDate) = ?
>>> and Approachedby = ?
>>> and (ApproachSV = 2
>>> or ApproachFV = 2
>>> or ApproachSkin = 2
>>> or ApproachUB = 2
>>> or ApproachLB = 2
>>> or ApproachMand = 2)
>>> AND (DonorReg = 'Yes' or DonorReg = 'Yes, form on file')
>>>
>>> . . . to this:
>>>
>>> SELECT Count(*)
>>> FROM Compstat.dbo.Conversion Conversion
>>> WHERE (Conversion.ApproachSV=2) AND (Year(ReferralDate)=?) AND
>>> (Month(ReferralDate)=?) AND (Conversion.ApproachedBy=?) AND
>>> (Conversion.DonorReg='Yes') OR (Year(ReferralDate)=?) AND
>>> (Month(ReferralDate)=?) AND (Conversion.ApproachedBy=?) AND
>>> (Conversion.DonorReg='Yes') AND (Conversion.ApproachFV=2) OR
>>> (Year(ReferralDate)=?) AND (Month(ReferralDate)=?) AND
>>> (Conversion.ApproachedBy=?) AND (Conversion.DonorReg='Yes') AND
>>> (Conversion.ApproachSkin=2) OR (Year(ReferralDate)=?) AND
>>> (Month(ReferralDate)=?) AND (Conversion.ApproachedBy=?) AND
>>> (Conversion.DonorReg='Yes') AND (Conversion.ApproachUB=2) OR
>>> (Year(ReferralDate)=?) AND (Month(ReferralDate)=?) AND
>>> (Conversion.ApproachedBy=?) AND (Conversion.DonorReg='Yes') AND
>>> (Conversion.ApproachLB=2) OR (Year(ReferralDate)=?) AND
>>> (Month(ReferralDate)=?) AND (Conversion.ApproachedBy=?) AND
>>> (Conversion.DonorReg='Yes') AND (Conversion.ApproachMand=2) OR
>>> (Conversion.ApproachSV=2) AND (Year(ReferralDate)=?) AND
>>> (Month(ReferralDate)=?) AND (Conversion.ApproachedBy=?) AND
>>> (Conversion.DonorReg='Yes, form on file') OR (Year(ReferralDate)=?) AND
>>> (Month(ReferralDate)=?) AND (Conversion.ApproachedBy=?) AND
>>> (Conversion.DonorReg='Yes, form on file') AND (Conversion.ApproachFV=2)
>>> OR (Year(ReferralDate)=?) AND (Month(ReferralDate)=?) AND
>>> (Conversion.ApproachedBy=?) AND (Conversion.DonorReg='Yes, form on
>>> file') AND (Conversion.ApproachSkin=2) OR (Year(ReferralDate)=?) AND
>>> (Month(ReferralDate)=?) AND (Conversion.ApproachedBy=?) AND
>>> (Conversion.DonorReg='Yes, form on file') AND (Conversion.ApproachUB=2)
>>> OR (Year(ReferralDate)=?) AND (Month(ReferralDate)=?) AND
>>> (Conversion.ApproachedBy=?) AND (Conversion.DonorReg='Yes, form on
>>> file') AND (Conversion.ApproachLB=2) OR (Year(ReferralDate)=?) AND
>>> (Month(ReferralDate)=?) AND (Conversion.ApproachedBy=?) AND
>>> (Conversion.DonorReg='Yes, form on file') AND
>>> (Conversion.ApproachMand=2)
>>>
>>>
>>>
>>> "Nick Hodge" <nick_hodgeTAKETHISOUT DeleteThis @zen.co.uk.ANDTHIS> wrote in message
>>> news:63EA0E03-0BA3-4A71-9ECF-BE6327899DEB@microsoft.com...
>>>> Don
>>>>
>>>> Check here
>>>>
>>>> http://www.nickhodge.co.uk/gui/datamenu/dataexamples/externaldataexamples.htm >>>>
>>>> (Under using parameters.......)
>>>>
>>>> --
>>>> HTH
>>>> Nick Hodge
>>>> Microsoft MVP - Excel
>>>> Southampton, England
>>>> nick_hodgeTAKETHISOUT DeleteThis @zen.co.ukANDTHIS
>>>> web: www.nickhodge.co.uk >>>> blog (non-tech): www.nickhodge.co.uk/blog/ >>>> "Don S" <NoSpamForMe DeleteThis @either.com> wrote in message
>>>> news:u9qd$l%23lHHA.960@TK2MSFTNGP03.phx.gbl...
>>>>> Hello all,
>>>>> I am querying data from a SQL server and would like the query to
>>>>> reference data in the spreadsheet. I'm not sure how to compose the
>>>>> SQL query. Here is what I have so far which, of course, does not
>>>>> work. I get syntax errors.
>>>>>
>>>>> Select Count(ReferralNumber)
>>>>> From Conversion
>>>>> Where YEAR(ReferralDate) = 'Sheet1'!$B$1
>>>>>
>>>>
>>>
>>>
>>
>
>
All times are: Eastern Time (US & Canada) (change)
Page 1 of 1
You can post new topics in this forum You can reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum
Home
Forums
Home
FAQ
Profile
Private Messages
Log in
Office