Unmatched Query

[Richardb]

I have a query that I am trying to find out who did not submit a report
during a particular time period. The unmatched works for all if I do not have
the date filed in the query. Following is the SQL Statement with the date
field.

SELECT DmList.[DM Name], Communications.Date
FROM DmList LEFT JOIN Communications ON DmList.[DM Name] =
Communications.[District Mgr]
WHERE (((Communications.[District Mgr]) Is Null) AND ((Communications.Date)
Between [Start Date] And [End Date]));

Any help would be appreciated.

Richard

 

[KARL DEWEY]

Try this ---
SELECT DmList.[DM Name], Communications.Date
FROM DmList LEFT JOIN Communications ON DmList.[DM Name] =
Communications.[District Mgr]
WHERE (Communications.[District Mgr]) Is Null AND (Communications.Date)
Between [Start Date] And [End Date] OR Communications.Date IS NULL;

 

[Richardb]

Thanks for the reply. No matter what date I select it still comes up with
the exact same number of people. So it is searching the whole table not just
the date selection.

Richard

Try this ---
SELECT DmList.[DM Name], Communications.Date
FROM DmList LEFT JOIN Communications ON DmList.[DM Name] =
Communications.[District Mgr]
WHERE (Communications.[District Mgr]) Is Null AND (Communications.Date)
Between [Start Date] And [End Date] OR Communications.Date IS NULL;

--
KARL DEWEY
Build a little - Test a little

I have a query that I am trying to find out who did not submit a report
during a particular time period. The unmatched works for all if I do not have
the date filed in the query. Following is the SQL Statement with the date
field.

SELECT DmList.[DM Name], Communications.Date
FROM DmList LEFT JOIN Communications ON DmList.[DM Name] =
Communications.[District Mgr]
WHERE (((Communications.[District Mgr]) Is Null) AND ((Communications.Date)
Between [Start Date] And [End Date]));

Any help would be appreciated.

Richard

 

[KARL DEWEY]

Is your Communications.Date field a DateTime datatype or text field?
Try changing you SQL to this ---
Between CVDate([Start Date]) And CVDate([End Date]) OR Communications.Date
IS NULL;
--
KARL DEWEY
Build a little - Test a little

Thanks for the reply. No matter what date I select it still comes up with
the exact same number of people. So it is searching the whole table not just
the date selection.

Richard

Try this ---
SELECT DmList.[DM Name], Communications.Date
FROM DmList LEFT JOIN Communications ON DmList.[DM Name] =
Communications.[District Mgr]
WHERE (Communications.[District Mgr]) Is Null AND (Communications.Date)
Between [Start Date] And [End Date] OR Communications.Date IS NULL;

--
KARL DEWEY
Build a little - Test a little

I have a query that I am trying to find out who did not submit a report
during a particular time period. The unmatched works for all if I do not have
the date filed in the query. Following is the SQL Statement with the date
field.

SELECT DmList.[DM Name], Communications.Date
FROM DmList LEFT JOIN Communications ON DmList.[DM Name] =
Communications.[District Mgr]
WHERE (((Communications.[District Mgr]) Is Null) AND ((Communications.Date)
Between [Start Date] And [End Date]));

Any help would be appreciated.

Richard

 

[Richardb]

I have a date time field. Below did not work.

Is your Communications.Date field a DateTime datatype or text field?
Try changing you SQL to this ---
Between CVDate([Start Date]) And CVDate([End Date]) OR Communications.Date
IS NULL;
--
KARL DEWEY
Build a little - Test a little

Thanks for the reply. No matter what date I select it still comes up with
the exact same number of people. So it is searching the whole table not just
the date selection.

Richard

Try this ---
SELECT DmList.[DM Name], Communications.Date
FROM DmList LEFT JOIN Communications ON DmList.[DM Name] =
Communications.[District Mgr]
WHERE (Communications.[District Mgr]) Is Null AND (Communications.Date)
Between [Start Date] And [End Date] OR Communications.Date IS NULL;

--
KARL DEWEY
Build a little - Test a little


:

I have a query that I am trying to find out who did not submit a report
during a particular time period. The unmatched works for all if I do not have
the date filed in the query. Following is the SQL Statement with the date
field.

SELECT DmList.[DM Name], Communications.Date
FROM DmList LEFT JOIN Communications ON DmList.[DM Name] =
Communications.[District Mgr]
WHERE (((Communications.[District Mgr]) Is Null) AND ((Communications.Date)
Between [Start Date] And [End Date]));

Any help would be appreciated.

Richard

 

[KARL DEWEY]

Your orignal post said "...The unmatched works for all if I do not have the
date filed in the query..."
I just built the table with data and it worked for me. Sample data below
with results --
DM Name
1
2
3
4
5
6

Date District Mgr
1/1/2008 1
2/2/2008 2
1/1/2008 4
2/1/2008 6

DM Name Date
3
5

What did query not do or do wrong?
Post some sample data. Results.
--
KARL DEWEY
Build a little - Test a little

I have a date time field. Below did not work.

Is your Communications.Date field a DateTime datatype or text field?
Try changing you SQL to this ---
Between CVDate([Start Date]) And CVDate([End Date]) OR Communications.Date
IS NULL;
--
KARL DEWEY
Build a little - Test a little

Thanks for the reply. No matter what date I select it still comes up with
the exact same number of people. So it is searching the whole table not just
the date selection.

Richard

:

Try this ---
SELECT DmList.[DM Name], Communications.Date
FROM DmList LEFT JOIN Communications ON DmList.[DM Name] =
Communications.[District Mgr]
WHERE (Communications.[District Mgr]) Is Null AND (Communications.Date)
Between [Start Date] And [End Date] OR Communications.Date IS NULL;

--
KARL DEWEY
Build a little - Test a little


:

I have a query that I am trying to find out who did not submit a report
during a particular time period. The unmatched works for all if I do not have
the date filed in the query. Following is the SQL Statement with the date
field.

