By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
428,586 Members | 1,150 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 428,586 IT Pros & Developers. It's quick & easy.

Find Missing Date Ranges

P: n/a
Transaction data is given with date ranges:
Beginning End
4/1/06 4/4/06
4/7/06 4/11/06
4/14/06 4/17/06
4/18/06 4/21/06
426/06 4/30/06

I am looking for suggestions on how to find the date ranges where there were
no transactions.
4/12/06 - 4/13/06
4/22/06 - 4/25/06

Thanks!

Steve
Apr 23 '06
Share this Question
Share on Google+
67 Replies


P: n/a
Are you too incompetent to use Google?

John... Visio MVP

"PC Datasheet" <fa***@email.com> wrote in message
news:eH*****************@newsread1.news.atl.earthl ink.net...
OK, where are the search results for contributions to this newsgroup by
your
A$$ pal Arno R in the last three months?

"John Marshall, MVP" <la******@stonehenge.ca> wrote in message
news:xc******************************@magma.ca...
"PC Datasheet" <fa***@email.com> wrote in message
news:aU******************@newsread1.news.atl.earth link.net...
Your opinion is as worthless as the filthy garbage you pollute this
newsgroup with.

All you are doing is demonstrating to all newsgroup readers what an
obsessed A$$ you are. Your filthy garbage makes no contribution to this
newsgroup.

Great example of polite behaviour.

John... Visio MVP


Apr 26 '06 #51

P: n/a
Hi Tim,

Thanks for the example! It leads to two questions ---
1. Could your example be turned into a crosstab query to show Labor, Parts
and Othercharges in separate columns and then for the rows show different
jobs?

2. << if you need transaction based date ranges they need to be introduced
in the above subqueries>>
Can a subquery be embedded in another subquery? How about a correlated
subquery?

Yes, I looked at the Access97 Help file on subqueries. It introduces and
explains the predicates which I found very helpful.

Steve

"Tim Marshall" <TI****@PurplePandaChasers.Moertherium> wrote in message
news:e2**********@coranto.ucs.mun.ca...
PC Datasheet wrote:
You have shown subqueries to be a powerful tool! I see why you use them
daily.


Hi Steve - they are a pretty common and useful thing for something like a
schema where you have Work Tickets (say for a repair shop or home
heating/air conditioning service) that have separate tables for:

Labour,
Parts,
Other Charges

For totals on a work ticket is a simple matter of

SELECT

wt_number,
wt_pk,
(SELECT sum(Labour_Hours) from labour where labour_wt_fk = wt_pk) as
Hours,
(SELECT sum(Labour_Cost) from labour where labour_wt_fk = wt_pk) as
[Labour Cost],
(SELECT sum(Parts_Cost) from parts where parts_wt_fk = wt_pk) as [Parts
Cost],
(SELECT sum(Other_Cost) from Labour where other_wt_fk = wt_pk) as [Other
Cost]

FROM

work_tickets

WHERE

<whatever - if you need transaction based date ranges they need to be
introduced in the above subqueries, not here>

I don't know about the godawful help in later versions, but A97's great
help file had a very good section subqueries.
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me

Apr 26 '06 #52

P: n/a
PC Datasheet wrote:
Thanks for the example! It leads to two questions ---
1. Could your example be turned into a crosstab query to show Labor, Parts
and Othercharges in separate columns and then for the rows show different
jobs?
Not sure - I do mostly Oracle and am currently using Jet for hobby
related apps. Oracle doesn't have cross tab queries, so I'm not sure.
You'd have to have a go at it.

However, to do what you're talking about, just the sql I gave would give
you precisely what you wanted unless there were some other tables and
joins in the main select that would cause jobs (work tickets was what i
called them) that caused a job to appear multiple times.
Can a subquery be embedded in another subquery?
Yes, but, in Oracle at least, any alias or identifier in the main select
seems to only be identified the next level down. Can't really think of
any examples now...
How about a correlated
subquery?


