Okay, I have a database of films.....I want a search function to be able to find any films that a given pair of actors appear in (Owen Wilson & Ben Stiller, for example) when they could be in any one of the 4 "Actor" fields.

Here's the code I have:

sql code:

---

SELECT tblFilms.ID, tblFilms.Title, tblFilms.Year, tblFilms.Actor1, tblFilms.Actor2, tblFilms.Actor3, tblFilms.Actor4
FROM tblFilms
WHERE (((tblFilms.Actor1)=[Enter the first actor who's films you would like to find] Or (tblFilms.Actor1)=[Enter the second actor who's films you would like to find])) OR (((tblFilms.Actor2)=[Enter the first actor who's films you would like to find] Or (tblFilms.Actor2)=[Enter the second actor who's films you would like to find])) OR (((tblFilms.Actor3)=[Enter the first actor who's films you would like to find] Or (tblFilms.Actor3)=[Enter the second actor who's films you would like to find])) OR (((tblFilms.Actor4)=[Enter the first actor who's films you would like to find] Or (tblFilms.Actor4)=[Enter the second actor who's films you would like to find]));

---

The best I can come up with is:

sql code:

---

SELECT those_columns FROM tblFilms WHERE (tblFilms.Actor1=NAME1 OR tblFilms.Actor2=NAME1 OR tblFilms.Actor3=NAME1 OR tblFilms.Actor4=NAME1)
INTERSECT
SELECT those_columns FROM tblFilms WHERE (tblFilms.Actor1=NAME2 OR tblFilms.Actor2=NAME2 OR tblFilms.Actor3=NAME2 OR tblFilms.Actor4=NAME2)

---

I'm using Microsoft Access 2007....when I try using INTERSECT it says there's a syntax error and missing operation

Well, it appears that it's not supported on Access

Anyway, when I googled a lot people say INTERSECT can be implemented using INNER JOIN, but I'm not sure how it works exactly. You'll have to fiddle with it.

It's a Report that takes its info from the query

edit:
maybe not

Hmm, yeah, it seems to have a problem with "INNER"

From the looks of it you have to do Inner Join ON something

http://msdn.microsoft.com/en-us/library/bb208854.aspx

You don't want to use an INNER JOIN in this case as an INNER JOIN will join 2 tables with like values.

sql code:

---

SELECT * FROM tblFilms
WHERE (Actor1=NAME1 OR Actor2=NAME1 OR Actor3=NAME1 OR Actor4=NAME1)
    AND (Actor1=NAME2 OR Actor2=NAME2 OR Actor3=NAME2 OR Actor4=NAME2)

---

We have a winner.....thanks Matty

No problem djdannyp.

Here is an example

quote:

---

Originally posted by matty
toddy yours wouldn't work because you are checking if tblFilms.Actor1 = Name1 AND tblFilms.Actor2 = Name1 AND etc.....

---

quote:

---

Originally posted by toddy

quote:

---

Originally posted by matty
toddy yours wouldn't work because you are checking if tblFilms.Actor1 = Name1 AND tblFilms.Actor2 = Name1 AND etc.....

---

you can tell i haven't used a query in a long time

---

sql code:

---

CREATE TABLE #TEST ( ID INT, Actor1 VARCHAR(50), Actor2 VARCHAR(50), Actor3 VARCHAR(50), Actor4 VARCHAR(50) )

INSERT INTO #TEST
SELECT 0, 'w', 'x', 'y', 'z'

DECLARE @name1 AS VARCHAR(50);
DECLARE @name2 AS VARCHAR(50);

SET @name1 = 'x';
SET @name2 = 'z'

SELECT A.* FROM #TEST A
    INNER JOIN #TEST B ON B.ID = A.ID
        AND (B.Actor1=@name2 OR B.Actor2=@name2 OR B.Actor3=@name2 OR B.Actor4=@name2)
WHERE (A.Actor1=@name1 OR A.Actor2=@name1 OR A.Actor3=@name1 OR A.Actor4=@name1)

---

quote:

---

Originally posted by matty
toddy, you were correct about INNER JOINing the tables here is how you could do it.

---

djdannyp,

If this is course work and the table layout was given to you, you can probably stop reading here.  Otherwise, continue. 

While you have been offered a few solutions that should work with what you have, the real problem here is the database design.  Relational database tables are not Excel spreadsheets and "Actor1, Actor2, Actor3, Actor4" is not the way to do it.  That's not even 1NF.

Instead, consider the following three-table relationship:

tblFilms: ID, Title, Year, ...
tblActors: ID, FirstName, LastName, ...
tblActorsInFilms: FilmID, ActorID, Role, ...

The last table ties together actors and films in a many-to-many relationship.  As the minimum, it would have the two ID columns to make the association. 

Consider the advantages:

• Your films can now have more than four actors listed without
  
• Any query involving actors becomes much simpler and doesn't require dealing with four columns
  
• You can now write practical aggregate function queries, such as how many movies feature each actor
  
• You can now store additional details for an actor, such as date of birth and country.  With that you could do spiffy things, such as find all films a particular actor starred in under the age of 25, films featuring Canadian actors and so forth. 
  
• You can also add columns specific to the actor-film relationship to tblActorsInFilms. This could be the role they played, for example. That too could become a one-to-many relationship in a normalized database: sometimes an actor plays more than one role in a film.  However, I won't be that thorough this time.

