how to bring up all vendors with a specific value, even if blank

[eeps24]

I am doing a query for vendors who have a value populated for a specific field. Currently there are only 2 vendors with this field populated. Hence my results only come up 2. However, I want it to bring in "all" vendors even if they have this field blank to show up in the report.

For example,



The field is called "OIG_SEARACH" and the value is just a date. So far I have 2 vendors that have a dates,but i want to include vendors with blank's as well.

Here is my code..
SELECT dbo_APVENMAST.VENDOR, dbo_APVENMAST.VENDOR_VNAME, dbo_APVNMXVAL.MATRIX_CAT, dbo_APVNMXVAL.MX_VALUE
FROM dbo_APVENMAST LEFT JOIN dbo_APVNMXVAL ON dbo_APVENMAST.AP_OBJ_ID = dbo_APVNMXVAL.AP_OBJ_ID
WHERE (((dbo_APVNMXVAL.MATRIX_CAT)="OIG_SEARCH"));

[June7]

Try:
WHERE (((dbo_APVNMXVAL.MATRIX_CAT) = "OIG_SEARCH" Or Is Null));


Your query works with OIG_SEARCH within quote marks?

[eeps24]

Try:
WHERE (((dbo_APVNMXVAL.MATRIX_CAT) = "OIG_SEARCH" Or Is Null));


Your query works with OIG_SEARCH within quote marks?

yes it works with quote marks. I did that in design view, access added the quotes by itself. I think your on to something with "or is null". Access gave me a huge list although I was expecting to see OIG_SEARCH next to each vendor but they were blank.

So I copied the code in microsoft sql server managment studio and here are my results. You see under the matrix_cat field, why does it say null there? I would expect to see OIG_SEARCH on every line for that column and just see null for the value. Am i missing anything?

[June7]

The Or Is Null is applied to the MATRIX_CAT field.

Apparently there are no records with OIG_SEARCH in the MATRIX_CAT field AND MS_VALUE is null.


I don't understand what you are trying to do. Exactly what, in plain English, is the rule you want to apply for filtering records?

[eeps24]

Point well taken Ok I will rephrase...Heres what I want to do...

In my screenshot above, you can see a list of vendors. All vendors have a field called OIG_SEARCH. However, only 3 vendors have data populated and the rest of the vendors have that value blank.

I want to do a query so it shows me all the vendors along with the values for the OIG_SEARCH,regardless even its blank.

Below is a screen shot I modified in a paint program, I would like to see "OIG_SEARCH" for every vendor, and not see Null. How ever Null for MX_Value is ok because its truly blank. So if you look at both screen shots, you will see what I want to do.

[John_G]

I'm a little confused with your terminology here - your statement "All vendors have a field called OIG_SEARCH." is incorrect. The field is called "MATRIX_CAT", and the data in the field for some records is "OIG_SEARCH".

Your second (modifed) screenshot shows false results - records 4 - 12 don't contain "OIG_SEARCH" in the MATRIX_CAT field - it is Null for those records, and that is what your first screenshot shows.

From #3 above:

You see under the matrix_cat field, why does it say null there? I would expect to see OIG_SEARCH on every line for that column and just see null for the value.

It says Null because that is the "value" in the MATRIX_CAT field for those records. You don't see "OIG_SEARCH" because it isn't there.

Can you clarify just what it is you want the query to show you?

[eeps24]

Hi John,

Every vendor has a field called "OIG_SEARACH" (screen shot below is a sample for a vendor). If you look to the right, (where I wrote the word blank) there should be a date there. I would say about 97% of the vendors dont have a value here. Initially when I did my query, it only shows 3 vendors that DO have values. I want to pull up all my vendors even if they dont have dates for OIG_SEARCH. As I mentioned, every vendor DOES have the field OIG_SEARCH, just not all have values here. Those Null's in the Matrix_cat above might of been because my query was incorrect. I wouldnt look at my code as I believe has errors.

[eeps24]

I cleaned my query and here is how i originally started. I only have 3 hits...vendors that DO have a value for OIG_SEARCH. I want to bring in even the vendors who DONT have values for OIG_SEARCH as well.

[John_G]

I may be missing something here, because you are showing us screenshots from SQL-Server, not MS Access, and I don't use SQL-Server.

That said, your query above does NOT reference a field called OIG_SEARCH anywhere. It is not in the Select field list, and it is not in the WHERE clause.

Your WHERE clause is referencing a field called MATRIX_CAT which has a value of "OIG_SEARCH" in it, and that is what your query results show.

What am I missing?

[June7]

Agree with John_G, your narrative is confusing.

However, try:

SELECT dbo_APVENMAST.VENDOR, dbo_APVENMAST.VENDOR_VNAME, Nz(dbo_APVNMXVAL.MATRIX_CAT, "OIG_SEARCH") AS MatCat, dbo_APVNMXVAL.MX_VALUE
FROM dbo_APVENMAST LEFT JOIN dbo_APVNMXVAL ON dbo_APVENMAST.AP_OBJ_ID = dbo_APVNMXVAL.AP_OBJ_ID
WHERE dbo_APVNMXVAL.MATRIX_CAT="OIG_SEARCH" Or Is Null;

or if you are building query in SQLServer (should also work in Access):

SELECT dbo_APVENMAST.VENDOR, dbo_APVENMAST.VENDOR_VNAME, IIf(dbo_APVNMXVAL.MATRIX_CAT Is Null, "OIG_SEARCH", dbo_APVNMXVAL.MATRIX_CAT) AS MatCat, dbo_APVNMXVAL.MX_VALUE
FROM dbo_APVENMAST LEFT JOIN dbo_APVNMXVAL ON dbo_APVENMAST.AP_OBJ_ID = dbo_APVNMXVAL.AP_OBJ_ID
WHERE dbo_APVNMXVAL.MATRIX_CAT="OIG_SEARCH" Or Is Null;

http://allenbrowne.com/QueryPerfIssue.html