Access 97 - Create Multiple Tables

Try this. First make a copy of your database.
Go to Tools>Analyze>Table and let the Wizard walk you through the process. (I think it was available in A97).
You can have an Employee table, a Projects table, and then an ?Assignment? table that more or less just includes EmpID and ProjectID and perhaps an assignment date or other such information.
Then use a Query to show employees assignments and their projects.

Answer #1 gives you the basic correct way to accomplish the end results you are looking for. Access 97 is a relational database. Having one table to list the employee data (ID, name, etc.) and a second listing the projects (project, who assigned to, perhaps dates) will provide what you need. Queries will enable you to provide a list of each employee and his/her projects.

A very nice way to accomplish this is to create a form with two tabs. The first tab would be the employee data, with arecord source for the form of the employee table.

The second tab would be the projects for that employee. It would use a subform which would be based on a query from the projects table with the criteria that employeeID (in projects table) = the contents of the employeeID control on the form (under the employee tab). You could then put an export projects button on the main form's footer (or header) that would output the data in the projects query to a text file as you mention you need. Thequery will only show the projects for the specific employee currently displayed on the form.

Creating a separate table for each employee defeats the purpose of using a relational database.

You could even go further and add a table with the projectID, status, and date. Or, if there is ever a possibility that more than one employee will be associated with a project, have a separate table with the projectID, the employee ID, and perhaps a date and even a comment field (to indicate the employee's level of responsibility in the project). The method I described above still applies: create a form with the employee ID (even a combo box to just select an employee just for the reports/export) and use a query with the criteria that employee ID = the contents of the control on the form.

Using one table listing all products for all employees is the best method as stated above. For your export you can create a query that filters to the desired employee and then base the export on the query.

If you still want to create seperate tables, then I would add a command button to the form and then in the code window on the Click event use the EXECUTE method of the db object to create the table:

Dim strTableName As String
strTableName = "[Products Table for " & strEmployeeID & "]"
dbs.Execute "SELECT Employees.* INTO " _
& strTableName & " FROM Employees;"

Instead of using Employees.* you can list the specific fields desired (EmployeeID, Product, etc.). You can also select fields from more than one table by using an INNER JOIN in the FROM section of the SELECT statement:

FROM Employees INNER JOIN " & strTableName & " ON Employees.EmployeeID = " & strTableName & " .EmployeeID;

If the export table is to be re-created each time then add the following code prior to the above dbs.Execute statement:

dbs.Execute "DROP TABLE " & strTableName & ";"

Queries can be exported as text. The information can even be used in Word format; either directly, or as a mail merge.
Queries are just a filtered look at the data that can be adjusted by changing the Criteria.