In message <[EMAIL PROTECTED]>, rkw1956 <[EMAIL PROTECTED]> writes >--- In [email protected], "rkw1956" <[EMAIL PROTECTED]> wrote: >> >I should have added that the duplicates refer to other companies that >have listed the same make in their own recordset. > >Example: >dealerId: 1 >company: ABC Motor Company >makes: Ford,Toyota,Jaguar,Vauxhall > >dealerId: 2 >company: DEF Motor Company >makes: Renault,Mazda,Jaguar,Fiat > >In this example Jaguar is a duplicate value and only one instance >should appear in the drop down list. > >Richard
Your problem is that you have designed your database incorrectly. You should have a Dealer table, which is what you already have. dealerID 1 dealercompany ABC Motors dealeraddress... phone... etc But then you should have a Makes table. makeID 5 makename Jaguar makeID 6 makename Mazda Finally, you should have DealerMakes table DealerMakesDealer 1 DealerMakesMake 5 DealerMakesDealer 2 DealerMakesMake 5 DealerMakesDealer 2 DealerMakesMake 6 If you make the key for DealerMakes as DealerMakesDealer + DealerMakesMake, and make it UNIQUE, you will never any duplicates. With these tables, you can easily see which Makes each Dealer carries, and you can also find which Dealers carry which Make You are breaking 2 basic rules of design: Never have the same information more than once (you have Jaguar in your example twice), and never have one field carrying more than one piece of information (ford, toyota...). This design would not only mean that your current problem could never arise, but in the future, when Jaguar make CityCars and CountryCars, you can find and expand your information about Jaguar, because it is all in one place, in the Makes table, and if you decide to make a Models table, you know where to attach it. And don't say that your databases will never change in the future, I have been working on databases for years, and still haven't found one that hasn't changed as situations in the real world change. -- Pete Clark Advertise your events in Spain - local and free http://hotcosta.com/events.php
