Results 1 to 2 of 2
  1. #1
    adakem is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Jan 2017
    Location
    London
    Posts
    1

    Exclamation how to loop through array

    Hi,



    I am a beginner with SQL, but seem to have got my head round most concepts, except I am unable to solve this one.

    I have a table that lists all countries called "Tbl_Countries". So far there are two fields - CountryName and Europe. I have another table called "Tbl_Master". In "Tbl_Master" there is a field called 'Country_List' which lists countries a person has visited - each country is separated by a semi-colon (. I need to return a true/false (or yes no) value if any country in 'Country_List' has a 'Yes' marked in the 'Europe' field in the 'Tbl_Countries' table in a new field called 'Europe'.

    Hope this is clear.

    In pseudocode style here is what I need to do:

    1- Put each country in 'Country_List' in an array, separating by the ";" symbol
    2- Check if any of these countries in the array appears in the table Country_List
    3- If it is in country_list, check if field 'Europe' = Yes.
    4 - If Yes, Return TRUE, else return false

    Example tables, with Europe Flag the sql code I am struggling on.

    Tbl_Master

    Name Countries Visited Europe Flag?
    Adam England; Japan TRUE/Yes
    John Australia; Japan FALSE/No


    Tbl_Countries

    Country Europe
    England Yes
    Japan No
    Australia No

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Code:
    In "Tbl_Master" there is a field called 'Country_List' which lists countries a person has visited - each country is separated by a semi-colon
    this is not how to store data - impossible to do in access without using a udf and will be very slow.

    you should have a separate table with one person/country per record e.g.

    tblPeople
    PersonPK autonumber
    PersonName string

    tblCountries
    CountryPK autonumber
    CountryName text
    Europe yes/no

    tblCountriesVisited
    PersonFK
    CountryFK

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Array in Loop possibly not resetting
    By laterdater in forum Macros
    Replies: 2
    Last Post: 10-26-2015, 09:57 AM
  2. VBA loop array of dictionary
    By Ruegen in forum Programming
    Replies: 2
    Last Post: 07-15-2015, 11:02 PM
  3. Replies: 2
    Last Post: 05-14-2015, 12:24 PM
  4. Loop or Array not recognized
    By dvgef2 in forum Forms
    Replies: 6
    Last Post: 05-31-2013, 08:37 AM
  5. Replies: 30
    Last Post: 08-30-2012, 05:14 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums