TimeZone parsing

[SteveKap]

Hello All!!

I'm extremely new to Access and trying to get something from the database
here at work. Hopefully, I can explain this so you understand. Also, I've
noticed the responses are in code .. which I assume is SQL code. Where does
one put that in a query?

Ok .. here goes:

Our database contains info that people give to us through a website. These
are live databases that update as people continue to enter information into
the site. I work in a call center an am asked to verify specific records by
telephone. Since our call center is open until 9pm west coast time, I need
to be able to exclude certain records due to time. Therefore, I wanted to be
able to see, through a query, which records are in which timezone.

In the main database, there is a field that lists the state the person called
from. I've created 4 simple queries that break out the records by timezone
by restricting the states it shows. Now, I want to run one query that gives
me a total of each of the 4. One would think it was as easy as a crosstab
query, but I'm not doing something right.

Any help you can offer would be appreciated!!

 

[John W. Vinson]

Hello All!!

I'm extremely new to Access and trying to get something from the database
here at work. Hopefully, I can explain this so you understand. Also, I've
noticed the responses are in code .. which I assume is SQL code. Where does
one put that in a query?

There are two "code" languages used in Access: SQL, used for Queries;
you can open a query in design view and select View... SQL from the
menu, or the SQL tool from the leftmost dropdown tool in the query
design toolbar. There is also VBA code - quite distinct - used in
Modules.

Ok .. here goes:

Our database contains info that people give to us through a website. These
are live databases that update as people continue to enter information into
the site. I work in a call center an am asked to verify specific records by
telephone. Since our call center is open until 9pm west coast time, I need
to be able to exclude certain records due to time. Therefore, I wanted to be
able to see, through a query, which records are in which timezone.

In the main database, there is a field that lists the state the person called
from. I've created 4 simple queries that break out the records by timezone
by restricting the states it shows. Now, I want to run one query that gives
me a total of each of the 4. One would think it was as easy as a crosstab
query, but I'm not doing something right.

If timezone lines followed state lines, your query might have a chance
of working.... BUT THEY DON'T. I'm in Parma, Idaho, in Mountain time;
I can see the little towns of Nyssa and Adrian, Oregon from my office
- and though they are in Oregon, they're still on Mountain time.

You'll need some other information in your table to get an accurate
timezone value!

As it is, if you don't mind large portions of the country being an
hour off, you could use an auxiliary table listing the nominal "time
zone" for each state (e.g. CA = +8, OR = +8, ID = +7), and join this
table to your query by State; Group By the "time zone" field in your
totals query.

John W. Vinson [MVP]

 

[SteveKap]

That sounds extremely viable! Thanks John!!

And, I do realize that some states exist in two timezones. I'll have to make
allowances for that, or parse out by area code (if THAT doesn't also cross
timelines).


Thanks so much for your help! I'll give it a shot now.

Hello All!!

I'm extremely new to Access and trying to get something from the database
here at work. Hopefully, I can explain this so you understand. Also, I've
noticed the responses are in code .. which I assume is SQL code. Where does
one put that in a query?

There are two "code" languages used in Access: SQL, used for Queries;
you can open a query in design view and select View... SQL from the
menu, or the SQL tool from the leftmost dropdown tool in the query
design toolbar. There is also VBA code - quite distinct - used in
Modules.

Ok .. here goes:

[quoted text clipped - 10 lines]

me a total of each of the 4. One would think it was as easy as a crosstab
query, but I'm not doing something right.

If timezone lines followed state lines, your query might have a chance
of working.... BUT THEY DON'T. I'm in Parma, Idaho, in Mountain time;
I can see the little towns of Nyssa and Adrian, Oregon from my office
- and though they are in Oregon, they're still on Mountain time.

You'll need some other information in your table to get an accurate
timezone value!

As it is, if you don't mind large portions of the country being an
hour off, you could use an auxiliary table listing the nominal "time
zone" for each state (e.g. CA = +8, OR = +8, ID = +7), and join this
table to your query by State; Group By the "time zone" field in your
totals query.

John W. Vinson [MVP]

 

[John W. Vinson]

And, I do realize that some states exist in two timezones. I'll have to make
allowances for that, or parse out by area code (if THAT doesn't also cross
timelines).

Areacodes DO cross timezones. Nyssa and Adrian are in area code 541 in
the Mountain time zone - along with most of Oregon which is Pacific.
I'm afraid zipcodes are the next layer down in granularity, and even
those might not suffice!

John W. Vinson [MVP]

 

[SteveKap]

I just noticed that. It occurs in Oregon, North and South Dakota, Nebraska,
etc. I'll just play it safe and call a state like Tennessee an Eastern Time
Zone state.

 

[Guest]

Also remember that Arizona does not observe Daylight Saving Time. At least
Indiana joined the rest of the crowd recently. Before last year it was by
county!

 

[Guest]

And, to get really bizarre, Oregon and Florida have have portions of their
state in adjacent time zones.
The is a portion of Eastern Oregon in Mountian time and the panhandle of
Florida is in Central Time.
Go figure!