Select only fields = True (1 Viewer)

[TLO]

Hi! I'm having problems creating a query for a Product that only returns the fields where the values are True or Yes. Screen shots of my tables are below.

I want a list of all records for Product eAG where the fields from T_PROD_REQs that are True and the fields from T_PROD_COUNTRIES are Yes, so I created a select query. When I run my select query for all EAG records, I get all fields, regardless of value, which makes sense. But if I enter True and Yes criteria in all of the columns, my results are all empty. What am I doing wrong?

I'm not terribly comfortable with SQL unless I have an example to use, but any guidance in Design view or SQL is greatly appreciated.

Thanks in advance for your help!

 

[plog]

You don't have a database you have an Access based spreadsheet. You need to read up on normalization:

Database normalization - Wikipedia

en.wikipedia.org

Thats the process of properly setting up your tables. What you want to do is trivially easy with a properly set up table structure. With what you have its going to be an enormous task.

In table T_PROD_REQS you have a field called '101A'. Does the 101 or the A represent anything outside of your database? Does 101 represent the department or file room or anything in your organization? Or is it just a way to differentiate field names in this table of many fields?

 

[TLO]

Thanks for the quick reply! What I'm trying to do is migrate a spreadsheet layout to a database and am definitely having problems envisioning the table design..

My master spreadsheet has rows of unique requirements (e.g. 10.1A) and each column is a country with an indicator as to whether each requirement is applicable to that country. Right now, I maintain a separate spreadsheet for each Product, that only includes countries that are applicable to that product.

I created a table of all requirements from the rows in my master spreadsheet, a table for each country listing those requirements, and table listing the applicable requirements for each product, and a table listing the applicable countries for each product. I'm trying to create a query where I can pull only the applicable requirements for the applicable countries for a specific product.

 

[plog]

I take that to mean 10.1A does have meaning outside your spreadsheet--It references some sort of document listing requirements. While you should definitely read up on normalization, work through a few tutorials and then apply what you learn to your data; I will tell you how your product requirements table should be structured.

One goal of a database is to accomodate data vertically (with more rows) and not horizontaly (with more fields). So, to know which requirements go to which products you simply need a 3 field table:

tblRequirements
field name, data type, description
req_ID, autonumber, primary key
ID_Product, number, foreign key to tblProducts which tells you which product this requirement is for
req_Number, text, this will hold what you currently have as your field names (101A, 102H, etc)

That's it. Those 3 fields let you know which requirements are applicable to all your products. There is no True/False field because if a product and a requirement are in the above table it means true.

That's just your requirements table. You still need to read up on normalization and do all your data following its rules. I suggest you read up, set up the tables you think you need in a blank Access database, complete the Relationship Tool in that database, expand all your tables there so you can see all the fields, then post a screen shot of it back here so we can verify you have the correct table structure. Then we can move on to building your query you initially posted about.

 

[MajP]

To add. Once you figure out the correct structure it will be so much easier to manage. You will probably end up with 3 or four fields vs whatever you have now. Also you can use a normalizing query to take your current data and import into your new structure. You do not have to manually enter the old data into the new structure.