Exclude data if multiple fields in a query have a value of 0

[NickC]

Hey all,



The issue that I am trying to solve is regarding different products that my company sells. I am trying to create a query that pulls in multiple products (About 5-8) and matches the product with the company ID that we sell it to. I can match the products to the company ID alright, but we have data from the past 100 years. Many of these companies no longer carry any of our products or no longer exist. This is where my problem comes in:

We give every company a 1 or 0 value to indicate if it carries each product, a 1 being yes, and 0 being no. What I am hoping to do is somehow put in a formula that will keep all of the companies that carry at least one of the products, but will exclude the companies that do not carry a single product and have a 0 for every product category. We are not interested in looking at companies that do not carry our product, but if they only carry one or two of our products would like them included in the table the query produces. I was tying a few different IF formulas as well as some OR formulas, but cannot seem to get the results that I am hoping for.

If what I am trying to do is not clear please let me know and I will try to rephrase it. Also I am not sure if the terminology that I am using is correct, especially with my use of the word formula. I am fairly new with working in Access, so any help is greatly appreciated. Thank you anyone that is willing to help and also anyone that was just willing to read this post and see if you could offer some advice.

Have a great day everyone,
Nick

[hapm]

How does the table for the companies look like? is there a field in the table for each categorie, with a 1 or 0 in it? Or do you use an additional table to store this n:m dependency between categories and companies? Somem more detail about your current table structure would be nice to help you out.

[NickC]

I'm sorry about the lack of detail, like I said I am new at Access and did not know exactly what would be required for people to understand my post. I also did not want to post way too much unnecessary info though. Hopefully this will help. The first column in my current table is the company ID numbers. The second-ninth column is each product then. The values in the first column (company ID numbers) are eight digit codes that we use to keep the company names anonymous. Every other column (The second through the ninth) is full of 1s or 0s that correspond with each company ID number in the first column. Each row is then a company ID number matched with 8 different 1s or 0s to show which product the company has purchased from us, and which ones it has not.

If there is a different format that I should use please let me know. I am open to change anything that will help me eliminate the companies that no longer carry our product. Thank you for your response.

[recyan]

Would suggest you post the ERD.
"The second- ninth column is each product" - This could be a potential design issue.

Thanks