query not working

**BigDan** · 11-11-2013, 01:25 PM

I've got an iterate query. I need it to read the prod numbers in one table, and use those to populate another table. I've done this many times with other queries.
I'm getting an error message this time.
Error Condition:
Unable to assign value to target table column number (8)
Additional Information:
(Access Error Text) Application-defined or object-defined error

I've looked at my target table col #8, which is Net Quote Cost.
In the query this is the 'code' given for the field:
Net Quote Cost: (1-0.16)*[QUOTE_COST]
Quote Cost is 2 fields before this, so field 6.
I should also mention that when I use this query without iterating it, it works fine. So something about it populating the target table is the problem.

Please help! I have a deadline I'm working on!

**June7** · 11-11-2013, 01:51 PM

Is it really necessary to save this calculated value and not just calculate when needed?

Not sure I've ever used an 'iterate' query.

I have used VBA to loop through a recordset.

Show the full sql statement and/or procedure code.

**Dal Jeanis** · 11-11-2013, 02:06 PM

Most likely, you need to replace [QUOTE_COST] in the query with whatever the formula is for field 6.
If that doesn't work, then post the full SQL of the query.

**BigDan** · 11-11-2013, 02:27 PM

Its not necessary but this type of query was being used by the guy before me in this job. I want to keep it that way if possible.

I'm pasting the SQL here. Please keep in mind I'm not very well versed with Access. I didnt know it at all before I started this job and in the last few months its all self taught.

PARAMETERS PROD_NBR Text ( 255 );
SELECT IW_OFFERING_SELECTN.PROD_NBR, IW_OFFERING_SELECTN.OFFRNG_ENG_DESC, IW_OFFERING_SELECTN.CORP_STAT_CD, IW_OFFERING_SELECTN.PRM_VNDR_PART_NBR, IW_OFFERING_SELECTN.DEALER_INTRO_DTE, IW_VNDR_OFFR_SELECTN.QUOTE_COST, "16%" AS [Section D], (1-0.16)*[QUOTE_COST] AS [Net Quote Cost], IIf([CURRENCY_CD]="009","USD","CAD") AS [Currency], Sum(IW_RETAIL_SALES_SUMRY.NET_QTY_SOLD) AS [Net POS Units], [Net Quote Cost]*[Net POS Units]*0.045 AS [Earned Royalty], IW_OFFERING_SELECTN.BUYER_NAM, IW_TIME_SELECTION.FISCAL_WK_NBR, IW_TIME_SELECTION.FISCAL_YR, IW_OFFERING_SELECTN.FNLN_CAT_NAM, IW_OFFERING_SELECTN.PROD_CLS, IW_OFFERING_SELECTN.CTC_BRAND_CODE, IW_OFFERING_SELECTN.PRM_VNDR_NBR
FROM IW_VNDR_OFFR_SELECTN INNER JOIN ((((IW_RETAIL_SALES_SUMRY INNER JOIN IW_TIME_SELECTION ON IW_RETAIL_SALES_SUMRY.TIME_SELECTN_KEY = IW_TIME_SELECTION.TIME_SELECTN_KEY) INNER JOIN IW_FACILITY_SELECTN ON IW_RETAIL_SALES_SUMRY.FCLTY_SELECTN_KEY = IW_FACILITY_SELECTN.FCLTY_SELECTN_KEY) INNER JOIN IW_DEAL_SELECTN ON IW_RETAIL_SALES_SUMRY.DEAL_SELECTN_KEY = IW_DEAL_SELECTN.DEAL_SELECTN_KEY) INNER JOIN IW_OFFERING_SELECTN ON IW_RETAIL_SALES_SUMRY.OFFRNG_SELECTN_KEY = IW_OFFERING_SELECTN.OFFRNG_SELECTN_KEY) ON IW_VNDR_OFFR_SELECTN.OFFRNG_SELECTN_KEY = IW_OFFERING_SELECTN.OFFRNG_SELECTN_KEY
WHERE (((IW_OFFERING_SELECTN.OFFRNG_SELECTN_LVL)="SKU") AND ((IW_FACILITY_SELECTN.FCLTY_SELECTN_LVL)="POS") AND ((IW_TIME_SELECTION.TIME_PD_TYP)="WKSTD") AND ((IW_DEAL_SELECTN.DEAL_SELECTN_LVL)="TOTDL" Or (IW_DEAL_SELECTN.DEAL_SELECTN_LVL)="TOTREGRTL"))
GROUP BY IW_OFFERING_SELECTN.PROD_NBR, IW_OFFERING_SELECTN.OFFRNG_ENG_DESC, IW_OFFERING_SELECTN.CORP_STAT_CD, IW_OFFERING_SELECTN.PRM_VNDR_PART_NBR, IW_OFFERING_SELECTN.DEALER_INTRO_DTE, IW_VNDR_OFFR_SELECTN.QUOTE_COST, IIf([CURRENCY_CD]="009","USD","CAD"), IW_OFFERING_SELECTN.BUYER_NAM, IW_OFFERING_SELECTN.BUS_CATEGORY_NAM, IW_TIME_SELECTION.FISCAL_WK_NBR, IW_TIME_SELECTION.FISCAL_YR, IW_OFFERING_SELECTN.FNLN_CAT_NAM, IW_OFFERING_SELECTN.PROD_CLS, IW_OFFERING_SELECTN.CTC_BRAND_CODE, IW_OFFERING_SELECTN.PRM_VNDR_NBR
HAVING (((IW_OFFERING_SELECTN.PROD_NBR)=[PROD_NBR]) AND ((IW_TIME_SELECTION.FISCAL_WK_NBR) Between 1 And 44) AND ((IW_TIME_SELECTION.FISCAL_YR)=2013));

