Between Time and then date

[Steve B]

Hello

Access 2000

I have a fixed table that is a data report from an Index Telephone switch,
it logs the date and time of the call in long format, i.e.

01/03/2008 01:45:33

The current table I am working on has 110,000 records spanning the month of
March

What I need to do is extract all calls that have been logged between
08:30:00 to 17:30:00 for each day, but give a total as a month total.

I'm a tad stumped trying to write the query that will only report back the
total volume of calls for the whole month, but those only between the times
above.

I can easily select a date range, but also specifying to only include a
certain time period has me stumped.

I've spent an hour seraching, but can't find any tutorials, so as usual,
usenet is my last port of call.

Thanks - Steve

 

[Douglas J. Steele]

Are you saying that all you've got is a single date/time field, indicating
when the call arrived (as opposed to one for when the call started and one
for when it terminated)?

Use the TimeValue function:

WHERE CallTime BETWEEN DateSerial(2008, 3, 1) AND DateSerial(2008, 4, 1)
AND TimeValue([CallTime]) BETWEEN TimeSerial(8, 30, 0) AND TimeSerial(17,
30, 00)

Note that you do need to check for CallTimes between March 1 and April 1,
not just between March 1 and March 31.

 

[Corinne Cowan]

(e-mail address removed)
m:


And you need to move on. Go away.
Don't go away angry, just go away.

Everyone else,

Please ignore anyone trolling these newsgroups. They apparently know
nothing
about Access, and little about databases in general. It appears that
their
sole purpose is to misinform posters. Unfortunately, these
newsgroups are
not actively monitored, or they would be banned.

 

[aaron.kempf]

If you've got 100,000 records you'd better move to SQL Server.

-Aaron

 

[Bob Quintal]

(e-mail address removed)
m:

If you've got 100,000 records you'd better move to SQL Server.

-Aaron

And you need to move on. Go away.
Don't go away angry, just go away.

Everyone else,

Please ignore anyone trolling these newsgroups. They apparently know
nothing
about Access, and little about databases in general. It appears that
their
sole purpose is to misinform posters. Unfortunately, these
newsgroups are
not actively monitored, or they would be banned.

 

[Bob Quintal]

(e-mail address removed)
:

Bob;

Screw yourself.

I'm not 'trolling'. I am here to give factual information.

If you want to deal with 100,000 records-- move to SQL Server.
Case closed.

Funny how I've got over a half million records in each of two tables
in an Access 97 mdb and have never seen Access have any trouble with
that number. And I'm in that mdb at least 3 or four times a week.

Other people here report databases with greater than 100K records
working properly,

So you have screwed your 'factual information' claim by posting your
comment.

Go away, and screw yourself into a light socket.
Don't go away angry, just go away.

Please advise when you are in the light socket, I want to turn the
switch to on.

-Aaron Kempf

 

[aaron.kempf]

Bob;

Screw yourself.

I'm not 'trolling'. I am here to give factual information.

If you want to deal with 100,000 records-- move to SQL Server.
Case closed.

-Aaron Kempf

 

[aaron.kempf]

I don't believe you. My factual information.. is my factual
information. Your factual information -- sounds to me-- like a bunch
of hogwash.

When was the last time that you had to run a compact and repair? To me
that sounds like 'unacceptable downtime' in my humble opinion.

When was the last time that you used Linked Table Manager? To me that
sounds like 'unacceptable downtime' in my humble opinion.

-Aaron

 

[aaron.kempf]

Why would I need to give examples? It's a fact of life-- Access
doesn't support 100k records and SQL Server handles that much data
_QUITE_ nicely.

Move to SQL Server, newbie!

-aaron

Aaron -

Why don't you provide some meaningful examples, rather than just displaying
hostility?

Bob

I don't believe you.  My factual information.. is my factual
information.  Your factual information -- sounds to me-- like a bunch
of hogwash.

When was the last time that you had to run a compact and repair? To me
that sounds like 'unacceptable downtime' in my humble opinion.

When was the last time that you used Linked Table Manager? To me that
sounds like 'unacceptable downtime' in my humble opinion.

-Aaron

news:e62cfea9-73c9-4527-bef5-

[quoted text clipped - 66 lines]

- Show quoted text -

 

[Graham R Seach]

Access doesn't support 100k records
Sorry Aaron, but that's just not even close to true. I built an Access 95
database many years ago that contained 6 million records (per day) for
statistical analysis. Very slow; took all night to do it; but it did it
nonetheless.

