Page 1 of 2 12 LastLast
Results 1 to 15 of 22

Thread: Overflow error

  1. #1
    Join Date
    May 2006
    Posts
    21

    Question Unanswered: Overflow error

    Hi

    In Access 2003, I have a query (say Query1), which takes data from Query2. This worked perfectly well for a couple of weeks, but the past 2 weeks has crashed on "overflow" error.

    I eliminated fields from the select part of Query1, one by one to find the offending field, which ended up being wk_scale. If I write a new query selecting wk_scale from Query2, it works fine. However, if I try and group by wk_scale (which will need to be done in Query1), then I get the overflow error. No calculations are being done on this field.

    When I run Query2 by itself, it works perfectly. However, if I try and sort on wk_scale, I get the overflow error (both if I try sort the resultset and if I add an order by in the query).

    wk_scale is "created" in Query2 by:
    IIf([week_scalar] Is Null,0,[week_scalar]) AS wk_scale

    The value for wk_scale ranges between 0 and 2. Most commonly a value similar to 0.770642201834862.

    Out of desperation, I have tried converting the field to a double (in both queries), but this didn't work.

    Please can someone help?

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    What are the types currently? What math is being done/used on these values?
    Back to Access ... ADO is not the way to go for speed ...

  3. #3
    Join Date
    May 2006
    Posts
    21
    Hi

    The queries in more detail, are:

    Query2 (real name: ty_style_set_week_sales_train):

    SELECT IIf([week_scalar] Is Null,0,[week_scalar]) AS wk_scale ...


    Query1 (query 1 calls query 2 (ty_style_set_week_sales_train, the other 2 sources are tables)):

    SELECT IIf([week_end_date] Between [instrwkending] And [exitwkending],[wk_scale]*[average weekly sales]*[Average Selling Price],0) AS ty_plan_gbp
    FROM (ty_style_set_week_sales_train
    LEFT JOIN [TOTAL Range Plan Data - MT]
    ON ty_style_set_week_sales_train.[Style Number] = [TOTAL Range Plan Data - MT].[Style Number])
    LEFT JOIN event_ty_sales_ever_train_static
    ON ty_style_set_week_sales_train.[Style Number] = event_ty_sales_ever_train_static.style
    GROUP BY IIf([week_end_date] Between [instrwkending] And [exitwkending],[wk_scale]*[average weekly sales]*[Average Selling Price],0)



    The problem seems to be putting the field wk_scale in the group by - that's where I think it falls over. Even if I write

    select wk_scale from ty_style_set_week_sales_train
    group by wk_scale


    it falls over on the group by. So it's falling over even before any math is done on it. Or is there something I'm missing?

    The whole process is a very involved one which I've had to take over from someone who left, and it's really a hard process to get my head around.

    Any help and suggestions will be appreciated.

  4. #4
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Why are you grouping on a single column query???? Just ORDER BY if you want some ordering ...
    Back to Access ... ADO is not the way to go for speed ...

  5. #5
    Join Date
    May 2006
    Posts
    21
    No - it's not a single-column query ... the query is just really long, so I left out all the other columns that were selected and grouped on ... would it help if I put the queries here in the entirety?

  6. #6
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by janineweyers
    No - it's not a single-column query ... the query is just really long, so I left out all the other columns that were selected and grouped on ... would it help if I put the queries here in the entirety?
    Do you have any aggregate functions in the SQL mandating the grouping? I'm just trying to figure out why you need to group ...
    Back to Access ... ADO is not the way to go for speed ...

  7. #7
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926

    Overflow

    Are these SQL Server linked tables? If so, add in a field with a timestamp data type. Also check to make sure you don't have "bogus" field which doesn't fit the field type (i.e. a date field with a value of 12/03 instead of 12/02/03.)
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  8. #8
    Join Date
    May 2006
    Posts
    21
    The 2 tables that are used are in access - the same db that the query is in.

    Thanks to M Owen and PKStormy for reading and showing interest to help.

    Here are the queries in their entirety (a bit of a slog to read through, but perhaps it will make more sense?):

    Query1:
    SELECT ty_style_set_week_sales_train.NFSD_CODE, ty_style_set_week_sales_train.[Style Number], ty_style_set_week_sales_train.NFSD_NAME,
    ty_style_set_week_sales_train.[Sub Group Code], ty_style_set_week_sales_train.name, ty_style_set_week_sales_train.Year_Week_Number, ty_style_set_week_sales_train.Week_Number, ty_style_set_week_sales_train.Week_End_Date, ty_style_set_week_sales_train.start_we, ty_style_set_week_sales_train.end_we, ty_style_set_week_sales_train.ty_sales,
    IIf([week_end_date] Between [instrwkending] And [exitwkending],[wk_scale]*[average weekly sales]*[Average Selling Price],0) AS ty_plan_gbp,
    [TOTAL Range Plan Data - MT].[Style Desc], ty_style_set_week_sales_train.sal_vol, ty_style_set_week_sales_train.ave_sp, ty_style_set_week_sales_train.str_stk_vol, ty_style_set_week_sales_train.str_stk_val, ty_style_set_week_sales_train.depwhf_stk_vol, ty_style_set_week_sales_train.depwhf_stk_val, ty_style_set_week_sales_train.tot_stk_vol, ty_style_set_week_sales_train.tot_stk_val, ty_style_set_week_sales_train.ord_stk_vol, ty_style_set_week_sales_train.ord_stk_val, ty_style_set_week_sales_train.pot_stk_vol, ty_style_set_week_sales_train.pot_stk_val, ty_style_set_week_sales_train.str_w_stk,
    [TOTAL Range Plan Data - MT].[Average Selling Price], ty_style_set_week_sales_train.cur_sp, ty_style_set_week_sales_train.plan_stores,
    IIf([week_end_date] Between [instrwkending] And [exitwkending],[wk_scale]*[average weekly sales],0) AS ty_plan_vol, event_ty_sales_ever_train_static.sal_vol AS cum_sales_vol_ever, event_ty_sales_ever_train_static.sal_val AS cum_sales_val_ever, ty_style_set_week_sales_train.s_end_wk, ty_style_set_week_sales_train.sal_val_xvat, ty_style_set_week_sales_train.cost_xvat,
    IIf([week_end_date] Between [instrwkending] And [exitwkending],[wk_scale]*[average weekly sales]*[Average Selling Price]/(1+[VAT Rate]),0) AS plan_sal_xvat,
    IIf([week_end_date] Between [instrwkending] And [exitwkending],[wk_scale]*[average weekly sales]*[landed cost price 1],0) AS plan_cost_xvat
    FROM (ty_style_set_week_sales_train
    LEFT JOIN [TOTAL Range Plan Data - MT]
    ON ty_style_set_week_sales_train.[Style Number] = [TOTAL Range Plan Data - MT].[Style Number])
    LEFT JOIN event_ty_sales_ever_train_static
    ON ty_style_set_week_sales_train.[Style Number] = event_ty_sales_ever_train_static.style
    GROUP BY ty_style_set_week_sales_train.NFSD_CODE, ty_style_set_week_sales_train.[Style Number], ty_style_set_week_sales_train.NFSD_NAME,
    ty_style_set_week_sales_train.[Sub Group Code], ty_style_set_week_sales_train.name, ty_style_set_week_sales_train.Year_Week_Number, ty_style_set_week_sales_train.Week_Number, ty_style_set_week_sales_train.Week_End_Date, ty_style_set_week_sales_train.start_we, ty_style_set_week_sales_train.end_we, ty_style_set_week_sales_train.ty_sales,
    IIf([week_end_date] Between [instrwkending] And [exitwkending],[wk_scale]*[average weekly sales]*[Average Selling Price],0),
    [TOTAL Range Plan Data - MT].[Style Desc], ty_style_set_week_sales_train.sal_vol, ty_style_set_week_sales_train.ave_sp, ty_style_set_week_sales_train.str_stk_vol, ty_style_set_week_sales_train.str_stk_val, ty_style_set_week_sales_train.depwhf_stk_vol, ty_style_set_week_sales_train.depwhf_stk_val, ty_style_set_week_sales_train.tot_stk_vol, ty_style_set_week_sales_train.tot_stk_val, ty_style_set_week_sales_train.ord_stk_vol, ty_style_set_week_sales_train.ord_stk_val, ty_style_set_week_sales_train.pot_stk_vol, ty_style_set_week_sales_train.pot_stk_val, ty_style_set_week_sales_train.str_w_stk,
    [TOTAL Range Plan Data - MT].[Average Selling Price], ty_style_set_week_sales_train.cur_sp, ty_style_set_week_sales_train.plan_stores,
    IIf([week_end_date] Between [instrwkending] And [exitwkending],[wk_scale]*[average weekly sales],0),
    event_ty_sales_ever_train_static.sal_vol, event_ty_sales_ever_train_static.sal_val, ty_style_set_week_sales_train.s_end_wk, ty_style_set_week_sales_train.sal_val_xvat, ty_style_set_week_sales_train.cost_xvat,
    IIf([week_end_date] Between [instrwkending] And [exitwkending],[wk_scale]*[average weekly sales]*[Average Selling Price]/(1+[VAT Rate]),0),
    IIf([week_end_date] Between [instrwkending] And [exitwkending],[wk_scale]*[average weekly sales]*[landed cost price 1],0);


    ty_style_set_week_sales_train:

    SELECT ty_style_set_weeks_train.NFSD_CODE,
    ty_style_set_weeks_train.[Style Number], ty_style_set_weeks_train.NFSD_NAME,
    ty_style_set_weeks_train.[Sub Group Code],
    ty_style_set_weeks_train.name,
    ty_style_set_weeks_train.Year_Number, ty_style_set_weeks_train.Week_Number, ty_style_set_weeks_train.Year_Week_Number, ty_style_set_weeks_train.Week_End_Date, ty_style_set_weeks_train.start_we,
    ty_style_set_weeks_train.end_we,
    IIf([sal_val] Is Null,0,[sal_val]) AS ty_sales,
    IIf([week_scalar] Is Null,0,[week_scalar]) AS wk_scale, event_ty_sales_train_static.sal_vol,
    IIf(nz([sal_vol])=0,0,[sal_val]/[sal_vol]) AS ave_sp, event_ty_sales_train_static.str_stk_vol, event_ty_sales_train_static.str_stk_val, event_ty_sales_train_static.depwhf_stk_vol, event_ty_sales_train_static.depwhf_stk_val, event_ty_sales_train_static.tot_stk_vol, event_ty_sales_train_static.tot_stk_val, event_ty_sales_train_static.ord_stk_vol, event_ty_sales_train_static.ord_stk_val,
    [tot_stk_vol]+[ord_stk_vol] AS pot_stk_vol,
    Val(nz([tot_stk_val]))+Val(nz([ord_stk_val])) AS pot_stk_val, event_ty_sales_train_static.str_w_stk,
    event_ty_sales_train_static.orig_sp,
    event_ty_sales_train_static.cur_sp,
    event_ty_sales_train_static.plan_stores,
    ty_style_set_weeks_train.season,
    event_ty_sales_train_static.s_end_wk, event_ty_sales_train_static.sal_val_xvat, event_ty_sales_train_static.cost_xvat
    FROM (ty_style_set_weeks_train
    LEFT JOIN event_prd_wk_scale_train
    ON (ty_style_set_weeks_train.[Style Number] = event_prd_wk_scale_train.[Style Number])
    AND (ty_style_set_weeks_train.Week_End_Date = event_prd_wk_scale_train.Date))
    LEFT JOIN event_ty_sales_train_static
    ON (ty_style_set_weeks_train.[Style Number] = event_ty_sales_train_static.style)
    AND (ty_style_set_weeks_train.Year_Week_Number = event_ty_sales_train_static.ywn);

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I think I know what Mike's next comment will be given his tack so far and he is right so I will throw in another couple of different ideas.

    Check out the NZ() function - probably won't make much difference but is easier to type than IiF(x IS NULL, y, z).

    Also - you sure week_scaler, average weekly sales and Average Selling Price are all numeric (not for example text with number values)?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  10. #10
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    HOLY CROSSED EYEBALLS BATMAN!!!! THose queries ARE doosies ... How you could debug them is a chore in itself ... I see no aggregate functions here ... WHY are you grouping in the 1st place? And Poots, yep - was gonna followup with what is the type for the ty_plan_gbp column ... THe NZ is a good touch too.
    Back to Access ... ADO is not the way to go for speed ...

  11. #11
    Join Date
    Jun 2004
    Location
    Seattle, WA
    Posts
    601
    I agree M Owen. I am surpized that Access didn't give out sooner.
    Janine - maybe you need to split things up?
    Ryan
    My Blog

  12. #12
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Actually Mike I was trying not to steal your thunder as you had done the hard work but yes - no need for a GROUP BY clause - just use DISTINCT.

    I have always liked the name Janine Nothing funny - I just like the name
    Testimonial:
    pootle flump
    ur codings are working excelent.

  13. #13
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by pootle flump
    Actually Mike I was trying not to steal your thunder as you had done the hard work but yes - no need for a GROUP BY clause - just use DISTINCT.

    I have always liked the name Janine Nothing funny - I just like the name
    Or in this case, ORDER BY the criteria desired to get things looking the way you want ...

    My suspicion is that the grouping was used as a quasi ordering action - by grouping on the columns in order was defacto ordering the rows ... MUCH easier to use ORDER BY and have less headaches ...
    Back to Access ... ADO is not the way to go for speed ...

  14. #14
    Join Date
    May 2006
    Posts
    21
    Thanks for the responses ... I'm at home now for a long weekend ... will try out your suggestions first thing Tuesday morning.

    The whole query process consists of at least 10 queries already that build up to the main query - it really is a nightmare to try and make sense of - taking over from someone else is no joke. I think I'd probably need to start reworking from waaaayyyyy back up in the process.

    Please do check back to this thread next week and see how I'm doing, as I'm sure I'll need some help

  15. #15
    Join Date
    May 2006
    Posts
    21
    There are multiple records per style per week ... and the resultset should only contain one record per style per week ... which I think is why the grouping is there.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •