How to structure WHERE clause in query (1 Viewer)

[tmyers]

In the database I have been working on, I am trying to redo a query a friend did for me. However since he had done it, I have completely restructured the tables and now no longer know to get it to do what I am after.

I am hoping to get some insight so I can better understand this (and not have to ask a question like this again).

I have 4 tables involved in this (don't know if they're all relevant). The tables are tblJobDetails, tblDrawings, tblDrawingfixtureType and tblFixtureType. I am trying to make a query datasheet to input types into table tblFixtureTypes, but keep them "under" a certain job (trying to use JobID in tblJobDetails). The problem I have run into is JobID is present in tblDrawings, but no further down that chain.

The couple queries I have done either show every type in the DB, or nothing. I am not able to get it to show just the types for that given job. Am I trying to tie it to the wrong thing? The initial criteria I tried was [Form]![JobQuote]![JobID], but it doesn't work. The query doesn't return anything with that in it.

What am I doing wrong on this one? I think I am not understanding how to properly use WHERE in SQL.
I can attach the DB if required.

 

[theDBguy]

Hi. Attaching the DB (zipped) would probably be a good idea.

 

[tmyers]

@theDBguy Added it to OP.

 

[plog]

I am trying to make a query datasheet to input types into table tblFixtureTypes, but keep them "under" a certain job (trying to use JobID in tblJobDetails)

I don't understand what this means. But I have 2 points to make:

1. You should use forms based on tables to input data into them. If you want to put data into tblFixtureTypes you should have a form/subform based on it to input data.

2. According to your Relationship Tool tblFixtureTypes lives outside tblJobDetails, it's essentially a list of valid types. Yes, they are related via 2 other tables, but tblFixture types is just a reference table that lists all valid types of fixtures. You should be able to add data to tblFixtureTypes regardless of what is in tblJobDetails.

Perhaps you can demonstarte your issue with expected results. I see what tables you have in your database, now show me what data you expect this query to produce.

 

[tmyers]

@plog if I entered data into tbFixtureTypes via a sub-form, how would I keep it under a particular job? Would I just be using a simple filter?

In the previous version, my friend had
SELECT Type.*, Notes.*
FROM Notes RIGHT JOIN Type ON Notes.NoteID = Type.NoteID;
That gathered all existing types across the DB (at the time we were using a table "Revision" to "sort" things, which was the cause of a lot of problems and the main cause as to why I started over).

The sub-form he built off of that had:
SELECT Type.TypeName, Notes.Note, Notes.NoteID, Type.TypeID, Type.RevisionID
FROM Notes RIGHT JOIN Type ON Notes.[NoteID] = Type.[NoteID]
WHERE (((Type.RevisionID)=[Forms]![QuoteForm]![RevisionID]));

The above is what I was trying to replicate, but I have not succeeded yet since now there are more tables in between (which I dont think matters), but Revision is now not being used in remotely the same way and other tables have been combined/removed. Since Revision is now completely different, I am struggling to figure out how he came to the above and got it working. Not surprised though, as he does this stuff for a living.

 

[The_Doc_Man]

The couple queries I have done either show every type in the DB, or nothing
...
I think I am not understanding how to properly use WHERE in SQL.

Since you have asked for information on WHERE clauses, I am going to become a bit pedantic. This is me in "professor" mode. I will state ahead of time that this is because you asked about WHERE clauses in the way you did.

SELECT clauses return a collection of records. Those collections can be used to drive forms or reports. You can use the word "list" for what is returned from a query as long as you understand that it would be common, not technical usage. You say you want to get some records back with certain values.

WHERE clauses are used to remove elements from the list returned by a SELECT query. Showing "too much" means you didn't provide criteria that eliminated records. Showing "nothing" means that you provided criteria that were too restrictive. When choosing a field to use for filtering, you need to consider how many records will have some particular value.

Let me provide some examples using a table listing a standard deck of playing cards with no jokers. I will number the examples for discussion:

1. If you filter by the suit, you have four choices, so "SELECT card FROM deck WHERE suit = 'SPADES' ;" would return 13 cards.

2. You could select for rank, so the query "SELECT card FROM deck WHERE rank = 'ACE' ;" would return 4 cards (if it is an honest deck.)

3. You could pick color. SELECT card FROM deck WHERE color = 'RED' ; will return 26 cards.

4. But consider this one: SELECT card FROM deck WHERE color = 'ORANGE' ; will return no cards (unless you have a specialty or novelty deck.)

5. SELECT card FROM deck WHERE background = 'WHITE' ; will return all 52 cards.

Let's do a couple of more complex queries just for discussion:

6.a SELECT card FROM deck WHERE color = 'BLACK' AND rank = 'JACK' ; will return two cards.
6.b SELECT card FROM deck WHERE color = 'BLACK' OR color = 'RED' ; will return 52 cards.
6.c SELECT card FROM deck WHERE color = 'BLACK' AND suit = 'DIAMONDS' ; will return no cards.

What is the moral of this exercise? When selecting which field to use to eliminate records, you have to think about what you expect to get back. When you do that, it requires you to appreciate the structure of your tables. You have to at least understand the nature of the table you are probing so that you would know whether your query can identify the differences among records. Because that is what a query actually does. It is looking for differences or similarities or relationships between the records in the table and some specification that you have stated in the WHERE clause.

Therefore, my examples 1, 2, and 3 show you cases where you pick an attribute that is more restrictive (#2) or less restrictive (#3). But if you pick something that isn't there (#4), you should expect to get nothing. And if you pick something that doesn't make a difference (#5) you return more than you intended.

When you start to include multiple criteria (the technical term for the elements of a WHERE clause), you have to consider what you know about those combinations. Example 6a eliminates all but two cards but 6b eliminates nothing whereas 6c eliminates everything.

Why this exercise? To point out that you must know - and be comfortable with - the content of your tables. You must know what goes together in general terms so that you can construct a query that uses the known properties of the table as a whole. If you are having trouble framing the WHERE clause, it is because you do not fully appreciate the structure of the table being queried.

It might seem like it can't happen, but you said you just restructured a bunch of things. You might have to get back in touch with your DB before you can fix those queries.

 

[tmyers]

That is really informative @The_Doc_Man. I agree with your point that since I restructured my DB, I probably need to take a step back and rethink things again, since what worked before wont work now.

 

[plog]

, how would I keep it under a particular job?

Again, according to your structure, Fixtures are independent of Jobs. It is essentially a reference table. You can add records to it with impunity.

Car Analogy: You have a car dealership with multiple locations and want to track inventory. One of those tables could be tblEngineTypes (e.g. Electric, Hybrid, Gas, etc.). That table lives outside your tblInventory table because its just a reference of possible Engine Types. If flux capacitor technology finally becomes viable in 5 years you would simply add it to tblEngineTypes regardless of what is in tblInventory. Thats how tblFixtureType is in your database.

Also again, if you want help with SQL, broken SQL doesn't help. I need to see expected results.Show me what data you expect a query to return based on data in your tables. Don't explain it, show me with data.