insert distinct records in a table (Access VBA) 1

[Shaves]

I need to insert "distinct" records in one table with a sql statement.

Following is the statement:

xSQL = "INSERT INTO HeaderFile ([Date], [Grp], [Reg], [ServLoc], [AcctNo], [InvNo], [Unit]) IN '\\C:\MyDB.mdb' SELECT DLFile.Date, DLFile.Grp, DLFile.Region, DLFile.ServLoc, DLFile.AcctNo, DLFile.InvNo, DLFile.UNIT FROM DLFile WHERE DLFile.Grp = """ & ProcGrp & """"

The DLFile contains the same information (except unit) on each line. (I didn't set up the database but I have to live with it.)

So what I would like to do is only insert the unique records baseed on Date, Grp, Reg, ServLoc, AcctNo, InvNo, and Unit.

I've seen some examples of selecting distinct records, writing them to a temp table and then writing them to the HeaderFile table from the temp table.

I'm hoping that I can do it right from the INSERT statement.

Thanks for the help.

 

[xlbo]

Set the key up appropriately on the table you are inserting into and it will not let you input non distinct records...

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[HarleyQuinn]

The DLFile contains the same information (except unit) on each line

Not sure if I've read this correctly but if Unit is different for each row then you do have distinct records...

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[Shaves]

sorry.....but the same unit number can appear on mulitple lines...

 

[PHV]

So, simply use the DISTINCT predicate:
... SELECT DISTINCT DLFile.Date, ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[HarleyQuinn]

Ah right, could you not just use SELECT DISTINCT instead of SELECT in the statement you base thwe INSERT on?

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.