SELECT DmList.[DM Name], Communications.Date
FROM DmList LEFT JOIN Communications ON DmList.[DM Name] =
Communications.[District Mgr]
WHERE (((Communications.[District Mgr]) Is Null) AND ((Communications.Date)
Between [Start Date] And [End Date]));

Any help would be appreciated.

Richard

 

[Richardb]

Karl,

The results list would be long since the DM list has 53 people. I could
send the whole DB. It is small since I just created recently.

Richard

Your orignal post said "...The unmatched works for all if I do not have the
date filed in the query..."
I just built the table with data and it worked for me. Sample data below
with results --
DM Name
1
2
3
4
5
6

Date District Mgr
1/1/2008 1
2/2/2008 2
1/1/2008 4
2/1/2008 6

DM Name Date
3
5

What did query not do or do wrong?
Post some sample data. Results.
--
KARL DEWEY
Build a little - Test a little

I have a date time field. Below did not work.

Is your Communications.Date field a DateTime datatype or text field?
Try changing you SQL to this ---
Between CVDate([Start Date]) And CVDate([End Date]) OR Communications.Date
IS NULL;
--
KARL DEWEY
Build a little - Test a little


:

Thanks for the reply. No matter what date I select it still comes up with
the exact same number of people. So it is searching the whole table not just
the date selection.

Richard

:

Try this ---
SELECT DmList.[DM Name], Communications.Date
FROM DmList LEFT JOIN Communications ON DmList.[DM Name] =
Communications.[District Mgr]
WHERE (Communications.[District Mgr]) Is Null AND (Communications.Date)
Between [Start Date] And [End Date] OR Communications.Date IS NULL;

--
KARL DEWEY
Build a little - Test a little


:

I have a query that I am trying to find out who did not submit a report
during a particular time period. The unmatched works for all if I do not have
the date filed in the query. Following is the SQL Statement with the date
field.

SELECT DmList.[DM Name], Communications.Date
FROM DmList LEFT JOIN Communications ON DmList.[DM Name] =
Communications.[District Mgr]
WHERE (((Communications.[District Mgr]) Is Null) AND ((Communications.Date)
Between [Start Date] And [End Date]));

Any help would be appreciated.

Richard

 

[Richardb]

The results are the same no matter what date range I use. It takes out the
same 11 DMs.

Your orignal post said "...The unmatched works for all if I do not have the
date filed in the query..."
I just built the table with data and it worked for me. Sample data below
with results --
DM Name
1
2
3
4
5
6

Date District Mgr
1/1/2008 1
2/2/2008 2
1/1/2008 4
2/1/2008 6

DM Name Date
3
5

What did query not do or do wrong?
Post some sample data. Results.
--
KARL DEWEY
Build a little - Test a little

I have a date time field. Below did not work.

Is your Communications.Date field a DateTime datatype or text field?
Try changing you SQL to this ---
Between CVDate([Start Date]) And CVDate([End Date]) OR Communications.Date
IS NULL;
--
KARL DEWEY
Build a little - Test a little


:

Thanks for the reply. No matter what date I select it still comes up with
the exact same number of people. So it is searching the whole table not just
the date selection.

Richard

:

Try this ---
SELECT DmList.[DM Name], Communications.Date
FROM DmList LEFT JOIN Communications ON DmList.[DM Name] =
Communications.[District Mgr]
WHERE (Communications.[District Mgr]) Is Null AND (Communications.Date)
Between [Start Date] And [End Date] OR Communications.Date IS NULL;

--
KARL DEWEY
Build a little - Test a little


:

I have a query that I am trying to find out who did not submit a report
during a particular time period. The unmatched works for all if I do not have
the date filed in the query. Following is the SQL Statement with the date
field.

SELECT DmList.[DM Name], Communications.Date
FROM DmList LEFT JOIN Communications ON DmList.[DM Name] =
Communications.[District Mgr]
WHERE (((Communications.[District Mgr]) Is Null) AND ((Communications.Date)
Between [Start Date] And [End Date]));

Any help would be appreciated.

Richard

 

[KARL DEWEY]

I asked for "sample" data, not the complete information. I want to see what
works and what does not. That will entail running your sample data through
my machine to see if I get the same results because my data works.
--
KARL DEWEY
Build a little - Test a little

Karl,

The results list would be long since the DM list has 53 people. I could
send the whole DB. It is small since I just created recently.

Richard

Your orignal post said "...The unmatched works for all if I do not have the
date filed in the query..."
I just built the table with data and it worked for me. Sample data below
with results --
DM Name
1
2
3
4
5
6

Date District Mgr
1/1/2008 1
2/2/2008 2
1/1/2008 4
2/1/2008 6

DM Name Date
3
5

What did query not do or do wrong?
Post some sample data. Results.
--
KARL DEWEY
Build a little - Test a little

I have a date time field. Below did not work.

:

Is your Communications.Date field a DateTime datatype or text field?
Try changing you SQL to this ---
Between CVDate([Start Date]) And CVDate([End Date]) OR Communications.Date
IS NULL;
--
KARL DEWEY
Build a little - Test a little


:

Thanks for the reply. No matter what date I select it still comes up with
the exact same number of people. So it is searching the whole table not just
the date selection.

Richard

:

Try this ---
SELECT DmList.[DM Name], Communications.Date
FROM DmList LEFT JOIN Communications ON DmList.[DM Name] =
Communications.[District Mgr]
WHERE (Communications.[District Mgr]) Is Null AND (Communications.Date)
Between [Start Date] And [End Date] OR Communications.Date IS NULL;

--
KARL DEWEY
Build a little - Test a little


:

I have a query that I am trying to find out who did not submit a report
during a particular time period. The unmatched works for all if I do not have
the date filed in the query. Following is the SQL Statement with the date
field.

SELECT DmList.[DM Name], Communications.Date
FROM DmList LEFT JOIN Communications ON DmList.[DM Name] =
Communications.[District Mgr]
WHERE (((Communications.[District Mgr]) Is Null) AND ((Communications.Date)
Between [Start Date] And [End Date]));

