There's no program that can do it for you. It requires relational database
development skills to design the db correctly. Microsoft pushes multivalued
fields because they want potential customers to think building a db app is a
no brainer, any kid could do it. But the truth is any kid could make a real
mess of it if they didn't have any training.
So here's an example for you.
Say you have a table for project assignments, and one or more employees can
be assigned to each project. A beginner in Access 2007 might think "I'll add
a multivalued field so I can pick Jack, Randy, Sue or Mary or any
combination" for each project and builds the table that way. But when it
comes to queries, imports and exports on that table, confusion reigns.
What that multivalued field represents is a many to many relationsip between
projects and employees. This is modeled in relational databases as three
tables with a one to many relationship between employees and
projectassignments and a one to many relationship between projects and
projectassignments. Here's how it works out:
Instead of a multivalued field you should design a table with each of the
employees's names. You can add an autonumber as a surrogate primary key to
make things easier. So the employees table has two columns, empid
(autonumber) and empname (text), and four records:
empid empname
----- -------
1 Jack
2 Randy
3 Sue
4 Mary
The empid is the primary key of the employees table and it's going to be the
foreign key of another table, the projectassignments table (the table that
the beginner thought needed a multivalued field).
But first we need to define a projects table. It has the following columns:
prjid autonumber
prjname text
startdate date
completiondate date
prjid is the primary key of the projects table and we need to define it
before the projectassignments table because this column is going to be a
foreign key in that table, too.
The projects table has two records:
prjid prjname startdate completiondate
----- --------- --------- ----------------
1 Payroll 9/15/2008
2 Benefits 10/1/2008
Next we define the projectassignments table:
prjasmtid autonumber
prjid long
empid long
The prjasmtid column is the primary key, prjid is the foreign key to the
projects table, and empid is the foreign key to the employees table. We
build a query to use as the source for our form for data input:
SELECT prjasmtid, prjid, empid
FROM projectassignments
ORDER BY prjasmtid, prjid, empid
We use the form wizard to build a form using this query as the source. For
the prjid and empid text box controls, these must be converted to combo boxes
so that the user can see the project name and employee name instead of
numbers. Before doing that make two queries:
qryemps
----------
SELECT empid, empname
FROM employees
ORDER BY empname
qryprojects
------------
SELECT prjid, prjname
FROM projects
ORDER BY prjname
For the combo box on prjid, rename the label as "Project Name" and use these
properties for the combo box:
row source = Table/Query
row source = qryprojects
bound column = 1
column count = 2
column width = 0";1"
For the combo box on empid, rename the label as "Employee Name" and use these
properties for the combo box:
row source = Table/Query
row source = qryemps
bound column = 1
column count = 2
column width = 0";1"
Now save the form and open it in form view. To add both Jack and Randy to
the Payroll project requires two records (because there are two employee
assignments). The first new record, select:
Project Name: Payroll
Employee Name: Jack
Next new record, select
Project Name: Payroll
Employee Name: Randy
To add Sue and Mary to the Benefits project also requires two records. The
first new record, select:
Project Name: Benefits
Employee Name: Sue
Next new record, select
Project Name: Benefits
Employee Name: Mary
Now comes the fun part, we want to query the db to find out who is currently
working on the Payroll project. We use the QBE query designer to add three
tables to the grid, employees, projectassignments and projects, and then add
three columns, PrjAsmtID, EmpName, PrjName. We set the criteria to PrjName =
"Payroll"
Here's the resulting query:
SELECT PrjAsmtID, EmpName, PrjName
FROM (ProjectAssignments INNER JOIN Employees ON ProjectAssignments.EmpID =
Employees.EmpID) INNER JOIN Projects ON ProjectAssignments.PrjID = Projects.
PrjID
WHERE PrjName = "Payroll";
Run the query and get this:
PrjAsmtID EmpName PrjName
1 Jack Payroll
2 Randy Payroll
We can easily see that we have two employees, Jack and Randy, working on the
Payroll project.
And who's working on the Benefits project? Here's the query for that:
SELECT PrjAsmtID, EmpName, PrjName
FROM (ProjectAssignments INNER JOIN Employees ON ProjectAssignments.EmpID =
Employees.EmpID) INNER JOIN Projects ON ProjectAssignments.PrjID = Projects.
PrjID
WHERE PrjName = "Benefits";
Run the query and get this:
PrjAsmtID EmpName PrjName
3 Sue Benefits
4 Mary Benefits
We can easily see that we have two employees, Sue and Mary, working on the
Benefits project.
Later on when we hire more employees and create more projects for them to
work on, the new employees will easily be added to the employees table, the
new projects will easily be added to the projects table and the form will
easily be used to assign employees to projects. Any future queries on who's
working on which projects can be made by modifying the criteria of the
prjname column in the query grid.
Chris
Microsoft MVP
You are right.. Beginners don't have the "experience" than experienced
developers.. I realized that now.. if it is advisable not to use
"multi-valued" fields, is there a program that executes that same
multi-valued funcionality? Funny to think that MS would promote multi-valued
fields as main reason to upgrade.... oh well....
Beginners use multivalued fields because they think it's easier, experienced
developers know better. Redesign your tables so they're normalized and
[quoted text clipped - 9 lines]
contains a multi-valued field".. The main table has multi-valued fields. Can
this append query be done using vba and avoid this error? Learning Access
