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

  • 10 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 :)













Post a reply

No one has replied yet! Why not be the first?

Sign in or Join us (it's free).

Contribute

Why not write for us? Or you could submit an event or a user group in your area. Alternatively just tell us what you think!

Our tools

We've got automatic conversion tools to convert C# to VB.NET, VB.NET to C#. Also you can compress javascript and compress css and generate sql connection strings.

“An idiot with a computer is a faster, better idiot” - Rich Julius