Any help would be appreciated.

Richard

 

[Richardb]

Sorry Carl. I am not sure how to get you the sample data becuse there are 9
fields in the Communications Table and 2 Fields in the DM Table. Any
suggestions would be helpfull. When I try to paste the date it all scambles
in the reply box.

Richard

I asked for "sample" data, not the complete information. I want to see what
works and what does not. That will entail running your sample data through
my machine to see if I get the same results because my data works.
--
KARL DEWEY
Build a little - Test a little

Karl,

The results list would be long since the DM list has 53 people. I could
send the whole DB. It is small since I just created recently.

Richard

Your orignal post said "...The unmatched works for all if I do not have the
date filed in the query..."
I just built the table with data and it worked for me. Sample data below
with results --
DM Name
1
2
3
4
5
6

Date District Mgr
1/1/2008 1
2/2/2008 2
1/1/2008 4
2/1/2008 6

DM Name Date
3
5

What did query not do or do wrong?
Post some sample data. Results.
--
KARL DEWEY
Build a little - Test a little


:

I have a date time field. Below did not work.

:

Is your Communications.Date field a DateTime datatype or text field?
Try changing you SQL to this ---
Between CVDate([Start Date]) And CVDate([End Date]) OR Communications.Date
IS NULL;
--
KARL DEWEY
Build a little - Test a little


:

Thanks for the reply. No matter what date I select it still comes up with
the exact same number of people. So it is searching the whole table not just
the date selection.

Richard

:

Try this ---
SELECT DmList.[DM Name], Communications.Date
FROM DmList LEFT JOIN Communications ON DmList.[DM Name] =
Communications.[District Mgr]
WHERE (Communications.[District Mgr]) Is Null AND (Communications.Date)
Between [Start Date] And [End Date] OR Communications.Date IS NULL;

--
KARL DEWEY
Build a little - Test a little


:

I have a query that I am trying to find out who did not submit a report
during a particular time period. The unmatched works for all if I do not have
the date filed in the query. Following is the SQL Statement with the date
field.

SELECT DmList.[DM Name], Communications.Date
FROM DmList LEFT JOIN Communications ON DmList.[DM Name] =
Communications.[District Mgr]
WHERE (((Communications.[District Mgr]) Is Null) AND ((Communications.Date)
Between [Start Date] And [End Date]));

Any help would be appreciated.

Richard

 

[KARL DEWEY]

That is ok, I can sort it out but you can do a query to pull only the fields
used in your query for the sample.
--
KARL DEWEY
Build a little - Test a little

Sorry Carl. I am not sure how to get you the sample data becuse there are 9
fields in the Communications Table and 2 Fields in the DM Table. Any
suggestions would be helpfull. When I try to paste the date it all scambles
in the reply box.

Richard

I asked for "sample" data, not the complete information. I want to see what
works and what does not. That will entail running your sample data through
my machine to see if I get the same results because my data works.
--
KARL DEWEY
Build a little - Test a little

Karl,

The results list would be long since the DM list has 53 people. I could
send the whole DB. It is small since I just created recently.

Richard

:

Your orignal post said "...The unmatched works for all if I do not have the
date filed in the query..."
I just built the table with data and it worked for me. Sample data below
with results --
DM Name
1
2
3
4
5
6

Date District Mgr
1/1/2008 1
2/2/2008 2
1/1/2008 4
2/1/2008 6

DM Name Date
3
5

What did query not do or do wrong?
Post some sample data. Results.
--
KARL DEWEY
Build a little - Test a little


:

I have a date time field. Below did not work.

:

Is your Communications.Date field a DateTime datatype or text field?
Try changing you SQL to this ---
Between CVDate([Start Date]) And CVDate([End Date]) OR Communications.Date
IS NULL;
--
KARL DEWEY
Build a little - Test a little


:

Thanks for the reply. No matter what date I select it still comes up with
the exact same number of people. So it is searching the whole table not just
the date selection.

Richard

:

Try this ---
SELECT DmList.[DM Name], Communications.Date
FROM DmList LEFT JOIN Communications ON DmList.[DM Name] =
Communications.[District Mgr]
WHERE (Communications.[District Mgr]) Is Null AND (Communications.Date)
Between [Start Date] And [End Date] OR Communications.Date IS NULL;

--
KARL DEWEY
Build a little - Test a little


:

I have a query that I am trying to find out who did not submit a report
during a particular time period. The unmatched works for all if I do not have
the date filed in the query. Following is the SQL Statement with the date
field.

SELECT DmList.[DM Name], Communications.Date
FROM DmList LEFT JOIN Communications ON DmList.[DM Name] =
Communications.[District Mgr]
WHERE (((Communications.[District Mgr]) Is Null) AND ((Communications.Date)
Between [Start Date] And [End Date]));

Any help would be appreciated.

Richard

 

[Richardb]

Here is the Communications data. I will send the DM data seperate.

ROM District Mgr Date Store Number Attempt or Actual Contact Name Title of
Contact Manner of Communications Outcome/Results
Todd Stewart Lee Schenck 2/7/2008 601 Attempt Joe ASM In-person Not
available at this time
Dennis Child Cassaundra Hoch 2/8/2008 4821 Attempt Test SM Email Problem
with DM
Meleah Cayton Brian Athey 2/11/2008 3406 Attempt George DM In-person This is
a test
Meleah Cayton Darryl High 2/11/2008 601 Actual Joe Money MEM In-person Joe
wants more endcaps
Terri Ekstrand Stephen Tillerson 2/12/2008 4702 Actual Homer
Simpson MERM In-person Wants Power Tools reset
Russ Colgin William Hiser 2/12/2008 608 Actual Amy Smart MERM In-person Very
happy with Cosmo service
Meleah Cayton Gary Burt 2/14/2008 424 Actual Joe Depot MERM In-person Joe
had complained about the set of Custom Leathercraft. He wants it in sku order.
Dennis Child Phillip Campbell 2/19/2008 6621 Actual Sivo
Aisabonghi DM In-person TEST-Met w/ Sivo to discuss reset plans
Russ Colgin Tom Odom 2/20/2008 1072 Actual Justin Garris SM Email Wants us
to proceed with reset on our next visit.
Meleah Cayton Gary Burt 2/21/2008 0455 Attempt Tony Hurst DM Email Send
email regading project rain. Reply received with approval and thanks.
Terri Ekstrand Chris Waddell 2/27/2008 4719, 4714, 1803 Attempt Lee
Stocker RMM Email left copies of e-mail of service of project rain at stores
4719, 4714, and 1803.
Todd Stewart Joseph Benedetto 3/1/2008 0673 Actual Carl
Hanes ASM Email Carl, contacted me and asked not to perform any resets until
their inventory has been completed, also requested not to perform any resets
with out his approval

