Importing duplicates

[phillsheen]

Morning everyone!

I have a little problem Im sure you can help me with.
At the moment I get a spreadsheet from the bank every month which i import into our database. At the moment I do this on the last day of the month first thing in the morning to ensure there are no duplicates.
I was asked by my boss to make this process more fool proof other staff can do the import while I might be away. What he would like is a process that can be done at any time of the month with out the worry of duplicates.
The import table looks like this:

Date/Description/Amount
01/06/08 Member001 £35
01/06/08 Member002 £35
02/06/08 Member003 £35
03/06/08 Member001 £35

Problem is I cant put a simple 'no duplicates' on the fields becasue as you can see there can be many of the same date, description and amount. What I would like to do is make it so that when I import the records, where all the fields are the same, it puts the duplicate record in a 'duplicate table' as it does with errors when you do a normal import.

Im sure this would be possible but Im not sure what Im looking for.
Any help would be fantastic!

Cheers
Phill

 

[namliam]

With most bank applications you can tell it to export up to a certain date... why not use that to ensure that you import untill yesterday. Then import that into your DB.

Then next week you start your export from today.

Finaly a check to see if the Max(allready imported) < min(New import) and you are ensured of no duplicates.

 

[phillsheen]

Cheers namliam for hte reply.
The reason I can no longer do the frist suggestion is that i dont know who will be downloading the bank details now so mistakes are more likely. I would like to make the importing fool poof rather than replying on someone getting the right date.

I also missed out something that would make the second idea not work. We also take cash payments from members at various times in the month and these also are taken into the same payments table. So if a client makes a cash payment on the 15/06/08 it will be recored but when we import the bank payments we would still want all records before the 15th not just hte ones after.

Thanks for the reply!!
Any more suggestions??
Phill

 

[Mike375]

Make a query on the imported table with a calculated field added to join the 3fields

NewFieldName:[Date] & "" & [Description] & "" &] & "" & [Amount]

Have it as a MakeTable query or an Append query to a table that has been made from the MakeTable query. Using & "" & will mean there are no spaces but if you want a space then & " " &

On your example none of them would show as duplicates

01/06/08 Member001 £35
01/06/08 Member002 £35
02/06/08 Member003 £35
03/06/08 Member001 £35

 

[phillsheen]

Hi,
Thanks for the reply!
Where abouts do i put the code '"NewFieldName:[Datepaid]" & "" & [Description] & "" & [Amount]'?
Does it go in the SQL view or the Design View?