Append query question

[Kivan]

Hi,



I have 3 tables, Person (which holds name of person etc.), Project (which holds name of project etc.) and ProjectMetrics (which holds particular project name and person name and some other data connected with particular person and project). Now I have an append query which appends project name (from project table) and person name (from person table) to the table ProjectMetrics. This query is created to help user assigning people to the projects (so there is a parameter ProjectName so if user want to append people to the project he write the name of that project and Voilà). The problem is user can append particular people to the project with no limit. So for example if user will run this query 3 times there will be:

Name                 Project
John Doe              Fire
John Doe              Fire
John Doe              Fire

And I want that John Doe to appear only one time in the project even after running query 3 times. Is there any possibility of doing this? Ah and I would't like to make Name and Project as primary keys in the ProjectMetrics table.

Here the SQL code.

PARAMETERS ProjectName Text ( 255 );
INSERT INTO [ProjectMetrics] ( Person, Project )
SELECT Person.IdPER, Project.IdPRO
FROM Person, Project
WHERE (((Project.[ProjectName])=[ProjectName]));

[orange]

I would suggest that the fields in your Projectmetrics table be changed to PersonId and ProjectId. Both of these should be long integer type. (The fields as you have them would have same names as your tables -- confusing at best),
In table ProjectMetrics make a unique index on the combination PersonId + ProjectId

Perhaps you already have this
-In your Person table, I would have a PersonId field that is autonumber and PK.
-In the Project table, I would have a ProjectId field that is autonumber and PK.

When you do this and run the query multiple times, Access will give you an error message saying that if you were to store that data you would create a duplicate record. So you will need to look for error conditions.

[Kivan]

Works perfectly, I didnt even know that there is possibility like that. Thanks a LOT .

[orange]

Glad to help.