recordset not updateable 3

[simon551]

I can't figure out why the recordset for qryPriceMissing is not updateable. The query qryBalShares is updateable. I appreciate your help if you can.

qryPriceMissing
SELECT B.SecID, B.MgrID, B.Tckr, B.Description, Sum(B.BalShares) AS SumOfBalShares, P.RDate, P.Price
FROM qryBalShares AS B LEFT JOIN [SELECT * FROM tblPrices WHERE RDate=Forms!frmFMVRecon!txtDate]. AS P ON B.SecID = P.SecID
GROUP BY B.SecID, B.MgrID, B.Tckr, B.Description, P.RDate, P.Price, P.SecID
HAVING (((B.MgrID)=[Forms]![frmFMVRecon]![txtMgrID]) AND ((P.SecID) Is Null))
ORDER BY B.MgrID, B.Tckr;

qryBalShares
SELECT Sec.SecID, Sec.Tckr,Sec.Description,I.LotID,I.NumShares,S.NumSold,[NumShares]-Nz([NumSold],0) AS BalShares
FROM tblSecurities AS Sec INNER JOIN (tblInventory AS I LEFT JOIN tblSales AS S ON I.LotID = S.LotID) ON Sec.SecID = I.SecID
WHERE ((([NumShares]-Nz([NumSold],0))>0));

 

[fredericofonseca]

I would say it is because one is dealing with individual records and the other is dealing with a "GROUP" BY.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[jebry]

Hi!

In general summary queries are not updateable since, by definition, each line in the query results refers to multiple lines in the underlying data sources. So Access will have no way of knowing what to update.

hth


Jeff Bridgham
Purdue University
Graduate School
Data Analyst

 

[simon551]

I tried taking the grouping out but it's still not updateable. The update needs to happen on SecID for tblPrices and tblSecurities (primary key). SecID can occur many places in qryBalShares. The qryBalShares is kind of an intermediary to get to the SecID that needs to have price updated. You might be saying that this is not possible which would be unfortunate.
Thanks.

 

[fredericofonseca]

what is your changed SQL (without the group by).

And do you really need an updateable recordset or is this something you can do with a straight UPDATE sql statemet?

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[PHV]

Have a look here:

http://msdn.microsoft.com/archive/en-us/dnaraccess/html/msdn_harness.asp

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[simon551]

SELECT B.SecID, B.MgrID, B.Tckr, B.Description, B.BalShares, P.RDate, P.Price
FROM qryBalShares AS B LEFT JOIN [SELECT * FROM tblPrices WHERE RDate=Forms!frmFMVRecon!txtDate]. AS P ON B.SecID = P.SecID
WHERE (((B.MgrID)=[Forms]![frmFMVRecon]![txtMgrID]) AND ((P.SecID) Is Null))
ORDER BY B.MgrID, B.Tckr;


This is not updateable. Even if it were, it wouldn't work for what I'm trying to do.
I have a stock inventory db. I import prices and then I go back to check for stocks that have a balance but no price and then I want to be able to update the price. There should only be 1 price for 1 date for 1 secID. Another way I could do this is to do an update query for, say, "inactive" (where balshares=0) but I am thinking I should be able to avoid that. I don't really need the balShares query to be updateable. I was just noting that, so to give details.

 

[simon551]

Thanks PHV. You've been a great help and I probably wouldn't even be thinking of doing this if you hadn't helped me get this far. I'll read that.

 

[simon551]

Can I do something like this:
SELECT Sec.SecID, P.RDate, P.Price
FROM (tblSecurities AS Sec LEFT JOIN (SELECT * FROM tblPrices WHERE RDate=Forms!frmFMVRecon!txtDate) AS P ON Sec.SecID = P.SecID) WHERE (SELECT BalShares FROM qryBalShares WHERE BalShares>0) ON Sec.SecID = qryBalShares.SecID
WHERE (((P.SecID) Is Null));