Tallying Null fields

[JohnB]

Is there a way to do this?
In the query below, the fields ProductImage1 through ProductImage9 should
contain the name of a photos. The query will display those records where
any of those 9 fields is blank. What I would like to do is have an
additional field that will show the number of those fields that are blank.
And that's the part that I've never done before: how do I tally those null
fields as the query reads through the file, and populate that new field with
the results?

SELECT Products.ProductName, Products.ProductMake, Products.ProductModel,
Products.ProductMake, Products.ProductStock
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));

TIA

 

[Dale Fye]

John,

First off, your table structure is all wrong for a relational database.
Almost any time that you have fields labeled with a Description and a numeric
value, you can be assured that you need another table. In your case, you
need a ProductImage table, that contains the ProductID (or ProductName), and
a single ProductImage field. You might also include an ImageSeq or Priority
field to keep track of the precedence of the images.

With that structure, it would be easy to identify how many images are
available for a particular product (and you would not be limited to the 9 you
have in your table).

Give that you may be stuck with what you have, I would create a UNION query
to normalize the data in that table. It would look something like:

SELECT ProductName, 1 as ImageSeq, ProductImage1 as ProdImage
FROM Products
WHERE ProductImage1 IS NOT NULL
UNION ALL
SELECT ProductName, 2 as ImageSeq, ProductImage2 as ProdImage
FROM Products
WHERE ProductImage2 IS NOT NULL
....
UNION ALL
SELECT ProductName, 9 as ImageSeq, ProductImage9 as ProdImage
FROM Products
WHERE ProductImage9 IS NOT NULL

From this query, you could create another query to quickly assess how many
images exist for each product.

SELECT ProductName, Count(ImageSeq) as ImageCount
FROM qry_ProductImages
GROUP BY ProductName

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

 

[KARL DEWEY]

Try this ---
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));

 

[JohnB]

That worked perfectly! Thanks!

Where does everyone find their information on creating these more advance
queries? For example, where would I find documentation on the IIF command?
I never seen that one.
I recently bought a book; Microsoft Access 2003 - Inside/Out
But that gives specific examples, and is covering a sample database. Which,
leaves out a lot of other stuff.

Does Microsoft provide a page(s) that lists all the Access query commands,
or is there a book that has that?

Thanks for your help.

Try this ---
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
Build a little - Test a little

Is there a way to do this?
In the query below, the fields ProductImage1 through ProductImage9 should
contain the name of a photos. The query will display those records where
any of those 9 fields is blank. What I would like to do is have an
additional field that will show the number of those fields that are
blank.
And that's the part that I've never done before: how do I tally those
null
fields as the query reads through the file, and populate that new field
with
the results?

SELECT Products.ProductName, Products.ProductMake, Products.ProductModel,
Products.ProductMake, Products.ProductStock
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));

TIA

 

[JohnB]

Thanks for the suggestion, but changing the table wasn't an option for me.
I went with Karl's option, it only required one query. Which is part of my
problem; I come here for help, but all I'm really doing is getting specific
help to a specific problem. I need to somehow become knowledgeable, using
some other method than this forum.

Thanks

 

[KARL DEWEY]

IIF(<Test>, <True Result>, <False Result>)
May be nested --
IIF(<Test>, <True Result>, IIF(<Test>, <True Result>, <False Result>) )

Test examples --
[Field] = "Xyyy"
[Field] Like "Xyyy*"
[Field] > 24
[Field] < 24
[Field] Between 4 And 5
etc.
--
KARL DEWEY
Build a little - Test a little

That worked perfectly! Thanks!

Where does everyone find their information on creating these more advance
queries? For example, where would I find documentation on the IIF command?
I never seen that one.
I recently bought a book; Microsoft Access 2003 - Inside/Out
But that gives specific examples, and is covering a sample database. Which,
leaves out a lot of other stuff.

Does Microsoft provide a page(s) that lists all the Access query commands,
or is there a book that has that?

Thanks for your help.

Try this ---
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
Build a little - Test a little

Is there a way to do this?
In the query below, the fields ProductImage1 through ProductImage9 should
contain the name of a photos. The query will display those records where
any of those 9 fields is blank. What I would like to do is have an
additional field that will show the number of those fields that are
blank.
And that's the part that I've never done before: how do I tally those
null
fields as the query reads through the file, and populate that new field
with
the results?

SELECT Products.ProductName, Products.ProductMake, Products.ProductModel,
Products.ProductMake, Products.ProductStock
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));

TIA

 

[Dale Fye]

John,

I don't know of any books that go into detail on all of the various
functions. I've been using Access since 1996 and still occassionally stumble
on one that I've not used or seen used before.

I've got the Access Developers Handbook (2000 edition) that I've been using
for years. But I gleaned most of what I know about Access from lurking on
the newsgroups, and reading the questions and answers posted here. I also
occassionally do Google searches but have found the most reliable, free
feedback right in these groups.

If your new to SQL as well, you might try SQL for Mere Mortals.

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.