| re: Need help designing an update query
tbone wrote:[color=blue]
> I'd like to have a way to add bidders who have won at least $1000 in
> the last auction to my mailing list. I'm having trouble with the
> update step.
>
> To find bidders who have won at least $1000 in the last auction, I
> created this query:
>
> SELECT Sum(Lots.HighBidAmount) AS SumOfHighBidAmount,
> Lots.HighBidderNumber
> FROM Lots, [Active auctions]
> GROUP BY Lots.HighBidderNumber, Lots.AuctionID
> HAVING (((Sum(Lots.HighBidAmount))>=1000) AND
> ((Lots.AuctionID)=First([Active auctions].[AuctionID])));
>
> [Active auctions] is a query that returns a single row identifying the
> active auction. The above query, which I named "Bidders winning $1000
> minimum" appears to return the correct results.
>
> Next, I figured I'd use the bidder numbers identified in the above
> query to select the bidder records to have the mail flag set. I tried
> this:
>
> UPDATE [Bidders winning $1000 minimum]
> INNER JOIN Bidders
> ON [Bidders winning $1000 minimum].HighBidderNumber = Bidders.BidderID
> SET Bidders.MailFlag = -1;
>
> I get this error: "Operation must use an updateable query."
>
> I also tried phrasing it the other way around:
>
> UPDATE Bidders
> INNER JOIN [Bidders winning $1000 minimum]
> ON Bidders.BidderID = [Bidders winning $1000 minimum].HighBidderNumber
> SET Bidders.MailFlag = -1;
>
> No luck. I also tried LEFT and RIGHT JOINs, to no avail.
>
> Can someone point me in the right direction?
> Thanks
> tbone[/color]
Can use use something else besides a Totals query? If you use a Totals
query, it is not updateable. Perhaps use Dsum()?
| 
