Table with 'free fields' to be named later?

  • Thread starter Thread starter Michael
  • Start date Start date
M

Michael

Hi,

Before i try to do something i wanted youre opinion on the following:

We have a database containing project + attendance data for instance, we
organise meetings on specific subjects.

The table with project data (meetings) contains stuff like hours to spent,
rates, who responsible etc. These are fields necesarry for each project
(i.e. are the same).

Now here comes our problem, every meeting has differences like do persons
attend workshop 1 or 2 etc., do they have lunch and so on. In other words,
characteristics specific for certain meetings.
Because of that we now export the persons to excel, add extra columns and
fill in the rest.

It would be preferable to have all the data in Access i.m.o. but.. to build
a specific table for each project is not an option.
Could we add extra fields (as a reference table) and name the fields, assign
the type of field (char, num, boolean etc.) and if its used yes or no later.

The table would be something like:
ID: project number (with a relation to the project table)
Free field 1: text field
Type for free field 1: text field where you enter "numeric", Boolean",
"date" etc.
Is free field 1 active: boolean
Free field 2: text field
Type for free field 2: text field where you enter "numeric", Boolean",
"date" etc.
Is free field 2 active: boolean
etc....

Well.. after saying this my question is if i am thinking in the right
direction and if its possible. Hopefully you might have some suggestion ot
examples?

Txs in advance,
Michael
 
Michael said:
Hi,

Before i try to do something i wanted youre opinion on the following:

We have a database containing project + attendance data for instance,
we organise meetings on specific subjects.

The table with project data (meetings) contains stuff like hours to
spent, rates, who responsible etc. These are fields necesarry for
each project (i.e. are the same).

Now here comes our problem, every meeting has differences like do
persons attend workshop 1 or 2 etc., do they have lunch and so on. In
other words, characteristics specific for certain meetings.
Because of that we now export the persons to excel, add extra columns
and fill in the rest.

It would be preferable to have all the data in Access i.m.o. but.. to
build a specific table for each project is not an option.
Could we add extra fields (as a reference table) and name the fields,
assign the type of field (char, num, boolean etc.) and if its used
yes or no later.

The table would be something like:
ID: project number (with a relation to the project table)
Free field 1: text field
Type for free field 1: text field where you enter "numeric", Boolean",
"date" etc.
Is free field 1 active: boolean
Free field 2: text field
Type for free field 2: text field where you enter "numeric", Boolean",
"date" etc.
Is free field 2 active: boolean
etc....

Well.. after saying this my question is if i am thinking in the right
direction and if its possible. Hopefully you might have some
suggestion ot examples?

Txs in advance,
Michael
Click to expand...

A couple of questions first.

Is the database copied and distributed or is their only one copy?

You say "It would be preferable to have all the data in Access i.m.o.
but.. to build a specific table for each project is not an option. Why is
it not an option?

I suspect the answer is to have a demographics table with that
information that does not change from meeting to meeting. This is likely to
include things like names and addresses phone numbers etc.

Next might be a general meeting data table with standard information
that covers that data that describes all meetings. This sounds like it will
be your master table.

You may also need a meeting specific table containing only those items
that are unique to the specific meetings, that can't be handled in the
general meeting table without adding additional fields. Many times this
kind of information may be handled in general fields.

You may want a table for projects.

Each of these tables may have relations to the others. I suggest what
you really want is:

Master Table (dates, times who attended* and projects handled*)

People Table* (Information about attendees)

Projects Table* (Information on the projects)

* Indicates a linked table.

You can then combine the data in queries as needed.
 
Some of the databases I 'inherited' from a previous employee of the company
I work for include 'spare' fields like this. They're a maintenance
nightmare, as no one now knows who's using which 'spare' field, for what
purpose. And looking at the record source of a report, and finding that it
returns all records where 'Flag2 = 1', is not very informative! :-(

Then there's the question of how many fields do you add? What ever number
you choose, in the majority of cases most fields will be unused, wasting
space and complicating your queries, and inevitably, sooner or later,
someone will need an extra field in the table.

Rebecca Riordan's article and example on 'Sub-Classing Entities' at the
following URL may be of interest ...

http://www.mvps.org/access/tables/tbl0013.htm
 
Back
Top