That is ok, I can sort it out but you can do a query to pull only the fields
used in your query for the sample.
--
KARL DEWEY
Build a little - Test a little

Sorry Carl. I am not sure how to get you the sample data becuse there are 9
fields in the Communications Table and 2 Fields in the DM Table. Any
suggestions would be helpfull. When I try to paste the date it all scambles
in the reply box.

Richard

I asked for "sample" data, not the complete information. I want to see what
works and what does not. That will entail running your sample data through
my machine to see if I get the same results because my data works.
--
KARL DEWEY
Build a little - Test a little


:

Karl,

The results list would be long since the DM list has 53 people. I could
send the whole DB. It is small since I just created recently.

Richard

:

Your orignal post said "...The unmatched works for all if I do not have the
date filed in the query..."
I just built the table with data and it worked for me. Sample data below
with results --
DM Name
1
2
3
4
5
6

Date District Mgr
1/1/2008 1
2/2/2008 2
1/1/2008 4
2/1/2008 6

DM Name Date
3
5

What did query not do or do wrong?
Post some sample data. Results.
--
KARL DEWEY
Build a little - Test a little


:

I have a date time field. Below did not work.

:

Is your Communications.Date field a DateTime datatype or text field?
Try changing you SQL to this ---
Between CVDate([Start Date]) And CVDate([End Date]) OR Communications.Date
IS NULL;
--
KARL DEWEY
Build a little - Test a little


:

Thanks for the reply. No matter what date I select it still comes up with
the exact same number of people. So it is searching the whole table not just
the date selection.

Richard

:

Try this ---
SELECT DmList.[DM Name], Communications.Date
FROM DmList LEFT JOIN Communications ON DmList.[DM Name] =
Communications.[District Mgr]
WHERE (Communications.[District Mgr]) Is Null AND (Communications.Date)
Between [Start Date] And [End Date] OR Communications.Date IS NULL;

--
KARL DEWEY
Build a little - Test a little


:

I have a query that I am trying to find out who did not submit a report
during a particular time period. The unmatched works for all if I do not have
the date filed in the query. Following is the SQL Statement with the date
field.

SELECT DmList.[DM Name], Communications.Date
FROM DmList LEFT JOIN Communications ON DmList.[DM Name] =
Communications.[District Mgr]
WHERE (((Communications.[District Mgr]) Is Null) AND ((Communications.Date)
Between [Start Date] And [End Date]));

Any help would be appreciated.

Richard

 

[Richardb]

DM Data

ID DM Name
5 Larry Long
6 Phillip Campbell
7 Sean Seaman
8 Eric Jergentz
9 Samuel Tichenor
10 Lowell Michels
11 Kim Shelton
12 Doug Mackay
13 William Cole
14 James Herpick
16 Jason Medina
18 William Stevens
19 Gary Burt
20 Larry Whitaker
21 Jeffrey Terry
22 Barbara Pencil
23 Andrew McGowen
24 Kevin Carlson
25 Shelly Bond
28 Patrick Parker
29 Stephen Tillerson
30 Bob Mosier
31 John Hurskainen
32 Michelle Grena
33 Charles Offutt
34 Michelle Baker
35 Paul Lauderbach
36 Chris Waddell
38 Frank Ramsdell
39 Steven Sabala
41 William Hiser
42 Kim Tish
44 Alejandro Alcala
45 Jason Thomas
46 Warren Chirichigno
47 Alberto Rangel
48 Joyce Mason
50 Brian Athey
51 Tom Odom
52 Darryl High
53 John Pickett
55 Samnang Eberhardt
58 Carol D'Angelo
59 Lee Schenck
60 Efren Maldonado
61 Mario Garcia
62 James (Jim) Doerr
63 Cassaundra Hoch
64 Joseph Benedetto
65 Adam Smith
66 Lisa Yeoman
67 Richard Wilson

That is ok, I can sort it out but you can do a query to pull only the fields
used in your query for the sample.
--
KARL DEWEY
Build a little - Test a little

Sorry Carl. I am not sure how to get you the sample data becuse there are 9
fields in the Communications Table and 2 Fields in the DM Table. Any
suggestions would be helpfull. When I try to paste the date it all scambles
in the reply box.

Richard

I asked for "sample" data, not the complete information. I want to see what
works and what does not. That will entail running your sample data through
my machine to see if I get the same results because my data works.
--
KARL DEWEY
Build a little - Test a little


:

Karl,

The results list would be long since the DM list has 53 people. I could
send the whole DB. It is small since I just created recently.

Richard

:

Your orignal post said "...The unmatched works for all if I do not have the
date filed in the query..."
I just built the table with data and it worked for me. Sample data below
with results --
DM Name
1
2
3
4
5
6

Date District Mgr
1/1/2008 1
2/2/2008 2
1/1/2008 4
2/1/2008 6

DM Name Date
3
5

What did query not do or do wrong?
Post some sample data. Results.
--
KARL DEWEY
Build a little - Test a little


:

I have a date time field. Below did not work.

:

Is your Communications.Date field a DateTime datatype or text field?
Try changing you SQL to this ---
Between CVDate([Start Date]) And CVDate([End Date]) OR Communications.Date
IS NULL;
--
KARL DEWEY
Build a little - Test a little


:

Thanks for the reply. No matter what date I select it still comes up with
the exact same number of people. So it is searching the whole table not just
the date selection.

Richard

:

Try this ---
SELECT DmList.[DM Name], Communications.Date
FROM DmList LEFT JOIN Communications ON DmList.[DM Name] =
Communications.[District Mgr]
WHERE (Communications.[District Mgr]) Is Null AND (Communications.Date)
Between [Start Date] And [End Date] OR Communications.Date IS NULL;

--
KARL DEWEY
Build a little - Test a little


:

I have a query that I am trying to find out who did not submit a report
during a particular time period. The unmatched works for all if I do not have
the date filed in the query. Following is the SQL Statement with the date
field.

SELECT DmList.[DM Name], Communications.Date
FROM DmList LEFT JOIN Communications ON DmList.[DM Name] =
Communications.[District Mgr]
WHERE (((Communications.[District Mgr]) Is Null) AND ((Communications.Date)
Between [Start Date] And [End Date]));

Any help would be appreciated.

Richard

 

[KARL DEWEY]

Try this --
SELECT DmList.[DM Name]
FROM DmList LEFT JOIN Communications ON DmList.[DM Name] =
Communications.[District Mgr]
WHERE (((Communications.CommDate) Not Between CVDate([Start Date]) And
CVDate([End Date]) Or (Communications.CommDate) Is Null));

--
KARL DEWEY
Build a little - Test a little

DM Data

ID DM Name
5 Larry Long
6 Phillip Campbell
7 Sean Seaman
8 Eric Jergentz
9 Samuel Tichenor
10 Lowell Michels
11 Kim Shelton
12 Doug Mackay
13 William Cole
14 James Herpick
16 Jason Medina
18 William Stevens
19 Gary Burt
20 Larry Whitaker
21 Jeffrey Terry
22 Barbara Pencil
23 Andrew McGowen
24 Kevin Carlson
25 Shelly Bond
28 Patrick Parker
29 Stephen Tillerson
30 Bob Mosier
31 John Hurskainen
32 Michelle Grena
33 Charles Offutt
34 Michelle Baker
35 Paul Lauderbach
36 Chris Waddell
38 Frank Ramsdell
39 Steven Sabala
41 William Hiser
42 Kim Tish
44 Alejandro Alcala
45 Jason Thomas
46 Warren Chirichigno
47 Alberto Rangel
48 Joyce Mason
50 Brian Athey
51 Tom Odom
52 Darryl High
53 John Pickett
55 Samnang Eberhardt
58 Carol D'Angelo
59 Lee Schenck
60 Efren Maldonado
61 Mario Garcia
62 James (Jim) Doerr
63 Cassaundra Hoch
64 Joseph Benedetto
65 Adam Smith
66 Lisa Yeoman
67 Richard Wilson

That is ok, I can sort it out but you can do a query to pull only the fields
used in your query for the sample.
--
KARL DEWEY
Build a little - Test a little

Sorry Carl. I am not sure how to get you the sample data becuse there are 9
fields in the Communications Table and 2 Fields in the DM Table. Any
suggestions would be helpfull. When I try to paste the date it all scambles
in the reply box.

Richard

:

I asked for "sample" data, not the complete information. I want to see what
works and what does not. That will entail running your sample data through
my machine to see if I get the same results because my data works.
--
KARL DEWEY
Build a little - Test a little


:

Karl,

The results list would be long since the DM list has 53 people. I could
send the whole DB. It is small since I just created recently.

Richard

:

Your orignal post said "...The unmatched works for all if I do not have the
date filed in the query..."
I just built the table with data and it worked for me. Sample data below
with results --
DM Name
1
2
3
4
5
6

Date District Mgr
1/1/2008 1
2/2/2008 2
1/1/2008 4
2/1/2008 6

DM Name Date
3
5

What did query not do or do wrong?
Post some sample data. Results.
--
KARL DEWEY
Build a little - Test a little


:

I have a date time field. Below did not work.

:

Is your Communications.Date field a DateTime datatype or text field?
Try changing you SQL to this ---
Between CVDate([Start Date]) And CVDate([End Date]) OR Communications.Date
IS NULL;
--
KARL DEWEY
Build a little - Test a little


:

Thanks for the reply. No matter what date I select it still comes up with
the exact same number of people. So it is searching the whole table not just
the date selection.

Richard

:

Try this ---
SELECT DmList.[DM Name], Communications.Date
FROM DmList LEFT JOIN Communications ON DmList.[DM Name] =
Communications.[District Mgr]
WHERE (Communications.[District Mgr]) Is Null AND (Communications.Date)
Between [Start Date] And [End Date] OR Communications.Date IS NULL;

--
KARL DEWEY
Build a little - Test a little


:

I have a query that I am trying to find out who did not submit a report
during a particular time period. The unmatched works for all if I do not have
the date filed in the query. Following is the SQL Statement with the date
field.

SELECT DmList.[DM Name], Communications.Date
FROM DmList LEFT JOIN Communications ON DmList.[DM Name] =
Communications.[District Mgr]
WHERE (((Communications.[District Mgr]) Is Null) AND ((Communications.Date)
Between [Start Date] And [End Date]));

Any help would be appreciated.

Richard

 

[Richardb]

When I run it, it comes up with Enter Parameter Value for
Communications.CommDate and than Start and end date

Try this --
SELECT DmList.[DM Name]
FROM DmList LEFT JOIN Communications ON DmList.[DM Name] =
Communications.[District Mgr]
WHERE (((Communications.CommDate) Not Between CVDate([Start Date]) And
CVDate([End Date]) Or (Communications.CommDate) Is Null));

--
KARL DEWEY
Build a little - Test a little

DM Data