I don't use correlated sub queries (for Oracle, anyway, and probably the
same definition in Jet, these use the exists/not exists operator in the
where clause) and prefer to use multiple row sub queries in the where
clause.

--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me
Apr 26 '06 #53

P: n/a
"PC Datasheet" <fa***@email.com> schreef in bericht news:eH*****************@newsread1.news.atl.earthl ink.net...
OK, where are the search results for contributions to this newsgroup by your
A$$ pal Arno R in the last three months?

What are you trying to prove here Steve ??

Dim lngContribAR as long 'Contributions by Arno R in the last three months
'Google for the answer if you like

Select Case lngContribAR
Case >= 100
Steve is a liar and a cheet
Case >= 75
Steve is a liar and a cheet
Case >= 50
Steve is a liar and a cheet
Case >= 25
Steve is a liar and a cheet
Case >= 0
Steve is a liar and a cheet
End Select

So: what are you trying to prove??
The number of contributions is not relevant here at all IMO.

You are just a liar and a cheet (and I am trying to be nice here...)
"Still waiting... " remember?

Arno R
Apr 26 '06 #54

P: n/a
Arno, I know your first language is not English; so the correct word is
cheat.

John... Visio MVP

"Arno R" <ar***********@tiscali.nl> wrote in message
news:44**********************@text.nova.planet.nl. ..
"PC Datasheet" <fa***@email.com> schreef in bericht
news:eH*****************@newsread1.news.atl.earthl ink.net...
OK, where are the search results for contributions to this newsgroup by
your
A$$ pal Arno R in the last three months?


What are you trying to prove here Steve ??

Dim lngContribAR as long 'Contributions by Arno R in the last three
months
'Google for the answer if you like

Select Case lngContribAR
Case >= 100
Steve is a liar and a cheet
Case >= 75
Steve is a liar and a cheet
Case >= 50
Steve is a liar and a cheet
Case >= 25
Steve is a liar and a cheet
Case >= 0
Steve is a liar and a cheet
End Select

So: what are you trying to prove??
The number of contributions is not relevant here at all IMO.

You are just a liar and a cheet (and I am trying to be nice here...)
"Still waiting... " remember?

Arno R
Apr 26 '06 #55

P: n/a

"John Marshall, MVP" <la******@stonehenge.ca> schreef in bericht news:c7********************@magma.ca...
Arno, I know your first language is not English; so the correct word is
cheat.


Thanks, John!
I sure hope Steve does NOT notice my silly (sure looks stupid...) mistake here.

Oh my GOD!
Please have merci on me!! (I mean mercy...)

Arno R

Apr 26 '06 #56

P: n/a
You said --
<<With a unique name like Arno, it is simple to do a search, just on this
newsgroup, and find several useful posts.>>

Now put your money where your mouth is and see if you can prove what you
said.

"John Marshall, MVP" <la******@stonehenge.ca> wrote in message
news:48******************************@magma.ca...
Are you too incompetent to use Google?

John... Visio MVP

"PC Datasheet" <fa***@email.com> wrote in message
news:eH*****************@newsread1.news.atl.earthl ink.net...
OK, where are the search results for contributions to this newsgroup by
your
A$$ pal Arno R in the last three months?

"John Marshall, MVP" <la******@stonehenge.ca> wrote in message
news:xc******************************@magma.ca...
"PC Datasheet" <fa***@email.com> wrote in message
news:aU******************@newsread1.news.atl.earth link.net...
Your opinion is as worthless as the filthy garbage you pollute this
newsgroup with.

All you are doing is demonstrating to all newsgroup readers what an
obsessed A$$ you are. Your filthy garbage makes no contribution to this
newsgroup.
Great example of polite behaviour.

John... Visio MVP



Apr 26 '06 #57

P: n/a
If I post the results here, it would just be wasting bandwidth and you would
just claim that it was all fake.

