Prev: How do I create a select YTD query that totals monthly summaries?
Next: Auto-populate form field
From: mcnews on 24 Jun 2008 17:30
UPDATE tblFollowUpData INNER JOIN vwPassenger ON tblFollowUpData.PLFId
= vwPassenger.PLFId SET tblFollowUpData.qstation = (SELECT qstation
FROM vwPassenger
WHERE Not isnull(vwPassenger.ConveyanceID) AND vwPassenger.PLFId =
tblFollowUpData.PLFId
AND (IsNull(tblFollowUpData.qstation) and Not
IsNull(vwPassenger.qstation)));


i get a 'Operation must use an updatebale query' error.

tia,
mcnewsxp
From: John Spencer on 24 Jun 2008 20:00
Would the following work for you?


UPDATE tblFollowUpData INNER JOIN vwPassenger
ON tblFollowUpData.PLFId= vwPassenger.PLFId
SET tblFollowUpData.qstation = vwPassenger.qStation

WHERE vwPassenger.ConveyanceID Is NOT Null
AND tblFollowUPDate.QStation is Null
AND vwPassenger.qStation is Not Null

Your query has the problem that the Subquery COULD return more than one
record and Access knows that and refuses to use the subquery.

Your other option would be to use the DLOOKUP function to return a value
(or one of the other VBA domain functions)



'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================


mcnews wrote:
> UPDATE tblFollowUpData INNER JOIN vwPassenger ON tblFollowUpData.PLFId
> = vwPassenger.PLFId SET tblFollowUpData.qstation = (SELECT qstation
> FROM vwPassenger
> WHERE Not isnull(vwPassenger.ConveyanceID) AND vwPassenger.PLFId =
> tblFollowUpData.PLFId
> AND (IsNull(tblFollowUpData.qstation) and Not
> IsNull(vwPassenger.qstation)));
>
>
> i get a 'Operation must use an updatebale query' error.
>
> tia,
> mcnewsxp
From: mcnews on 25 Jun 2008 07:18


John Spencer wrote:
> Would the following work for you?
>
>
> UPDATE tblFollowUpData INNER JOIN vwPassenger
> ON tblFollowUpData.PLFId= vwPassenger.PLFId
> SET tblFollowUpData.qstation = vwPassenger.qStation
>
> WHERE vwPassenger.ConveyanceID Is NOT Null
> AND tblFollowUPDate.QStation is Null
> AND vwPassenger.qStation is Not Null
>
> Your query has the problem that the Subquery COULD return more than one
> record and Access knows that and refuses to use the subquery.
>
> Your other option would be to use the DLOOKUP function to return a value
> (or one of the other VBA domain functions)
>
>
i get the same error.
i'd prefer not to use the dlookup because i may need to make thisa
stored proc on the sql server.
thanks much, tho.


> mcnews wrote:
> > UPDATE tblFollowUpData INNER JOIN vwPassenger ON tblFollowUpData.PLFId
> > = vwPassenger.PLFId SET tblFollowUpData.qstation = (SELECT qstation
> > FROM vwPassenger
> > WHERE Not isnull(vwPassenger.ConveyanceID) AND vwPassenger.PLFId =
> > tblFollowUpData.PLFId
> > AND (IsNull(tblFollowUpData.qstation) and Not
> > IsNull(vwPassenger.qstation)));
> >
> >
> > i get a 'Operation must use an updatebale query' error.
> >
> > tia,
> > mcnewsxp
From: John Spencer on 25 Jun 2008 08:10
Is vwPassenger a query or a view on the SQL server?

If it is a query, then post the SQL for it. If it is a view, then you
may need to find a workaround - such as a stored proc on the SQL server.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================


mcnews wrote:
>
> John Spencer wrote:
>> Would the following work for you?
>>
>>
>> UPDATE tblFollowUpData INNER JOIN vwPassenger
>> ON tblFollowUpData.PLFId= vwPassenger.PLFId
>> SET tblFollowUpData.qstation = vwPassenger.qStation
>>
>> WHERE vwPassenger.ConveyanceID Is NOT Null
>> AND tblFollowUPDate.QStation is Null
>> AND vwPassenger.qStation is Not Null
>>
>> Your query has the problem that the Subquery COULD return more than one
>> record and Access knows that and refuses to use the subquery.
>>
>> Your other option would be to use the DLOOKUP function to return a value
>> (or one of the other VBA domain functions)
>>
>>
> i get the same error.
> i'd prefer not to use the dlookup because i may need to make thisa
> stored proc on the sql server.
> thanks much, tho.
From: mcnews on 25 Jun 2008 12:28
On Jun 25, 8:10 am, John Spencer <spen...(a)chpdm.umbc> wrote:
> Is vwPassenger a query or a view on the SQL server?
>
> If it is a query, then post the SQL for it. If it is a view, then you
> may need to find a workaround - such as a stored proc on the SQL server.
>
> '====================================================
> John Spencer
> Access MVP 2002-2005, 2007-2008
> The Hilltop Institute
> University of Maryland Baltimore County
> '====================================================
>
> mcnews wrote:
>
> > John Spencer wrote:
> >> Would the following work for you?
>
> >> UPDATE tblFollowUpData INNER JOIN vwPassenger
> >> ON tblFollowUpData.PLFId= vwPassenger.PLFId
> >> SET tblFollowUpData.qstation = vwPassenger.qStation
>
> >> WHERE vwPassenger.ConveyanceID Is NOT Null
> >> AND tblFollowUPDate.QStation is Null
> >> AND vwPassenger.qStation is Not Null
>
> >> Your query has the problem that the Subquery COULD return more than one
> >> record and Access knows that and refuses to use the subquery.
>
> >> Your other option would be to use the DLOOKUP function to return a value
> >> (or one of the other VBA domain functions)
>
> > i get the same error.
> > i'd prefer not to use the dlookup because i may need to make thisa
> > stored proc on the sql server.
> > thanks much, tho.

view.
must be the problem.
i wrote some code to do it.
thanks again.
 | 
Pages: 1
Prev: How do I create a select YTD query that totals monthly summaries?
Next: Auto-populate form field