Mulit Function Query

  • Thread starter Thread starter LewisDUA
  • Start date Start date
L

LewisDUA

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?
 
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?
Click to expand...

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?
 
Under the field you want to show the "missing" data, in the criteria cell
type Is Null.

Richard
 
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.

Do you get what i'm saying? And thanks.
 
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.

Steve
(e-mail address removed)
 
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.
Click to expand...

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?
 
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.
Click to expand...

That's simply not true, Steve. You can use OR instead of AND.
 
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,
 
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?
Click to expand...

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.
 
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!
 
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?
Click to expand...

Yes... unfortunately it does!

Because your PMP table's structure *IS WRONG* and it will not meet your needs.

Again... a yes/no field cannot be "missing". It cannot be Null. It can only be
YES or NO. It will default to NO.

If these fields are textboxes containing a text string "yes", "no", or "N/A"
or the like you can escape this particular problem.

It will be absolutely impossible to tell if a given soldier in fact has a NO
value for their PQR, or if the PQR checkbox just never got checked.

An additional problem that you can't escape is that if you ever need to add or
change an column in the PMP table you'll need to change the structure of your
table... redesign all the queries which include that table... rebuild all the
forms and reports that use that table... OUCH!!

It would seem that you have a Many to Many relationship from a given soldier
to a whole bunch of "things to check" about that solder - her DD93, her SGLV,
his PQR, etc. etc. The correct way to model this is with a many to many
relationship: a table with one row per "thing to check", related one to many
to a third table with fields for the soldier's unique ID, the ID of the "thing
to check", and possibly other fields (if you need to know something other than
the bare existance of a SGLV value).
 
Ok, the tables are all setup correctly. The only thing i'm having problems
with is ONE query. What i mean by missing is NO. I don't care about NULL or
yes or N/A.

I want to run a query that will show all of their NOs. Is their a line of
code for that? Or should i just go higher then this?
 
Ok, the tables are all setup correctly. The only thing i'm having problems
with is ONE query. What i mean by missing is NO. I don't care about NULL or
yes or N/A.

I want to run a query that will show all of their NOs. Is their a line of
code for that? Or should i just go higher then this?
Click to expand...

I don't understand your question. "Higher"?

A query with a criterion such as

WHERE [DD 93] = "No" OR SGLV="No" OR
[DA2-1] = "No" OR [20 YR] = "No" OR ...etc

will find records where any one (or more than one) of the fields contain the
text string "No". Use = False instead of = "No" if the field is in fact a
Yes/No field.
 
Back
Top