Duplicate records in Access table

[FastLearner]

Hi,

I have a macro to copy the data from Excel to Access 2007 table. I only copy one row of the data to Access table with the time of the export. So in the table I will have more than one record for any given date with time format as "mm/dd/yyyy".

My requirement is that if I am submitting the data to Access for the second time in the day, I should over write the previous record in Access table instead of appending the next row.

Any work around or new way of doing this is greatly appreciated.

Thanks,

[OBP]

FastLearner, welcome to the Forum.
I would approach this by making the Date field Indexed with no duplicates.
I would then import the data to a Temporary Table and run an Append Query to add new Records and an Update Query to update records that are already there.

I am not clear on whether you Table allows more than one record per date, but with different Criteria like a CustomerID or Name.
Or if you are only allowed one Record per day.

If you are allowed more than one record then I woul combine the ID and Date fields as a new field and make that Indexed & Unique.

[FastLearner]

Thanks very much for your response. You have got my problem correctly. The Date & Name are the fields that should be unique per day.

If I import the Date, Name & other statistics to access table, then I need to have only one record at any date for the name. But the agents will upload more than once in a day with their name & other statistics. So I can't Indexed and set the uniqueness check for the date & name, because they can't enter more than once.

They can enter more than once, but it should append the previous record if it already exists by the Date & Name match.

I am okay with temp table and append, but can you please elaborate more on that. Thanks very much again.

[OBP]

FastLearner, I would create a field called NameandDate and Combine the Name and Date fields in to it in the Append Query.
You can run a query to update any records that you already have in the Table.
Set that field to Indexed and Unique.
When you run an append Query that field will exclude any Names that are already in the table for that Date.
When you Run the Update query the temporary Table can be joined to the Real table via the new Field.

I think Access 2007 allows you combine Fields in to a Single Unique key and you may be able to use that, but I prefer to control the Key myself.

[FastLearner]

Dear OBP, Actually I'm using .New ro add the values into Access table as new row from Excel. I can write another field with DateandTime.

My macro code looks like this:
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _
"Data Source = C:\mydata\Salesdata.mdb;"

On Error Resume Next

'Open Recordset in Access table
Set rs = New ADODB.Recordset
rs.Open "Tracking", cn, adOpenKeyset, adLockOptimistic, adCmdTable

Range("M80").Select
ActiveCell.FormulaR1C1 = "=TEXT(NOW(), ""mm/dd/yyyy hh:mm:ss AM/PM"")"
Range("M81").Select

'Get all cells in the row 80
r = 80
With rs
.AddNew ' create a new record
' add values to each field in the record
.Fields("Field1") = Range("C" & r).Value
.Fields("Field2") = Range("D" & r).Value
.Fields("Field3") = Range("E" & r).Value
.Fields("Additional_Info") = Range("F" & r).Value
.Fields("Field4") = Range("G" & r).Value
.Fields("Field5") = Range("H" & r).Value
.Fields("Name") = Range("I" & r).Value
.Fields("DateEntered") = Range("J" & r).Value
.Fields("DatenadName") = Range("K" & r).Value

' add more fields if necessary...
.Update ' stores the new record


End With


rs.Close
Set rs = Nothing
cn.Close

Set db = Nothing
If Err.Number = 0 Then
MsgBox "The data has been updated successfully. Thank you"
Exit Sub
End If
End Sub

How to proceed after this.

Your assistance is very much appreciated.

[OBP]

Is Tracking a temporary Table, if not a Duplicate will crash the VBA code if you are using the main table.

[FastLearner]

Actually I didn't set any unique cheque so it will allow duplicate records. Now if I remove the time part and take only Date & Name how do I use your method.

For now, Tracking is my main table.

[FastLearner]

Now, Tracking is my temp table and after writing one record in to this temp table I want to append to master table with duplicate check on the "DateandName" field. Can you please advice me how to append to the new table and over write if the record found.

Thanks very much for your time.

[OBP]

In the Master Table make the DateandName field Indexed & Unique, you may have delete any Duplicate Records before you can do this.
You can create a "Find Duplicates Query" using the Find Duplicates Query Wizard on the Query Tab Main Menu>New icon.
Create a Query based on the Tracking Table and then on the Query Design Menu>Query click the Append icon to change it to an Append Query.
Access will ask you to select a Table to Append the data to, Select the Master Table and in the Append To row select the fields in the Master table that each one should go in to.

In the VBA that transfers the Excel Data (you can actually use Transfer Spreadsheet from within Access to do that) add a line of code that runs the new Append query.

[FastLearner]

Dear OBP, I did follow your instructions, but the master tracking table is not getting update.

My Tracking table is:
TrackingIDTracking_DateEmployee_NameSalesProposal112/4/2009Paul Kerner$25001


Master TrackingIDTracking_DateEmployee_NameSalesProposal112/4/2009Paul Kerner$25001


When Tracking table becomes

TrackingIDTracking_DateEmployee_NameSalesProposal112/4/2009Paul Kerner$25001
1 12/4/2009 Paul Kerner $45003

Then Master Tracking should be updated as:

Master TrackingIDTracking_DateEmployee_NameSalesProposal112/4/2009Paul Kerner$45003

This is my requirement. Your time is greatly appreciated.

[FastLearner]

Dear OBP, Would it be possible to call you on any number for few mins. Really need to resolve this today. Your time is appreciated.

[OBP]

FastLearner, my wife doesn't allow posters to contact me by phone.
But I will try and help you now.
Did you create the Update Query?

[FastLearner]

After creating the duplicate records query wizard, and making it as append query I receive an error while executing the query. It says the (AgentNameanddt) field couldn't be found. Could please help me here

[OBP]

Fastlearner, I have private mailed you my email address, please note that i am in the UK and I do not "work" on problems after 6.00pm here.