A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

aggregate calculation works in select query but not an update quer



 
 
Thread Tools Display Modes
  #1  
Old May 27th, 2010, 08:53 PM posted to microsoft.public.access.queries
bicyclops
external usenet poster
 
Posts: 29
Default 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.


Ads
  #2  
Old May 27th, 2010, 09:36 PM posted to microsoft.public.access.queries
bicyclops
external usenet poster
 
Posts: 29
Default 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  
Old May 27th, 2010, 09:56 PM posted to microsoft.public.access.queries
Jerry Whittle
external usenet poster
 
Posts: 4,732
Default 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  
Old May 27th, 2010, 10:43 PM posted to microsoft.public.access.queries
Jerry Whittle
external usenet poster
 
Posts: 4,732
Default 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  
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.


 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 01:10 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright 2004-2020 OfficeFrustration.
The comments are property of their posters.