Blanks in one or all of the fields

[HelpMe!]

I have a table with the following fields:

SupervisorLast
SupervisorFirst
EmployeeLast
EmployeeFirst
Probe1PeriodStart
Probe1PeriodEnd
Probe1CompletionDate
Probe2PeriodStart
Probe2PeriodEnd
Probe2CompletionDate
Probe3PeriodStart
Probe3PeriodEnd
Probe3CompletionDate

I want to run a query that will show me only the records that have blanks in
one or all of the “Completion Date†fields. I have no idea where to begin.
Any suggestions?

 

[MGFoster]

I have a table with the following fields:

SupervisorLast
SupervisorFirst
EmployeeLast
EmployeeFirst
Probe1PeriodStart
Probe1PeriodEnd
Probe1CompletionDate
Probe2PeriodStart
Probe2PeriodEnd
Probe2CompletionDate
Probe3PeriodStart
Probe3PeriodEnd
Probe3CompletionDate

I want to run a query that will show me only the records that have blanks in
one or all of the “Completion Date†fields. I have no idea where to begin.
Any suggestions?

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You probably know that this table is not in Normal Form - the Probe1,
Probe2, etc. should be in a separate table. That said - your solution:
the query's criteria should be like this:

WHERE Probe1CompletionDate IS NULL
OR Probe2CompletionDate IS NULL
OR Probe3CompletionDate IS NULL

This will return rows (records) that have 1, or 2, or all 3 Completion
Dates that are blank (NULL). If you want rows where just 1 Completion
Date is NULL and the others are not NULL, or ALL the Completion Dates
are NULL, the criteria would look like this:

WHERE (Probe1CompletionDate IS NULL
AND Probe2CompletionDate IS NOT NULL
AND Probe3CompletionDate IS NOT NULL)
OR (Probe2CompletionDate IS NULL
AND Probe1CompletionDate IS NOT NULL
AND Probe3CompletionDate IS NOT NULL)
OR (Probe3CompletionDate IS NULL
AND Probe1CompletionDate IS NOT NULL
AND Probe2CompletionDate IS NOT NULL)
OR (Probe1CompletionDate IS NULL
AND Probe2CompletionDate IS NULL
AND Probe3CompletionDate IS NULL)


HTH,
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSmjLfIechKqOuFEgEQKS+ACgxL9NqpZ+lS0twIB0A4ZBlbHK2bMAoMSA
Hzg5UcLr9OvfKZ6KXk2wG4iO
=q8s/
-----END PGP SIGNATURE-----

 

[HelpMe!]

Okay, i separated the probe info into three separate tables with the Employee
ID as the primary. then i created a query that has the following fields:

EmployeeID
EmployeeLast
EmployeeFirst
Supervisors_ID
Probe1PeriodStart
Probe1PeriodEnd
Probe1CompletionDate
Probe2PeriodStart
Probe2PeriodEnd
Probe2CompletionDate
Probe3PeriodStart
Probe3PeriodEnd
Probe3CompletionDate

Now, i need to have the Probe1PeriodBegin critera "Is Not Null", so my query
returns only those employees that have probation dates.

i have to run a report that includes all blanks in the completion date
fields, indicating that one or more probe reports are not complete.

I dont understand how to run a query that will show me only the records that
have blanks in one, two or three of the “Completion Date†fields.

(I am working with 2007, if that makes a difference)

 

[MGFoster]

Okay, i separated the probe info into three separate tables with the Employee
ID as the primary. then i created a query that has the following fields:

EmployeeID
EmployeeLast
EmployeeFirst
Supervisors_ID
Probe1PeriodStart
Probe1PeriodEnd
Probe1CompletionDate
Probe2PeriodStart
Probe2PeriodEnd
Probe2CompletionDate
Probe3PeriodStart
Probe3PeriodEnd
Probe3CompletionDate

Now, i need to have the Probe1PeriodBegin critera "Is Not Null", so my query
returns only those employees that have probation dates.

i have to run a report that includes all blanks in the completion date
fields, indicating that one or more probe reports are not complete.

I dont understand how to run a query that will show me only the records that
have blanks in one, two or three of the “Completion Date†fields.

(I am working with 2007, if that makes a difference)

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Hmmm.... I hope you didn't create tables like this:

Probe1
PeriodStart
PeriodEnd
CompletionDate

Probe2
PeriodStart
PeriodEnd
CompletionDate

.... etc. ...

'cuz that's also incorrect. As someone else stated in their post you
should have tables like this:

ProbeEmployees
EmployeeID
SupervisorID
EmployeeLast
EmployeeFirst

Then a related table like this:

Probes
EmployeeID - links to ProbeEmployees table
SupervisorID - also link to ProbeEmployees table
ProbeNumber - 1, 2, or 3
PeriodStart
PeriodEnd
CompletionDate

The query that would show all probes that have a NULL CompletionDate
would be:

SELECT E.EmployeeID, E.EmployeeLast, E.EmployeeFirst
FROM ProbeEmployees As E INNER JOIN Probes As P ON E.EmployeeID =
P.EmployeeID And E.SupervisorID = P.SupervisorID
WHERE P.ProbeNumber IN (1,2,3)
AND P.CompletionDate IS NULL
GROUP BY E.EmployeeID, E.EmployeeLast, E.EmployeeFirst
HAVING COUNT(*) = 3

Note that the number of probes searched for is 3 - the "...IN (1,2,3)"
expression; and, the HAVING clause searches for a count of the number of
times the employee info shows, as being 3 - the same number of items in
the "IN (...)" expression. This means that the query will show
employees that have blanks in all three completion dates. If you want
to show just those employees that have 1 or more blank completion dates
just remove the HAVING clause.

HTH,
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSmjokYechKqOuFEgEQITnwCcCdixnyz/xwcQHkvq2B/lpyI+dnIAoIs0
tOJj8cZH5kFEauFxEmtMeHTw
=AVCK
-----END PGP SIGNATURE-----