unknown number of entries at design time

[Mike Cloud]

Hi

I've got a set of simple data types (numbers, a text
field) that I want to store in my database. The problem
is that I don't know at design time how many of these
there will be. I can specify the set of types (for
example: int, int, text), but I don't know how many times
this set will need to be stored. Calling the above
(int,int,text) a set, I don't know how many sets there
will be. (Sorry about the repetition for those who got it
the first time...)
Is there any way to do this in MS Access?

Thanks in advance for any help you might be able to
offer...


Mike

 

[John Vinson]

Hi

I've got a set of simple data types (numbers, a text
field) that I want to store in my database. The problem
is that I don't know at design time how many of these
there will be. I can specify the set of types (for
example: int, int, text), but I don't know how many times
this set will need to be stored. Calling the above
(int,int,text) a set, I don't know how many sets there
will be. (Sorry about the repetition for those who got it
the first time...)
Is there any way to do this in MS Access?

ummm...

this is exactly how Access is designed to work.

You create a Table with the desired fields, of type int, int, and text
say; you can then add anywhere from zero to millions of records to
that table.

What you're calling a "set" would commonly be called a Record; what
you're calling a "data type" would commonly be called a Field.

Perhaps I'm misunderstanding the problem!

 

[MIchael Cloud]

Hi -- Thanks for the input. In thinking about what you
said, at first I thought you were right, but on thinking
through building a solution along the lines of what you
had said I again came upon the same problem. Let me tie
it down to specifics so that it is more clear. Here is
the problem:

I have a table a table called 'Sales'. Every entry
in the table represents one credit card sale -- One
charge on someones CC, one transaction with the credit
card company. In the data associated with a sale I want
to record (at least) the ID of each product that was sold
in that CC transaction. I know the data type of an ID
(an int), but I don't know, at design time, how many
product ID's I will need to record. Now what I was
thinking based on your comment, was, okay, I just create
a new table, whose record type is a Product ID (int), and
then have as many entries in that new table as I need.
The problem with this solution is that we have pushed off
one problem by creating a new one. The new problem is
that I now don't know how many tables I have in my
database at design time. For each Sale, I have to
create, on the fly, a new table. I am very new to
Access, but that sounds hard.
One obvious solution to this problem is that instead
of defining the Product ID entry as an int, I could
define it as an OLE object, specifically an excel spread
sheet, with a collection of product ID's. I don't like
this solution because it seems that then I lose any built
in type checking, validation etc. I have no control over
what my users will attach as a spread sheet. I think.

Comments, thoughts?

Thanks for all your help

Mike

 

[John Vinson]

I have a table a table called 'Sales'. Every entry
in the table represents one credit card sale -- One
charge on someones CC, one transaction with the credit
card company. In the data associated with a sale I want
to record (at least) the ID of each product that was sold
in that CC transaction. I know the data type of an ID
(an int), but I don't know, at design time, how many
product ID's I will need to record.

You're using a relational database; USE IT RELATIONALLY!

Take a look at the Northwind sample database that comes with Access.
It models *exactly* this scenario.

You have a many to many relationship between Sales and Products. The
way Access (or any relational database) handles many to many
relationships is with a third "resolver" table:

Sales
SaleID Autonumber
<credit card info>
<sale date>
<other sale-specific fields>

Products
ProductID
ProductName
<other info about the product>

SaleDetails
SaleID <link to Sales>
ProductID <link to Products>
Quantity
<etc etc>

If a given Sale sold a dozen products, you'ld have a dozen rows for
that sale in SaleDetails. There is *no* need for a proliferation of
fields *or* of tables; you just add *records* in the resolver table.