So are you admitting to the fact that you do not have the technical skills
to do the research or (as most of the regulars know) you know your
statements are false?

I do not have to prove anything, I have provided enough information for
independant verification. I'm still waiting for one of your thousand
followers to stand up in your defense.

John... Visio MVP

"PC Datasheet" <fa***@email.com> wrote in message
news:vH******************@newsread1.news.atl.earth link.net...
You said --
<<With a unique name like Arno, it is simple to do a search, just on this
newsgroup, and find several useful posts.>>

Now put your money where your mouth is and see if you can prove what you
said.
"John Marshall, MVP" <la******@stonehenge.ca> wrote in message
news:48******************************@magma.ca...
Are you too incompetent to use Google?

John... Visio MVP

"PC Datasheet" <fa***@email.com> wrote in message
news:eH*****************@newsread1.news.atl.earthl ink.net...
OK, where are the search results for contributions to this newsgroup by
your
A$$ pal Arno R in the last three months?

"John Marshall, MVP" <la******@stonehenge.ca> wrote in message
news:xc******************************@magma.ca...
"PC Datasheet" <fa***@email.com> wrote in message
news:aU******************@newsread1.news.atl.earth link.net...
> Your opinion is as worthless as the filthy garbage you pollute this
> newsgroup with.
>
> All you are doing is demonstrating to all newsgroup readers what an
> obsessed A$$ you are. Your filthy garbage makes no contribution to
> this newsgroup.
Great example of polite behaviour.

John... Visio MVP



Apr 26 '06 #58

P: n/a
Kiss my A$$!!!

It wasn't until the scum of this newsgroup, Arno R, John Marshall and Larry
Linson interjected their venom that this thread degraded.

"Bookreader" <Bo***********@yahoo.com> wrote in message
news:n8********************************@4ax.com...
On Mon, 24 Apr 2006 02:17:28 GMT, "PC Datasheet" <fa***@email.com>
wrote:

I have not been a party to all these arguments but please stop
advertising or go away.

This thread started out as a reasonable question with a reasonable
answer. When I looked at it though, I saw your first response and
then a big tree of back and forth that has zero to do with the topic.

Please cut it out or get lost.

Thank you.

Bookreader

Apr 26 '06 #59

P: n/a
the sequence of these two posts is MOST interesting. You start by
biting the very hand that feeds you, and then you turn around and ask
for more food...

Is there something I'm missing here? For a minute you sounded like a
new Steve. Well, for a minute anyway. Shame. It was nice while it
lasted.

Apr 26 '06 #60

P: n/a
You are nothing but a sleazy, despicable coward!! You know full well that
your scummy pal, Arno R, is not a contributor to this newsgroup!! You have
shown all the newsgroup readers that what you say is just not true. All you
would have to do is post the hyperlink to the contributions and they could
not be disputed. What can not be disputed is that your word is worthless you
A$$!!!

"John Marshall, MVP" <la******@stonehenge.ca> wrote in message
news:rZ********************@magma.ca...
If I post the results here, it would just be wasting bandwidth and you
would just claim that it was all fake.

So are you admitting to the fact that you do not have the technical skills
to do the research or (as most of the regulars know) you know your
statements are false?

I do not have to prove anything, I have provided enough information for
independant verification. I'm still waiting for one of your thousand
followers to stand up in your defense.

John... Visio MVP

"PC Datasheet" <fa***@email.com> wrote in message
news:vH******************@newsread1.news.atl.earth link.net...
You said --
<<With a unique name like Arno, it is simple to do a search, just on this
newsgroup, and find several useful posts.>>

Now put your money where your mouth is and see if you can prove what you
said.
"John Marshall, MVP" <la******@stonehenge.ca> wrote in message
news:48******************************@magma.ca...
Are you too incompetent to use Google?

John... Visio MVP

"PC Datasheet" <fa***@email.com> wrote in message
news:eH*****************@newsread1.news.atl.earthl ink.net...
OK, where are the search results for contributions to this newsgroup by
your
A$$ pal Arno R in the last three months?

