By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,705 Members | 1,852 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,705 IT Pros & Developers. It's quick & easy.

Insert DISTINCT Records

P: 14

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??

Jan 27 '09 #1
Share this Question
Share on Google+
4 Replies

Expert 100+
P: 489
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.
Jan 27 '09 #2

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

Jan 27 '09 #3

Expert 100+
P: 489

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.

Expand|Select|Wrap|Line Numbers
  1. INSERT INTO t_affectedJobs ( JobNumber, IssuedQuanity, IssuedDate, JobStatus, NCMRIdent )
  2. 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
  3. 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
  4. 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"));
Jan 27 '09 #4

Expert Mod 15k+
P: 31,411
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.
Jan 27 '09 #5

Post your reply

Sign in to post your reply or Sign up for a free account.