Yes, it would have been better to do it using an enterprise database, but
that was not an option in that case.
Yes, it does.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia


Why would I need to give examples? It's a fact of life-- Access
doesn't support 100k records and SQL Server handles that much data
_QUITE_ nicely.

Move to SQL Server, newbie!

-aaron

Aaron -

Why don't you provide some meaningful examples, rather than just
displaying
hostility?

Bob

I don't believe you. My factual information.. is my factual
information. Your factual information -- sounds to me-- like a bunch
of hogwash.

When was the last time that you had to run a compact and repair? To me
that sounds like 'unacceptable downtime' in my humble opinion.

When was the last time that you used Linked Table Manager? To me that
sounds like 'unacceptable downtime' in my humble opinion.

-Aaron

news:e62cfea9-73c9-4527-bef5-

[quoted text clipped - 66 lines]

- Show quoted text -

--
Message posted via
AccessMonster.comhttp://www.accessmonster.com/Uwe/Forums.aspx/access/200804/1-
Hide quoted text -

- Show quoted text -

 

[aaron.kempf]

It is true. Sorry. I've had too many Access failures over the
years.. to continue using that crappy DB.

Sorry Aaron, but that's just not even close to true. I built an Access 95
database many years ago that contained 6 million records (per day) for
statistical analysis. Very slow; took all night to do it; but it did it
nonetheless.

Yes, it would have been better to do it using an enterprise database, but
that was not an option in that case.

Yes, it does.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia


Why would I need to give examples?  It's a fact of life-- Access
doesn't support 100k records and SQL Server handles that much data
_QUITE_ nicely.

Move to SQL Server, newbie!

-aaron

Aaron -

Why don't you provide some meaningful examples, rather than just
displaying
hostility?

Bob

I don't believe you. My factual information.. is my factual
information. Your factual information -- sounds to me-- like a bunch
of hogwash.
When was the last time that you had to run a compact and repair? To me
that sounds like 'unacceptable downtime' in my humble opinion.
When was the last time that you used Linked Table Manager? To me that
sounds like 'unacceptable downtime' in my humble opinion.
-Aaron
[quoted text clipped - 66 lines]
- Show quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

 

[Graham R Seach]

Aaron,

Your statement was (and I quote):Given I built one, I'd say it does, and so would the government department I
built it for.
Your inability to deliver a stable Access solution is not proof that Access
doesn't support 100k records.

I can't see the original question in my newsreader, so I'm unsure whether
anyone provided a workable solution to the OP's question. Did the OP get a
solution (aside from move to SQL Server)?

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia


It is true. Sorry. I've had too many Access failures over the
years.. to continue using that crappy DB.

Sorry Aaron, but that's just not even close to true. I built an Access 95
database many years ago that contained 6 million records (per day) for
statistical analysis. Very slow; took all night to do it; but it did it
nonetheless.

Yes, it would have been better to do it using an enterprise database, but
that was not an option in that case.

Yes, it does.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia


Why would I need to give examples? It's a fact of life-- Access
doesn't support 100k records and SQL Server handles that much data
_QUITE_ nicely.

Move to SQL Server, newbie!

-aaron

Aaron -

Why don't you provide some meaningful examples, rather than just
displaying
hostility?

Bob

I don't believe you. My factual information.. is my factual
information. Your factual information -- sounds to me-- like a bunch
of hogwash.
When was the last time that you had to run a compact and repair? To me
that sounds like 'unacceptable downtime' in my humble opinion.
When was the last time that you used Linked Table Manager? To me that
sounds like 'unacceptable downtime' in my humble opinion.
-Aaron
[quoted text clipped - 66 lines]
- Show quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

 

[aaron.kempf]

I disagree.

I say that Access doesn't support 100k records.
It isn't reliable enough.
It isn't scalable enough.

Sorry-- I guess that we can just agree to disagree.
And don't you sit there and attack my credibility.

It isn't stable. It isn't stable. It isn't stable.

So I graduated to SQL Server a decade ago. And I'm _SO_ glad that I
did.

When I came to that realization- that Access couldn't reliably work
with 25mb of data-- that is when I moved on.
It has _NOTHING_ to do with design.. Or best practices.

Access just isn't reliable enough.

I'm not going to cave in just because you're stupid enough to use
Access for that many records.

Sorry

-Aaron

Aaron,

Your statement was (and I quote):>>It's a fact of life-- Access doesn't support 100k records