"John Marshall, MVP" <la******@stonehenge.ca> wrote in message
news:xc******************************@magma.ca...
> "PC Datasheet" <fa***@email.com> wrote in message
> news:aU******************@newsread1.news.atl.earth link.net...
>> Your opinion is as worthless as the filthy garbage you pollute this
>> newsgroup with.
>>
>> All you are doing is demonstrating to all newsgroup readers what an
>> obsessed A$$ you are. Your filthy garbage makes no contribution to
>> this newsgroup.
>
>
> Great example of polite behaviour.
>
> John... Visio MVP
>
>



Apr 26 '06 #61

P: n/a
And by the way, you are an embarrassment to what MVP stands for!!!

"John Marshall, MVP" <la******@stonehenge.ca> wrote in message
news:rZ********************@magma.ca...
If I post the results here, it would just be wasting bandwidth and you
would just claim that it was all fake.

So are you admitting to the fact that you do not have the technical skills
to do the research or (as most of the regulars know) you know your
statements are false?

I do not have to prove anything, I have provided enough information for
independant verification. I'm still waiting for one of your thousand
followers to stand up in your defense.

John... Visio MVP

"PC Datasheet" <fa***@email.com> wrote in message
news:vH******************@newsread1.news.atl.earth link.net...
You said --
<<With a unique name like Arno, it is simple to do a search, just on this
newsgroup, and find several useful posts.>>

Now put your money where your mouth is and see if you can prove what you
said.
"John Marshall, MVP" <la******@stonehenge.ca> wrote in message
news:48******************************@magma.ca...
Are you too incompetent to use Google?

John... Visio MVP

"PC Datasheet" <fa***@email.com> wrote in message
news:eH*****************@newsread1.news.atl.earthl ink.net...
OK, where are the search results for contributions to this newsgroup by
your
A$$ pal Arno R in the last three months?

"John Marshall, MVP" <la******@stonehenge.ca> wrote in message
news:xc******************************@magma.ca...
> "PC Datasheet" <fa***@email.com> wrote in message
> news:aU******************@newsread1.news.atl.earth link.net...
>> Your opinion is as worthless as the filthy garbage you pollute this
>> newsgroup with.
>>
>> All you are doing is demonstrating to all newsgroup readers what an
>> obsessed A$$ you are. Your filthy garbage makes no contribution to
>> this newsgroup.
>
>
> Great example of polite behaviour.
>
> John... Visio MVP
>
>



Apr 26 '06 #62

P: n/a
For your benefit
http://groups.google.com/groups?as_q...=2006&safe=off

Now that I have posted the link, you can start disputing it.

For the lurkers, This is enough of my replying to stevies stupidity (in this
thread).

John... Visio MVP

"PC Datasheet" <fa***@email.com> wrote in message
news:Ja*****************@newsread3.news.atl.earthl ink.net...
You are nothing but a sleazy, despicable coward!! You know full well that
your scummy pal, Arno R, is not a contributor to this newsgroup!! You have
shown all the newsgroup readers that what you say is just not true. All
you would have to do is post the hyperlink to the contributions and they
could not be disputed. What can not be disputed is that your word is
worthless you A$$!!!

"John Marshall, MVP" <la******@stonehenge.ca> wrote in message
news:rZ********************@magma.ca...
If I post the results here, it would just be wasting bandwidth and you
would just claim that it was all fake.

So are you admitting to the fact that you do not have the technical
skills to do the research or (as most of the regulars know) you know your
statements are false?

I do not have to prove anything, I have provided enough information for
independant verification. I'm still waiting for one of your thousand
followers to stand up in your defense.

John... Visio MVP

"PC Datasheet" <fa***@email.com> wrote in message
news:vH******************@newsread1.news.atl.earth link.net...
You said --
<<With a unique name like Arno, it is simple to do a search, just on
this newsgroup, and find several useful posts.>>

