Query Assistance

[access2010]

Could we please receive a suggestion on how to have our Access 2003 Query Work based on;

If the Transaction_Type type contains any of these words
"Bought" Or "Purchased" Or "Selling"

And if the Investigate = Yes

Your assistance will be appreciated.

Thank you.
Nicole

 

[June7]

SELECT Investments01_tbl.Stock_Name, Investments01_tbl.Transaction_Type, Investments01_tbl.Investigate
FROM Investments01_tbl
WHERE (((Investments01_tbl.Transaction_Type) In ("Bought","Purchased","Selling")) AND ((Investments01_tbl.Investigate)=True));

or

SELECT Investments01_tbl.Stock_Name, Investments01_tbl.Transaction_Type, Investments01_tbl.Investigate
FROM Investments01_tbl
WHERE (((Investments01_tbl.Transaction_Type) <> "Watch" AND ((Investments01_tbl.Investigate)=True));

 

[arnelgp]

see also query1.

 

[access2010]

Thank you both for your suggestions>

What we are trying to do is print a report if ANY of these four criteria's are met;

If transaction type contains either of the words, Bought, Purchased or Selling or if Investigate contains Yes.

I am sorry if we gave you the wrong information.

Crystal

 

[June7]

So you want OR instead of AND?

Do you want user to make choices on form and only print report if user chooses those values? Use code behind form.

Or apply that filter criteria in DoCmd.OpenReport WHERE argument and use report OnNoData event to cancel report if there is no data.

 

[access2010]

Sorry for my miscommunication.

We would like to print a report if ANY of the four criteria are shown.

Crystal

 

[bob fitz]

Sorry for my miscommunication.

We would like to print a report if ANY of the four criteria are shown.

Crystal

Then use:

SELECT Investments01_tbl.Stock_Name, Investments01_tbl.Transaction_Type, Investments01_tbl.Investigate
FROM Investments01_tbl
WHERE (((Investments01_tbl.Transaction_Type)="Bought" Or (Investments01_tbl.Transaction_Type)="Purchased" Or (Investments01_tbl.Transaction_Type)="Selling")) OR (((Investments01_tbl.Investigate)=Yes));

 

[CJ_London]

If transaction type contains either of the words,

I presume you mean equals, not contains?

 

[access2010]

So you want OR instead of AND?

Do you want user to make choices on form and only print report if user chooses those values? Use code behind form.

Or apply that filter criteria in DoCmd.OpenReport WHERE argument and use report OnNoData event to cancel report if there is no data.

Thank you June 7, for your question.

We would like to print our report if ANY of the individual choices are made

Nicole

 

[access2010]

I presume you mean equals, not contains?

Thank you CJ_London for your note.

We would like to print our report if ANY of the individual choices are made

 

[access2010]

Then use:

SELECT Investments01_tbl.Stock_Name, Investments01_tbl.Transaction_Type, Investments01_tbl.Investigate
FROM Investments01_tbl
WHERE (((Investments01_tbl.Transaction_Type)="Bought" Or (Investments01_tbl.Transaction_Type)="Purchased" Or (Investments01_tbl.Transaction_Type)="Selling")) OR (((Investments01_tbl.Investigate)=Yes));

Thank you for your Query, bob fitz.

What we are trying to print is any records that MAY contain,
Transaction_Type is Bought
or if Transaction_Type is Purchased
or if Transaction_Type is Selling
or if Investigate is Yes

Any records that MAY contain any of the above choices

Nicole

 

[bob fitz]

Thank you for your Query, bob fitz.

What we are trying to print is any records that MAY contain,
Transaction_Type is Bought
or if Transaction_Type is Purchased
or if Transaction_Type is Selling
or if Investigate is Yes

Any records that MAY contain any of the above choices

Nicole

Is that not what the query I suggested gives you

 

[June7]

And my suggestion should do that if you replace AND with OR.

 

[access2010]

Thank you all for your suggestions.
The Query below is giving us the information that we are looking for.


SELECT Investments01_tbl.Stock_Name, Investments01_tbl.Transaction_Type, Investments01_tbl.Investigate
FROM Investments01_tbl
WHERE (((Investments01_tbl.Transaction_Type) Like "*Bought*" Or (Investments01_tbl.Transaction_Type) Like "*Purchased*" Or (Investments01_tbl.Transaction_Type) Like "*Sold*")) OR (((Investments01_tbl.Investigate)="yes"));

Crystal

 

[access2010]

see also query1.

Thank you, arnelgp for your suggestion, which is producing the results that we want.

Fabiola

 

[access2010]

Do not use LIKE unless you really are looking at a partial string. It prevents the database engine from using an index to resolve the query. That means RBAR processing. Row by agonizing row. The query engine must process every single row in the table to determine what records to select. Obviously, the query will get slower and slower over time as the row count grows. Poor practice.

If you are looking at a partial string, consider, very strongly, changing your database to use a discrete value. Perhaps one that groups a long list of options.

Also, What is the data type for Investigate? If it is YesNo, then use True instead of "yes" to select rows.

Thank you Pat for your suggestion.

We are using the suggestion of arnelgp.

Fabiola

 

[June7]

Sample database posted shows Transaction_Type field has a single word value and only 4 values. My suggestions using IN() or <>"Watch" allow a shorter and simpler SQL.

 

[arnelgp]

PS, back in post #4 you indicated that @arnelgp 's solution didn't work. In #14 you posted a solution with LIKE which isn't what arnel used in his sample db although he never told us what he actually did. I never looked at it earlier because I can't be bothered with "try this" solutions.

the solution is not for you so why bother.
and we are all grown up people here, so there is no need to full elaboration and explanation of what the db is supposed to do.
the OP knows what he/she wants and she finds it on that section of the db.

Sample database posted shows Transaction_Type field has a single word value and only 4 values. My suggestions using IN() or <>"Watch" allow a shorter and simpler SQL.

yes, all the samples are "single" words.
and my proposal was:

In ("Bought","Purchased","Selling")

 

[access2010]

Thank you all for your suggestions.
I was off work while sick and another person tried to correct our problem, without success.

Attached is our updated database with the query.

The field "One Record" = MUST = contain the word "RESEARCH"
Plus if the field "Trade" contains ANY of these words, "BUYING" or "PURCHASED" or "SELLING"
= AND = if the field "Investigate" contains the word "YES"

I am sorry for the mix up.
Nicole

 

[CarlettoFed]

Where is the problem?