If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Prevent duplicate append - nothing unique
Help! I have been working on this all day and
cannot get my head around a solution.... I am running Access 2003. I have created the following select query: SELECT ScrapData.ScrapDate, ScrapData.PartNo, ScrapData.Description, ScrapData.CostCtr, ScrapData.Value FROM ScrapData GROUP BY ScrapData.ScrapDate, ScrapData.PartNo, ScrapData.Description, ScrapData.CostCtr, ScrapData.Value, HAVING (((ScrapData.ScrapDate) Between [Forms]![frmSelectCMCC]![FromDate] And [Forms]![frmSelectCMCC]! [ToDate])) ORDER BY ScrapData.ScrapDate; called "qryPart1CM" from there, I have created an append query to my countermeasure table: INSERT INTO Countermeasure ( PartNo, Description, Cost, CostCtr, WeekNo ) SELECT TOP 3 qryPart1CM.PartNo, qryPart1CM.Description, Sum(qryPart1CM.Value) AS Cost, qryPart1CM.CostCtr, qryPart1CM.WeekNo FROM qryPart1CM GROUP BY qryPart1CM.PartNo, qryPart1CM.Description, qryPart1CM.CostCtr, qryPart1CM.WeekNo HAVING (((qryPart1CM.CostCtr)=[Forms]![frmSelectCMCC]![cboCC])) ORDER BY Sum(qryPart1CM.Value) DESC; Basically, each user responsible for a specific "CostCtr" must enter countermeasures for Scrap data on a weekly basis. The countermeasures are for the "Top 3" scrap parts by value. I cannot open the form based on the query as it is not updatable, so I thought I would run an append query to a table and bind my form to it. However, I need to prevent duplication to the "Countermeasure" table and cannot figure out a way to create a unique field to accomplish this.... Can anyone help? |
Thread Tools | |
Display Modes | |
|
|