Criteria set up for a Query

[JBC]

Greetings, this is my first time on this forum. Hope this works.

I have a database with over 1,000 records. I have 5 fields (columns) in the table that are part of the criteria. Each record has data in each of these five fields. One of the fields (column) has an easy criteria. I have two entries in this field "Engineering" or "Marketing". I am only interested in "Marketing", so I simply type in "Marketing" and that part of the criteria for the query works fine. However, I need to build a criteria that now brings into play all 4 fields (columns). If any record contains "R1", I want to include it into the query. If I type in R1 in the "criteria" row for each record of the query, I will only get the records that actually have R1 in each of the four columns. This result for the query is only part of what I need from the query. I need all records that have R1 in any field (Column). So, one record could have R1 in the first column and "!" in the other four columns or any combination. I actually tried to set it ups such that the "criteria" row in the query had (column 1) R1, (column 2) !, (column 3) !, (column 4) !, (column 5) !. Then on the on the "or" row, i changed the combination so that it was R1, R1,!,!,! and then on the next row, R1,R1,R1,!, Then on the next row, R1,R1,R1,R1,R1, etc., until all possible combination had been met. I ran out of rows. How can I set this up so that query returns what I seek. JBC

[ItsMe]

If the OR operator does not suffice then I will guess you do not have a normalized structure for your tables. It would indicate to me that you have "Committed Spreadsheet" by building out columns in your table instead of relying upon Key values and creating additional Rows/Records. Having said that, 5 columns does not seem excessive.

Perhaps you can explain the field names to your table and how they may relate to your project as well as some basics about how your tables are joined. If you like, you can upload your DB for review. Be sure to remove personal data, compact and repair, and then ZIP before uploading.

[JBC]

Sure, each row is a software feature. Each column is a system in which the feature is to work. So, feature A would have an R1 in a column (system) if it was in release 1 of the code base to support that system. If there is not a release to support that specifica column (system), the entry is "!". So, I want to see all records that have R1 in any of the 5 systems (columns). I will then want to do a query with criteria to see R2 in any of the columns, etc. Some entries are "tbd" and some are "fut" meaning we are thinking of it but it is not assigned to a release.

[JBC]

i only have 1 table

[JBC]

If I could add rows to the "design view" of the query (the section with columns and rows below the table), i would could have a solution. Is it possible to add rows to finish out the combinations?

[JBC]

My table is a download from another database (DOORS). I want to use Access for various reasons and not DOORS. So, I hope I do not have to deconstruct the table such that each system has its own table. That would seem to defeat the purpose of a table.

[JBC]

Okay, I figured out one way to do this one. I used the "not" criteria function. I was able to say what I did not want to pull for each column and it seems to work. Let me review and see if this did the trick.

[JBC]

That did not seem to work. I put in: Not "R2" Or "tbd" Or "fut" for each of the 5 columns on the criteria row. However, I still see rows in the result that have "tbd" or "fut" in the cell under columns.

[ItsMe]

... Is it possible to add rows to finish out the combinations?

Most anything is possible. I would probably go with normalizing the data structure. Relational databases will have at least two tables. If you have only one table then you do not have a way to create a relationship and will have to commit to adding columns to your table.

Suggest studying up on Normalization, RDBMS, Primary Key, Foreign Key, etc. Create two tables, one for Release and one for System. With that, determine how adding records to the table(s) in a normalized way will benefit a query.

[JBC]

it seems i need a way to write a query criteria that takes into account all the columns (systems) simultaneously and not one column at a time - i think.

[JBC]

Yes, will do. It seems that is the only way. Add tables and "normalize". Too bad. Now I need to do more simlpy download out of DOORS.

[June7]

A UNION query could manipulate the one table into a normalized structure. Then use the UNION query as source for filtering records.

Does the table have a unique ID field - maybe an autonumber type?

SELECT ID, EngMarkField, "1" AS Source, DataField1 AS Data FROM tablename
UNION SELECT ID, EngMarkField, "2", DataField2 FROM tablename
UNION SELECT ID, EngMarkField, "3", DataField3 FROM tablename
UNION SELECT ID, EngMarkField, "4", DataField4 FROM tablename
UNION SELECT ID, EngMarkField, "5", DataField5 FROM tablename;

Subsitute with your actual field and table names.

UNION must be typed or copy/paste in SQL View window of query builder. Limit of 50 SELECT lines.

You should even be able to use the UNION as source for a CROSSTAB matrix.

[JBC]

Okay, I will read up on UNION queries. Not familiar with them, thanks. Does the table have a unique ID Field? Field equals column in Access terminology. Not sure this makes sense to me but let me look at UNIONs first and get back to you. I should note that my "Primary Key" a unique DOORS feature ID. That is not useful right now as I only have one table. So, 1000s of features (each with an ID - FLXXXX) in rows and 5 systems as columns. The cells under the column are fille in with R1, R2, TBD, or "!" if nothing was entered.

[June7]

Unique ID field means value does not repeat. Sounds like the primary key DOORS feature ID would be that.

[JBC]

What I have started to do is run a query for each field (column)/system. So, I have System A, System B, System C, System D, System E. I put in the criteria of Not "R2", and "R3" and "TBD", "!", "Fut" and only selected one column for that query. It returns the right informatoin. If I add another column and put in the same criteria I only get the rows (records) that have the match between the two columns - not what I want. I want the records (features) that have R1 for each system (column). I only get this if I do the queries one column (system) at a time.

correct, the DOORS feature ID does not repeat - Primary Key.