Array functions using MS Access

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am using MS Excel to analyze a large set of data using several array functions including SUM, MEDIAN, and STDEV. This data set and the processing requirements now exceed the capabilities of MS Excel. It is possible to execute such array functions in MS Access through the use of queries? If so, how? I have the same record structure across several tables -- the field names are different across each table, however.
 
Scott

I can't quite visualize your Access table structure from your description.

I can tell you that there is good news and bad ...

For you to use the aggregate functions (median is not included) of Access,
your data needs to be (reasonably) well-normalized. If you have "repeating
fields" (e.g., "January Totals", "February Totals", etc.) as you might and
probably would in Excel, the Access aggregate functions won't work.

But, in the above example, if you have two fields ([DateOfXXXX], [Amount]),
you could use an aggregate function, grouping by the date and summing the
amount. If you only wanted monthly totals, you could use the Month()
function on your date and group by Month([DateOfXXXX]).

Another downside, in this example, is that January of 2000 and January of
2004 are both Januarys!
 
Thanks, Jeff. I have four types of data, each for approximately 55 quarters. I have labeled this data using a structure similar to this: 1999Q3A, 1999Q3B, etc. I am using the letters to code for the type of data and the year and quarter to code for the date

If I set up four tables, each representing a data type (e.g. A, B, etc.), is there a way to run array calculations such that I can find data that falls within a certain numerical range within one array and then pull the corresponding data out of another table (similar to the functionality offered by Excel -- match the record and field intersection in one table with another)? If not, what is one to do if Excel no longer operates due to file size?
 
Scott

The "repeating fieldname" character you described confirms my suspicion that
your data is more like a spreadsheet than a relational database.

So let's talk data relationships for a bit... and you will want to read up
on normalization if you're to make good use of Access' capabilities. You
really don't want to be embedding so many pieces of information in your
fieldnames (year, quarter, data type).

I don't know if you have actual dates, but it sounds like you have a year
and a quarter. Those are two of your fields. The "data type" (A, B, ..) is
a third field. A fourth is the value you've recorded for the combination of
the other three.

?!Or do you have individual detail "rows", with actual dates? If you did,
you could use Access to calculate the quarterly totals.

I'm not understanding your use of the term "array", nor what you are trying
to do between tables. Could you provide an example, using your data? Can
you describe WHAT you want to accomplish, rather than HOW?
 
Back
Top