**Dal Jeanis** · 11-11-2013, 02:33 PM

Use this and see if it fixes the problem

Code:

(1-0.16)*IW_VNDR_OFFR_SELECTN.QUOTE_COST AS [Net Quote Cost],

**BigDan** · 11-11-2013, 02:46 PM

Thanks a lot. Is there some way I can put that into a field in Design mode? I dont actually know how to play with SQL. I'm guessing over here I simply replace it with the relevant part above, which I can do if that's the only option, but Design mode is preferable.

But I just want to reiterate that this query works fine when I run it manually, and I enter in one Prod Number myself. Its only screwing up when I iterate it with a list of prod #s and try to enter the result into an existing table. So wouldnt that point to some other issue?

**Dal Jeanis** · 11-11-2013, 02:50 PM

You have more fields that won't be calculable. In Access, I don't believe you can use the name of a calculated field in the same query the field is being created. It seems like they'd create a way to allow you to use that for efficiency's sake, but I've never seen it done. EDIT - looks like it actually works! Holy cow, that will save me some code here and there. Query below changed back to use the easy way.

Code:

   IW_VNDR_OFFR_SELECTN    AS TV
   IW_RETAIL_SALES_SUMRY   AS TR
   IW_TIME_SELECTION       AS TT
   IW_FACILITY_SELECTN     AS TF
   IW_DEAL_SELECTN         AS TD
   IW_OFFERING_SELECTN     AS TW

Here's a cleanup of the SQLcode, with all the tables aliased. I don't know which table Currency_cd is on, so I marked it ZZ. and you can put the correct table alias from the list above in that spot of the SQL.

Code:

PARAMETERS PROD_NBR Text ( 255 );
SELECT 
   TW.PROD_NBR, 
   TW.OFFRNG_ENG_DESC, 
   TW.CORP_STAT_CD, 
   TW.PRM_VNDR_PART_NBR, 
   TW.DEALER_INTRO_DTE, 
   TV.QUOTE_COST, 
   "16%" AS [Section D], 
   (1-0.16)*TV.QUOTE_COST AS [Net Quote Cost], 
   IIf(ZZ.[CURRENCY_CD]="009","USD","CAD") AS [Currency], 
   Sum(TR.NET_QTY_SOLD) AS [Net POS Units], 
   [Net Quote Cost]*[Net POS Units]*0.045 AS [Earned Royalty], 
   TW.BUYER_NAM, 
   TT.FISCAL_WK_NBR, 
   TT.FISCAL_YR, 
   TW.FNLN_CAT_NAM, 
   TW.PROD_CLS, 
   TW.CTC_BRAND_CODE, 
   TW.PRM_VNDR_NBR
FROM 
   IW_VNDR_OFFR_SELECTN AS TV
   INNER JOIN 
    ( ( ( (IW_RETAIL_SALES_SUMRY AS TR
           INNER JOIN 
           IW_TIME_SELECTION AS TT
           ON TR.TIME_SELECTN_KEY = TT.TIME_SELECTN_KEY) 
         INNER JOIN IW_FACILITY_SELECTN AS TF
         ON TR.FCLTY_SELECTN_KEY = TF.FCLTY_SELECTN_KEY) 
       INNER JOIN IW_DEAL_SELECTN AS TD
       ON TR.DEAL_SELECTN_KEY = TD.DEAL_SELECTN_KEY) 
     INNER JOIN IW_OFFERING_SELECTN AS TW
     ON TR.OFFRNG_SELECTN_KEY = TW.OFFRNG_SELECTN_KEY) 
   ON TV.OFFRNG_SELECTN_KEY = TW.OFFRNG_SELECTN_KEY
