Prev: SetWarning in macro
Next: Why am I missing Calendar Control 12.0
From: John W. Vinson on 6 Apr 2010 14:21
On Tue, 6 Apr 2010 10:58:28 -0700, LewisDUA
<LewisDUA(a)discussions.microsoft.com> wrote:

>That would work for one piece of data. But, i have muilt data pieces.
>
>I have Name, then lets say we also have Doc1, Doc2, and Doc3.
>John is missing all three.
>Tim is missing Doc2.

You can put IS NULL as a criterion on all three fields... on *separate lines*
in the query grid, to use OR logic. The record will be returned if any one (or
two or three) fields are blank.

If you have fields named Doc1, Doc2 and Doc3, though, your table design is
suspect! What is in fact the structure of your tables?
--

John W. Vinson [MVP]
From: John W. Vinson on 6 Apr 2010 14:53
On Tue, 6 Apr 2010 14:01:51 -0400, "Steve" <notmyemail(a)address.com> wrote:

>Yiu can only do this for one field. If you set the criteria as Null for two
>or more fields, the data must be "missing" for ALL the fields you set the
>criteria as Null to be returned by the query.

That's simply not true, Steve. You can use OR instead of AND.


--

John W. Vinson [MVP]
From: LewisDUA on 6 Apr 2010 15:11
I have alot of different types of information. financial information,
medical. personal type files. They aren't called Doc1 and so on. I have them
named by Document names.
I have a Personel Table, a Training, Medical, Folder. Those are the main ones.
Lets say for a W4, if you have it. Its coded as Yes, if you don't then No.
or N/A.
In my query its pulling data from Personnel and Folder.
My goal, is to get it where it shows their name, and what they are missing.
And nothing else. Do you get what i mean?

Thanks,


"John W. Vinson" wrote:

> On Tue, 6 Apr 2010 09:56:21 -0700, LewisDUA
> <LewisDUA(a)discussions.microsoft.com> wrote:
>
> >I am working on this query. I want to run it, so it will show what my users
> >are missing in their files. But only what they are missing. What would the
> >criteria be in order to do that?
>
> You'll have to give us some more explanation. I know that one thing that's
> missing from my files is a stock certificate for 20,000 shares of Microsoft,
> and I'm pretty sure there's also no title deed to the Brooklyn Bridge...
>
> What's in their files, how are the tables structured, and how can a "missing"
> record be identified?
>
> --
>
> John W. Vinson [MVP]
> .
>
From: John W. Vinson on 6 Apr 2010 16:56
On Tue, 6 Apr 2010 12:11:02 -0700, LewisDUA
<LewisDUA(a)discussions.microsoft.com> wrote:

>I have alot of different types of information. financial information,
>medical. personal type files. They aren't called Doc1 and so on. I have them
>named by Document names.
>I have a Personel Table, a Training, Medical, Folder. Those are the main ones.
>Lets say for a W4, if you have it. Its coded as Yes, if you don't then No.
>or N/A.
>In my query its pulling data from Personnel and Folder.
>My goal, is to get it where it shows their name, and what they are missing.
>And nothing else. Do you get what i mean?

No. Because I don't know how your tables are structured!

What are your Tables?
What is each table's Primary Key?
What are some representative fields?


I'm guessing that your tables are "spreadsheets" with a yes/no field for W4, a
yes/no field for this, a yes/no field for that. If so you're in a pickle,
because yes/no fields cannot be NULL - only yes or no - and you cannot be
certain that John has a No in the W4 field because he hasn't filled out a W4
or because nobody got around to entering that bit of info into the table!

Please post a description of (at least the relevant part of) your table with
fieldnames and datatypes and (if appropriate) some dummy sample data.
--

John W. Vinson [MVP]
From: LewisDUA on 6 Apr 2010 18:23

Personnel Table, i use a three fields: Name, UIC(Which is a number for the
unit), PARA(Which is their job slot)
Then in PMP Table i have DD 93 (Yes/No) SGLV(Yes/No) PQR(Yes/No)
DA2-1(Yes/No) 20 YR (Yes/No/N-A)....etc
My Personnel Tables key SoldierID and my PMP Table's ID is SoldierID. Their
is more tables, but i'm not using them in this query.
Does that help?

Thanks John!


"John W. Vinson" wrote:

> On Tue, 6 Apr 2010 12:11:02 -0700, LewisDUA
> <LewisDUA(a)discussions.microsoft.com> wrote:
>
> >I have alot of different types of information. financial information,
> >medical. personal type files. They aren't called Doc1 and so on. I have them
> >named by Document names.
> >I have a Personel Table, a Training, Medical, Folder. Those are the main ones.
> >Lets say for a W4, if you have it. Its coded as Yes, if you don't then No.
> >or N/A.
> >In my query its pulling data from Personnel and Folder.
> >My goal, is to get it where it shows their name, and what they are missing.
> >And nothing else. Do you get what i mean?
>
> No. Because I don't know how your tables are structured!
>
> What are your Tables?
> What is each table's Primary Key?
> What are some representative fields?
>
>
> I'm guessing that your tables are "spreadsheets" with a yes/no field for W4, a
> yes/no field for this, a yes/no field for that. If so you're in a pickle,
> because yes/no fields cannot be NULL - only yes or no - and you cannot be
> certain that John has a No in the W4 field because he hasn't filled out a W4
> or because nobody got around to entering that bit of info into the table!
>
> Please post a description of (at least the relevant part of) your table with
> fieldnames and datatypes and (if appropriate) some dummy sample data.
> --
>
> John W. Vinson [MVP]
> .
>
First  |  Prev  |  Next  |  Last
Pages: 1 2 3
Prev: SetWarning in macro
Next: Why am I missing Calendar Control 12.0