Forum Home
Press F1
Thread ID: 49024	2004-09-08 06:29:00	spreadsheet sorting	Nigel Thomson (629)	Press F1

Post ID	Timestamp	Content	User
270309	2004-09-09 09:13:00	Hi Nigel I had a go at importing your sample file and finding the duplicates and think I have the answer. Apologies in advance for the long reply but I needed to do some explaining along the way. Assuming you are (sort of) happy with table 3 then I will work with that (plus it's in the right layout to support the many-to-many relationship we discussed some time ago). Firstly, I'm going to assume that where there are duplicate entries that you actually want them stripped out as opposed to using introducing another variable such as quantity. (Food for thought : if model b2 has part number 334b twice, does this mean that part is used twice in the model? Or is it a scan / import problem? If it is used twice then would you want that quantity recorded somewhere in your model / parts table?) I imported your file into access into a new table (I used a txt version and stripped out the dots using a find and replace function), on importing the file I gave appropriate names to the various fields in the process (i.e. "model number", "part number" & "desc" - I have used these variable names in my example below). The trick here is to not have your indexes set up, in other words I imported it into a new (temporary) table with no indexes. If you try to import that data into a table with the indexes already defined then you will get errors for the duplicates (as you did with your append query) - you can either accept the import / append errors and let the duplicates drop out (which is not what you want), or you can identify the errors beforehand and maybe do something about them. From your post it appears you would rather identify the duplicates and then do something about them so it would be better to import the data into a temporary table (e.g. table = "import_test_xx" or named something else) First up, it is much safer to do your importing etc into temporary tables, that way if you stuff it up then you won't muck up your real tables that are the foundation of your database. Once you are happy with the data in the temporary tables then you append the data to the real tables using an append query (possibly as part of a macro for a higher level process?). Anyway, once your data is in the temporary table (duplicates and all given there are no indexes), to create a new query to identify the duplicates, do the following (pardon the completeness of the instructions) : Query, New, Design View, select table "import_test_xx" (or whatever you have called it), Add, Close, first field select "model number" (or whatever you have called the model variable), second field select "part number", third field select "desc" (the part a, part b bit), View, Totals, change the "group by" for desc to "Count", in the criteria field under "desc" enter ">1" (without the quotes), View, Datasheet View - this will give you a list of the duplicate entries and the number of times they have been duplicated. Save the query. I think this has answered your question, but the next thing I think you should look at is why the duplicates are appearing. Were the duplicates in the original data that you scanned? Or did they appear only after you converted from table 1 to table 3? If that is the case, then you might want to have another look at your query that was used to create table 3. If they were in the original data that's cool but I would be concerned about stripping data out that might be needed (in other words, there was no duplicate, the append query inadvertently changed the data and now we strip out the duplicate and the original record may be lost) - so, you might want to have a count of some sort between the various steps - count the number of part records before and after the various stages of converting from one table to the next. This way you will know your data is incomplete. HTH, Andrew	andrew93 (249)
1 2