ID DM Name
5 Larry Long
6 Phillip Campbell
7 Sean Seaman
8 Eric Jergentz
9 Samuel Tichenor
10 Lowell Michels
11 Kim Shelton
12 Doug Mackay
13 William Cole
14 James Herpick
16 Jason Medina
18 William Stevens
19 Gary Burt
20 Larry Whitaker
21 Jeffrey Terry
22 Barbara Pencil
23 Andrew McGowen
24 Kevin Carlson
25 Shelly Bond
28 Patrick Parker
29 Stephen Tillerson
30 Bob Mosier
31 John Hurskainen
32 Michelle Grena
33 Charles Offutt
34 Michelle Baker
35 Paul Lauderbach
36 Chris Waddell
38 Frank Ramsdell
39 Steven Sabala
41 William Hiser
42 Kim Tish
44 Alejandro Alcala
45 Jason Thomas
46 Warren Chirichigno
47 Alberto Rangel
48 Joyce Mason
50 Brian Athey
51 Tom Odom
52 Darryl High
53 John Pickett
55 Samnang Eberhardt
58 Carol D'Angelo
59 Lee Schenck
60 Efren Maldonado
61 Mario Garcia
62 James (Jim) Doerr
63 Cassaundra Hoch
64 Joseph Benedetto
65 Adam Smith
66 Lisa Yeoman
67 Richard Wilson

That is ok, I can sort it out but you can do a query to pull only the fields
used in your query for the sample.
--
KARL DEWEY
Build a little - Test a little


:

Sorry Carl. I am not sure how to get you the sample data becuse there are 9
fields in the Communications Table and 2 Fields in the DM Table. Any
suggestions would be helpfull. When I try to paste the date it all scambles
in the reply box.

Richard

:

I asked for "sample" data, not the complete information. I want to see what
works and what does not. That will entail running your sample data through
my machine to see if I get the same results because my data works.
--
KARL DEWEY
Build a little - Test a little


:

Karl,

The results list would be long since the DM list has 53 people. I could
send the whole DB. It is small since I just created recently.

Richard

:

Your orignal post said "...The unmatched works for all if I do not have the
date filed in the query..."
I just built the table with data and it worked for me. Sample data below
with results --
DM Name
1
2
3
4
5
6

Date District Mgr
1/1/2008 1
2/2/2008 2
1/1/2008 4
2/1/2008 6

DM Name Date
3
5

What did query not do or do wrong?
Post some sample data. Results.
--
KARL DEWEY
Build a little - Test a little


:

I have a date time field. Below did not work.

:

Is your Communications.Date field a DateTime datatype or text field?
Try changing you SQL to this ---
Between CVDate([Start Date]) And CVDate([End Date]) OR Communications.Date
IS NULL;
--
KARL DEWEY
Build a little - Test a little


:

Thanks for the reply. No matter what date I select it still comes up with
the exact same number of people. So it is searching the whole table not just
the date selection.

Richard

:

Try this ---
SELECT DmList.[DM Name], Communications.Date
FROM DmList LEFT JOIN Communications ON DmList.[DM Name] =
Communications.[District Mgr]
WHERE (Communications.[District Mgr]) Is Null AND (Communications.Date)
Between [Start Date] And [End Date] OR Communications.Date IS NULL;

--
KARL DEWEY
Build a little - Test a little


:

I have a query that I am trying to find out who did not submit a report
during a particular time period. The unmatched works for all if I do not have
the date filed in the query. Following is the SQL Statement with the date
field.

SELECT DmList.[DM Name], Communications.Date
FROM DmList LEFT JOIN Communications ON DmList.[DM Name] =
Communications.[District Mgr]
WHERE (((Communications.[District Mgr]) Is Null) AND ((Communications.Date)
Between [Start Date] And [End Date]));

Any help would be appreciated.

Richard

 

[KARL DEWEY]

Sorry, I forgot to tell you that your field name of 'date' gave me problems
as that is a reserved word in Access so I named the field CommDate.
--
KARL DEWEY
Build a little - Test a little

When I run it, it comes up with Enter Parameter Value for
Communications.CommDate and than Start and end date

Try this --
SELECT DmList.[DM Name]
FROM DmList LEFT JOIN Communications ON DmList.[DM Name] =
Communications.[District Mgr]
WHERE (((Communications.CommDate) Not Between CVDate([Start Date]) And
CVDate([End Date]) Or (Communications.CommDate) Is Null));

--
KARL DEWEY
Build a little - Test a little

DM Data

ID DM Name
5 Larry Long
6 Phillip Campbell
7 Sean Seaman
8 Eric Jergentz
9 Samuel Tichenor
10 Lowell Michels
11 Kim Shelton
12 Doug Mackay
13 William Cole
14 James Herpick
16 Jason Medina
18 William Stevens
19 Gary Burt
20 Larry Whitaker
21 Jeffrey Terry
22 Barbara Pencil
23 Andrew McGowen
24 Kevin Carlson
25 Shelly Bond
28 Patrick Parker
29 Stephen Tillerson
30 Bob Mosier
31 John Hurskainen
32 Michelle Grena
33 Charles Offutt
34 Michelle Baker
35 Paul Lauderbach
36 Chris Waddell
38 Frank Ramsdell
39 Steven Sabala
41 William Hiser
42 Kim Tish
44 Alejandro Alcala
45 Jason Thomas
46 Warren Chirichigno
47 Alberto Rangel
48 Joyce Mason
50 Brian Athey
51 Tom Odom
52 Darryl High
53 John Pickett
55 Samnang Eberhardt
58 Carol D'Angelo
59 Lee Schenck
60 Efren Maldonado
61 Mario Garcia
62 James (Jim) Doerr
63 Cassaundra Hoch
64 Joseph Benedetto
65 Adam Smith
66 Lisa Yeoman
67 Richard Wilson


:

That is ok, I can sort it out but you can do a query to pull only the fields
used in your query for the sample.
--
KARL DEWEY
Build a little - Test a little


:

