Shoutbox

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

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

---

It's all good. I test software and a lot of stuff I write database queries for.

There is a better way of doing it I just cannot think of it off the top of my head

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

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)

---

Returns:
ID    Actor1    Actor2    Actor3    Actor4
0    w            x            y            z

You just need to join the table on itself on the primary key. However this is more strenuous on the database as you are actually quering all the rows twice.

quote:

---

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

---

i thought you could but i mainly only use SQL for input rather than output, so code was more guess work then anything (especially as i couldn't be bothered opening access to test )

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.

The query you asked about would then be as follows:

select F.ID, F.Title, F.Year
from tblFilms as F
join tblActorsInFilms as AF1 on AF1.FilmID = F.ID and AF1.ActorID = @ActorID1
join tblActorsInfilms as AF2 on AF2.FilmID = AF1.FilmID and AF2.ActorID = @ActorID2

Actors 1 and 2 were the parameters to the query, so I assume you don't need that returned in the results.  If you do for some reason, that wouldn't be hard to add.

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.

---

Sounds cool... too bad I don't have 2010. 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 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

---

Don't break a working thing thus

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

---

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

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.

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

Yeah, thanks for the suggestion Adeptus....I'm kinda going for the "If it aint broke...." approach at the moment.  I understand that when you code/work with databases a lot of the time you'd want to suggest something more practical, I'm the same way with stuff I spend a lot of my time on