Array Question

If I have an access db named salesinfo that has a table called table1.

just assume it has a field known as totalsales and a field known as month, and year, and also has a field salesperson.

I want to put in an array, the sales data where the salespersons name ="david" and the year="1998", and it needs to be grouped by month.  (jan, feb, march, etc)

I need this data in an array, because I want to use mschart with this array.  I need to understand how to do this array, so I can do similar arrays on other criteria.

Thank you.
checkoutAsked:
Who is Participating?
 
jforbesConnect With a Mentor Commented:
When trying a recordset to the database, just say the following:
Bare in mind that these field names may not total match yours

say there were 2 fields in your database Table1

SalesPerson
SalesYear

Say your recordset workspace is rs

    arrSalesInfo(count).sSalesPersonName = rs!SalesPerson
    arrSalesInfo(count).sSalesYear = rs!SalesYear

when a value of something is on the right of the = sign then the value on the left receives the value of the right.

now if the clause was the following:
    rs!SalesYear = arrSalesInfo(count).sSalesYear
the recordset would now equal the array.

a way you can test to see if your array is full
put this statement right after the

    arrSalesInfo(count).sSalesPersonName = rs!SalesPerson
    arrSalesInfo(count).sSalesYear = rs!SalesYear
    debug.print arrSalesInfo(count).sSalesPersonName, arrSalesInfo(count).sSalesYear

This will print the values that are stored within the array on the immediate window.

Hope this Helps

-John M. Forbes
Programmer/Analyst
Competitive Technologies Inc.
jforbes@comptechinc.net
jforbes@nwrain.com
0
 
MelissaCCommented:
Just declare the array:

dim myArray(1) as currency
dim count as integer
count=1
myArray(count-1)=ReadFirstItemFromTable()


Each time you put an item in the array, you can redim the array:
count=count+1
Redim PRESERVE myArray(count)  'the preserve makes sure the older
                               'values stay in the array
myArray(count-1)=ReadNextItemFromTable()

The grouping by month can be done in de sql-statement
"select sales from table1 where name="david and year="1998" group by month

0
 
checkoutAuthor Commented:
Melissa,

Thank you for your time, I am sure your example may work, but I need a working example. I gave the details so I can have code written, Then I learn how to write an array.  At this point, I have never done this before so it is very new to me.  If you could be so kind as to write the code for me, I will appriciate it!


Thank you for your time.



0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

 
jforbesCommented:
You can make a Type, then array the type.

Option Explicit
   Dim Total as long
'Set type to use in the array
Private Type typSalesInfo
     Dim lTotalSales as Long
     Dim sMonth as String
     Dim sSalesPersonName as String
     Dim sSalesYear as String
End Type

'Set an increment counter to expand the array by if it exceeds 'limit
Private Const cstSalesInfoInc = 1000

'Declare the array as the type created above
Private arrSalesInfo() as typSalesInfo

Private Sub FillArr()
   On Error Goto ErrProc
   'Set for array count
   Dim count as long
   'This sets the array for 1000 as the type
   ReDim arrSalesInfo(cstSalesInfoInc) As typSalesInfo
 
   do while 'Some Crieteria, most likely database EOF  
    count = count + 1
'***** In this area you can add you data reading calls from   'the database

'once you receive info you can enter it in the array this way

  arrSalesInfo(count).sSalesPersonName = "david" 'Rs!SalesPerson
  arrSalesInfo(count).sSalesYear = "1998" ' This could also be rs

Loop  
   
   Total = count
'This capture the out of dimension error and redims the array.
ErrProc:
   If Err.Number = 9 Then
      ReDim Preserve arrSalesInfo(UBound(arrSalesInfo) + cstSalesInfoInc) As typSalesInfo
      Resume
   End If
End Sub

Once you retrieve all info you can just do a for next to retrieve it.

Hope this Helps

John M. Forbes
Programmer/Analyst
Competitive Technologies Inc.


0
 
jforbesCommented:
By the way Melissa is right you should probably do an SQL Statement to filter the info.

dim SQL as String
Bare in mind name of data fields need to be changed to accomadate
SQL = "SELECT SalesPersonName, Year FROM SalesInfo WHERE SalesPersonName = "david" AND Year = "1998"
0
 
checkoutAuthor Commented:
I am having problems with your example, first:

Private type statement says "invalid outside procedure"

I removed the "dim" and it worked.  I am having problems with
arrsalesinfo(count).ssalespersonname="david"
arrsalesinfo(count).ssalesyear="1998"

I get subscript out of range.  I created a workspace and opened the db and the recordset.  but How do I tie the recordset to this array?  I feel like I am missing some pieces.

Anyhelp is appreciated

0
 
jforbesCommented:
First the Type can only be in the Declaration Section of the Program, and when declaring variables under Option Explicit you should use the Private or Public instead of Dim.  Read the differences about them in the online help.

The array must be Dimentioned before it can be used.

The redim statement below must be done right when you enter the sub.

If you are entering the sub multiple times, you would want to use the Redim Preserve statement

If you have more than 1000 records in the database, and are trying to run the array, you must turn off Break on All Errors, because the array will be over the limit and have to be sent to the ErrProc to be redimentioned.

Put the following all in one sub.
Private Sub FillArr()
   On Error Goto ErrProc
   'Set for array count
   Dim count as long

   'This sets the array for 1000 as the type
   ReDim arrSalesInfo(cstSalesInfoInc) As typSalesInfo
 
   do while 'Some Crieteria, most likely database EOF  
    count = count + 1
'***** In this area you can add you data reading calls from the database

'once you receive info you can enter it in the array this way

  arrSalesInfo(count).sSalesPersonName = "david" 'Rs!SalesPerson
  arrSalesInfo(count).sSalesYear = "1998" ' This could also be rs

Loop    
   
   Total = count
'This capture the out of dimension error and redims the array.
ErrProc:
   If Err.Number = 9 Then
      ReDim Preserve arrSalesInfo(UBound(arrSalesInfo) + cstSalesInfoInc) As typSalesInfo
      Resume
   End If
End Sub
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.