As great as that all sounds, I'm not looking for anything anywhere near that complicated.  What I've got at the moment is perfectly fit for purpose. It's just a little utility to keep track of films I've seen and stuff.   My main problem is actually the depreciated 'Switchboard' style that I'm having to grapple with changing

It's not intended to include anywhere near that much information, plus adding all of that stuff to the 400+ existing films would take AGES.

Thanks for the information, but I'm happy with what I've got at the moment, I don't want it getting much more complicated, just wanted to get that one query working.

Coursework?   I'm Almost 24 and in full-time employment, my days of coursework finished long ago....and I never studied IT anyway

Even if it sounds complicated, the design by Adeptus is very easy; it sounds way more complicated than it is.

I have almost the exact same layout for my movie DB, just as many other people have I imagine (because it is a very generic/logic way of doing it).

It also makes your DB filesize a hell of a lot smaller (now you store a lot of the same strings, the actor names, a lot of times), not to mention the DB will be a lot faster.

And indeed, pulling reports out of it would be childsplay compared to coming up with queries which work on your one table design. Creating reports and other queries, even (re)designing the (crappy) switchboard would be actually more easier.

Converting your one table design to a multi table design like shown by Adeptus shouldn't be a big problem either, especially in Access where many stuff is wysiwyg. You can use create table queries (one time) to create for example the actor table, etc...

Actually, I was doing almost the exact same thing at the moment for work, converting a one table design (excel) to a multiple table DB. The table consists of roughly 12000 records. So it speaks for its own that I cba to convert it by simply typing everything over again or doing stuff manually. But using table creation queries, temporary tables and columns, a wizard here and there, the whole thing was converted in no-time (not to mention going from a 23MB plain one tab excel file to a 800K access file inlcuding reports, fancy UI, etc...   I always hated Access a bit because it is 'different' than the other MS products, but each time I work with it for a couple of days, it grows again on me - now if they only make it easier to layout reports

The filesize is hardly a problem as it's only 4MB   And it's not slow in the slightest, everything happens instantly.

Redesigning the switchboard has proved pretty easy using the new Nagivational Control in Access 2010, it's now looking pretty snazzy and with a few more queries/features. 

If I was starting it from scratch then I'd possible be more inclined to go with a design like that, but given that everything works 100% as intended and I've already got a lot of data in it (as does my gf in her version of it), it would take way too much data entry to fill everything in, when the amount of detail in there at the moment is just right

quote:

---

Originally posted by djdannyp
Redesigning the switchboard has proved pretty easy using the new Nagivational Control in Access 2010, it's now looking pretty snazzy and with a few more queries/features.

---

quote:

---

Originally posted by djdannyp
If I was starting it from scratch then I'd possible be more inclined to go with a design like that, but given that everything works 100% as intended and I've already got a lot of data in it (as does my gf in her version of it), it would take way too much data entry to fill everything in, when the amount of detail in there at the moment is just right

---

PS: another big advantage of multiple-table design... you wont get not-found entries when you search for "John" but have typed "Jonh" in one of the actor fields just trying to convice you

djdannyp,

It is perfectly fine if you don't want to redo your database.  We all live with kludges that are the way they are because that's how it was done at one time and it works well enough to not spend more time on it.

Being a geek who designs databases and writes code in the course of his work, I find great satisfaction in making things more elegant and efficient.  That is why I proposed another way of doing this to you.  You don't necessarily have to implement it, it is sufficient that you now see the principle and may use it in some future project.

quote:

---

Originally posted by CookieRevised
Sounds cool... too bad I don't have 2010

---

quote:

---

Originally posted by CookieRevised

Do you know if it is possible to convert the new format (assuming Access 2010 has a 'new' format too, like the other office programs), into something compatible with 2000/2003? Because if it is way easier and more intuitive to design stuff like forms/reports and switchboards in Access 2010, I might consider installing it just for that (provided it can be installed side-by-side with the old... hmm)

---

quote:

---

Originally posted by CookieRevised

PS: another big advantage of multiple-table design... you wont get not-found entries when you search for "John" but have typed "Jonh" in one of the actor fields just trying to convice you

---

You wouldn't be you if you didn't try to convince me .  Luckily I'm a meticulous typist

quote:

---

Originally posted by djdannyp

quote:

---

Originally posted by CookieRevised
Sounds cool... too bad I don't have 2010

---

http://www.microsoft.com/office/2010/en/download-...-plus/default.aspx

---

quote:

---

Originally posted by djdannyp

quote:

---

Originally posted by CookieRevised

Do you know if it is possible to convert the new format (assuming Access 2010 has a 'new' format too, like the other office programs), into something compatible with 2000/2003? Because if it is way easier and more intuitive to design stuff like forms/reports and switchboards in Access 2010, I might consider installing it just for that (provided it can be installed side-by-side with the old... hmm)

---

No, unfortuantely you can't save it in a compatibility mode if it uses any of the new features as they aren't backwards compatible.

---

I'm not entirely sure how they work, but you can't save them in an earlier format to use in Access 2000/2003 (they don't even work in 2007!)  The Navigational Control thing is totally new for 2010, it replaces switchboards in a much more dynamic and accessible manner.

And ribbon rules!

ah ok, thanks for the info