Good Morning All,
Is there a *quick* way of checking if data exists in a table during an import process?
We have data that is collected manually in a spread sheet that I would like house in a table, currently it is something in the region of 50K records in size, periodically older entries will be changed so the corresponding entry in the table will need to be amended to.
The excel table has 5 fields, the first 4 are categorisations and the final field a count, the access table has the same fields. I've tried a variety of methods to search for the matching entries in the table (loading the table into an array, a sql select query matching on the fields values, and dlookups but all seem quite slow, taking an hour or so to run through.
In essence I need something that will take 5 pieces of data from the excel sheet (Date, Catergory1, Catergory2, Catergory3, Catergory4, Tally), match this against the entries in the table; if all match skip to the next entry, if all match but the tally is different, update the tally field in the table with the new value, and finally if date, carergory1, catergory2, catergory3, catergory4 cannot be found add a new entry.
If anyone can point me in the right direction I'd be very grateful.