Merging

[Tony McGuire]

I am trying to create my own film DVD kartotek however there is one thing which I wish to do but am not sure if it is possible or not!
As well as the title and other fields I have 3 fields called 'Actor 1', 'Actor 2' and 'Actor 3'. I have then created queries to show all actors who's name begins with A* or B* and so on through til Z*. This is good enough and I now get their names but in 3 colums : Actor 1, 2 and 3. However I want to create a new list of names which shows a list of all their names but in just one column. Can I in any way merge columns 'Actor 1' 'Actor 2' and 'Actor 3' so that I am free from writing all the names again under 1 column/field? I will then have to figure out a way of preventing duplicates ( i.e 'Actor 1 in the first post could well be 'Actor 3' in the 15th post..... Can anyone help?
Danmac

 

[namliam]

A "union" is what you are looking for, but in essence you have a design flaw here.

Instead of your design you want a seperate Actor table.
Then have a "Junction" table between your movie and actor table which will act as the link between actor and movie.

This will resolve all kinds of issues, among which your duplication problem as well as enable you to add role specific data to your junction table, i.e. Lead/support role etc.
Also it will prefent issues along the lines of I need/want a 4th actor or I only want to add 1 actor, thusly leaving 2 fields empty

 

[Tony McGuire]

Hi Mailman (I am not too good at anagrams)

Thanks for your help and I am sure that it will help.

?tnorf ot kcab etirw uoy od yhw yaw eht yB

Danmac

 

[namliam]

?tnorf ot kcab etirw uoy od yhw yaw eht yB

I hope it does help,

I wrote backwards because my 'real' nick is Mailman, but on so many sites/fora it is already taken

So I addopted the revers name, to make myself a little more unique