Append Query to conditionaly allow duplicates

[JIMR]

Using Windows XP
MS Access 2003 using 2000 format

I have a Maintenance Management database I am attempting to create and at the point of generating Work Orders using an append query

I have searched the forum and found a few similar threads but they don't (I think) address my specific issue.

I am pulling information together using the append query and then appending the to the WO table.

Here is what I am trying to get the take place here. 3 fields are being used to identify and eliminate duplication; Description, Asset# and Status.

Example data for fields

Description - Monthly Conveyor Lube Route Z1
Asset # - CV50039
Status - OPEN

The only condition that I want to allow duplicates is if the status is Closed. There are 5 other status's that I do not want a duplicate. In other words I can have 50 WO's that match Description = Montly Conveyor Route Z1, Asset # = CV50039 and Status = CLOSED but only 1 WO that has a status other than Closed.

Any Suggestions?

 

[Guus2005]

You can extend your primary key or extend your primary key. You could add the date and time to it. Take your pick.

HTH

 

[JIMR]

Hmmm, I have been tossing around the idea of using the date.

Any other possibilities?

 

[gemma-the-husky]

one way is to allow duplicates in general, in the table

but check where appropriate to make sure you dont allow duplicates where you dont want them.

Also,
you could add a count to the record, thats always a 0 in general, but allows increments in the case of the "multiple" status. Then a combination key could be used to would reject multiple 0 values.

 

[JIMR]

one way is to allow duplicates in general, in the table

but check where appropriate to make sure you dont allow duplicates where you dont want them.

Also,
you could add a count to the record, thats always a 0 in general, but allows increments in the case of the "multiple" status. Then a combination key could be used to would reject multiple 0 values.

Not sure I am understanding how you can "allow duplicates in general" then check "where you don't want duplicates". Assuming your meaning in a single table, doesn't it have to be either one or the other?

 

[JIMR]

So I was thinking that the count was making more sense for what I am trying to accomplish and then something occurred to me. there is a standard metric used in my field for the number of times a Preventive Maintenance Work Order is performed. This may be the best way but would like to see what your response is to my previous post.