A followup to my TALLY NULL question

[JohnB]

Karl gave me the query below, in response to an earlier question. It totals
the number of null fields, from fields ProductImage1 through ProductImage9.
And puts the result into "Total Nulls". Works great.

I would like to do something a little different, using that same tally
process:
I have another table, in another database (I'll call it Table2). Both
tables have a common field; ProductStock. I would like a new query to
display the ProductStock field (and only that) from Table2, whenever the
Total Nulls field is less than 9. Sounds simple enough, but it's way over
my head.

How would I do that?
Thanks



SELECT Products.ProductName, Products.ProductMake, Products.ProductModel,
Products.ProductMake, Products.ProductStock, IIF(Products.ProductImage1 Is
Null, 1, 0) + IIF(Products.ProductImage2 Is Null, 1, 0) +
IIF(Products.ProductImage3 Is Null, 1, 0) + IIF(Products.ProductImage4 Is
Null, 1, 0) + IIF(Products.ProductImage5 Is Null, 1, 0) +
IIF(Products.ProductImage6 Is Null, 1, 0) + IIF(Products.ProductImage7 Is
Null, 1, 0) + IIF(Products.ProductImage8 Is Null, 1, 0) +
IIF(Products.ProductImage9 Is Null, 1,0) AS [Total nulls]
FROM Products
WHERE (((Products.ProductImage1) Is Null)) OR (((Products.ProductImage2) Is
Null)) OR (((Products.ProductImage3) Is Null)) OR (((Products.ProductImage4)
Is Null)) OR (((Products.ProductImage5) Is Null)) OR
(((Products.ProductImage6) Is Null)) OR (((Products.ProductImage7) Is Null))
OR (((Products.ProductImage8) Is Null)) OR (((Products.ProductImage9) Is
Null));

 

[KARL DEWEY]

Both tables have a common field; ProductStock. I would like a new query to
display the ProductStock field (and only that) from Table2
WHY?
If they are the same in both tables then there is no need to display it from
the second table.

 

[JohnB]

Table2 is the company's master database of inventory, located on a server on
the internal network. The first table, the table with the image name fields
(I'll call it Table1) is the Access database that is used on the company
website. The website has photos of the vehicle inventory. Not all of the
inventory in Table2 has had pictures taken of it. So what I'm trying to
accomplish here is have a query that will show inventory that has not yet
had pictures taken and put on the website or, doesn't have the requisite
number of pictures; 9.
Does that make sense?

display the ProductStock field (and only that) from Table2
WHY?
If they are the same in both tables then there is no need to display it
from
the second table.
--
KARL DEWEY
Build a little - Test a little

Karl gave me the query below, in response to an earlier question. It
totals
the number of null fields, from fields ProductImage1 through
ProductImage9.
And puts the result into "Total Nulls". Works great.

I would like to do something a little different, using that same tally
process:
I have another table, in another database (I'll call it Table2). Both
tables have a common field; ProductStock. I would like a new query to
display the ProductStock field (and only that) from Table2, whenever the
Total Nulls field is less than 9. Sounds simple enough, but it's way
over
my head.

How would I do that?
Thanks



SELECT Products.ProductName, Products.ProductMake, Products.ProductModel,
Products.ProductMake, Products.ProductStock, IIF(Products.ProductImage1
Is
Null, 1, 0) + IIF(Products.ProductImage2 Is Null, 1, 0) +
IIF(Products.ProductImage3 Is Null, 1, 0) + IIF(Products.ProductImage4 Is
Null, 1, 0) + IIF(Products.ProductImage5 Is Null, 1, 0) +
IIF(Products.ProductImage6 Is Null, 1, 0) + IIF(Products.ProductImage7 Is
Null, 1, 0) + IIF(Products.ProductImage8 Is Null, 1, 0) +
IIF(Products.ProductImage9 Is Null, 1,0) AS [Total nulls]
FROM Products
WHERE (((Products.ProductImage1) Is Null)) OR (((Products.ProductImage2)
Is
Null)) OR (((Products.ProductImage3) Is Null)) OR
(((Products.ProductImage4)
Is Null)) OR (((Products.ProductImage5) Is Null)) OR
(((Products.ProductImage6) Is Null)) OR (((Products.ProductImage7) Is
Null))
OR (((Products.ProductImage8) Is Null)) OR (((Products.ProductImage9) Is
Null));