If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
aggregate calculation works in select query but not an update quer
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. |
#2
|
|||
|
|||
aggregate calculation works in select query but not an update
OK; I guess it's back to the drawing board; thanks.
"Jerry Whittle" wrote: I'm very surprised that the query below is updateable when attached to a form. There's multiple Right and Left Joins and some functions which would keep those fields from being updated. TblBOMDetails seems to be joined a bunch of different ways to other tables. If this complicate query is updateable, then it's just possible that basing an update query on it is just too much for Access to handle. -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "bicyclops" wrote: 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. |
#3
|
|||
|
|||
aggregate calculation works in select query but not an update quer
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. |
#4
|
|||
|
|||
aggregate calculation works in select query but not an update
I'm very surprised that the query below is updateable when attached to a
form. There's multiple Right and Left Joins and some functions which would keep those fields from being updated. TblBOMDetails seems to be joined a bunch of different ways to other tables. If this complicate query is updateable, then it's just possible that basing an update query on it is just too much for Access to handle. -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "bicyclops" wrote: 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. |
#5
|
|||
|
|||
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. |
Thread Tools | |
Display Modes | |
|
|