Criteria Question

[knw]

I have been messing with a database attempting to run a query with multiple tables. Each of the table information I am inserting into the table have a field for the "Start Month". I have renamed all the fields so that they are unique, however they all still contain a month name (i.e. January, February). I want to be able to set the criteria field for each of the Start Month fields to a specific month...I have been using the expression ex. "November" to filter for just the November Start Months. This will work when I put it for just one of the Start Months, but for the others (ex. 3 currently) it will only bring up blank/no entries in the query.

Does anyone have any suggestion for this, or any other criteria expression I could use that would make this work?

Help please!

 

[KeithG]

Can you post your SQL?

 

[CraigDolphin]

Lets say your query has two field containing the start month: field 1 and field 2.

If you put a criteria on the same row for each field this is like saying
Show me all records where field 1 = "november" AND field 2 = "november"
If you put them on different critria rows then its like saying
Show me all records where field 1 = "november" OR field 2 = "november"

Now, in your query, if you have multiple fields, with criteria all on the same line, you only need one of those fields to not match (null, december, whatever) in all the records and you'll get no records back.

So, if you really do want to show only records that really have "November" in each and every one of those fields, and you're getting no results, you should check to see if there is any related data that actually meets that condition in every table. If there is, but it's not showing up, then maybe you have a join problem. Remember, null is a value that does not equal "November".

If what you really want is show me all related records where November appears in at least one of those fields, then try playing the criteria for each field on a separate row in your query grid.

 

[knw]

I understand what you are saying about putting "November" on different rows in the criteria grid. That's awesome I didn't know that was possible. It is now working the only thing is, because the different fields are showing up on same row in the query table and because of this there are months displaying that should not be. Like in the first row it is displaying one entry for November, but because all the fields are displaying on the first line it is also showing October. Do you know how to eliminate this so that it will only display November and not the other months even though they are located on the same row?

I appologize if this is not making sense....the criteria section is new to me...Thank you so much for your help though!! Truely appreciated!

Lets say your query has two field containing the start month: field 1 and field 2.

If you put a criteria on the same row for each field this is like saying
Show me all records where field 1 = "november" AND field 2 = "november"
If you put them on different critria rows then its like saying
Show me all records where field 1 = "november" OR field 2 = "november"

Now, in your query, if you have multiple fields, with criteria all on the same line, you only need one of those fields to not match (null, december, whatever) in all the records and you'll get no records back.

So, if you really do want to show only records that really have "November" in each and every one of those fields, and you're getting no results, you should check to see if there is any related data that actually meets that condition in every table. If there is, but it's not showing up, then maybe you have a join problem. Remember, null is a value that does not equal "November".

If what you really want is show me all related records where November appears in at least one of those fields, then try playing the criteria for each field on a separate row in your query grid.

 

[CraigDolphin]

I think here we run into more serious difficulties.

In my post, remember, I qualified my advice by saying 'related data' and 'related records'.

If your database is correctly designed and normalized, each table you have in your query should be joined by a key field of some kind. The very fact that you have multiple tables with multiple start_date type fields suggests very strongly to me that your database table and relationships design is badly flawed.

If you have a properly normalized database design you shouldn't have similar information repeated in different tables. Or repeated in a field within a table.

My guess is, that with what you have, you need to query a table with a start date for the criterion "November" and get a bunch of results. Let's pretend we put those results in box A (actually a select query).

Then you want to separately query another table in a similar fashion and get some more results from that table. Let's pretend we put those in box (query) B.

You do this for several tables and each one gets some results placed in a series of boxes/queries.

Then, you want to assemble all the results from box A, box B, box C.... etc into one big box.

To put all the boxes into one box you need a specialized query: this is called a union query.

Firstly, each box represent a separate select query and each of these must contain the same fields (F1,F2,F3, say), and meet whatever criteria and conditions you want to impose on the data (dates, month, whatever)

Secondly, you need to combine them into one query. To do this you create a new query in SQL view, then input something like this:

select F1, F2, F3
from boxA
UNION
select F1, F2, F3
from boxB
UNION
select F1, F2, F3
from boxC;

and so on for all the boxes (aka select queries)....

Doing this should solve your immediate problem. But, my strong advice is to learn about database table normalization and re-create your db so that it is normalized.

Then, you get could to the same result with one very simple select query.