View Single Post
  #5  
Old May 27th, 2010, 10:44 PM posted to microsoft.public.access.queries
bicyclops
external usenet poster
 
Posts: 29
Default aggregate calculation works in select query but not an update

QryBOMDetails2 is a biggie. But it's also attached to a form & normally
updates with no problem. Sql is below.

SELECT TblBOMDetails.LineItem, TblBOMDetails.BOMDetailID,
TblBOMDetails.PartNumID, TblBOMDetails.ChildBOMID, [PartPrefix] & "-" &
Format([Part#Suffix],"0000") AS [ItemPart#], TblBOMDetails.ParentBOMID,
TblBOMDetails.Qty, TblBOMDetails.OverBuyQty, TblBOMDetails.Refdes,
TblComps.Description, TblPartPrefix.[Prefix Description],
TblBOMDetails.MfrPNID, TblMFRPN.MFRPNPart, TblMFR.MFRMfrName,
TblMFRPN.MFRDesc, TblMFRPN.Price AS MfrBasePrice, TblBOMDetails.VendorPNID,
TblVendorPN.VendorID, TblVendor.SUSupplier, TblVendorPN.VendorPN,
TblBOMDetails.CustPNID, TblCompanies.CompanyName, TblCustPN.CustPN,
TblBOMDetails.SourceFromVendor, TblBOMDetails.VendorSourceQty,
TblBOMDetails.SourceFromCust, TblBOMDetails.CustSourceQty,
TblBOMDetails.SourceFromMfr, TblBOMDetails.MfrSourceQty,
TblBOMDetails.SourceFromStock, TblBOMDetails.StockSourceQty,
TblComps.StockQty, TblBOM.BuildQty,
(Nz([BuildQty],1)*[Qty])+(Nz([OverBuyQty],0)) AS ExtQty, TblComps.UnitsID,
TblUnits.BuyAs, TblBOMDetails.Price AS BOMPrice, TblBOMDetails.MfrPNTemp,
TblVendor.SUID,
IIf([ExtQty](Nz([VendorSourceQty],0))+(Nz([CustSourceQty],0))+(Nz([MfrSourceQty],0))+(Nz([StockSourceQty],0)),1,0) AS QtyMismatchFlag
FROM TblVendor RIGHT JOIN (TblVendorPN RIGHT JOIN (TblUnits RIGHT JOIN
(TblPartPrefix RIGHT JOIN (TblMFR RIGHT JOIN ((TblCompanies RIGHT JOIN
TblCustPN ON TblCompanies.CompanyID = TblCustPN.CustID) RIGHT JOIN (TblBOM
RIGHT JOIN ((TblComps RIGHT JOIN TblBOMDetails ON TblComps.[Part#ID] =
TblBOMDetails.PartNumID) LEFT JOIN TblMFRPN ON TblBOMDetails.MfrPNID =
TblMFRPN.MFRPNID) ON TblBOM.BOMID = TblBOMDetails.ParentBOMID) ON
TblCustPN.CustPNID = TblBOMDetails.CustPNID) ON TblMFR.MFRID =
TblMFRPN.MFRPNMFRID) ON TblPartPrefix.PartPrefixID = TblComps.PartPrefixID)
ON TblUnits.ID = TblComps.UnitsID) ON TblVendorPN.VendorPNID =
TblBOMDetails.VendorPNID) ON TblVendor.SUID = TblVendorPN.VendorID
ORDER BY TblBOMDetails.LineItem;


"Jerry Whittle" wrote:

QryBOMDetails2


We probably need to see the sql for it. There's many reasons why a query
isn't updateable.

BTW: Check out my signature line!
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"bicyclops" wrote:

The below query works well as a Select Query. But when I turn it into an
update query, I get a whole lot of nothing in the results.

Interestingly, if I try to call this query from a Macro on my form, I get
error (-20324) repeated the number of times that there are records in the
query.

It was designed to go into a table of price breaks & decide whether the
quantities being ordered meet the price breaks. It then takes either that
adjusted price, or the original price & populate the values into a table.

Select Query:
SELECT QryBOMDetails2.ParentBOMID, QryBOMDetails2.BOMPrice,
IIf(IsNull(DMin("[Price]","TblMfrPriceBreak","[MfrPNID]=" & [MfrPNID] & "And
[Level]=" &
[ExtQty])),[mfrbaseprice],DMin("[Price]","TblMfrPriceBreak","[MfrPNID]=" &
[MfrPNID] & "And [Level]=" & [ExtQty])) AS q
FROM QryBOMDetails2
WHERE (((QryBOMDetails2.ParentBOMID)=[Forms]![FrmBOMEntry]![TxtBOMID]) AND
((QryBOMDetails2.BOMPrice) Is Null));

Update Query:
UPDATE QryBOMDetails2 SET QryBOMDetails2.BOMPrice =
IIf(IsNull(DMin("[Price]","TblMfrPriceBreak","[MfrPNID]=" & [MfrPNID] & "And
[Level]=" &
[ExtQty])),[mfrbaseprice],DMin("[Price]","TblMfrPriceBreak","[MfrPNID]=" &
[MfrPNID] & "And [Level]=" & [ExtQty]))
WHERE (((QryBOMDetails2.BOMPrice) Is Null) AND
((QryBOMDetails2.ParentBOMID)=[Forms]![FrmBOMEntry]![TxtBOMID]));

FYI I'm doing this using the query builder.
Thanks in advance for any advice.