Given I built one, I'd say it does, and so would the government departmentI
built it for.

Your inability to deliver a stable Access solution is not proof that Access
doesn't support 100k records.

I can't see the original question in my newsreader, so I'm unsure whether
anyone provided a workable solution to the OP's question. Did the OP get a
solution (aside from move to SQL Server)?

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia


It is true.  Sorry.  I've had too many Access failures over the
years.. to continue using that crappy DB.

Sorry Aaron, but that's just not even close to true. I built an Access 95
database many years ago that contained 6 million records (per day) for
statistical analysis. Very slow; took all night to do it; but it did it
nonetheless.

Yes, it would have been better to do it using an enterprise database, but
that was not an option in that case.

Yes, it does.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

<[email protected]> wrote in message

Why would I need to give examples? It's a fact of life-- Access
doesn't support 100k records and SQL Server handles that much data
_QUITE_ nicely.

Move to SQL Server, newbie!

-aaron

Aaron -
Why don't you provide some meaningful examples, rather than just
displaying
hostility?
Bob
(e-mail address removed) wrote:
I don't believe you. My factual information.. is my factual
information. Your factual information -- sounds to me-- like a bunch
of hogwash.
When was the last time that you had to run a compact and repair? To me
that sounds like 'unacceptable downtime' in my humble opinion.
When was the last time that you used Linked Table Manager? To me that
sounds like 'unacceptable downtime' in my humble opinion.
-Aaron
[quoted text clipped - 66 lines]
- Show quoted text -
--
Message posted via
AccessMonster.comhttp://www.accessmonster.com/Uwe/Forums.aspx/access/200804/1-
Hide quoted text -
- Show quoted text -- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

 

[Graham R Seach]

Just in case Steve (the OP) wasn't given a solution...

Be advised, I have NOT tested this, but it may suffice to get him started.

SELECT Count(*) As MonthlyCount
FROM MyTable
WHERE (Format(CallDate, "hhnnss")
BETWEEN Format(TimeSerial(8,30,0),"hhnnss")
AND Format(TimeSerial(17,30,0), "hhnnss"))
AND Year(CallDate) = Year(Date())
AND Month(CallDate) = Month(Date())

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

 

[aaron.kempf]

and the 2 gov't depts that I've worked for.. Paid me to upsize from
Access to SQL Server.

So why are _YOU_ right and _I_ am wrong?

Seriously.

It's called 'ego-centrism' right?

-Aaron

Aaron,

Your statement was (and I quote):>>It's a fact of life-- Access doesn't support 100k records

Given I built one, I'd say it does, and so would the government departmentI
built it for.

Your inability to deliver a stable Access solution is not proof that Access
doesn't support 100k records.

I can't see the original question in my newsreader, so I'm unsure whether
anyone provided a workable solution to the OP's question. Did the OP get a
solution (aside from move to SQL Server)?

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia


It is true.  Sorry.  I've had too many Access failures over the
years.. to continue using that crappy DB.

Sorry Aaron, but that's just not even close to true. I built an Access 95
database many years ago that contained 6 million records (per day) for
statistical analysis. Very slow; took all night to do it; but it did it
nonetheless.

Yes, it would have been better to do it using an enterprise database, but
that was not an option in that case.

Yes, it does.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

<[email protected]> wrote in message

Why would I need to give examples? It's a fact of life-- Access
doesn't support 100k records and SQL Server handles that much data
_QUITE_ nicely.

Move to SQL Server, newbie!

-aaron

Aaron -
Why don't you provide some meaningful examples, rather than just
displaying
hostility?
Bob
(e-mail address removed) wrote:
I don't believe you. My factual information.. is my factual
information. Your factual information -- sounds to me-- like a bunch
of hogwash.
When was the last time that you had to run a compact and repair? To me
that sounds like 'unacceptable downtime' in my humble opinion.
When was the last time that you used Linked Table Manager? To me that
sounds like 'unacceptable downtime' in my humble opinion.
-Aaron
[quoted text clipped - 66 lines]
- Show quoted text -
--
Message posted via
AccessMonster.comhttp://www.accessmonster.com/Uwe/Forums.aspx/access/200804/1-
Hide quoted text -
- Show quoted text -- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

 

[Graham R Seach]

So why are _YOU_ right and _I_ am wrong?
Because you stated "Access doesn't support 100k records". You did NOT
qualify that statement in any way.
I didn't attack your credibility Aaron. I responded to a single statement.
Apology accepted.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia




