Query Assistance (1 Viewer)

[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

 

[Pat Hartman]

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.

 

[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

 

[Pat Hartman]

If @arnelgp suggested "like", it is because YOU SAID -
If the Transaction_Type type contains any of these words
"Bought" Or "Purchased" Or "Selling"

Which implies, that the type might be several words long such as
"Selling single items"
"Selling old inventory"

and you do not want to have to type the entire string.

So IF the Type is just the single word, then he misinterpreted your sentence - which could be read two ways. Remember, not all posters here speak English as their native language and who knows what your statement ended up as in whatever language arne speaks if he needed to use Google Translate.

So, if you do need "LIKE" because your type code is actually a phrase rather than one word, then LIKE is the solution you need. HOWEVER, that doesn't make it right or good. Using LIKE presents the very real problem I suggested. It is not a criticism of the solution @arnelgp suggested. You need to rethink your types and how they are used so that you don't need to use LIKE to pick out what you need.

LIKE should be reserved for searches of things like names or addresses where you really do need a fuzzy type of search. You NEVER design an application that requires fuzzy searching on something like type or category.

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.

 

[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.

 

[Pat Hartman]

I'm with June here. But only if your data is actually what you posted in the sample db.