Tough question.. I think

Results 1 to 5 of 5

Thread: Tough question.. I think

  1. #1
    Join Date
    Dec 1969

    Default Tough question.. I think

    Hi there,<BR><BR>here is my scenario:<BR><BR>I have four different "city" fields (eg. city1, city2, city3...) in my access database. Now, I want to be able to search through all the 4 cities fields but only display the "distinct" total of the 4 cities combined.<BR><BR>For example.. <BR>if one member in my database has the following cities listed:<BR>City1 = New York<BR>City2 = Chicago<BR>City3 = Los Angeles<BR>City4 = San Diego<BR><BR>and another member has the cities listed:<BR>City1 = Chicago<BR>City2 = New York<BR>City3 = Orlando<BR>City4 = Miami<BR><BR>then after running a search from a searchpage I only want to display: New York, Chicago, Los Angeles, San Diego, Orlando, Miami<BR><BR>instead of:<BR>New York, Chicago, Los Angeles, San Diego, Chicago, New York, Orlando, Miami<BR><BR><BR>What would be the best way of doing this?<BR>(I hope this is somewhat understandable)<BR><BR>Thanks in advance for all your help!<BR><BR>andre<BR>

  2. #2
    Rokea Guest

    Default RE: Tough question.. I think

    Well, thats only a question of programming logic.. so there are many may ways to solve this.. the first thing i can think of is to first:<BR>Retrieve all the users from your database with their cities (in a recordset).<BR><BR>2nd:<BR>Create an Array capable of containning (4*Number of users) fields (cuz you said you have 4 cities per users).<BR><BR>3rd:<BR>Take the first user, store his 4 cities in the first 4 fields of your array.<BR><BR>4th:<BR>Take the first city of user 2, and check in all the array field if youv already got that city name entered. <BR><BR> -If the name already exist, stop and repeat step 4 (this time with city # 2). <BR> -If the name dosnt yet exist in your array, go at the first unoccupied place in your array and insert the citys name. Then repeat step 4 (this time with city #2).<BR><BR><BR>Keep on looping cities like thism and change user when youv checked all 4 cities for the current user, and that&#039ll do it.<BR><BR>Hope this helps.<BR><BR>Rokea<BR>

  3. #3
    jason Guest

    Default RE: Tough question.. I think

    Not sure if Access will do this, but I believe SQL might. If you can select the 4 cities into a one column temp table, you could then do a select distinct on the one column, that would leave you with a distinct list of all cities.<BR><BR>Hope that helps.

  4. #4
    Rokea Guest

    Default RE: Tough question.. I think

    I dont think thats a very good way of doing it, because it suposes that the cities are always entered in the same order.<BR>(disctinct will MAKe the distinction between OrlandoMiamiNewYorkBaltimore and BaltimoreOrlandoMiamiNewYork, which is not what we want.<BR><BR>Rokea

  5. #5
    dhasza Guest

    Default RE: Tough question.. I think

    do 4 select distincts; union them and then use the union as a subquery and select distinct from that<BR><BR>select .... from (select... union select ... union select ... union select...)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts