Insert DISTINCT Records

[buddydasari]

All

I want to run an insert query to insert new records but only distinct one's.Is there any direct query syntax to do that.I have an idea to write a function to check the records one by one but that will be cumbersome if dataset is large.any other ideas??


thanks
Sree

[DonRayner]

While in the query design view , click Properties on the toolbar to display the query's property sheet.

To prevent showing duplicate records in a query based on fields in the underlying table, Set the UniqueRecords property to yes. This would be SELECT DISTINCTROW if you were writing the SQL in VBA

To prevent showing duplicate records in a query based on fields in the query design grid, Set the UniqueValues property to yes. This would be SELECT DISTINCT if you were writing the SQL in VBA.

[buddydasari]

DonRayner
But my query is in the coding part and moreover its not a selection query its an insertion query.


thanks
Sree

[DonRayner]

@buddydasari
Sree,

Write your insert query and add either DISTINCT or DISTINCTROW after the SELECT statement. Here is an example of one of my own insert queries.

INSERT INTO t_affectedJobs ( JobNumber, IssuedQuanity, IssuedDate, JobStatus, NCMRIdent )
 SELECT DISTINCT dbo_Material_Req.Job, -[Quantity] AS PickQuantity, dbo_Material_Trans.Material_Trans_Date AS Expr1, dbo_Job.Status, Forms!t_NCMaterial!idholder AS Expr1
FROM dbo_Material_Trans, dbo_Job RIGHT JOIN (dbo_Material INNER JOIN dbo_Material_Req ON dbo_Material.Material = dbo_Material_Req.Material) ON dbo_Job.Job = dbo_Material_Req.Job
WHERE (((dbo_Material.Material)=[Forms]![f_NonConformance_add]![NCBatchNumber]) AND ((dbo_Material_Trans.Lot)=[Forms]![f_NonConformance_add]![MaterialLotNumber]) AND ((dbo_Material_Trans.Tran_Type)="issue"));
 

[NeoPa]

@buddydasari
So you have two options. Both of which Don has already covered :

1. Change your SQL in the way indicated. Adding DISTINCT or DISTINCTROW (according to your needs) as a predicate after the SELECT keyword.
2. Create the INSERT (append) query in the QBE grid and change the same properties as you would in a simple SELECT query.
   When happy with the query use the SQL view to copy the resultant (working) SQL to your code.