this AND that
kee...@yahoo.com.invalid
know what I want, but searching for THIS brings up hundreds of responses, but a
this AND that search could seriously cut down on the responses.
Steve
search on twice. Create a form named SearchForm to display your search
results. Make the nquery the recordsource of the form. Add two unbound
textboxes to the form. Name the first textbox This and the second textbox
That. Add a command button named DisplaySearchResults to the form. Put the
following code in the Click event of the button:
If IsNull(Me!This) Or IsNull(Me!That) Then
MsgBox "This and That must be filled in before a search can be done"
Else
Me.Requery
End
Go back to the query and under the first search field, enter the following
expression in the criteria:
Like Me!This
And under the second search field, enter the following expression in the
criteria:
Like Me!That
You're now good to go!!
Steve
san...@penn.com
<kee...@yahoo.com.invalid> wrote in message
news:f2lqi59g2dlpr4ebk...@4ax.com...
Tom Wickerath
> Go back to the query and under the first search field, enter the following
> expression in the criteria:
> Like Me!This
> And under the second search field, enter the following expression in the
> criteria:
> Like Me!That
>
> You're now good to go!!
Well, not quite...
You need to enter criteria in the query that looks more like this:
[Forms]![NameOfForm]![This]
and
[Forms]![NameOfForm]![That]
Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________
"Steve" wrote:
Create a query to do the search. Include in the query the field you want to
search on twice. Create a form named SearchForm to display your search
results. Make the nquery the recordsource of the form. Add two unbound
textboxes to the form. Name the first textbox This and the second textbox
That. Add a command button named DisplaySearchResults to the form. Put the
following code in the Click event of the button:
If IsNull(Me!This) Or IsNull(Me!That) Then
MsgBox "This and That must be filled in before a search can be done"
Else
Me.Requery
End
Go back to the query and under the first search field, enter the following
expression in the criteria:
Like Me!This
And under the second search field, enter the following expression in the
criteria:
Like Me!That
You're now good to go!!
Steve
__________________________________________
Tom Wickerath
a sample database to show you how to implement such functionality. The sample
includes minimal VBA code:
http://www.accessmvp.com/TWickerath/downloads/customdialogbox.zip
If you are brave and want to take the plunge into using more extensive VBA
code, try the following samples:
http://www.accessmvp.com/TWickerath/downloads/elements.zip
http://www.accessmvp.com/TWickerath/downloads/Chap08QBF.zip
http://www.seattleaccess.org/downloads.htm
See the download "Query By Form"
Tom Wickerath, February 12, 2008
The first sample, for the Elements database, is designed to show one thing
only: how to "iterate" the .ItemsSelected property of a list box with Multi
Select property set to either Simple or Extended.
The second sample, Chap08QBF, includes some functionality that I have added
to the sample that is published in chapter 8 of "Access 2000 Power
Programming", written by F. Scott Barker. My enhancements include adding
Access MVP Allen Browne's calender form (http://allenbrowne.com/ser-51.html),
adding export to Excel functionality, and allowing the user to double-click a
row in the results subform to open that record for editing.
The third sample, available on the Seattle Access User Group web site,
includes a Word document that attempts to explain how the technique works,
and a stripped down sample of the Northwind database (Access 2003 version)
with QBF (Query By Form) capability.
Enjoy!
Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________
kee...@yahoo.com.invalid
wrote:
>Here is a link to a zipped file on my web site, that includes a tutorial and
>a sample database to show you how to implement such functionality. The sample
>includes minimal VBA code:
I'll give it a try, but the keywords in my post was 'SIMPLE'. I have the 2
pound $50.00 access 2000 book. But so far it's main duty has been as a
paperweight. Great index... If you know what you're looking for.
KenSheridan via AccessMonster.com
'parameter queries' or similar in your paperweight if you are not sure
what's meant by 'parameters' in this context).
Lets take a very simple example, and say you have a query which includes
columns for City and State. If you put [Enter state:] in the criteria row of
the State column in query design view you'll be prompted when you open the
query (or a form or report based on it) to enter the state. This will give
you more rows than you want if you are only interested in a particular city,
so by putting [Enter city:] in the criteria row of the City column you'll
also be prompted to enter the city, which will narrow down the search. At
this stage I should point out that if you just have the parameter in the City
column, so you only enter the city name, that's not good enough because city
names can be duplicated across states; both need to be entered to pin it down
to one city (even then its not absolutely guaranteed to do so, because with
smaller towns/cities you might even find duplication within a state).
Now that's fine if you want to pin the results down by city as well as state,
but lets say you sometimes do want to enter a state and return rows for all
cities in that state. What you do here is enter the parameter in the State
column as above, but this time in the City column's criteria row you enter:
[Enter city:] OR [Enter city:] IS NULL
Now if you enter a state when prompted, but when prompted for a city just
click the OK button without entering a city name you'll get all the rows for
the selected state regardless of city. This is because the parameter is now
NULL, having no value, so the '[Enter city:] IS NULL' part of the above
expression will be TRUE for every row, whatever the city name, i.e. the
parameter for city is now an optional one.
One other thing to note about this sort of thing is that if you save the
query after doing the above, then open it again in design view, Access will
have moved things around. Don't worry, the underlying logic is the same and
the query will work in exactly the same way.
The above uses simple system generated parameter prompts, which is fine for
someone starting out, but in a developed database application the parameters
would usually be references to controls on an unbound dialogue form, e.g.
Forms![frmCityStateDlg]![txtState]
and:
Forms![frmCityStateDlg]![txtCity] OR [frmCityStateDlg]![txtCity] IS NULL
in which case the city and state can be entered in one step in the form, and
then a form or report based on the query opened with a button on the form.
Once you've got your teeth around using simple parameters, therefore, you can
think about trying this slightly more sophisticated approach. And then you
can look at going even further and doing things like selecting multiple items
from a multi-select list box so that you can return rows which match any of
the selected items.
Ken Sheridan
Stafford, England
kee...@yahoo.com.invalid wrote:
>>Here is a link to a zipped file on my web site, that includes a tutorial and
>>a sample database to show you how to implement such functionality. The sample
>>includes minimal VBA code:
>
>I'll give it a try, but the keywords in my post was 'SIMPLE'. I have the 2
>pound $50.00 access 2000 book. But so far it's main duty has been as a
>paperweight. Great index... If you know what you're looking for.
--
Message posted via http://www.accessmonster.com
KenSheridan via AccessMonster.com
Should have been:
Forms![frmCityStateDlg]![txtCity] OR Forms![frmCityStateDlg]![txtCity] IS
NULL
Ken Sheridan
Stafford, England
--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access/200912/1
kee...@yahoo.com.invalid
<u51882@uwe> wrote:
>Probably the simplest approach is to use a query with parameters (look up
>'parameter queries' or similar in your paperweight if you are not sure
>what's meant by 'parameters' in this context).
You're just making it more complicated. Save your work until I get to the point
I need more help.
As for 2 pounds, that's a rough estimate. it's more than 2 inches thick. It's
actually 3 inches with a test CD. 1318 pages. The complete reference access
2000.
Steve
look at Tom Wickerath's correction.
Steve
san...@penn.com
<kee...@yahoo.com.invalid> wrote in message
news:7tvsi5lpka119329b...@4ax.com...
KenSheridan via AccessMonster.com
to what they asked for. If you take another look at the original post you'll
see that they want to refine a search so it returns fewer rows. Implicit in
this is that it requires a Boolean AND operation on two separate attributes.
What you gave them, at least after Tom corrected it, was a Boolean OR
operation on the same attribute, which would of course return more not fewer
rows.
I'll save you having to work it out for yourself. Here's your first response
amended to do what's required:
**************************
Create a query to do the search. Include in the query the two fields you want
to
search on. Create a form named SearchForm to display your search
results. Make the query the RecordSource of the form. Add two unbound
textboxes to the form. Name the first textbox This and the second textbox
That. Add a command button named DisplaySearchResults to the form. Put the
following code in the Click event procedure of the button:
If IsNull(Me.This) Or IsNull(Me.That) Then
MsgBox "This and That must be filled in before a search can be done"
Else
Me.Requery
End
Go back to the query and under the first search field, enter the following
expression in the criteria:
Forms!SearchForm!This
And under the second search field, enter the following expression in the
criteria:
Forms!SearchForm!That
**************************
Ken Sheridan
Stafford, England
Steve wrote:
>Go back and look at my response. I gave you a SIMPLE solution. Be sure to
>look at Tom Wickerath's correction.
>
>Steve
>san...@penn.com
>
>On Sun, 20 Dec 2009 17:39:17 GMT, "KenSheridan via AccessMonster.com"
><u51882@uwe> wrote:
>
>>Probably the simplest approach is to use a query with parameters (look up
>>'parameter queries' or similar in your paperweight if you are not sure
>>what's meant by 'parameters' in this context).
>
>You're just making it more complicated. Save your work until I get to the
>point
>I need more help.
>As for 2 pounds, that's a rough estimate. it's more than 2 inches thick.
>It's
>actually 3 inches with a test CD. 1318 pages. The complete reference access
>2000.
--
Steve
boolean AND search. Originally the OP searched for a single value in his
search field. This returned a multitude of records. By duplicating the
search field and setting the criteria in one to THIS and the criteria in the
other to THAT, the query will only return records that contain both THIS
"AND" THAT in the search field. This in most cases will return less records
contrary to what you say.
Steve
"KenSheridan via AccessMonster.com" <u51882@uwe> wrote in message
news:a0e38ca272800@uwe...
KenSheridan via AccessMonster.com
Firstly you cannot apply an AND operation to one column. Each row can only
legitimately have one value at one column position so cannot be x AND y, only
x OR y. I can't deny that the former will return less rows as you say, as
it would return precisely zero rows!
Secondly, you acknowledged that Tom had corrected your post, and indeed
exhorted the OP take account of Tom's correction, but what Tom said was put
the parameters on separate criteria rows of a single column in design view,
so you've already accepted that its an OR operation which you were
recommending.
So, your first post did indeed recommend an AND operation, but one which
makes no sense. You now seem to have reverted to that position. But you'd
previously accepted Tom's correction to make it an OR operation, which does
make sense, but, as I said, does the opposite of what the OP wants in that it
can only return more rows, or at best the same number, not fewer rows as they
want.
Maybe you were thinking of a situation where an entity *relates* to rows with
two values of the same attribute type in a referencing table, which does of
course make sense, e.g.
SELECT Cat
FROM Schrödinger
WHERE EXISTS
(SELECT Cat
FROM QuantumStates
WHERE QuantumStates.Cat = Schrödinger.Cat
AND State IN ("Dead", "Alive")
GROUP BY Cat
Having COUNT(*) = 2);
But this too is an OR operation of course as State IN ("Dead", "Alive")
equates to State = "Dead" OR State = "Alive". The referenced cat can be both
dead and alive simultaneously in Schrödinger's famous thought experiment, but
in relational database terms its not the cat which has both attribute values
simultaneously, but rather the values of two States in rows in QuantumStates
to which it is related.
Ken Sheridan
Stafford, England
Steve wrote:
>I disagree! When you put criteria in the same row of a query you create a
>boolean AND search. Originally the OP searched for a single value in his
>search field. This returned a multitude of records. By duplicating the
>search field and setting the criteria in one to THIS and the criteria in the
>other to THAT, the query will only return records that contain both THIS
>"AND" THAT in the search field. This in most cases will return less records
>contrary to what you say.
>
>Steve
>
>> Unfortunately Steve, you gave them an answer which was diametrically
>> opposed
>[quoted text clipped - 63 lines]
>>>access
>>>2000.
--
Message posted via http://www.accessmonster.com
camila
<kee...@yahoo.com.invalid> wrote in message
news:f2lqi59g2dlpr4ebk...@4ax.com...
kee...@yahoo.com.invalid
I won't add or subtract. Most of Access is over my head. I'm amazed that I made
this DB on my own 7 years ago. But I looked at the example zip's. one of them
was interesting 3 columns, date, title, and rating.
What I was looking for specifically was actress AND actress. But I have some
stuff to look at, and it'll take weeks if not months to implement them
CORRECTLY. But this sample db, worked the same way I Add the actress names to
my form. with a lookup table. But without serious modification to my table, I'm
not sure I can make a actress AND actress search.
I created 12 fields for actress naming them actress01-12. with the Access
search, I use the general search that searches the entire db any fields any
case. vs individual fields.
Steve
understand applying it in a query better!
<<Firstly you cannot apply an AND operation to one column.>>
You certainly can! Suppose you have a text field in a query named Word. You
can find all words in that field that contain an "E" AND an "O" with the
criteria Like "*" & "e" & "*" And Like "*" & "o" & "*". You can also do the
same thing by putting the Word field in two separate columns in the query
and putting Like "*" & "e" & "*" in the criteria of the first instance and
putting Like "*" & "o" & "*" in the criteria of the second instance in the
same row as the criteria in the first instance.
<< .....but what Tom said was put the parameters on separate criteria rows
of a single column in design view...>>>
You also need to learn to read!
That is not at all what Tom said!! Tom pointed out that my Like Me!This
should be Like[Forms]![NameOfForm]![This] and my Like Me!That should be
Like[Forms]![NameOfForm]![That]. And that is correct, my error. In my
suggested solution, I stated both criteria should be in the same row to
return an AND search.
Steve
"KenSheridan via AccessMonster.com" <u51882@uwe> wrote in message
news:a0e4ce0f1d834@uwe...
> FROM Schr�dinger
> WHERE EXISTS
> (SELECT Cat
> FROM QuantumStates
> WHERE QuantumStates.Cat = Schr�dinger.Cat
> AND State IN ("Dead", "Alive")
> GROUP BY Cat
> Having COUNT(*) = 2);
>
> But this too is an OR operation of course as State IN ("Dead", "Alive")
> equates to State = "Dead" OR State = "Alive". The referenced cat can be
> both
> dead and alive simultaneously in Schr�dinger's famous thought experiment,
Steve
need to look like:
TblMovie
MovieID
MovieTitle
etc
TblActress
ActressID
ActressFirstName
ActressLastName
etc
TblActressInMovie
ActressInMovieID
MovieID
ActressID
Suppose Actress A (ActessID 1) and ActressB (ActressID 2) starred and
costarred in MovieID 1 and also later appeared in MovieID 5. The records in
TblActressInMovie would look like:
1 1 1
2 1 2
3 5 1
4 5 2
For data entry you first need a form based on TblActress for entering the
names of actresses into your database. Then you need a form/subform for
entering movies and the actress(es) in each movie. The main form would be
based on TblMovie and the subform would be based on TblActressInMovie. Set
the LinkMaster and LinkChild properties of the subform control in the main
form to MovieID. The only field you need in the subform is a combobox to
enter ActressID. For each record, Access will automatically enter MovieID.
Now what I am surmising for your search is that you want to find a list of
movies where an actress or two actresses appeared. Create a query to do the
search. Include in the query the MovieID field twice. Create a form named
SearchForm to display your search results. Make the nquery the recordsource
of the form. Add two unbound comboboxes to the form. Both comboboxes should
have TblActress as the rowsource. Name the first combotbox StarringActress
and the second Combobox CostarringActress. Add a command button named
DisplaySearchResults to the form. Put the following code in the Click event
of the button:
Me.Requery
Go back to the query and under the first search field, enter the following
expression in the criteria:
Forms!SearchForm!StarringActress Or (Forms!SearchForm!StarringActress
IsNull)
And under the second search field, enter the following expression in the
criteria:
Forms!SearchForm!CoStarringActress Or (Forms!SearchForm!CoStarringActress
IsNull)
Make sure the criteria in both columns are in the same row.
The revised criteria is more flexible! You can select an actress in either
combobox and leave the other blank and you will get a list of movies in
which the actress appeared. Or you can select an actress in both comboboxes,
and you will get a list of only the movies in which both appeared.
You're now good to go!!
Steve
san...@penn.com
<kee...@yahoo.com.invalid> wrote in message
news:5ktui55nl0ggb7pbj...@4ax.com...
KenSheridan via AccessMonster.com
different actresses in a movie is the problem and it will make searching on
two actresses very difficult. It can be done, but it would be very
cumbersome. Steve is right about the design needed here; you do need three
tables, one representing the Movies entity type, another the Actresses entity
type, and a third which models the many-to-many relationship between them,
Steve's TblActressInMovie table. You'll see that this has two foreign key
columns, MovieID and ActressID which reference the primary keys of the other
tables. What this does is resolve the many-to-many relationship into two one-
to-many relationships, which is how such relationships are always modelled in
a relational database. The TblActressInMovie table does not need an
ActressInMovieID primary key column, however, as the other two columns make
up a composite primary key. Diagrammatically the model looks like this:
TbleMovie----<TblActressInMovie>----TblActress
You are probably thinking that recasting your data into the above model would
be an overwhelming task, but in fact its not that difficult to do with a
series of 'append' queries'. We'd need to know the full details of you
current tables' designs, including all field names and their data types to
give you the precise details of the queries you need to do so.
When it comes to searching for movies which have two actresses in them, I'm
afraid its not so simple as Steve thinks. To understand why you need to know
a little about how a query decides which rows to return. At bottom its quite
simple, for each row the query applies criteria, and if the answer is TRUE
then that row is returned, otherwise it isn't. When more than one criterion
is applied Boolean logic is employed. The three principle operators of
Boolean logic are AND, OR and NOT. When we use the terms 'and' or 'or' in
everyday English, however, we don't always use them in the very precise way
they are used in Boolean logic, so its necessary to be clear exactly what
these terms do mean:
AND: When this is used each criterion either side of the AND must be TRUE
for the operation to evaluate to TRUE and a row be returned. So if we look
at the rows in Steve's TblActressInMovie table and ask 'does ActressID = 42
AND ActressID = 66' for instance, then that makes no sense as the ActressID
value in any one row in the table can't be both 42 and 66 at the same time.
OR: When this is used any one criterion either side of the AND must be TRUE
for the operation to evaluate to TRUE and a row be returned. So if we look
at the rows in Steve's TblActressInMovie table and ask 'does ActressID = 42
OR ActressID = 66' for instance, then that does make sense as the ActressID
value in any one row in the table can be either 42 and 66.
However, this doesn't really help us with what you want. We can't use a
simple AND operation as Steve is suggesting because it makes no logical sense.
Nor can we use an OR operation as, while that does make sense, it would give
us all the movies which included one or other of the actresses, not both.
So as you can see, things are not as simple as might appear at first site,
and I'm afraid they do get even more complex if we are going to provide a
solution to your problem. With the three tables involved what's being asked
is which rows in the movies table point to two rows in the actresses in
movies table one of which has the value for actress 1 and the other of which
has the value for actress 2. The way this is done is by means of a subquery
based on the TblActressInMovie table within a query based on the TblMovie
table. This query can't be built in design view, only in SQL view. I'll
give you the query first, and then I'll explain how it works:
SELECT *
FROM TblMovie
WHERE EXISTS
(SELECT MovieID
FROM TblActressInMovie INNER JOIN TblActress
ON TblActressInMovie.ActressID = TblActress.ActressID
WHERE TblActressInMovie.MovieID = TblMovie.MovieID
AND (Actress = [Enter actress 1:]
OR Actress = [Enter actress 2:])
GROUP BY MovieID
HAVING COUNT(*) = 2;);
The way this query works is:
1. The subquery joins the TblActressInMovie and TblActress tables on
ActressID so that the Actress column from the latter can be returned. I've
assumed a single Actress column rather than separate first and last name
columns for simplicity and because this is what I assume you have at present
in your 12 columns.
2. The subquery is correlated with the outer query on blActressInMovie.
MovieID = TblMovie.MovieID. This in effect means the subquery runs
independently for each row in the outer query.
3. The subquery is restricted by the parameters [Enter actress 1:] OR [Enter
actress 2:]. For simplicity I've used basic system generated prompts here
rather that references to controls on a form, though the latter would be
better, and it would not be difficult to amend the query in die course.
4. The subquery includes a HAVING clause which restricts the rows returned
by it to those where the number of rows answering the criteria is 2. This
means that it will only return rows if there are two rows each of which
contains one of the actress names entered at the parameter prompts.
We've come long way from your 'simple' solution I'm afraid, but that is not
because we are overcomplicating things, but because while the problem can be
simplified as far as possible, it can't be simplified any further, whatever
Steve might mistakenly think. The query is in fact quite a simple one as
queries go, even though it might look complex to anyone not familiar with SQL.
Simplicity and complexity are after all only relative to the experience of
the individual concerned. An equivalent of the above query could be produced
without having to write any SQL by breaking it down into two queries, one
which is the free-standing equivalent of the subquery, and the final one
which joins this to the TblMovie table. But even this does require an
understanding of how to build a free-standing equivalent of the subquery in
design view, so it doesn't make things a whole lot easier.
Ken Sheridan
Stafford, England
kee...@yahoo.com.invalid wrote:
>>Unfortunately Steve, you gave them an answer which was diametrically opposed
>>to what they asked for. If you take another look at the original post you'll
>[quoted text clipped - 33 lines]
>>Ken Sheridan
>>Stafford, England
>
>I won't add or subtract. Most of Access is over my head. I'm amazed that I made
>this DB on my own 7 years ago. But I looked at the example zip's. one of them
>was interesting 3 columns, date, title, and rating.
>
>What I was looking for specifically was actress AND actress. But I have some
>stuff to look at, and it'll take weeks if not months to implement them
>CORRECTLY. But this sample db, worked the same way I Add the actress names to
>my form. with a lookup table. But without serious modification to my table, I'm
>not sure I can make a actress AND actress search.
>I created 12 fields for actress naming them actress01-12. with the Access
>search, I use the general search that searches the entire db any fields any
>case. vs individual fields.
--
KenSheridan via AccessMonster.com
anything else where an AND operation on a single column is appropriate. We
are talking about equality comparisons. You can't dodge the issue by moving
the goal posts. Address the thread, not some hypothetical question of your
own making.
Wrong again Steve. What Tom said was that it should 'look' like this in the
design grid:
[Forms]![NameOfForm]![This]
and
[Forms]![NameOfForm]![That]
i.e. not an AND operation but an OR one.
Does anyone actually buy the crap snake oil you peddle around here? You just
haven't a clue.
Ken Sheridan
Stafford, England
Steve wrote:
>You're the one that needs to understand Boolean logic better and you need to
>understand applying it in a query better!
>
><<Firstly you cannot apply an AND operation to one column.>>
>You certainly can! Suppose you have a text field in a query named Word. You
>can find all words in that field that contain an "E" AND an "O" with the
>criteria Like "*" & "e" & "*" And Like "*" & "o" & "*". You can also do the
>same thing by putting the Word field in two separate columns in the query
>and putting Like "*" & "e" & "*" in the criteria of the first instance and
>putting Like "*" & "o" & "*" in the criteria of the second instance in the
>same row as the criteria in the first instance.
>
><< .....but what Tom said was put the parameters on separate criteria rows
>of a single column in design view...>>>
>You also need to learn to read!
>
>That is not at all what Tom said!! Tom pointed out that my Like Me!This
>should be Like[Forms]![NameOfForm]![This] and my Like Me!That should be
>Like[Forms]![NameOfForm]![That]. And that is correct, my error. In my
>suggested solution, I stated both criteria should be in the same row to
>return an AND search.
>
>Steve
>
>>I think you need to understand Boolean logic a little better, Steve.
>>
>[quoted text clipped - 72 lines]
Steve
[Forms]![NameOfForm]![This]
and
[Forms]![NameOfForm]![That]
That's NOT AT All what Tom said!! You are wrong again and are trying to make
false statements to cover your butt!!
Tom said: (quoted from his post)
"You need to enter criteria in the query that looks more like this:
[Forms]![NameOfForm]![This]
and
[Forms]![NameOfForm]![That]"
Nowhere does he use the word "OR". Note however the word "AND".
Further, look at the OP's later post ---
Quote - "What I was looking for specifically was actress AND actress"
Ken, if you develop applications for other people, you are not worth your
salt. You can't even interpret what is being said. Again, you need to learn
to read. You try and impose what you think other people want rather than try
to understand what they say they want. Further, the OP chastised you for
trying to make his problem more complex than it is when all he wanted was
SIMPLE.
Steve
"KenSheridan via AccessMonster.com" <u51882@uwe> wrote in message
news:a0eedbf7575ee@uwe...
kee...@yahoo.com.invalid
<u51882@uwe> wrote:
>Unfortunately your table design is wrong. Having multiple columns for
>different actresses in a movie is the problem and it will make searching on
>two actresses very difficult. It can be done, but it would be very
>cumbersome. Steve is right about the design needed here; you do need three
>tables, one representing the Movies entity type, another the Actresses entity
>type, and a third which models the many-to-many relationship between them,
>Steve's TblActressInMovie table. You'll see that this has two foreign key
>columns, MovieID and ActressID which reference the primary keys of the other
>tables. What this does is resolve the many-to-many relationship into two one-
>to-many relationships, which is how such relationships are always modelled in
>a relational database. The TblActressInMovie table does not need an
>ActressInMovieID primary key column, however, as the other two columns make
>up a composite primary key. Diagrammatically the model looks like this:
>
>TbleMovie----<TblActressInMovie>----TblActress
tables in the db coincide with the fields in the FORM.
tables:
actors_all_names<---- male & female
color <- color of dvd cases
tower <-4 towers, this is just 4 numbers I created this
table at the 121 record point, when I saw this was going to be a huge db.
videos <- This one combines all the other tables into the
individual fields
-------------------------------
videos table fields =
making it simple, I uploaded the 3 tables to this link
http://img69.imageshack.us/g/tableallactorsnames.jpg/
The actors, videos are the main tables.
I don't have a titles tables, as those get added to the videos table. as
I fill in the videos form.
>
>You are probably thinking that recasting your data into the above model would
>be an overwhelming task, but in fact its not that difficult to do with a
>series of 'append' queries'. We'd need to know the full details of you
>current tables' designs, including all field names and their data types to
>give you the precise details of the queries you need to do so.
>
>When it comes to searching for movies which have two actresses in them, I'm
>afraid its not so simple as Steve thinks. To understand why you need to know
>a little about how a query decides which rows to return. At bottom its quite
>simple, for each row the query applies criteria, and if the answer is TRUE
>then that row is returned, otherwise it isn't. When more than one criterion
>is applied Boolean logic is employed. The three principle operators of
>Boolean logic are AND, OR and NOT. When we use the terms 'and' or 'or' in
>everyday English, however, we don't always use them in the very precise way
>they are used in Boolean logic, so its necessary to be clear exactly what
>these terms do mean:
>
>AND: When this is used each criterion either side of the AND must be TRUE
>for the operation to evaluate to TRUE and a row be returned. So if we look
>at the rows in Steve's TblActressInMovie table and ask 'does ActressID = 42
>AND ActressID = 66' for instance, then that makes no sense as the ActressID
>value in any one row in the table can't be both 42 and 66 at the same time.
>
The above is why I have so many individual actress and title fields. When I add
a actor name to a new record, I need to fill in more fields than one for actor.
And the form uses a lookup box for each of the actor name fields to save
scrolling thru 1042 names.
the actor table is set to indexed, NO duplicates.
KenSheridan via AccessMonster.com
he intended, but it looks clear enough to me.
Ok then Steve, convince me how good you are and how useless I am. Post the
SQL for a query written the way you think it should be done, using the table
definitions you posted, and we'll see if it works. I know my approach does
because I've tested it, and you are welcome to do so with the SQL I posted.
And BTW, we are still waiting for the 'simple' solution you maintained was
possible in the following thread:
http://www.accessmonster.com/Uwe/Forum.aspx/access/124422/New-Page#a0c7c3abb8ddbuwe
Ken Sheridan
Stafford, England
Steve wrote:
><<Tom said was that it should 'look' like this in the design grid:>>
>
> [Forms]![NameOfForm]![This]
>and
> [Forms]![NameOfForm]![That]
>
>That's NOT AT All what Tom said!! You are wrong again and are trying to make
>false statements to cover your butt!!
>
>Tom said: (quoted from his post)
>"You need to enter criteria in the query that looks more like this:
>
> [Forms]![NameOfForm]![This]
>and
> [Forms]![NameOfForm]![That]"
>
>Nowhere does he use the word "OR". Note however the word "AND".
>
>Further, look at the OP's later post ---
>Quote - "What I was looking for specifically was actress AND actress"
>
>Ken, if you develop applications for other people, you are not worth your
>salt. You can't even interpret what is being said. Again, you need to learn
>to read. You try and impose what you think other people want rather than try
>to understand what they say they want. Further, the OP chastised you for
>trying to make his problem more complex than it is when all he wanted was
>SIMPLE.
>
>Steve
>
>> Wee are not talking here about pattern matching or values within a range
>> or
>[quoted text clipped - 55 lines]
KenSheridan via AccessMonster.com
but the query will be a rather cumbersome one. Here's the SQL. Just copy
this to the clipboard, open the query designer, don't add any tables, and
switch to SQL view and paste it in in place of what's there already. Then
open it:
SELECT *
FROM videos
WHERE (actor 01 = [Enter first actor:]
OR actor02 = [Enter first actor:]
OR actor03 = [Enter first actor:]
OR actor04 = [Enter first actor:]
OR actor05 = [Enter first actor:]
OR actor06 = [Enter first actor:]
OR actor07 = [Enter first actor:]
OR actor08 = [Enter first actor:]
OR actor09 = [Enter first actor:]
OR actor10 = [Enter first actor:]
OR actor11 = [Enter first actor:]
OR actor12 = [Enter first actor:]
OR actor13 = [Enter first actor:]
OR actor14 = [Enter first actor:]
OR actor15 = [Enter first actor:])
AND (actor 01 = [Enter second actor:]
OR actor02 = [Enter second actor:]
OR actor03 = [Enter second actor:]
OR actor04 = [Enter second actor:]
OR actor05 = [Enter second actor:]
OR actor06 = [Enter second actor:]
OR actor07 = [Enter second actor:]
OR actor08 = [Enter second actor:]
OR actor09 = [Enter second actor:]
OR actor10 = [Enter second actor:]
OR actor11 = [Enter second actor:]
OR actor12 = [Enter second actor:]
OR actor13 = [Enter second actor:]
OR actor14 = [Enter second actor:]
OR actor15 = [Enter second actor:]);
When you open the query it will prompt you first for the first actor, and
then for the second actor. The way it works is that there are two sets of OR
operations on each actor column. Each set is enclosed in parentheses so that
it evaluates independently of the other set. The first set checks to see if
the first actor entered is in any of the 15 columns, the second set to see if
the second actor is in any of the 15 columns. Between each set is an AND, so
if both the first and second sets of OR operations evaluate to TRUE, i.e.
each of the two actors is in one of the columns, then the row will be
returned.
I'd recommend saving the query in SQL view, not switching to design view as
that would change things around quite a lot. It would work the same, but the
logic won't be as apparent.
Performance should be improved if you make sure all 15 actor columns are
indexed (allowing duplicates) in the table design.
Ken Sheridan
Stafford, England
kee...@yahoo.com.invalid wrote:
>>Unfortunately your table design is wrong. Having multiple columns for
>>different actresses in a movie is the problem and it will make searching on
>[quoted text clipped - 11 lines]
>>
>>TbleMovie----<TblActressInMovie>----TblActress
>tables in the db coincide with the fields in the FORM.
>tables:
>actors_all_names<---- male & female
>color <- color of dvd cases
>tower <-4 towers, this is just 4 numbers I created this
>table at the 121 record point, when I saw this was going to be a huge db.
>
>videos <- This one combines all the other tables into the
>individual fields
>-------------------------------
>videos table fields =
>making it simple, I uploaded the 3 tables to this link
>http://img69.imageshack.us/g/tableallactorsnames.jpg/
>
>The actors, videos are the main tables.
>
>I don't have a titles tables, as those get added to the videos table. as
>I fill in the videos form.
>
>>You are probably thinking that recasting your data into the above model would
>>be an overwhelming task, but in fact its not that difficult to do with a
>[quoted text clipped - 18 lines]
>>AND ActressID = 66' for instance, then that makes no sense as the ActressID
>>value in any one row in the table can't be both 42 and 66 at the same time.
>
>The above is why I have so many individual actress and title fields. When I add
>a actor name to a new record, I need to fill in more fields than one for actor.
>And the form uses a lookup box for each of the actor name fields to save
>scrolling thru 1042 names.
>the actor table is set to indexed, NO duplicates.
>
>>OR: When this is used any one criterion either side of the AND must be TRUE
>>for the operation to evaluate to TRUE and a row be returned. So if we look
>[quoted text clipped - 85 lines]
>>>search, I use the general search that searches the entire db any fields any
>>>case. vs individual fields.
--
kee...@yahoo.com.invalid
Hot dog.. trimmed 155 responses down to 24...Much simpler to wade thru that..
Ok that's a query. I don't know how to make a search box on the db to use this
query.. Anymore help available ?
Oh there were syntax errors. But I found it. You have 'actor 01' it's actually
'actor01' No space. All 15 actor columns were already indexed allowing
duplicates. It's just the all_actors_name table that's indexed not allowing
duplicates.
And thanks for getting it this far..
Tom Wickerath
I was only addressing the obvious error, with the references to the Me
keyword. I was not concerning myself with the query logic, whether the
criteria was ANDed together or ORed. Honestly, I didn't look at it close
enough to comment on that part.
Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________
KenSheridan via AccessMonster.com
developer, will understand the logical impossibility of two ANDed equality
operations on the same column.
Ken Sheridan
Stafford, England
Tom Wickerath wrote:
>Hi Ken,
>
>I was only addressing the obvious error, with the references to the Me
>keyword. I was not concerning myself with the query logic, whether the
>criteria was ANDed together or ORed. Honestly, I didn't look at it close
>enough to comment on that part.
>
>
>Tom Wickerath
>Microsoft Access MVP
>http://www.accessmvp.com/TWickerath/
>__________________________________________
>
>> Wee are not talking here about pattern matching or values within a range or
>> anything else where an AND operation on a single column is appropriate. We
>[quoted text clipped - 16 lines]
>> Ken Sheridan
>> Stafford, England
--
James A. Fortune
wrote:
> Thanks for the clarification, Tom. You of course, as an experienced
> developer, will understand the logical impossibility of two ANDed equality
> operations on the same column.
>
> Ken Sheridan
> Stafford, England
I have a counterexample to your claim :-)
MyField = "This" AND MyField = "This"
Of course, if the first "This" = 'This' and the second "This" =
'That', you could have trouble trying to return any records.
Concerning the original question, last year I posted some code that
goes part of the way in having Access create a search form
automatically from a specified Access table. The link is:
http://groups.google.com/group/microsoft.public.access/msg/0fd179f25848911f
I didn't do much streamlining of the code, so it has too many
variables, but does go a long way toward the goal. I think Lyle
posted code in comp.databases.ms-access (CDMA) that does something
similar, but in a much more refined manner. I think the most serious
problem I had left was trying to fit the boxes nicely on the form,
given the height limit of Access forms. The plan was to create the
code creating the dynamic SQL search string behind a command button
such that the conditions specified on the form 'AND' together. BTW,
Merry Christmas Tom!
James A. Fortune
MPAP...@FortuneJames.com
My company scored a 97 on our first attempt at getting ISO certified
to the AS 9100 standard (aerospace ISO 9001:2008). The average score
for first time attempts is 86. No discrepancies from the standard
were discovered in any of my databases thanks to the advice of an
excellent auditor who consulted for us. One of the three minor
discrepancies discovered during the four day audit was when a hole was
drilled in a tool (tools are the structures aircraft companies use to
manufacture plane parts) in response to an email request from a
customer (an email is not formal enough for such a change to happen).
The certification will allow us to bid on millions of dollars of
projects that require such certification and the percentage of
projects that require the certification are growing. Understandably,
there was adequate pressure from customers to make getting certified a
top priority. Although adhering to the standard is a continuous
process, I can finally get back to some older projects.
kee...@yahoo.com.invalid
<u51882@uwe> wrote:
>If all has gone as planned you should now be able to open the form and select
>one or two actors in the combo boxes, upon which the form will be requeried
>to show only the films for in which the actor or actors have appeared.
I figured how to make the combo box and get it to work with the FULL name of
BOTH actors, but being naturally lazy, is there a SIMPLE way to modify this
search you created so I can use Partial names and NOT have to enter BOTH names
?
ie: Just want to find those movies with SCHW, cause spelling out Arnuld's full
name if nothing else is just asking for misspellings. And I might only want
Arnold's movies, and entering 'Arnold would get everything by Arnold
Schwarzenegger, Franz Arnold, Arnold Rockwood ,etc...
If not, this works great as is.. I just need to watch my spelling and remember
this is a 2 name full search. It's great. I use the single page form.
KenSheridan via AccessMonster.com
you just select it from the combo box's drop down list. Make sure that the
combo box's AutoExpand property is True (Yes in the properties sheet). That
way, rather than scrolling down the whole list, you can type a name in, but
the clever thing is that, as you type each character it will automatically go
the first match for you. So, by the time you've typed Arn it should either
have found the first Arnold in the list, or at least be very close to it so
you then only have to scroll down a few items in the list and click on the
right one. If you also set the combo box's LimitToList property to True (Yes)
that will only allow you to enter a name which exists in the list.
Ken Sheridan
Stafford, England
--
kee...@yahoo.com.invalid
<u51882@uwe> wrote:
>The point of using a combo box is that you don't need to spell out the name,
>you just select it from the combo box's drop down list. Make sure that the
>combo box's AutoExpand property is True (Yes in the properties sheet). That
>way, rather than scrolling down the whole list, you can type a name in, but
>the clever thing is that, as you type each character it will automatically go
>the first match for you. So, by the time you've typed Arn it should either
>have found the first Arnold in the list, or at least be very close to it so
>you then only have to scroll down a few items in the list and click on the
>right one. If you also set the combo box's LimitToList property to True (Yes)
>that will only allow you to enter a name which exists in the list.
>
>Ken Sheridan
>Stafford, England
There's probably a small problem here. What you explain works exactly that way
in the form I created on using the TABLE ALL_ACTORS_NAMES. But using the combo
box to LOOKUP the names from the query, doesn't do that because the query is
just code. I don't get a drop down list using the combo box on the query..
I mean when I tap the combo box, it pops up an input box asking 1st actress,
then 2nd actress, and after that it does it's magic, and picks JUST those
movies where BOTH are in the movie.
In my Actor01-12 fields, I start typing a name, and it starts filling in names.
Then I can hit the box and it expands to show all matches FROM that point down
in the ALL_ACTORS_NAMES table.
But there are no names in the query.
kee...@yahoo.com.invalid
>On Tue, 22 Dec 2009 14:12:44 GMT, "KenSheridan via AccessMonster.com"
><u51882@uwe> wrote:
New problem... I know I can re-create the form and all will go back to normal,
but that's a full day.. I know I should have had a backup form inside this DB,
but never thought of the day I couldn't open the form..
I was trying to make it do the drop down thing. Switched from a combo box to a
list box. Then deleted the list box... Things froze. had to break out to quit.
reopening the db, the form no longer opens.
used the auto repair and compact. No luck still can't open it.
renamed the query. nada..
So how do I repair this or at least open it ? It doesn't open in design view
either.
None of the tables seem to be damaged.
KenSheridan via AccessMonster.com
last back up before you made the amendments.
If not try selecting the form in the database window, copying it to the
clipboard and then pasting it under a new name.
If the new copy still won't open try opening a blank new database and
importing all the objects from your existing database into it. If it still
won't open I think its back to the drawing board.
Ken Sheridan
Stafford, England
kee...@yahoo.com.invalid wrote:
>New problem... I know I can re-create the form and all will go back to normal,
>but that's a full day.. I know I should have had a backup form inside this DB,
>but never thought of the day I couldn't open the form..
>
>I was trying to make it do the drop down thing. Switched from a combo box to a
>list box. Then deleted the list box... Things froze. had to break out to quit.
>reopening the db, the form no longer opens.
>used the auto repair and compact. No luck still can't open it.
>renamed the query. nada..
>So how do I repair this or at least open it ? It doesn't open in design view
>either.
>None of the tables seem to be damaged.
--
KenSheridan via AccessMonster.com
changed the form's RecordSource to the name of the query the form became no
longer directly based on the table, but on the query. A query can be used
just like a table, however, so you can base a form or report on it, join it
to other tables or queries in another query etc, just as if it were a real
table.
In fact when we talk about a 'table' we usually mean a 'base table' i.e. a
real table which exists as a permanent object in the database. But a table
can also be the 'result table' of a query, i.e. a virtual table. Both can be
used in the same way, though in some cases the result table of a query might
not be updatable, depending on the query, so while its fine for viewing data
in a form or report, cannot be used for editing data. In your case the query
you created, with the parameters referencing the two unbound controls on the
form, is updatable as the parameters merely restrict what rows are returned,
which is why its fine as a RecordSource for the form.
In a good database application a table or query is never used directly in
datasheet view for entering or editing data; that should always be done in a
form based on the table or query. It takes a little extra work, but 'no pain,
no gain'. A form can be in datasheet view of course, so it looks just the
same as a query or table in datasheet view, but that rules out the ability to
include unbound controls like your two unbound combo boxes. To see the rows
together rather than in single form view a better solution is a form in
continuous forms view; this gives you the continuous layout, but you can
include unbound controls like the two combo boxes in the form's header or
footer.
Ken Sheridan
Stafford, England
kee...@yahoo.com.invalid wrote:
>>The point of using a combo box is that you don't need to spell out the name,
>>you just select it from the combo box's drop down list. Make sure that the
>[quoted text clipped - 9 lines]
>>Ken Sheridan
>>Stafford, England
>There's probably a small problem here. What you explain works exactly that way
>in the form I created on using the TABLE ALL_ACTORS_NAMES. But using the combo
>box to LOOKUP the names from the query, doesn't do that because the query is
>just code. I don't get a drop down list using the combo box on the query..
>I mean when I tap the combo box, it pops up an input box asking 1st actress,
>then 2nd actress, and after that it does it's magic, and picks JUST those
>movies where BOTH are in the movie.
>In my Actor01-12 fields, I start typing a name, and it starts filling in names.
>Then I can hit the box and it expands to show all matches FROM that point down
>in the ALL_ACTORS_NAMES table.
>But there are no names in the query.
>
>>>>If all has gone as planned you should now be able to open the form and select
>>>>one or two actors in the combo boxes, upon which the form will be requeried
>[quoted text clipped - 10 lines]
>>>If not, this works great as is.. I just need to watch my spelling and remember
>>>this is a 2 name full search. It's great. I use the single page form.
--
kee...@yahoo.com.invalid
<u51882@uwe> wrote:
>Firstly, is the .mdb file itself regularly backed up? If so go back to the
>last back up before you made the amendments.
>
I strictly follow my own advice 'backup, backup, backup'. I had 10 working
backups on hand. I just panicked cause I didn't have the db FORM itself backed
up in the DB. I do now..
kee...@yahoo.com.invalid
<u51882@uwe> wrote:
>You can't use a combo box directly in a query, only in form. When you
>changed the form's RecordSource to the name of the query the form became no
>longer directly based on the table, but on the query. A query can be used
>just like a table, however, so you can base a form or report on it, join it
>to other tables or queries in another query etc, just as if it were a real
>table.
I sure miss dbase 1-4..
Tom Wickerath
> BTW, Merry Christmas Tom!
I just read your reply....I hope you had a Merry Christmas as well.
> My company scored a 97 on our first attempt at getting ISO certified
> to the AS 9100 standard (aerospace ISO 9001:2008).
Now you have me curious. What company do your work for? You can reply by
private e-mail if you don't wish to tell the world.
Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________
"James A. Fortune" wrote:
> .
>
Tom Wickerath
Yes, of course.
Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________
David W. Fenton
> You of course, as an experienced
> developer, will understand the logical impossibility of two ANDed
> equality operations on the same column.
Certainly for equality, but not for LIKE (which is not, of course,
relevant to the example).
--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
KenSheridan via AccessMonster.com
WHERE MyDate >= [Start date;]
AND MyDate < DATEADD("d",1, [End date:])
which is equally irrelevant to this example of course.
Ken Sheridan
Stafford, England
David W. Fenton wrote:
>> You of course, as an experienced
>> developer, will understand the logical impossibility of two ANDed
>> equality operations on the same column.
>
>Certainly for equality, but not for LIKE (which is not, of course,
>relevant to the example).
>
--
David W. Fenton
> And also for a value within (or without for that matter) a range,
> e.g.
>
> WHERE MyDate >= [Start date;]
> AND MyDate < DATEADD("d",1, [End date:])
>
> which is equally irrelevant to this example of course.
Naturally. Then there's this:
http://tinyurl.com/y9dggxj =>
http://thedailywtf.com/Articles/SQL-Error-191-Nested-Way-Too-Fing-Dee
ply.aspx