Create append/delete query into a table that has multi-valued fiel

J

JLPerez

Hi..
I'm trying to convert a SQL query into a table with a name that the user
chooses. However, when I try to use "append" I get this error " Select * can
not be used in a INSERT INTO query when the source or destination table
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
Thx
 
C

Chris O'C via AccessMonster.com

Beginners use multivalued fields because they think it's easier, experienced
developers know better. Redesign your tables so they're normalized and
you'll avoid the problems with multivalued fields.

Chris
Microsoft MVP
 
J

JLPerez

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....
 
C

Chris O'C via AccessMonster.com

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]
 
L

Linq Adams via AccessMonster.com

"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."

IMHO, Chris is right on the money here! Microsoft's hype promoting Access as
a tool that anyone can use to develop databases is responsible for at least
75% of the questions that appear here and in other Access forums!

Suits in the front office believe all the hype, ask if anyone has any
experience in Access and then assigns them to a project that would take an
experienced developer months to complete.

The "experience" the employee has usually turns out to be a single semester
class not in Access, but rather in the entire MS Office suite, which means
they actually spent about a week or a week-and-a-half on Access, learning how
to put together a very simple database using nothing but Wizards!

And so they end up here, lost as a ball in high weeds! Microsoft really does
everybody a disservice with all their hype promoting this idea!
 
J

JLPerez

Chris;

THANKS for writing this lengthy explanation. Your effort is appreciated. I
see now that I have to redesign some parts of the project. I'll take into
consideration all your pointers.. Thanks again

In defense of all the "newbies" programmers out there, you guys (experts)
can't blame us for trying to utilize the funtionalities that Access offers or
claims to offer. It is obviuos that most of us wouldn't be asking for help
here if the software works as it's supposed to. The fact that your experience
and expertice has made you guys arrive to the conclusion that multi-values
field doesn't work or other issues, is a valuable asset to this post.
However, there is no need to treat "newbies" in a condesending tone. Ling
Adams is right to point out that most of us who "claim" to have experience
could be one of those one-class MS suit. We are trying to learn and get to
your level. Trust me, as you said, I really don't have that much time to
waste searching for answers. I do appreciate your valuable contributions and
acknoledge the fact you guys are volumteers. Remember, you guys were not
experts when you started. Time and practice makes perfect (almost).

Again, Thanks for this answer and I'll take this into consideration in the
future...

Chris O'C via AccessMonster.com said:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top