Now put your money where your mouth is and see if you can prove what you
said.
"John Marshall, MVP" <la******@stonehenge.ca> wrote in message
news:48******************************@magma.ca...
Are you too incompetent to use Google?

John... Visio MVP

"PC Datasheet" <fa***@email.com> wrote in message
news:eH*****************@newsread1.news.atl.earthl ink.net...
> OK, where are the search results for contributions to this newsgroup
> by your
> A$$ pal Arno R in the last three months?
>
>
>
> "John Marshall, MVP" <la******@stonehenge.ca> wrote in message
> news:xc******************************@magma.ca...
>> "PC Datasheet" <fa***@email.com> wrote in message
>> news:aU******************@newsread1.news.atl.earth link.net...
>>> Your opinion is as worthless as the filthy garbage you pollute this
>>> newsgroup with.
>>>
>>> All you are doing is demonstrating to all newsgroup readers what an
>>> obsessed A$$ you are. Your filthy garbage makes no contribution to
>>> this newsgroup.
>>
>>
>> Great example of polite behaviour.
>>
>> John... Visio MVP
>>
>>
>
>



Apr 27 '06 #63

P: n/a
You posted links to questions he posted to this newsgroup and links to the
garbage he continually pollutes this newsgroup with you stupid A$$!!!
"John Marshall, MVP" <la******@stonehenge.ca> wrote in message
news:Ne******************************@magma.ca...
For your benefit
http://groups.google.com/groups?as_q...=2006&safe=off

Now that I have posted the link, you can start disputing it.

For the lurkers, This is enough of my replying to stevies stupidity (in
this thread).

John... Visio MVP

"PC Datasheet" <fa***@email.com> wrote in message
news:Ja*****************@newsread3.news.atl.earthl ink.net...
You are nothing but a sleazy, despicable coward!! You know full well that
your scummy pal, Arno R, is not a contributor to this newsgroup!! You
have shown all the newsgroup readers that what you say is just not true.
All you would have to do is post the hyperlink to the contributions and
they could not be disputed. What can not be disputed is that your word is
worthless you A$$!!!

"John Marshall, MVP" <la******@stonehenge.ca> wrote in message
news:rZ********************@magma.ca...
If I post the results here, it would just be wasting bandwidth and you
would just claim that it was all fake.

So are you admitting to the fact that you do not have the technical
skills to do the research or (as most of the regulars know) you know
your statements are false?

I do not have to prove anything, I have provided enough information for
independant verification. I'm still waiting for one of your thousand
followers to stand up in your defense.

John... Visio MVP

"PC Datasheet" <fa***@email.com> wrote in message
news:vH******************@newsread1.news.atl.earth link.net...
You said --
<<With a unique name like Arno, it is simple to do a search, just on
this newsgroup, and find several useful posts.>>

Now put your money where your mouth is and see if you can prove what
you said.
"John Marshall, MVP" <la******@stonehenge.ca> wrote in message
news:48******************************@magma.ca...
> Are you too incompetent to use Google?
>
> John... Visio MVP
>
> "PC Datasheet" <fa***@email.com> wrote in message
> news:eH*****************@newsread1.news.atl.earthl ink.net...
>> OK, where are the search results for contributions to this newsgroup
>> by your
>> A$$ pal Arno R in the last three months?
>>
>>
>>
>> "John Marshall, MVP" <la******@stonehenge.ca> wrote in message
>> news:xc******************************@magma.ca...
>>> "PC Datasheet" <fa***@email.com> wrote in message
>>> news:aU******************@newsread1.news.atl.earth link.net...
>>>> Your opinion is as worthless as the filthy garbage you pollute this
>>>> newsgroup with.
>>>>
>>>> All you are doing is demonstrating to all newsgroup readers what an
>>>> obsessed A$$ you are. Your filthy garbage makes no contribution to
>>>> this newsgroup.
>>>
>>>
>>> Great example of polite behaviour.
>>>
>>> John... Visio MVP
>>>
>>>
>>
>>
>
>



