Q: Arrays in Access

  • Thread starter Thread starter Geoff Jones
  • Start date Start date
G

Geoff Jones

Hi

I'm new to Access and was wondering if the possible is possible:

I would like to create a database where each record can contain a list of
numbers. The number of numbers stored in each record is not known in
advance. What I'm trying to do is store an array of doubles in each record.
I need to know how to store an array of such numbers in a record and the
choices I have for inputting the numbers into new records.

Can anybody tell me the best way to do this?

Thanks in advance

Geoff
 
You can store your data as strings that are delimited. Then use code to grab
that string, parse it out to an array. You then have the problem of
reversing this.

ms-access does have the most useful functions split(), and join() that moves
a string to an array, and then you can move the array back to a string.
However, this is generally a bad idea, as the rest of the system like
reports, sorting and grouping etc that you get with SQL cannot be used on
that array data. You wind up writing tons of code to do the smallest task
when all these cool report and data manipulate tools are available..but you
can use them!

Think of all of the zillions of applications in the market place that has to
store more then one value for something. Things like invoice, and then the
"many" values for the invoice details.

The same goes for you might have people, and then a list of their children.

In the above to common and classic examples, we are talking relational
theory here. You have ONE invoice, but MANY invoice details.

You have ONE customer, and they can have MANY children. We call these common
data problems a ONE to MANY relationship. In most of these cases, we DO NOT
know in advance how many of the "many" there will be. So, we need designs
that are flexible.

So, no, you don't want to, or need to store "many" values in one record, you
can use relation data modeling to accomplish the same goal. This is a very
important concept to grasp, as it means that a commercial accounting system
can be designed, and when you add new part "assemblies" to the inventory
system, NO new tables, and NO new table re-structuring needs to occur. This
also means that code does not need to be changed each time a new account, or
part assembly is added to the system. You can imagine how complex those part
assemblies get, and some even have different units for weight and
measurements. Yet, a good relation design will handle these part assemblies
with ease, and not that many talbes are needed to model this!

So, if you need more then one value for each record, or "entity", then you
can use a related table to store the "many" values for that one thing.

For some reading on this concept check out:

ACC2002: Database Normalization Basics
http://support.microsoft.com/?id=283878

Database Normalization Tips by Luke Chung
http://www.fmsinc.com/tpapers/genaccess/databasenorm.html

Data Normalization Fundamentals by Luke Chung
http://www.fmsinc.com/tpapers/datanorm/index.html

324613 - Support WebCast: Database Normalization Basics
http://support.microsoft.com/?id=324613
 
Back
Top