why query does not work on over bulky records but work on smaller amt of data

[JoJo S via AccessMonster.com]

I have exhausted all my means as to why this qry does not work on vast volume
of data when it works on smaller amount of data. Thanks to anyone who would
offer advice. Table1 is on Access and Table2 in a data warehouse which I
access through ODBC.

The query employs two tbls:

Table1

Id DOS
10 1/5/2004
10 2/5/2004
11 3/5/2004
12 4/5/2004
13 5/5/2004
13 6/5/5004
13 7/5/2004

Table2

Id PlanStartDt PlanEndDt Plan
10 1/1/2003 1/31//2004 P1
10 2/1/2004 3/31/2004 P1
11 1/1/2004 12/31/2004 P3
13 1/1/2004 6/31/2004 P4

My query goes like:

SELECT table1.id, table1.DOS, Table2.Plan, iif(table2.id is not null, “y”,”n”)
as PlanCovg
INTO NewTable
FROM table1
LEFT JOIN table2
ON table1.id=table2.id AND table1.dos between table2.PlanStartDt and table2.
PlanEndDate

Table1 has over 130k records. The query result should be like:

Id DOS PLAN PlanCovg
10 1/5/2004 P1 Y
10 2/5/2004 P1 Y
11 3/5/2004 P3 Y
12 4/5/2004 N
13 5/5/2004 P4 Y
13 6/5/5004 P4 Y
13 7/5/2004 N

When I run the query based on over 130k records from table1, it won’t return
all records for situations like id=13 (from tbl1) where some of the dos from
table1 is covered by plan from table2 and some others not. It drops the
records where dos is not covered by plan totally. See below.
But the strange thing is that if I make table1 contain smaller number of
records, I get correct return.

13 5/5/2004 P4 Y
13 6/5/5004 P4 Y
13 7/5/2004 N (This record is dropped when I run on bulky table1, but stays
when I run on smaller amount of data)

Thank you very much!!!

 

[Guest]

The way I see it
13 7/5/2004 is not between 13 1/1/2004 6/31/2004 P4
and should not be returned any time.

Also it looks like you are using text fields instead of date fields.

Try this --
SELECT table1.ID, table1.DOS, table2.Plan, IIf([table2].[id] Is Not
Null,"y","n") AS PlanCovg INTO NewTable
FROM table1 LEFT JOIN table2 ON table1.ID = table2.ID
WHERE (((table1.DOS) Between [table2].[PlanStartDt] And
[table2].[PlanEndDt]));

 

[JoJo S via AccessMonster.com]

Thank you for your reply. Because 7/5/2004 is not between 1/1/2004 and
6/30/2004 (sorry about data type confusion by writing it as 6/31), I want the
result table to mark ID13 as "N" for coverage.

Anyway, I found out by scouring this website that there is such reported
Access flaw with outer join involving a literal value. See the link. Thanks
for your input too.

http://allenbrowne.com/bug-10.html

The way I see it
13 7/5/2004 is not between 13 1/1/2004 6/31/2004 P4
and should not be returned any time.

Also it looks like you are using text fields instead of date fields.

Try this --
SELECT table1.ID, table1.DOS, table2.Plan, IIf([table2].[id] Is Not
Null,"y","n") AS PlanCovg INTO NewTable
FROM table1 LEFT JOIN table2 ON table1.ID = table2.ID
WHERE (((table1.DOS) Between [table2].[PlanStartDt] And
[table2].[PlanEndDt]));

I have exhausted all my means as to why this qry does not work on vast volume
of data when it works on smaller amount of data. Thanks to anyone who would

[quoted text clipped - 56 lines]

Thank you very much!!!

 

[Guest]

Ok, try this - TESTED.
SELECT table1.ID, table1.DOS, table2.Plan, IIf([table2].[id] Is
Null,"N",IIf([DOS] Between [table2].[PlanStartDt] And
[table2].[PlanEndDt],"Y","N")) AS PlanCovg INTO NewTable
FROM table1 LEFT JOIN table2 ON table1.ID = table2.ID;

Thank you for your reply. Because 7/5/2004 is not between 1/1/2004 and
6/30/2004 (sorry about data type confusion by writing it as 6/31), I want the
result table to mark ID13 as "N" for coverage.

Anyway, I found out by scouring this website that there is such reported
Access flaw with outer join involving a literal value. See the link. Thanks
for your input too.

http://allenbrowne.com/bug-10.html

The way I see it
13 7/5/2004 is not between 13 1/1/2004 6/31/2004 P4
and should not be returned any time.

Also it looks like you are using text fields instead of date fields.

Try this --
SELECT table1.ID, table1.DOS, table2.Plan, IIf([table2].[id] Is Not
Null,"y","n") AS PlanCovg INTO NewTable
FROM table1 LEFT JOIN table2 ON table1.ID = table2.ID
WHERE (((table1.DOS) Between [table2].[PlanStartDt] And
[table2].[PlanEndDt]));

I have exhausted all my means as to why this qry does not work on vast volume
of data when it works on smaller amount of data. Thanks to anyone who would

[quoted text clipped - 56 lines]

Thank you very much!!!

 

[JoJo S via AccessMonster.com]

Thank you. I'll try it tonight and check the result tomorrow. Thanks again!

Ok, try this - TESTED.
SELECT table1.ID, table1.DOS, table2.Plan, IIf([table2].[id] Is
Null,"N",IIf([DOS] Between [table2].[PlanStartDt] And
[table2].[PlanEndDt],"Y","N")) AS PlanCovg INTO NewTable
FROM table1 LEFT JOIN table2 ON table1.ID = table2.ID;

Thank you for your reply. Because 7/5/2004 is not between 1/1/2004 and
6/30/2004 (sorry about data type confusion by writing it as 6/31), I want the

[quoted text clipped - 26 lines]

 

[JoJo S via AccessMonster.com]

Sorry, this does not work. If you are still intrigued, here is the real
sources of the two tbles and my qry and you can test it. Thank you very much!

TheTestPlans (t2)

num plan start end
1 AB 5/1/1997 11/13/1997
1 AB 11/14/1997 4/30/1997
1 CD 5/1/1998 4/25/1999
1 CD 4/26/1999 6/30/2002
1 CD 7/1/2002 8/28/2002
1 CD 8/29/2002 7/19/2003
1 CD 7/20/2003 12/31/2003
1 CD 1/1/2004 2/29/2004

The Test (t1)

MEMBER_ID dt_filled
1 1/9/2004
1 2/5/2004
1 2/6/2004
1 2/13/2004
1 2/26/2004
1 3/10/2004
1 4/8/2004
1 4/9/2004

SELECT DISTINCT t1.member_id, t2.plan, t1.dt_filled, IIf(t2.num Is Null,"n",
IIf(t1.dt_filled Between t2.start And t2.end,"y","n")) AS covg INTO newtest
FROM TheTest AS t1 LEFT JOIN TheTestPlans AS t2 ON T1.member_id=T2.num
ORDER BY t1.dt_filled;

Thank you. I'll try it tonight and check the result tomorrow. Thanks again!

Ok, try this - TESTED.
SELECT table1.ID, table1.DOS, table2.Plan, IIf([table2].[id] Is

[quoted text clipped - 9 lines]