Apr 27 '06 #64

P: n/a
PC Datasheet wrote:
You posted links to questions he posted to this newsgroup and links to the
garbage he continually pollutes this newsgroup with you stupid A$$!!!


Steve, out of curiousity, I went through my news reader's active posts
yesterday and marked Arno's post that were not questions from him and
not pointers to your violation of standard usenet ettiquette. My
university news server retains posts in the group for a little less than
a month, given the volume on cdma. Excluding a good response to a
question from me, I counted at least 8 specific and solid responses to
others' questions.

It's understandable that you might not notice these, given you're a
target here, but unfortunately, what he's doing is quite legitimate.
You can modify your sig to be less controversial but still keep your
business address and logo, something like "<url> Help with access, Word,
Excel".
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me
Apr 27 '06 #65

P: n/a
PC Datasheet wrote:
You posted links to questions he posted to this newsgroup and links to the
garbage he continually pollutes this newsgroup with you stupid A$$!!!


You're wrong about John's link.

Now give it up, and please stop acting like the angry teenage kid who's
been caught masturbating (analogy hint - stop doing it in public and
find a more appropriate place - see my other response).
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me
Apr 27 '06 #66

P: n/a

"PC Datasheet" <fa***@email.com> schreef in bericht news:B7*****************@newsread1.news.atl.earthl ink.net...
You posted links to questions he posted to this newsgroup and links to the
garbage he continually pollutes this newsgroup with you stupid A$$!!!


Still don't know what *exactly* you want to prove here Steve, but I got curious about some figures myself.
Since I can see (and check and count) in my Outbox what I posted I have *exact* figures here...
(Google will only show the number of threads, unless you repeat the search including the omitted results...)

So: Last three months I posted 189 messages to the groups.
********************************************
But we will concentrate only on *this group cdma* here, agreed?
This leaves 72 messages to this group in the last three months.
I made a distinction between answering and asking- (as Arno R), and hunting you (as StopAdvertising)
I skipped some messages (8) that were only a response to you or others about your bad behaviour.

==> As Arno R I posted 39 messages (answers) in 24 threads.
==> As Arno R I posted 25 messages (questions) in 7 threads.

==> As StopTheAdvertising I posted 27 messages as response to your jobhunting in 21 threads.
(As you can see *I really try* to limit myself to only ONE answer per thread this way.
Does not work all the time: I particular thread with 4 contributions to you, a few threads with two)

Recap:
So I have been answering some questions here.
-- Also I offered one poster to look at his mdb if he would mail it.
-- I mailed some animated gifs to a poster here who asked for that.
Also I asked a few questions myself here (without hiding my name!)
==>> This is all *pretty normal* behaviour in this newsgroup isn't it Steve??

So again: what *exactly* do you want to prove here Steve ??

But:
I will 'confess' that I have been very busy with a new project lately and
I will 'confess' that I have been very busy hunting you, and maintaining the website.

There has also been an issue with a person complaining to my provider about that website...
So I did not have the time to answer more questions.

If you would only skip the stupid sigline with the idiotic claim " xxxx users ..."
In that case I would answer more questions here, promised!!

Btw: Does this post count as helpful??

Regards
Arno R


Apr 27 '06 #67

P: n/a
<pi********@hotmail.com> wrote
the sequence of these two posts is MOST interesting. You start by
biting the very hand that feeds you, and then you turn around and ask
for more food...

Is there something I'm missing here? For a minute you sounded like a
new Steve. Well, for a minute anyway. Shame. It was nice while it
lasted.


Tom Ellison has proven here my previous impression of him - that he has the
patience and forbearance of a saint, as well as a truly impressive knowledge
of databases, especially SQL.
May 6 '06 #68

67 Replies

This discussion thread is closed

Replies have been disabled for this discussion.