and the 2 gov't depts that I've worked for.. Paid me to upsize from
Access to SQL Server.

So why are _YOU_ right and _I_ am wrong?

Seriously.

It's called 'ego-centrism' right?

-Aaron

Aaron,

Your statement was (and I quote):>>It's a fact of life-- Access doesn't
support 100k records

Given I built one, I'd say it does, and so would the government department
I
built it for.

Your inability to deliver a stable Access solution is not proof that
Access
doesn't support 100k records.

I can't see the original question in my newsreader, so I'm unsure whether
anyone provided a workable solution to the OP's question. Did the OP get a
solution (aside from move to SQL Server)?

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia


It is true. Sorry. I've had too many Access failures over the
years.. to continue using that crappy DB.

Sorry Aaron, but that's just not even close to true. I built an Access
95
database many years ago that contained 6 million records (per day) for
statistical analysis. Very slow; took all night to do it; but it did it
nonetheless.

Yes, it would have been better to do it using an enterprise database,
but
that was not an option in that case.

Yes, it does.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

<[email protected]> wrote in message

Why would I need to give examples? It's a fact of life-- Access
doesn't support 100k records and SQL Server handles that much data
_QUITE_ nicely.

Move to SQL Server, newbie!

-aaron

Aaron -
Why don't you provide some meaningful examples, rather than just
displaying
hostility?
Bob
(e-mail address removed) wrote:
I don't believe you. My factual information.. is my factual
information. Your factual information -- sounds to me-- like a bunch
of hogwash.
When was the last time that you had to run a compact and repair? To
me
that sounds like 'unacceptable downtime' in my humble opinion.
When was the last time that you used Linked Table Manager? To me that
sounds like 'unacceptable downtime' in my humble opinion.
-Aaron
[quoted text clipped - 66 lines]
- Show quoted text -
--
Message posted via
AccessMonster.comhttp://www.accessmonster.com/Uwe/Forums.aspx/access/200804/1-
Hide quoted text -
- Show quoted text -- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

 

[Tony Toews [MVP]]

Why would I need to give examples? It's a fact of life-- Access
doesn't support 100k records

Absolute rubbish.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Douglas J. Steele]

I don't see the point of the calls to the Format function in there.

SELECT Count(*) As MonthlyCount
FROM MyTable
WHERE TimeValue(CallDate)
BETWEEN TimeSerial(8,30,0)
AND TimeSerial(17,30,0)
AND Year(CallDate) = Year(Date())
AND Month(CallDate) = Month(Date())

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Just in case Steve (the OP) wasn't given a solution...

Be advised, I have NOT tested this, but it may suffice to get him started.

SELECT Count(*) As MonthlyCount
FROM MyTable
WHERE (Format(CallDate, "hhnnss")
BETWEEN Format(TimeSerial(8,30,0),"hhnnss")
AND Format(TimeSerial(17,30,0), "hhnnss"))
AND Year(CallDate) = Year(Date())
AND Month(CallDate) = Month(Date())

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

 

[Graham R Seach]

Hi Doug,

In this case, the OP wanted only the times between 0830 and 1730.
I've just never figured out how to reduce the datetime datatype to an
integer, for specific comparison purposes. I'm sure open to an education, if
you're willing.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

I don't see the point of the calls to the Format function in there.

SELECT Count(*) As MonthlyCount
FROM MyTable
WHERE TimeValue(CallDate)
BETWEEN TimeSerial(8,30,0)
AND TimeSerial(17,30,0)
AND Year(CallDate) = Year(Date())
AND Month(CallDate) = Month(Date())

 

[rquintal]

I don't believe you.  My factual information.. is my factual
information.  Your factual information -- sounds to me-- like a bunch
of hogwash.

When was the last time that you had to run a compact and repair? To me
that sounds like 'unacceptable downtime' in my humble opinion.

When was the last time that you used Linked Table Manager? To me that
sounds like 'unacceptable downtime' in my humble opinion.

-Aaron

I only need to use linked table manager when I move the server on
which the files reside, about as often as you,d need to recompile an
app when the SQL Server is moved to a new server.

I've never run compact and repair in Access 97. I occasionally run
Compact, but Repair is a separate function in '97 and I've never
needed to use it. How often does a dbAdmin need to restore from a
backup in SQL server.?

Well, washing is something that's polite in today's society.

Go away, and wash yourself, hog.
Don't go away angry, just go away.