Query Form Problem

[bceo]

I have build a Access 2003 database for my Movie Collection. I have set up
an form query to search the database for things like year, MPAA Rating, Gender. I
have used the following criteria in the query

[Forms]![Query Form]![txtField Name]
​

and it works just fine

When I set up the search text box to search for all the movies a certain actor is in, using this method, it didn't work. The data in the field has normally 3 or 4 actor's names. I notice that if I leave only one name in the field, then the query works.

This tells me that the criteria [Forms]![Query Form]![txtCast] is not correct for this type of field.

Can anyone tell me how to correct the criteria so that it will work?
Thanks in advance!

 

[jdraw]

Please show us the SQL for the query.
Do you have a picture of your tables and relationships?

What does this mean exactly?

The data in the field has normally 3 or 4 actor's names

 

[bceo]

The SQL for the query looks like this,
SELECT [All Movies List].Title, [All Movies List].Year, [All Movies List].[MPAA Rating], [All Movies List].Gender, [All Movies List].Cast, [All Movies List].Plot
FROM [All Movies List]
WHERE ((([All Movies List].Cast)=[Forms]![Query Form]![txtCast]));

The field for this query would look like this,
Sean Connery, Ursula Andress, Joseph Wiseman, Jack Lord, Bernard Lee

This the cast information for a James Bond Movie. What I wish to do is to have a form query search the database for only one of the names.
IE: search the database for all movies in which Sean Connery is an actor in the movie, then using a command prompt, preview a report of all of the data for all the movies in which Sean Connery stars in.

I hope this information is helpful.

 

[bob fitz]

Could it be that your tables have not been normalized correctly?
IMHO multiple data entries in a single field means that you should have another table.

 

[bceo]

Thanks for the reply Bob, I do not understand what you mean by normalizing my table.
The field (Cast) is just a text field in which I type in the main members of the cast that appear in the movie. I am not sure how I would use another table for the Cast. I have thousands of movies in my collection.
Thanks again for the help.

 

[bob fitz]

Normalization is all about getting your table structure right. I'm sure that a search in any Access forum or in google could keep you reading for months . I would also advise you to have a read about relationships.

I have attached a small db for you to take a look at that addresses some of these issues. Take a look at the tables and the relationships between those tables. There is also a form and a query that may be of interest.

Please post back if you need more clarification.

 

[bob fitz]

Hi again,

Just found this. It may be of interest to you.
http://forums.aspfree.com/attachment.php?attachmentid=4712

 

[bceo]

Bob, thanks a lot for the information and the sample database. I now see where you are trying to tell me.
The big problem I now have in fixing the database is how to redo the cast names into a table without typing hundreds, if not ,a thousand names. I have collected. over the years, over a 1100 movies.
Any ideas, that don 't cost money, I am retired and on a fixed budget?

 

[bob fitz]

Hi bceo

I'm glad you found the db of some help.

The big problem I now have in fixing the database is how to redo the cast names into a table without typing hundreds, if not ,a thousand names. I have collected. over the years, over a 1100 movies.

Hmmm.. I thought you may come back with a question like that.

Any ideas, that don 't cost money, I am retired and on a fixed budget?

If you can zip your db and get it small enough to post it, I would be happy to take a look at and see what I can do, but it may take me a little while to deal with it. There would of course be NO cost. I only want to help if I can.

 

[bceo]

Thanks Bob, I have attatched a .rar file of the db. When I use winzip the file was twice as big as the .rar file. I hope this is still acceptable. I haven't change the database to bring in inline with the info you supplied, so don't be to harsh on me I am new to this. Once again thanks a bunch.

 

[bob fitz]

Hi
The bad news is that you don’t seam to have attached a file to your post. Also, I don’t know what a “rar” file is, so a zip file would be better for me. Compact the file before creating the zip file. To compact the db, from the main menu select: Tools/Database Utilities/Compact and Repair Database. If it really has to be a rar file, then attach it and I'll see what I can do with it.

The good news it that I have written some code that I think will put the required data in to the new tables for you. Fingers crossed .

 

[bceo]

Sorry about the missing file, I thought I had attached correctly.
I have compacted the db as you instructed and have compressed the file using Winzip as you requested.
I will once again try to attached the file using the attached icon above the message window I am typing into.

 

[bob fitz]

OK. I have your file. I will try to take a look at it tomorrow.

 

[bceo]

Thanks again Bob

 

[bob fitz]

Hi bceo

I have attached an amended version of your db.
I have created some new tables as I think you require, and some new forms to go with the tables. You may also want to have a look at the query “qryFindActorsFilms”. I have left your “Cast” field in the table of films in case you want to check the transfer of data to new tables but it no longer needed by the db and could be deleted.

IMO it is better not to use spaces in the names of tables, forms, queries, controls etc. I would also use a prefix to these names that indicates what the object is.

For the benefit of anybody following this thread, the code I wrote to extract the data from your “Cast” field and put it in the new tables is in a module. I’m sure there are others that could have done it more efficiently but this did the job in less than a couple of minutes. I only wish I could have written it that quickly.

If you have any questions or you need/want an explanation of any of the code in the form modules then please post back.

I hope this will be of some help to you.

 

[bceo]

Bob, sorry about not getting back to you sooner. I really appreciate all the work you have done on my database. It has taken me some time to understand all the things you added and changed. I am not a programmer so your fix for my actor problem was beyond me, but thanks for the fix.
It took a little time for me to figure out how to add the actors, when I added new movies to the database. In end, of course, it was simple. I over thought it. I am used to just entering data in to a field on a form, that I did not realize the window that opens when you click "edit" was the input form.
Once again a big thank you Bob