Report Query

  • Thread starter That Crazy Hockey Dood
  • Start date
T

That Crazy Hockey Dood

Good Afternoon..

I was assisted with writing a query by someone in these forums a year or so
ago. However, something recently happened that caused for question on the
accuracy of the query. Below is the SQL:

SELECT Exception.Date, Exception.[Thru Date], Exception.[Associate Name],
Exception.[Event Type], Exception.FMLA, Exception.Initial, Exception.Note
FROM [Exception]
WHERE (((Nz([Date],#1/1/1900#))>=Nz([Forms]![Report
Generator]![Date],Nz([Date],#1/1/1900#))) AND ((Nz([Thru
Date],#12/31/2099#))<=Nz([Forms]![Report Generator]![Thru
Date],Nz([Date],#12/31/2099#))) AND (([Associate Name] &
"")=Nz([Forms]![Report Generator]![Name],[Associate Name] & "")) AND (([Event
Type] & "")=Nz([Forms]![Report Generator]![Event],[Event Type] & "")) AND
(([FMLA] & "")=Nz([Forms]![Report Generator]![FMLA],[FMLA] & "")));

The query is hitting a table called Exception and pulling back the data
based upon a form that users input one or a combination of Date, Thru Date,
Associate Name, Event Type and FMLA.

Outside of the fact that I committed a sin by using reserved words, why
would I see inaccurate results. For example: I chose for the report to tell
me events between 1/8/2009 and 1/9/2009. The report gives me anything it
matches on for those days. However, an event that started on 1/6/2009
through 1/10/2009 is not reported on.

Any assistance is appreciated.

Thanks,
Jim
 
M

Marshall Barton

That said:
I was assisted with writing a query by someone in these forums a year or so
ago. However, something recently happened that caused for question on the
accuracy of the query. Below is the SQL:

SELECT Exception.Date, Exception.[Thru Date], Exception.[Associate Name],
Exception.[Event Type], Exception.FMLA, Exception.Initial, Exception.Note
FROM [Exception]
WHERE (((Nz([Date],#1/1/1900#))>=Nz([Forms]![Report
Generator]![Date],Nz([Date],#1/1/1900#))) AND ((Nz([Thru
Date],#12/31/2099#))<=Nz([Forms]![Report Generator]![Thru
Date],Nz([Date],#12/31/2099#))) AND (([Associate Name] &
"")=Nz([Forms]![Report Generator]![Name],[Associate Name] & "")) AND (([Event
Type] & "")=Nz([Forms]![Report Generator]![Event],[Event Type] & "")) AND
(([FMLA] & "")=Nz([Forms]![Report Generator]![FMLA],[FMLA] & "")));

The query is hitting a table called Exception and pulling back the data
based upon a form that users input one or a combination of Date, Thru Date,
Associate Name, Event Type and FMLA.

Outside of the fact that I committed a sin by using reserved words, why
would I see inaccurate results. For example: I chose for the report to tell
me events between 1/8/2009 and 1/9/2009. The report gives me anything it
matches on for those days. However, an event that started on 1/6/2009
through 1/10/2009 is not reported on.
Click to expand...


Not sure about the default values or the parenthesis, but I
think the date part of the Where clause should be more like:

WHERE ((Nz([Date],#1/1/1900#)<=Nz([Forms]![Report
Generator]![ThruDate],Nz([ThruDate],#12/31/2099#)))
AND (Nz([Thru Date],#12/31/2099#)>=Nz([Forms]![Report
Generator]![Date],Nz([Date],#1/1/1900#))))
AND . . .
 
M

Marshall Barton

That said:
I was assisted with writing a query by someone in these forums a year or so
ago. However, something recently happened that caused for question on the
accuracy of the query. Below is the SQL:

SELECT Exception.Date, Exception.[Thru Date], Exception.[Associate Name],
Exception.[Event Type], Exception.FMLA, Exception.Initial, Exception.Note
FROM [Exception]
WHERE (((Nz([Date],#1/1/1900#))>=Nz([Forms]![Report
Generator]![Date],Nz([Date],#1/1/1900#))) AND ((Nz([Thru
Date],#12/31/2099#))<=Nz([Forms]![Report Generator]![Thru
Date],Nz([Date],#12/31/2099#))) AND (([Associate Name] &
"")=Nz([Forms]![Report Generator]![Name],[Associate Name] & "")) AND (([Event
Type] & "")=Nz([Forms]![Report Generator]![Event],[Event Type] & "")) AND
(([FMLA] & "")=Nz([Forms]![Report Generator]![FMLA],[FMLA] & "")));

The query is hitting a table called Exception and pulling back the data
based upon a form that users input one or a combination of Date, Thru Date,
Associate Name, Event Type and FMLA.

Outside of the fact that I committed a sin by using reserved words, why
would I see inaccurate results. For example: I chose for the report to tell
me events between 1/8/2009 and 1/9/2009. The report gives me anything it
matches on for those days. However, an event that started on 1/6/2009
through 1/10/2009 is not reported on.
Click to expand...


Not sure about the default values or the parenthesis, but I
think the date part of the Where clause should be more like:

WHERE ((Nz([Date],#1/1/1900#)<=Nz([Forms]![Report
Generator]![ThruDate],Nz([ThruDate],#12/31/2099#)))
AND (Nz([Thru Date],#12/31/2099#)>=Nz([Forms]![Report
Generator]![Date],Nz([Date],#1/1/1900#))))
AND . . .
 
T

That Crazy Hockey Dood

Marsh..

That was the issue. I had to remove an additional ) in your suggestion but
it worked like a charm. I am confused though and maybe I am reading this
wrong.

WHERE ((Nz([Date],#1/1/1900#)<=Nz([Forms]![Report
Generator]![Thru Date],Nz([Thru Date],#12/31/2099#))) AND (Nz([Thru
Date],#12/31/2099#)>=Nz([Forms]![Report
Generator]![Date],Nz([Date],#1/1/1900#)))

Is this saying where the DATE (beginning at 1/1/1900) is less than or equal
to the field THRU DATE (in the form Report Generator) and where the THRU DATE
is greater than or equal to the field DATE (in the form Report Generator)
return the matched values?

I guess I want to understand more so then just taking the code and running
with it.

Thanks,
Jim


--
If it works then you are doing something right!!


Marshall Barton said:
That said:
I was assisted with writing a query by someone in these forums a year or so
ago. However, something recently happened that caused for question on the
accuracy of the query. Below is the SQL:

SELECT Exception.Date, Exception.[Thru Date], Exception.[Associate Name],
Exception.[Event Type], Exception.FMLA, Exception.Initial, Exception.Note
FROM [Exception]
WHERE (((Nz([Date],#1/1/1900#))>=Nz([Forms]![Report
Generator]![Date],Nz([Date],#1/1/1900#))) AND ((Nz([Thru
Date],#12/31/2099#))<=Nz([Forms]![Report Generator]![Thru
Date],Nz([Date],#12/31/2099#))) AND (([Associate Name] &
"")=Nz([Forms]![Report Generator]![Name],[Associate Name] & "")) AND (([Event
Type] & "")=Nz([Forms]![Report Generator]![Event],[Event Type] & "")) AND
(([FMLA] & "")=Nz([Forms]![Report Generator]![FMLA],[FMLA] & "")));

The query is hitting a table called Exception and pulling back the data
based upon a form that users input one or a combination of Date, Thru Date,
Associate Name, Event Type and FMLA.

Outside of the fact that I committed a sin by using reserved words, why
would I see inaccurate results. For example: I chose for the report to tell
me events between 1/8/2009 and 1/9/2009. The report gives me anything it
matches on for those days. However, an event that started on 1/6/2009
through 1/10/2009 is not reported on.
Click to expand...


Not sure about the default values or the parenthesis, but I
think the date part of the Where clause should be more like:

WHERE ((Nz([Date],#1/1/1900#)<=Nz([Forms]![Report
Generator]![ThruDate],Nz([ThruDate],#12/31/2099#)))
AND (Nz([Thru Date],#12/31/2099#)>=Nz([Forms]![Report
Generator]![Date],Nz([Date],#1/1/1900#))))
AND . . .
Click to expand...
 
T

That Crazy Hockey Dood

Marsh..

That was the issue. I had to remove an additional ) in your suggestion but
it worked like a charm. I am confused though and maybe I am reading this
wrong.

WHERE ((Nz([Date],#1/1/1900#)<=Nz([Forms]![Report
Generator]![Thru Date],Nz([Thru Date],#12/31/2099#))) AND (Nz([Thru
Date],#12/31/2099#)>=Nz([Forms]![Report
Generator]![Date],Nz([Date],#1/1/1900#)))

Is this saying where the DATE (beginning at 1/1/1900) is less than or equal
to the field THRU DATE (in the form Report Generator) and where the THRU DATE
is greater than or equal to the field DATE (in the form Report Generator)
return the matched values?

I guess I want to understand more so then just taking the code and running
with it.

Thanks,
Jim


--
If it works then you are doing something right!!


Marshall Barton said:
That said:
I was assisted with writing a query by someone in these forums a year or so
ago. However, something recently happened that caused for question on the
accuracy of the query. Below is the SQL:

SELECT Exception.Date, Exception.[Thru Date], Exception.[Associate Name],
Exception.[Event Type], Exception.FMLA, Exception.Initial, Exception.Note
FROM [Exception]
WHERE (((Nz([Date],#1/1/1900#))>=Nz([Forms]![Report
Generator]![Date],Nz([Date],#1/1/1900#))) AND ((Nz([Thru
Date],#12/31/2099#))<=Nz([Forms]![Report Generator]![Thru
Date],Nz([Date],#12/31/2099#))) AND (([Associate Name] &
"")=Nz([Forms]![Report Generator]![Name],[Associate Name] & "")) AND (([Event
Type] & "")=Nz([Forms]![Report Generator]![Event],[Event Type] & "")) AND
(([FMLA] & "")=Nz([Forms]![Report Generator]![FMLA],[FMLA] & "")));

The query is hitting a table called Exception and pulling back the data
based upon a form that users input one or a combination of Date, Thru Date,
Associate Name, Event Type and FMLA.

Outside of the fact that I committed a sin by using reserved words, why
would I see inaccurate results. For example: I chose for the report to tell
me events between 1/8/2009 and 1/9/2009. The report gives me anything it
matches on for those days. However, an event that started on 1/6/2009
through 1/10/2009 is not reported on.
Click to expand...


Not sure about the default values or the parenthesis, but I
think the date part of the Where clause should be more like:

WHERE ((Nz([Date],#1/1/1900#)<=Nz([Forms]![Report
Generator]![ThruDate],Nz([ThruDate],#12/31/2099#)))
AND (Nz([Thru Date],#12/31/2099#)>=Nz([Forms]![Report
Generator]![Date],Nz([Date],#1/1/1900#))))
AND . . .
Click to expand...
 
M

Marshall Barton

That said:
That was the issue. I had to remove an additional ) in your suggestion but
it worked like a charm. I am confused though and maybe I am reading this
wrong.

WHERE ((Nz([Date],#1/1/1900#)<=Nz([Forms]![Report
Generator]![Thru Date],Nz([Thru Date],#12/31/2099#))) AND (Nz([Thru
Date],#12/31/2099#)>=Nz([Forms]![Report
Generator]![Date],Nz([Date],#1/1/1900#)))

Is this saying where the DATE (beginning at 1/1/1900) is less than or equal
to the field THRU DATE (in the form Report Generator) and where the THRU DATE
is greater than or equal to the field DATE (in the form Report Generator)
return the matched values?

I guess I want to understand more so then just taking the code and running
with it.
Click to expand...


Understanding is Good Idea ;-)

For two intervals to overlap, start1 must be before end2 and
start2 must be before end1. Use a pencil and paper to draw
some interval lines to see how that works for cases where
one interval is before, after, contained in or overlapping
the start or end of the other interval.
 
M

Marshall Barton

That said:
That was the issue. I had to remove an additional ) in your suggestion but
it worked like a charm. I am confused though and maybe I am reading this
wrong.

WHERE ((Nz([Date],#1/1/1900#)<=Nz([Forms]![Report
Generator]![Thru Date],Nz([Thru Date],#12/31/2099#))) AND (Nz([Thru
Date],#12/31/2099#)>=Nz([Forms]![Report
Generator]![Date],Nz([Date],#1/1/1900#)))

Is this saying where the DATE (beginning at 1/1/1900) is less than or equal
to the field THRU DATE (in the form Report Generator) and where the THRU DATE
is greater than or equal to the field DATE (in the form Report Generator)
return the matched values?

I guess I want to understand more so then just taking the code and running
with it.
Click to expand...


Understanding is Good Idea ;-)

For two intervals to overlap, start1 must be before end2 and
start2 must be before end1. Use a pencil and paper to draw
some interval lines to see how that works for cases where
one interval is before, after, contained in or overlapping
the start or end of the other interval.
 
T

That Crazy Hockey Dood

Marsh..

It is a bit confusing I will admit but I suppose it gives the appropriate
thought process to both arguements about the chicken and the egg.

On one hand, to start you have to have the egg before the chicken. However,
the other hand says you have to have a chicken to get an egg.

If you know of any web pages that explain this a little better then please
feel free to share. I have looked it over and over and I still confuse
myself.

Jim
--
If it works then you are doing something right!!


Marshall Barton said:
That said:
That was the issue. I had to remove an additional ) in your suggestion but
it worked like a charm. I am confused though and maybe I am reading this
wrong.

WHERE ((Nz([Date],#1/1/1900#)<=Nz([Forms]![Report
Generator]![Thru Date],Nz([Thru Date],#12/31/2099#))) AND (Nz([Thru
Date],#12/31/2099#)>=Nz([Forms]![Report
Generator]![Date],Nz([Date],#1/1/1900#)))

Is this saying where the DATE (beginning at 1/1/1900) is less than or equal
to the field THRU DATE (in the form Report Generator) and where the THRU DATE
is greater than or equal to the field DATE (in the form Report Generator)
return the matched values?

I guess I want to understand more so then just taking the code and running
with it.
Click to expand...


Understanding is Good Idea ;-)

For two intervals to overlap, start1 must be before end2 and
start2 must be before end1. Use a pencil and paper to draw
some interval lines to see how that works for cases where
one interval is before, after, contained in or overlapping
the start or end of the other interval.
Click to expand...
 
T

That Crazy Hockey Dood

Marsh..

It is a bit confusing I will admit but I suppose it gives the appropriate
thought process to both arguements about the chicken and the egg.

On one hand, to start you have to have the egg before the chicken. However,
the other hand says you have to have a chicken to get an egg.

If you know of any web pages that explain this a little better then please
feel free to share. I have looked it over and over and I still confuse
myself.

Jim
--
If it works then you are doing something right!!


Marshall Barton said:
That said:
That was the issue. I had to remove an additional ) in your suggestion but
it worked like a charm. I am confused though and maybe I am reading this
wrong.

WHERE ((Nz([Date],#1/1/1900#)<=Nz([Forms]![Report
Generator]![Thru Date],Nz([Thru Date],#12/31/2099#))) AND (Nz([Thru
Date],#12/31/2099#)>=Nz([Forms]![Report
Generator]![Date],Nz([Date],#1/1/1900#)))

Is this saying where the DATE (beginning at 1/1/1900) is less than or equal
to the field THRU DATE (in the form Report Generator) and where the THRU DATE
is greater than or equal to the field DATE (in the form Report Generator)
return the matched values?

I guess I want to understand more so then just taking the code and running
with it.
Click to expand...


Understanding is Good Idea ;-)

For two intervals to overlap, start1 must be before end2 and
start2 must be before end1. Use a pencil and paper to draw
some interval lines to see how that works for cases where
one interval is before, after, contained in or overlapping
the start or end of the other interval.
Click to expand...
 
M

Marshall Barton

That said:
It is a bit confusing I will admit but I suppose it gives the appropriate
thought process to both arguements about the chicken and the egg.

On one hand, to start you have to have the egg before the chicken. However,
the other hand says you have to have a chicken to get an egg.

If you know of any web pages that explain this a little better then please
feel free to share. I have looked it over and over and I still confuse
myself.
Click to expand...


A better explanation? There is no better explanation than
the one you come up with after looking at a piece of paper
with some lines on it. Just draw two interval lines such as
s1 -------------- e1
s2 ------------------------ e2
If your newsreader program renders that in a fashion
somewhat similar to the way I typed it, you can see that
s1<e2 and s2<e1

and compare that with:
s1 ---------- e1
s2 ---------- e2
where s1<e2 but s2>e1

Repeat that kind of thing for the other cases of overlapping
and non overlapping intervals and all should become clear.

I did Google for Overlapping Intervals, but the search
results mostly tended to be esoteric math related to more
complex subjects. The best one I could find is at:
http://www.rgrjr.com/emacs/overlap.html
 
M

Marshall Barton

That said:
It is a bit confusing I will admit but I suppose it gives the appropriate
thought process to both arguements about the chicken and the egg.

On one hand, to start you have to have the egg before the chicken. However,
the other hand says you have to have a chicken to get an egg.

If you know of any web pages that explain this a little better then please
feel free to share. I have looked it over and over and I still confuse
myself.
Click to expand...


A better explanation? There is no better explanation than
the one you come up with after looking at a piece of paper
with some lines on it. Just draw two interval lines such as
s1 -------------- e1
s2 ------------------------ e2
If your newsreader program renders that in a fashion
somewhat similar to the way I typed it, you can see that
s1<e2 and s2<e1

and compare that with:
s1 ---------- e1
s2 ---------- e2
where s1<e2 but s2>e1

Repeat that kind of thing for the other cases of overlapping
and non overlapping intervals and all should become clear.

I did Google for Overlapping Intervals, but the search
results mostly tended to be esoteric math related to more
complex subjects. The best one I could find is at:
http://www.rgrjr.com/emacs/overlap.html
 
T

That Crazy Hockey Dood

Marsh..

That did help. Luckily I am viewing this via the .html pages and it kept
your example the way you intended. The problem I had this AM was I drew the
lines parallel opposed to having them offset as you do in your first diagram.
I kept confusing all of it with the 2nd example b/c "logical" timeline
progression would be more like the 2nd diagram.

Thank you for taking that extra moment to straighten me out on this. I just
hope that the logic sticks the next time I have to create a report with this
type of logic.

Jim
 
T

That Crazy Hockey Dood

Marsh..

That did help. Luckily I am viewing this via the .html pages and it kept
your example the way you intended. The problem I had this AM was I drew the
lines parallel opposed to having them offset as you do in your first diagram.
I kept confusing all of it with the 2nd example b/c "logical" timeline
progression would be more like the 2nd diagram.

Thank you for taking that extra moment to straighten me out on this. I just
hope that the logic sticks the next time I have to create a report with this
type of logic.

Jim
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Using a Form to create a Report 2
Using a Form to create a Report Pt. 2 2
Using a Form to create Reports 1
Query Field not showing up as available on report??? 4
Report Limitation 2
Capture filtered records only on report 4
Summary Report 2
Problems with Syntax to Reference Text Boxes on Report 2

Top