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" wrote:
> 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
>
>
> JLPerez wrote:
> >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
>
> --
> Message posted via http://www.accessmonster.com
>
>
Similar Threads
