How do I find fields with missing data in a query?

[Guest]

Hi,

I have a table that contains data on people we have paid in the past. The
table contains the fields: ClientID, LastName, January, February, March,
etc... Sometimes, we will begin to pay a person in January, but not in
February or March. Sometimes, we will begin to pay a person in February, but
not March. Sometimes, we will pay a person in January, NOT February, but
then, again, in March. This adds up to a lot of variables when dealing w/
the past 2 years data. My problem is that I want to be able to find ANY
clients where we have paid them AT LEAST ONCE, but have data missing in ANY
of the fields. The tricky part for me is that some of these clients haven't
been paid as long as the others... If I do a query with something like
January >0, February Is Null, March Is Null, that would only show me those
that two months. What if I didn't begin paying until February? That data
wouldn't show because I specifed only to show if Jan > 0. I apologize for
such a lengthy question, and thank all in advance for any help you may offer.
Thanks!

 

[Rick B]

Your database structure is wrong. A field name should not contain data
(like Jan, Feb, etc.)

You should have (at least) two tables here. One with the Client ID and
name, and address, etc. Another table to track payments (ClientID, Date,
Amount, etc.)

Now, that does not answer your question, but the point is that you need to
get your database structure normalized before you go any further. You are
trying to build a spreadsheet in a database application. If you want a
spreadsheet, you should use Excel.

 

[Guest]

We do have a separate table for that information. What I was referring to is
the underlying table of a form. We have this form in Access to allow easier
use, as well as, update information throughout the various tables this
information is contained in. When someone is paid, employees will go to the
form and input this information for the corresponding month. I need to find
fields in which we have made a payment before, but for some reason have a
month, or two, or even more fields blank.

 

[Rick B]

Again, you would not have "fields blank" in a month not paid if you indeed
have a normalized design. You would have Only records for months paid.
What you'd really need to do here is look for people who are missing records
for a particular month.

Maybe I misunderstand though, and I'll let someone else jump in.