Sorry Carl. I am not sure how to get you the sample data becuse there are 9
fields in the Communications Table and 2 Fields in the DM Table. Any
suggestions would be helpfull. When I try to paste the date it all scambles
in the reply box.

Richard

:

I asked for "sample" data, not the complete information. I want to see what
works and what does not. That will entail running your sample data through
my machine to see if I get the same results because my data works.
--
KARL DEWEY
Build a little - Test a little


:

Karl,

The results list would be long since the DM list has 53 people. I could
send the whole DB. It is small since I just created recently.

Richard

:

Your orignal post said "...The unmatched works for all if I do not have the
date filed in the query..."
I just built the table with data and it worked for me. Sample data below
with results --
DM Name
1
2
3
4
5
6

Date District Mgr
1/1/2008 1
2/2/2008 2
1/1/2008 4
2/1/2008 6

DM Name Date
3
5

What did query not do or do wrong?
Post some sample data. Results.
--
KARL DEWEY
Build a little - Test a little


:

I have a date time field. Below did not work.

:

Is your Communications.Date field a DateTime datatype or text field?
Try changing you SQL to this ---
Between CVDate([Start Date]) And CVDate([End Date]) OR Communications.Date
IS NULL;
--
KARL DEWEY
Build a little - Test a little


:

Thanks for the reply. No matter what date I select it still comes up with
the exact same number of people. So it is searching the whole table not just
the date selection.

Richard

:

Try this ---
SELECT DmList.[DM Name], Communications.Date
FROM DmList LEFT JOIN Communications ON DmList.[DM Name] =
Communications.[District Mgr]
WHERE (Communications.[District Mgr]) Is Null AND (Communications.Date)
Between [Start Date] And [End Date] OR Communications.Date IS NULL;

--
KARL DEWEY
Build a little - Test a little


:

I have a query that I am trying to find out who did not submit a report
during a particular time period. The unmatched works for all if I do not have
the date filed in the query. Following is the SQL Statement with the date
field.

SELECT DmList.[DM Name], Communications.Date
FROM DmList LEFT JOIN Communications ON DmList.[DM Name] =
Communications.[District Mgr]
WHERE (((Communications.[District Mgr]) Is Null) AND ((Communications.Date)
Between [Start Date] And [End Date]));

Any help would be appreciated.

Richard

 

[Richardb]

Thanks that seems to work. I appreciate all your help.

Richard

Sorry, I forgot to tell you that your field name of 'date' gave me problems
as that is a reserved word in Access so I named the field CommDate.
--
KARL DEWEY
Build a little - Test a little

When I run it, it comes up with Enter Parameter Value for
Communications.CommDate and than Start and end date

Try this --
SELECT DmList.[DM Name]
FROM DmList LEFT JOIN Communications ON DmList.[DM Name] =
Communications.[District Mgr]
WHERE (((Communications.CommDate) Not Between CVDate([Start Date]) And
CVDate([End Date]) Or (Communications.CommDate) Is Null));

--
KARL DEWEY
Build a little - Test a little


:

DM Data

ID DM Name
5 Larry Long
6 Phillip Campbell
7 Sean Seaman
8 Eric Jergentz
9 Samuel Tichenor
10 Lowell Michels
11 Kim Shelton
12 Doug Mackay
13 William Cole
14 James Herpick
16 Jason Medina
18 William Stevens
19 Gary Burt
20 Larry Whitaker
21 Jeffrey Terry
22 Barbara Pencil
23 Andrew McGowen
24 Kevin Carlson
25 Shelly Bond
28 Patrick Parker
29 Stephen Tillerson
30 Bob Mosier
31 John Hurskainen
32 Michelle Grena
33 Charles Offutt
34 Michelle Baker
35 Paul Lauderbach
36 Chris Waddell
38 Frank Ramsdell
39 Steven Sabala
41 William Hiser
42 Kim Tish
44 Alejandro Alcala
45 Jason Thomas
46 Warren Chirichigno
47 Alberto Rangel
48 Joyce Mason
50 Brian Athey
51 Tom Odom
52 Darryl High
53 John Pickett
55 Samnang Eberhardt
58 Carol D'Angelo
59 Lee Schenck
60 Efren Maldonado
61 Mario Garcia
62 James (Jim) Doerr
63 Cassaundra Hoch
64 Joseph Benedetto
65 Adam Smith
66 Lisa Yeoman
67 Richard Wilson


:

That is ok, I can sort it out but you can do a query to pull only the fields
used in your query for the sample.
--
KARL DEWEY
Build a little - Test a little


:

Sorry Carl. I am not sure how to get you the sample data becuse there are 9
fields in the Communications Table and 2 Fields in the DM Table. Any
suggestions would be helpfull. When I try to paste the date it all scambles
in the reply box.

Richard

:

I asked for "sample" data, not the complete information. I want to see what
works and what does not. That will entail running your sample data through
my machine to see if I get the same results because my data works.
--
KARL DEWEY
Build a little - Test a little


:

Karl,

The results list would be long since the DM list has 53 people. I could
send the whole DB. It is small since I just created recently.

Richard

:

Your orignal post said "...The unmatched works for all if I do not have the
date filed in the query..."
I just built the table with data and it worked for me. Sample data below
with results --
DM Name
1
2
3
4
5
6

Date District Mgr
1/1/2008 1
2/2/2008 2
1/1/2008 4
2/1/2008 6

DM Name Date
3
5

What did query not do or do wrong?
Post some sample data. Results.
--
KARL DEWEY
Build a little - Test a little


:

I have a date time field. Below did not work.

:

Is your Communications.Date field a DateTime datatype or text field?
Try changing you SQL to this ---
Between CVDate([Start Date]) And CVDate([End Date]) OR Communications.Date
IS NULL;
--
KARL DEWEY
Build a little - Test a little


:

Thanks for the reply. No matter what date I select it still comes up with
the exact same number of people. So it is searching the whole table not just
the date selection.

Richard

:

Try this ---
SELECT DmList.[DM Name], Communications.Date
FROM DmList LEFT JOIN Communications ON DmList.[DM Name] =
Communications.[District Mgr]
WHERE (Communications.[District Mgr]) Is Null AND (Communications.Date)
Between [Start Date] And [End Date] OR Communications.Date IS NULL;

--
KARL DEWEY
Build a little - Test a little


:

I have a query that I am trying to find out who did not submit a report
during a particular time period. The unmatched works for all if I do not have
the date filed in the query. Following is the SQL Statement with the date
field.

SELECT DmList.[DM Name], Communications.Date
FROM DmList LEFT JOIN Communications ON DmList.[DM Name] =
Communications.[District Mgr]
WHERE (((Communications.[District Mgr]) Is Null) AND ((Communications.Date)
Between [Start Date] And [End Date]));

Any help would be appreciated.

Richard

 

[Richardb]

Karl,

This is not working. Can I get additional help?

Richard

Sorry, I forgot to tell you that your field name of 'date' gave me problems
as that is a reserved word in Access so I named the field CommDate.
--
KARL DEWEY
Build a little - Test a little

When I run it, it comes up with Enter Parameter Value for
Communications.CommDate and than Start and end date

Try this --
SELECT DmList.[DM Name]
FROM DmList LEFT JOIN Communications ON DmList.[DM Name] =
Communications.[District Mgr]
WHERE (((Communications.CommDate) Not Between CVDate([Start Date]) And
CVDate([End Date]) Or (Communications.CommDate) Is Null));

--
KARL DEWEY
Build a little - Test a little


:

DM Data

ID DM Name
5 Larry Long
6 Phillip Campbell
7 Sean Seaman
8 Eric Jergentz
9 Samuel Tichenor
10 Lowell Michels
11 Kim Shelton
12 Doug Mackay
13 William Cole
14 James Herpick
16 Jason Medina
18 William Stevens
19 Gary Burt
20 Larry Whitaker
21 Jeffrey Terry
22 Barbara Pencil
23 Andrew McGowen
24 Kevin Carlson
25 Shelly Bond
28 Patrick Parker
29 Stephen Tillerson
30 Bob Mosier
31 John Hurskainen
32 Michelle Grena
33 Charles Offutt
34 Michelle Baker
35 Paul Lauderbach
36 Chris Waddell
38 Frank Ramsdell
39 Steven Sabala
41 William Hiser
42 Kim Tish
44 Alejandro Alcala
45 Jason Thomas
46 Warren Chirichigno
47 Alberto Rangel
48 Joyce Mason
50 Brian Athey
51 Tom Odom
52 Darryl High
53 John Pickett
55 Samnang Eberhardt
58 Carol D'Angelo
59 Lee Schenck
60 Efren Maldonado
61 Mario Garcia
62 James (Jim) Doerr
63 Cassaundra Hoch
64 Joseph Benedetto
65 Adam Smith
66 Lisa Yeoman
67 Richard Wilson


:

That is ok, I can sort it out but you can do a query to pull only the fields
used in your query for the sample.
--
KARL DEWEY
Build a little - Test a little


:

Sorry Carl. I am not sure how to get you the sample data becuse there are 9
fields in the Communications Table and 2 Fields in the DM Table. Any
suggestions would be helpfull. When I try to paste the date it all scambles
in the reply box.

Richard

:

I asked for "sample" data, not the complete information. I want to see what
works and what does not. That will entail running your sample data through
my machine to see if I get the same results because my data works.
--
KARL DEWEY
Build a little - Test a little


:

Karl,

The results list would be long since the DM list has 53 people. I could
send the whole DB. It is small since I just created recently.

Richard

:

Your orignal post said "...The unmatched works for all if I do not have the
date filed in the query..."
I just built the table with data and it worked for me. Sample data below
with results --
DM Name
1
2
3
4
5
6

Date District Mgr
1/1/2008 1
2/2/2008 2
1/1/2008 4
2/1/2008 6

DM Name Date
3
5

What did query not do or do wrong?
Post some sample data. Results.
--
KARL DEWEY
Build a little - Test a little


:

I have a date time field. Below did not work.

:

Is your Communications.Date field a DateTime datatype or text field?
Try changing you SQL to this ---
Between CVDate([Start Date]) And CVDate([End Date]) OR Communications.Date
IS NULL;
--
KARL DEWEY
Build a little - Test a little


:

Thanks for the reply. No matter what date I select it still comes up with
the exact same number of people. So it is searching the whole table not just
the date selection.

Richard

:

Try this ---
SELECT DmList.[DM Name], Communications.Date
FROM DmList LEFT JOIN Communications ON DmList.[DM Name] =
Communications.[District Mgr]
WHERE (Communications.[District Mgr]) Is Null AND (Communications.Date)
Between [Start Date] And [End Date] OR Communications.Date IS NULL;

--
KARL DEWEY
Build a little - Test a little


:

I have a query that I am trying to find out who did not submit a report
during a particular time period. The unmatched works for all if I do not have
the date filed in the query. Following is the SQL Statement with the date
field.

SELECT DmList.[DM Name], Communications.Date
FROM DmList LEFT JOIN Communications ON DmList.[DM Name] =
Communications.[District Mgr]
WHERE (((Communications.[District Mgr]) Is Null) AND ((Communications.Date)
Between [Start Date] And [End Date]));

Any help would be appreciated.

Richard

 

[Michael Gramelspacher]

Karl,

This is not working. Can I get additional help?

Richard

perhaps:

SELECT DmList.[DM Name]
FROM DmList
LEFT JOIN (SELECT *
FROM Communications
WHERE Communications.CommDate BETWEEN CVDATE([Start Date])
AND CVDATE([End Date])) AS T
ON DmList.[DM Name] = T.[District Mgr]
WHERE T.[District Mgr] IS NULL;

 

[John W. Vinson]

This is not working. Can I get additional help?

"This" and "Not working" are both ambiguous.

Please post the SQL of the query you actually tried, and describe in what
manner it is not working.