Creating an array based on user input query results (Access 2003)

• • Reply

  14 years ago

  by KPAdmin

  Andy Wallace

  , United Kingdom

  Joined 14 years ago

  Hi, I'm fairly new to access development and need a few pointers regarding arrays, namely their creation.

  The database is based on sales history data and what's required is the user is presented with 12 months (periods) of sales history for a given product from a specified starting period.  I have so far managed to get the query to return a summarised total qty and value for each period, which is exactly what I need.  However this does not always produce 12 rows as some months there are no sales for a specific product.

  What I would like to do ultimately is populate an array with this data, and where there is no history for a given period, give the sales qty and sales value fields a value of zero, so that it in essence produces a full 12 month array.  That way I can display the results in a form with each period as a column.

  The fields that would be passed to the array would be "PeriodID" (integer), "PeriodEnd" (date),"Total_Qty" (integer) and "Total_Val" (integer.)

  I want the array to generate a row of data if it can't find anything for a period in the query result.  For example, if it queries on a given product from period 1 to 12 and only finds records for 6 to 12, I want it to create rows for 1-5 with the corresponding PeriodID and PeriodEnd and insert zeros for the qty and value.

  e.g.

  1, 02/07/05, 0, 0
  2, 30/07/05, 0, 0
  3, 27/08/05, 0, 0
  4, 01/10/05, 0, 0
  5, 29/10/05, 0, 0
  6, 26/11/05, 2681, 25250
  7, 31/12/05, 4259, 44986
  8, 28/01/06, 1253, 11980
  9, 25/02/06, 960, 8950
  10, 01/04/06, 1143, 10455
  11, 29/04/06, 2402, 22870
  12, 27/05/06, 2520, 24250
  
  Any advice will be greatly appreciated.
  Many thanks :)
  
  
  
  
  
  
  
  
  
  
  
  
  
  

  Report abuse