Query problem (97)

Topic

New Reply

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

Reply | Quote

Replies

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

Try this example

Reply | Quote

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

Reply | Quote

I would tackle this problem by creating the following
a) Query that listed all people who speak Dutch
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

Reply | Quote

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

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.

Reply | Quote

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.

Reply | Quote

Neat

And you can extend it for more choices

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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))) 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

Reply | Quote

Extremely powerful… and it takes a while to learn. I suggest that you pick up a good book on programming Access. Any good computer bookstore will have a variety of books to choose from. Look for one that goes beyond the basic Access introduction into the area of programming. One of the best is Access Developer’s Handbook by Litwin, Getz, and Gilbert. It’s not really a beginners’ book, but is very thorough.

One of the things you will learn is how to apply an SQL string as the recordset for a form or a listbox.

For this specific example, you will notice that the WHERE xxx IN (SELECT… ) part is very structured. If you design a form with “bits of code” attached to various controls designating the various languages, you can build up a SQL string as the user clicks each control. Then once the string is formed, you apply it to a form or a listbox.

Experiment by building the string manually and applying it to a listbox. Nest some additional WHERE xxx IN (SELECT….) clauses. Then move on to building the string when the user clicks the various parts of the form.

HTH

Reply | Quote