Defining an Array in an Access Table

[EddieN1]

I have a table that represents a General Ledger Account. In that table I want to record the net change for each of 12 months of the year. Is there a way to define it in such a way that I can refer to the field as something like AccountNetChange(idx) where idx represents the month in question?

[June7]

Access 2007 does not allow calculations in tables (new feature in Access 2010). Use queries to calculate data. What is the structure of this table? Sounds like you need to do an aggregate (totals) query.

[EddieN1]

I don't think I explained my question properly... One way to set up the table would be...

GLAcct long integer
JanAmt double
FebAmt double
MarAmt double
AprAmt double
MayAmt double
JunAmt double
JulAmt double
AugAmt double
SepAmt double
OctAmt double
NovAmt double
DecAmt double

However, what I would really like is something like...

AcctAmt(12) double (array with 12 entries, one for each month)

Then I could reference the Amount something like AcctAmt(idx) where idx is the month in question.

Is this possible in Access 2007?

Thanks, Eddie

[June7]

Will this data have multiple years?

The first arrangement would be easy to reference specific month. Would every record have a value in every field?

With second arrangement method to get specific month would be to filter the records to the one of interest.

[EddieN1]

Every record will almost always have a value in each month as we progress through the year, however, this is for only one year. At the end of the year, all the amounts get moved to "previous year fields" and the 12 amount fields get reset to zero to start the new year. The first method would require something like...

dim tmpAmt double

if month = 1 then
tmpAmt = JanAmt
elseif month = 2 then
tmpAmt = FebAmt
elseif month = 3 then
tmpAmt = MarAmt
elseif ...

After adding to and subtracting from tmpAmt multiple times...

if month = 1 then
JanAmt = tmpAmt
elseif month = 2 then
FebAmt = tmpAmt
elseif month = 3 then
MarAmt = tmpAmt
elseif...

I would prefer to simply refer to the table columns as...

AcctAmt(month) = +, -, etc.

Any ideas?

[June7]

First of all, don't think I would do such an arrangement requiring move of data to 'previous year fields'. I would have one set of fields and filter by the year of interest.

What is all the adding and subtracting for? You are updating an aggregate value? Again, think not something I would do. A concept of relational database is 'enter raw data, do calcs in reports'. I would enter transaction records that would be summed and then calc difference of the 'in' and 'out' to get the aggregate total.

Not really understanding what you want AcctAmt(month) to be and how you would use it - a custom function?

[EddieN1]

The application is a typical General Ledger accounting system. For each GL Account number, I carry certain header information, such as Account Name, Account Type (Asset, Liablility, Revenue, Expense, etc.), and others. I also want 12 currency buckets to accumulate the net debit/credit for that account for each month in the calendar year. These buckets get updated from various sub-systems, such as sales, cash application, invoices, checks, inventory adjustments, payroll, fixed assets, journal entries, etc for the current month. After all the accumulations are completed at the end of the month, I want to print Balance Sheets, Income Statements, etc. for the current month. When I print the statements for, say January, I don't want to have to reference the "JanAmt" field and then reference the "FebAmt" fields for February, etc. I want to set an index to a value of 1 through 12 (or rather zero through 11) based on the month and then refer to the "current" month's buckets as simply AcctAmt(idx) where idx is the index or subscript in the array for the desired month. I can create an array in Visual Basic with a "Dim AcctAmt(12) as double". The number 12 in parentheses tells VBA to create 12 buckets that I can reference as AcctAmt(3), AcctAmt(10), or AcctAmt(idx) if I've dimmed idx as integer. I want to be able to do the same thing when I create a column in an Access table. Does this explain it better? Thanks, Eddie

[June7]

Access tables don't have an X and Y (column/row) reference the way an Excel spreadsheet or a coded array has. You query a table to retrieve specific field(s) and also filter the records. This is a RecordSet. The RecordSet can be as small as one field and one record but it is still a set, not a single value.

[EddieN1]

Please disregard this post.

[orange]

Eddie,

Here is a generic model for General Ledger/Accounting from databaseanswers.org
Here are the related business rules/requirements

Access tables do not store Arrays per se. You could build an Array(s) and populate same from your Tables for calculations if that is what you want.
Access (relational databases generally) stores data in tables. Queries are used to select data from tables based on criteria.