Creating an Access query: Query By Example 1

[Australia]

I have three tables called DVD, Movie and Director. I need to create a query to ask the user for the name of a director and retrieve the DVDs directed by that director.

Director - Movie (Director ID is the foreign key in the Movie table)
Movie - DVD (Movie ID is the foreign key in the DVD table)

Please help me in code...

Thanks!

 

[hermanlaksko]

I assume that you are using a form to show your results.

Then I would use a combobox with the directors and on AfterUpdate on this combobox I would put the following code:

me.recordsource ="SELECT * FROM DVD Where DirID=" & me!Combo1.column(1)

should do the trick

 

[raskew]

The code could look something like this. Modify the table and field names to agree with your application and it should return the desired result.

PARAMETERS [enter Director Name] Text;
SELECT tblDVD.DVDName, tblMovie.MovieName, tblDirector.DirectorName
FROM (tblDVD INNER JOIN tblMovie ON tblDVD.MovieID = tblMovie.MovieID) INNER JOIN tblDirector ON tblMovie.DirectorID = tblDirector.DirectorID
WHERE (((tblDirector.DirectorName)=[enter Director Name]));

 

[Quintios]

That has to be the coolest code I've ever seen... It came out in 'symbol' font over here...

Onwards,

Q-

 

[raskew]

Hmmm, very strange.

I used

and it looks like when I wanted at this end. It could have something to do with sunspots, crossing the international dateline, the equator or something. Let me repost it in Arial without any bells and whistles:

PARAMETERS [enter Director Name] Text;
SELECT tblDVD.DVDName, tblMovie.MovieName, tblDirector.DirectorName
FROM (tblDVD INNER JOIN tblMovie ON tblDVD.MovieID = tblMovie.MovieID) INNER JOIN tblDirector ON tblMovie.DirectorID = tblDirector.DirectorID WHERE (((tblDirector.DirectorName)=[enter Director Name]));