Use criteria to filter only select records within a field

[tx333]

I have a table with the following fields and data:

Prod_Cat: 1, 2, 3...10
New_Money: 0 through 999

I would like to create a query that excludes records when Prod_Cat = 6 and New_Money is not greater than 0 and Prod_Cat = 9 and New_Money is nor greater than 0. Is there a way to assign criteria in the New_Money field that applies only to those Prod_Cat types, but not to the rest of the Prod_Cat types without creating a separate query and then merging?
Trey

 

[randysmid]

Hi,
Are you going to use a form that the user will select certain criteria, and using a query, generate a report? If so, I wrote an FAQ on this topic. Actually, I have two FAQ's. One uses a form and a query, with the report's recordsource being based on the query. The other one uses "filter" that is sent directly to the report. Here they are:
form and query: faq703-2696
filter: faq703-2657 HTH,
Randy Smith
California Teachers Association

 

[tx333]

I don't think that is quite what I'm looking for, I'm not using a form and the filters will not change over time. As I update the source table in the future, I plan to use the same criteria to eliminate the same record combinations. For example:
I want all instances of Prod_Cat:1 regardless of the number in New_Money and no instances of Prod_Cat: 6 when New_Money = 0 if my table has the following records:

Prod_Cat New_Money
1 100
2 0
3 500
6 0
6 500


The results I would like would be:
Prod_Cat New_Money
1 100
2 0
3 500
6 0 (Delete this record)
6 500
etc.

I may not be explaining the problem clearly, or I may not have understood the FAQs you directed me to. I will read them again.
Thanks.
Trey

 

[randysmid]

Hi,
I am beginning to understand a little bit more. First, right click on the line connecting the two tables together. Select Join Properties, and make sure the option selected will get you all Prod_Cat records. The default on joins is to provide only those records where both are matched. You may want to save this as qryProdCat1.
Now create a new query, and use qryProdCat1 as the recordsource. You can now use criteria to exclude certain conditions, such as Prod_cat <> 6. Is this enough to get you started in the right direction? HTH,
Randy Smith
California Teachers Association

 

[tx333]

Right now, the query I built has all the records, and I set the criteria to include only records with New_Money > 0, which is right for 2 of my Prod_Cat types, but excludes other Prod_Cat types that can have 0 in New_Money. I don't want to exclude all of any one Prod_Cat.

I thought about building two separate queries, one that brings in all but the &quot;no New_Money&quot; Prod_Cats and one that captures the 2 Prod_Cats that need to have a New_Money value > 0 and then combining those two tables, but I was looking for a more efficient solution, if it exists.
Trey

 

[randysmid]

Hi,
I personally believe that the two separate queries is the best way to go. First, it is easily maintainable. Second, you don't really gain any speed advantage in trying to accomplish it in just one query.
In some of my more complicated systems, I am using up to 8 queries to get the data into a format usable by my reports. In those instances, some of the data needs to be repeated, and other instances of data that needs to be excluded. By separating the queries, it is much easier to debug if the results are incorrect. Just my opinion! HTH,
Randy Smith
California Teachers Association

 

[r937]

if you already have a query that filters some records, display the query in SQL View and you should see something like

WHERE New_Money > 0

replace this with

WHERE NOT ( Prod_Cat IN (6,9) AND New_Money <= 0 )

save and open in Design View and it will show you how to do it there




rudy

 

[tx333]

The debugging issue makes sense, I'll go that route.
Thanks again for all your help and rapid response.
Trey