Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    3 Star Lounger siflaar's Avatar
    Join Date
    Jan 2001
    Location
    Amersfoort, Netherlands
    Posts
    203
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Query problem (97)

    Hello,
    A friend of me has the following problem: He has a table with names, a table with languages and a table to connect the data where he selects names and connect them to languages. The Connection table makes use of the lookup wizard.
    So far so good. But now he wants te create a query to see all names who speak "Dutch" and "English" and that is not working.
    Who could help my friend?

    Greetings,
    Patrick Schouten
    Greetings,

    Patrick Schouten
    (The Netherlands)

  2. #2
    Lounger
    Join Date
    Jan 2001
    Location
    Slough, Berkshire, England
    Posts
    46
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query problem (97)

    I think that a little more information is needed before I could offer any advice.
    Can you outline the structure of the tables and the query he is using?

    David

  3. #3
    3 Star Lounger siflaar's Avatar
    Join Date
    Jan 2001
    Location
    Amersfoort, Netherlands
    Posts
    203
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Re: Query problem (97)

    David,

    I understand. I was trying to attach a example database, but that seems to be a problem here on my work, so I will attach a database later on to make it more clear.
    Thanks for looking.

    Greetings,

    Patrick Schouten
    Greetings,

    Patrick Schouten
    (The Netherlands)

  4. #4
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Newbury, Berkshire, UK, Berkshire, England
    Posts
    243
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query problem (97)

    I assume you mean he's got a many-to-many relationship between the Names and Languages table, with an intersection table to handle breaking the many-to-many

    Depends what the keys are on the two main tables, but if e.g. in the Language table, it's the actual name, I'd have thought a query on Name joined to Intersection where the language is like English or French ought to get the records he's after

  5. #5
    3 Star Lounger siflaar's Avatar
    Join Date
    Jan 2001
    Location
    Amersfoort, Netherlands
    Posts
    203
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Re: Query problem (97)

    Table1 : Names
    Fields: IDName (key), Name
    Table2 : Language
    Fields: IDLanguage (key), Language
    Table 3: Connect
    Fields: IDName, IDLanguage (this of the use of the Lookup Wizard)

    Yes there is an many-to-many relation between Name and Language, that is the reson for the intersectiontable Connect.

    Running a query on the table Connect gives a problem, because it only contains numbers and not the Languagenames to put in a criteria.
    Also is it not possible to have AND critera; 'Show me all names that speak Dutch and France'.

    Greetings,
    Patrick Schouten
    Greetings,

    Patrick Schouten
    (The Netherlands)

  6. #6
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Newbury, Berkshire, UK, Berkshire, England
    Posts
    243
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query problem (97)

    Try this example
    Attached Files Attached Files

  7. #7
    3 Star Lounger siflaar's Avatar
    Join Date
    Jan 2001
    Location
    Amersfoort, Netherlands
    Posts
    203
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Re: Query problem (97)

    Hello Steve,

    Thanks, this is what I also did,
    but now I ONLY want to see all people who speak Dutch AND English (not an OR).

    Succes,

    PAtrick Schouten
    Greetings,

    Patrick Schouten
    (The Netherlands)

  8. #8
    Lounger
    Join Date
    Jan 2001
    Location
    Slough, Berkshire, England
    Posts
    46
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query problem (97)

    I would tackle this problem by creating the following
    a) Query that listed all people who speak Dutch
    [img]/forums/images/smilies/cool.gif[/img] Query that listed all people who speak English
    Then create a query based on these two queries. Access will, by default, connect the two queries so that only those rows where the two entries are equal, ie people who speak both Dutch and English.
    I have attached a modified version of Steve's database.
    I hope this is what you need

    David
    Attached Files Attached Files

  9. #9
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    632
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query problem (97)

    You can't do it directly. Instead, you need to select the people who speak English, and then from that group, select the people that speak Dutch. It can be done using a Subselect

    <aircode>

    select IDName from connect where (IDLanguage = "dutch") and IDName IN (select IDName from connect where IDLanguage="English")

    The key to this technique is using the IN operator.

    Hope this helps.
    --------------------------------------------------
    Jack MacDonald
    Vancouver, Canada

  10. #10
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Query problem (97)

    Hi,
    FWIW I think you can do it in one go using a totals query and a couple of MAX expressions. I'm attaching a sample db - let me know if I've missed something obvious but it seems to work OK.
    Attached Files Attached Files
    Regards,
    Rory

    Microsoft MVP - Excel

  11. #11
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Newbury, Berkshire, UK, Berkshire, England
    Posts
    243
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query problem (97)

    Neat <img src=/S/bravo.gif border=0 alt=bravo width=16 height=30>

    And you can extend it for more choices

  12. #12
    3 Star Lounger siflaar's Avatar
    Join Date
    Jan 2001
    Location
    Amersfoort, Netherlands
    Posts
    203
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Re: Query problem (97)

    Hello Rory,

    Thanks for this neat sollution.
    This will do the trick... But now lets move to the next step.
    I want to show a form on the screen where a user can select any number of lanuages.
    How can I change the query to refect any outcome of the form input, to show a list of people that speaks ALL the selected languages?

    (It just looked as a simple question and now I see things in Access happen that moves me to a new level)

    Greetings,
    Patrick Schouten
    Greetings,

    Patrick Schouten
    (The Netherlands)

  13. #13
    3 Star Lounger siflaar's Avatar
    Join Date
    Jan 2001
    Location
    Amersfoort, Netherlands
    Posts
    203
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Re: Query problem (97)

    Hello Jackson,

    When I try your solution the query result is showing nothing. I could by that I'm doing something wrong (would't be the first time hihi)
    Maybe you could send me an working example.

    Greetings,
    Patrick Schouten
    Greetings,

    Patrick Schouten
    (The Netherlands)

  14. #14
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    632
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query problem (97)

    Done.

    In anticipation of your next question, you build up the more complex query by building the SQL on-the-fly. Combine as many AND operations as you like by concatenating successive IN statements. The result of one query becomes the basis for building the next query.

    Tried to post an attachment, but was unsuccessful. If you send me your email, I will send it to you directly.
    --------------------------------------------------
    Jack MacDonald
    Vancouver, Canada

  15. #15
    3 Star Lounger siflaar's Avatar
    Join Date
    Jan 2001
    Location
    Amersfoort, Netherlands
    Posts
    203
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Re: Query problem (97)

    Thanks for the example

    The SQL statement you use, I can even read, wow.
    ---
    SELECT Connect.IDName, Connect.IDLanguage, Names.Name
    FROM [Names] INNER JOIN [Connect] ON Names.IDName = Connect.IDName
    WHERE (((Connect.IDName) In (SELECT Connect.IDName FROM Connect WHERE (((Connect.IDLanguage)=1))[img]/forums/images/smilies/wink.gif[/img]) AND ((Connect.IDLanguage)=3));
    ---

    But could you explane to me, how I could write 'on the fly' the SQL code when a user selects some languages from a form?
    I'm new to VBA and SQL, but it looks very powerfull

    Greetings,
    Patrick Schouten
    Greetings,

    Patrick Schouten
    (The Netherlands)

Page 1 of 2 12 LastLast

Posting Permissions

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