WHERE 
   (   ((TW.OFFRNG_SELECTN_LVL)="SKU") 
   AND ((TF.FCLTY_SELECTN_LVL)="POS") 
   AND ((TT.TIME_PD_TYP)="WKSTD") 
   AND ((TD.DEAL_SELECTN_LVL)="TOTDL" OR (TD.DEAL_SELECTN_LVL)="TOTREGRTL"))
GROUP BY 
   TW.PROD_NBR, 
   TW.OFFRNG_ENG_DESC, 
   TW.CORP_STAT_CD, 
   TW.PRM_VNDR_PART_NBR, 
   TW.DEALER_INTRO_DTE, 
   TV.QUOTE_COST, 
   IIf([CURRENCY_CD]="009","USD","CAD"), 
   TW.BUYER_NAM, 
   TW.BUS_CATEGORY_NAM, 
   TT.FISCAL_WK_NBR, 
   TT.FISCAL_YR, 
   TW.FNLN_CAT_NAM, 
   TW.PROD_CLS, 
   TW.CTC_BRAND_CODE, 
   TW.PRM_VNDR_NBR
HAVING 
   (((TW.PROD_NBR)=[PROD_NBR]) 
AND ((TT.FISCAL_WK_NBR) Between 1 And 44) 
AND ((TT.FISCAL_YR)=2013));

Open a fresh query in a test version of your database, put the query into SQL mode, and paste the above code into the query. Change the ZZ to the correct table alias. Then run it, and see what happens.

I'm assuming that it's working "coincidentally" when you run it for a single value. Not sure why, but yes, i'm sure that the fixed version will work, if I haven't mistyped something.

Once it's working, you should be able to switch back to design mode and see how the SQL query shows up in picture format.

**BigDan** · 11-11-2013, 02:51 PM

I played with the SQL and tried the above code. Same as before, it worked fine when I ran it manually but when I tried to iterate it into a pre-existing table it failed.

**Dal Jeanis** · 11-11-2013, 02:54 PM

Ummm. Explain, please, what you mean by "iterate it into an existing table". Exactly how do you do that?

Are you trying to use SELECT INTO http://msdn.microsoft.com/en-us/libr...ffice.12).aspx on a preexisting table, rather than INSERT INTO http://msdn.microsoft.com/en-us/libr...ffice.12).aspx?

**BigDan** · 11-11-2013, 03:06 PM

Originally Posted by Dal Jeanis

Ummm. Explain, please, what you mean by "iterate it into an existing table". Exactly how do you do that?

My previous response was written in response to your post #5, not #7. I guess we both posted at a similar time.

What i do with my iterate queries is I first run it manually, in make table mode, and when it asks me for prod# i type in 000, which creates a new blank table. Then I change the query back to select mode and save it.

Then I open a macro, give the query I'm running, give the name of the table that has the prod nbrs as the input, and the name of the (blank) table i just created in the previous sentence, as the output.

**Dal Jeanis** · 11-11-2013, 03:08 PM

Ah. You need an APPEND query, not a MAKETABLE query.

**BigDan** · 11-11-2013, 03:17 PM

I'm afraid this doesnt work either. I'm guessing there's some minor thing we're overlooking but I just dont get what that is.

Originally Posted by Dal Jeanis

You have more fields that won't be calculable. In Access, I don't believe you can use the name of a calculated field in the same query the field is being created. It seems like they'd create a way to allow you to use that for efficiency's sake, but I've never seen it done. EDIT - looks like it actually works! Holy cow, that will save me some code here and there. Query below changed back to use the easy way.

Code:

   IW_VNDR_OFFR_SELECTN    AS TV
   IW_RETAIL_SALES_SUMRY   AS TR
   IW_TIME_SELECTION       AS TT
   IW_FACILITY_SELECTN     AS TF
   IW_DEAL_SELECTN         AS TD
   IW_OFFERING_SELECTN     AS TW

Here's a cleanup of the SQLcode, with all the tables aliased. I don't know which table Currency_cd is on, so I marked it ZZ. and you can put the correct table alias from the list above in that spot of the SQL.

Code:

PARAMETERS PROD_NBR Text ( 255 );
SELECT 
   TW.PROD_NBR, 
   TW.OFFRNG_ENG_DESC, 
   TW.CORP_STAT_CD, 
   TW.PRM_VNDR_PART_NBR, 
   TW.DEALER_INTRO_DTE, 
   TV.QUOTE_COST, 
   "16%" AS [Section D], 
   (1-0.16)*TV.QUOTE_COST AS [Net Quote Cost], 
   IIf(ZZ.[CURRENCY_CD]="009","USD","CAD") AS [Currency], 
   Sum(TR.NET_QTY_SOLD) AS [Net POS Units], 
   [Net Quote Cost]*[Net POS Units]*0.045 AS [Earned Royalty], 
   TW.BUYER_NAM, 
   TT.FISCAL_WK_NBR, 
   TT.FISCAL_YR, 
   TW.FNLN_CAT_NAM, 
   TW.PROD_CLS, 
   TW.CTC_BRAND_CODE, 
   TW.PRM_VNDR_NBR
FROM 
   IW_VNDR_OFFR_SELECTN AS TV
   INNER JOIN 
    ( ( ( (IW_RETAIL_SALES_SUMRY AS TR
           INNER JOIN 
           IW_TIME_SELECTION AS TT
           ON TR.TIME_SELECTN_KEY = TT.TIME_SELECTN_KEY) 
         INNER JOIN IW_FACILITY_SELECTN AS TF
         ON TR.FCLTY_SELECTN_KEY = TF.FCLTY_SELECTN_KEY) 
       INNER JOIN IW_DEAL_SELECTN AS TD
       ON TR.DEAL_SELECTN_KEY = TD.DEAL_SELECTN_KEY) 
     INNER JOIN IW_OFFERING_SELECTN AS TW
     ON TR.OFFRNG_SELECTN_KEY = TW.OFFRNG_SELECTN_KEY) 
   ON TV.OFFRNG_SELECTN_KEY = TW.OFFRNG_SELECTN_KEY
WHERE 
   (   ((TW.OFFRNG_SELECTN_LVL)="SKU") 
   AND ((TF.FCLTY_SELECTN_LVL)="POS") 
   AND ((TT.TIME_PD_TYP)="WKSTD") 
   AND ((TD.DEAL_SELECTN_LVL)="TOTDL" OR (TD.DEAL_SELECTN_LVL)="TOTREGRTL"))
GROUP BY 
   TW.PROD_NBR, 
   TW.OFFRNG_ENG_DESC, 
   TW.CORP_STAT_CD, 
   TW.PRM_VNDR_PART_NBR, 
   TW.DEALER_INTRO_DTE, 
   TV.QUOTE_COST, 
   IIf([CURRENCY_CD]="009","USD","CAD"), 
   TW.BUYER_NAM, 
   TW.BUS_CATEGORY_NAM, 
   TT.FISCAL_WK_NBR, 
   TT.FISCAL_YR, 
   TW.FNLN_CAT_NAM, 
   TW.PROD_CLS, 
   TW.CTC_BRAND_CODE, 
   TW.PRM_VNDR_NBR
HAVING 
   (((TW.PROD_NBR)=[PROD_NBR]) 
AND ((TT.FISCAL_WK_NBR) Between 1 And 44) 
AND ((TT.FISCAL_YR)=2013));

Open a fresh query in a test version of your database, put the query into SQL mode, and paste the above code into the query. Change the ZZ to the correct table alias. Then run it, and see what happens.

I'm assuming that it's working "coincidentally" when you run it for a single value. Not sure why, but yes, i'm sure that the fixed version will work, if I haven't mistyped something.

Once it's working, you should be able to switch back to design mode and see how the SQL query shows up in picture format.

**Dal Jeanis** · 11-11-2013, 04:28 PM

Try removing the condition in the Having clause that says (((TW.PROD_NBR)=[PROD_NBR]) and just run it once as a maketable query.

**BigDan** · 11-11-2013, 04:34 PM

Originally Posted by Dal Jeanis

Try removing the condition in the Having clause that says (((TW.PROD_NBR)=[PROD_NBR]) and just run it once as a maketable query.

A maketable query wouldnt work since I'm using this query to populate an existing table.

**Dal Jeanis** · 11-12-2013, 08:52 AM

That was a diagnostic test - to see if something different happens when you run it without a test value, as opposed to with zeroes.

query not working

Thread Tools

query not working

Similar Threads

Sum IIF query not working!

Print Report function working in adp format but is not working in ade

my query working fine. i want the query to find the total showing all the rows

Help please! iif not working in query!

Query is not working

Posting Permissions