Making a field in a query a required field?

[MariaL]

Hello Everyone. I have a table where the need to pull three records from the same table is required in one record. Example: A project record shows the project manager, second in command and worker. All three of these people are employees and pull from the employee table. To get this record, you have to create an ID table, query each table to show the last and first name, then, query them together to be able to add the full record in one spot. Therefore, the only place you can see the full record is in the query. The record is actually stored in three different tables. The problem is, if you leave the manger blank and fill in everything else, that record will not show in the query the next time you pull it up. I tried to make all three table fields, required fields. This did not work. How can you force the end user to choose all three records in the query? Or, am I doing this all wrong from the beginning? Thank you, I appreciate your time and help.

 

[Tranman]

MariaL,
In the three queries that select the Mgr, Leader, and Worker, you can make the criteria be a function. Then when you run the "combining" query, as each employee query is run, it will run the corresponding function which requires an input. This will not require an accurate entry, but will at least make they put something in. Here is a sample:

The query is:

SELECT EMP.EMP_NBR, EMP.F_NAME, EMP.L_NAME
FROM EMP
WHERE (((EMP.EMP_NBR)=GetMgr()));


The GetMgr function is:

Public Function GetMgr()
Dim strMgr As String
Do Until strMgr <> &quot;&quot;
strMgr = InputBox(&quot;Please Enter Mgr Number&quot;, &quot;MANAGER&quot
Loop
GetMgr = strMgr
End Function

Then you have a similar query and matching function for the other two roles.

You probably already have an append query based upon the &quot;combining&quot; query.

You really should verify each entry as it's made to be sure that the person entered as manager is really a manager; etc. but that's probably for another day.

If some of this is above your head (some people are pretty reluctant about functions until they have written a couple of them), just respond with any questions and I'll help you out.

Good Luck
Tranman

 

[AvGuy]

I think you're correct; you need to rethink the problem from the beginning. If I read you correctly you have 3 tables, 1 for the project managers, 1 for the seconds-in-command, a 1 for workers. I presume each of these tables has some field in it that connects it to a projects table. I further presume that managers to projects is a one-to-many relationship in that any one manager could have more than one project and the same with the second-in-command and worker and further that these folks may change roles in various projects.

So the trick is finding some unique key where the three that form an unique project or list of projects can be extracted together.

Try this approach. Create a form with 3 combo boxes on it. In the first box display a list of all managers by name. Do the same with seconds-in-command and ditto in the third box with workers. When a user has selected all three names, give him a GO button where a query is executed against the Projects table and delivers the projects where these three employees participate in the designated roles.

Use the employeeID number as the bound element in each of the combo boxes, but display only the name. When the user has finished selecting 3 names you'll have 3 EmployeeID numbers to supply as criteria to a query that finds the unique project number.

If this works for you, consider a sort of cascading approach. Have the user select the manager first, then in the second combo box set the rowsource to an SQL statement that delivers only those seconds-in-command where the project is managed by the person selected in the choose manager combo box. Do the same for the third(worker) box delivering only worker names where the selected manager is the manager and the selected second-in-command is the second-in-command. You can set the rowsource for second-in-command in the AfterUpdate event procedure of the choose manager box and do the same for the other boxes.

Another approach, and the one I would use, is to simply build a table that contains the ID numbers for the individuals and the projects upon which they work. Then it's a simple matter to dig out the information, viz:

Project 12345
Manager 1847
Second 2642
Worker 1429

That kind of table gives you great power and speed over a variety of inquiries.

AvGuy