Duplicate key when populating Access DB

[ohmbru2]

I'm using DTS to manipulate some data in Access. Today I do this within Access, but I want to use DTS so I can schedule a job to run in the dark of night.

The process I use today is to run a query that appends records to a table. The data contains an Id field that is not unique. I want only the first occurence of the reocrd so I've configured the destination table with the ID as the key so subsequent records with that ID are rejected.

My attempt to do this with DTS failed because the of duplicate keys. Access warns you that the records were not added, but the DTS job stops altogether:

"The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship..."

I've tried to design a query that results in the exact recordset I need, but that has not been successful.

I also looked for a way to get around this in DTS but it seems limited.

If anyone has a creative solution for me, I would be glad to hear it.

 

[unclerico]

can you expand on what you mean by "the first occurence of the record"?? Are you sorting the result set first, for example:

SELECT field1, field2, field3, field4
  FROM table1
 ORDER BY field1

or can you get the MIN or MAX of a field like this

SELECT field1, field2, field3, MIN(field4)
  FROM table1

 

[ohmbru2]

I'm after the rows with an asterisk:

ID DateTime Type
1 6/1/2006 7:59:00 Blue*
1 6/1/2006 7:03:00 Red
1 6/1/2006 7:01:00 Pink
2 6/8/2006 7:01:00 Pink*
2 6/7/2006 7:01:00 Blue
3 6/13/2006 7:01:00 Pink*
3 6/10/2006 8:01:00 Orange
3 6/10/2006 6:01:00 Red

 

[unclerico]

So have you tried something like this:

SELECT [id], MAX([datetime]) [datetime], type
  FROM table
 GROUP BY [id], type

That should output

ID  DateTime          Type
1   6/1/2006 7:59:00  Blue
2   6/8/2006 7:01:00  Pink
3   6/13/2006 7:01:00 Pink

 

[AlexCuse]

From what I understand here, you want to ONLY insert a record into the Access db if it's the max datetime? Provided all the data in Access is always contained in the SQL server table, I would add a step to the dts and first truncate the table in access, then use unclerico's query to populate it.

If you want to be comparing the records and keeping anything in access (ex an ID that does not exist in SQL Server), you'd need to set up another constraint on your delete query like this:

delete from [access]
where access.[id] = sqlserver.[id]
and access.[datetime] = sqlserver.[datetime]
and access.[type] = sqlserver.[type]

Without knowing what exactly you are trying to do, this is the best I can come up with.

Hope this Helps,

Alex

PS - UncleRico, great handle!!