Help With Some SQL

I have tried getting this right from within an Access Grid but without
success. My table looks like this:

12/24/2004 10:50:00 AM | CC | Level 2 | Heigle, Terry | 230
12/24/2004 3:00:00 PM | CC| Level 3 | Jost, Karen | 310
12/24/2004 7:00:00 PM | CC | Level 3 | Jost, Karen | 240
12/24/2004 10:00:00 PM | CC | Level 4 | Jost, Karen | 180
12/24/2004 11:00:00 PM | CC | Level 2 | Smith, Bob | 60

My ultimate output would be a crosstab sort of statment that would have the
four uniqe levels as columns and the dates as rows with a summation of the
amount of time at each level (level is the last value furthest from the
left)

My rudimentary SQL as come up with this...

TRANSFORM Sum(tblHistory.previousminutes) AS SumOfpreviousminutes
SELECT tblHistory.dtmTime
FROM tblHistory
GROUP BY tblHistory.dtmTime
PIVOT tblHistory.status;

The only thing wrong with this is it lists all entries for 12/24/2005
instead of one row for 12/24...

Can anyonoe suggest a corrrection

Regards

John KostenbaderJohn Kostenbader (john@.kostenbader.com) writes:
> I have tried getting this right from within an Access Grid but without
> success. My table looks like this:
> 12/24/2004 10:50:00 AM | CC | Level 2 | Heigle, Terry | 230
> 12/24/2004 3:00:00 PM | CC| Level 3 | Jost, Karen | 310
> 12/24/2004 7:00:00 PM | CC | Level 3 | Jost, Karen | 240
> 12/24/2004 10:00:00 PM | CC | Level 4 | Jost, Karen | 180
> 12/24/2004 11:00:00 PM | CC | Level 2 | Smith, Bob | 60
>
> My ultimate output would be a crosstab sort of statment that would have
> the four uniqe levels as columns and the dates as rows with a summation
> of the amount of time at each level (level is the last value furthest
> from the left)
> My rudimentary SQL as come up with this...
> TRANSFORM Sum(tblHistory.previousminutes) AS SumOfpreviousminutes
> SELECT tblHistory.dtmTime
> FROM tblHistory
> GROUP BY tblHistory.dtmTime
> PIVOT tblHistory.status;
> The only thing wrong with this is it lists all entries for 12/24/2005
> instead of one row for 12/24...

Since the you think that the result is almost right, I assume that
you are looking for answer in Access. In this case, you should post
to an Access newsgroup, as the syntax you are using is peculiar to
Access.

If you want to run your question in SQL Server, this is the right
place, but alas I have problem to understnad what is what. The
recommendation for this sort of questions is to include:

o CREATE TABLE statement for your table.
o INSERT statement with sample data.
o The desired result given the sample.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are. Sample data is also a good idea, along with clear
specifications.

You show time as Chronons and not durations, so your schema is probably
wrong . You show times in non -ISO-8601 fomats in violation of
Standard SQL. Also, read ISO-11179 so you will stop using those silly,
redundant "tbl-" prefixes. It makes you look like an OO programmer!

Try again and we can help when you give us enough to work with.|||I've posted in plenty of groups in the past without this rudeness...I'm very
sorry to the gentleman who thought I posted in the wrong group and I
apologize to the gentleman who believes me an armature and still uses "tbl"
(it happens to be a table scheme I'm comfortable with and use regularly

I've got news for you...I am an armature looking for some assistance. I
believe that is the original intent of such newsgroups.

Thank you for the intention of your posts

"John Kostenbader" <john@.kostenbader.com> wrote in message
news:89CdnS3X8axzVOLfRVn-2g@.rcn.net...
>I have tried getting this right from within an Access Grid but without
>success. My table looks like this:
> 12/24/2004 10:50:00 AM | CC | Level 2 | Heigle, Terry | 230
> 12/24/2004 3:00:00 PM | CC| Level 3 | Jost, Karen | 310
> 12/24/2004 7:00:00 PM | CC | Level 3 | Jost, Karen | 240
> 12/24/2004 10:00:00 PM | CC | Level 4 | Jost, Karen | 180
> 12/24/2004 11:00:00 PM | CC | Level 2 | Smith, Bob | 60
>
> My ultimate output would be a crosstab sort of statment that would have
> the four uniqe levels as columns and the dates as rows with a summation of
> the amount of time at each level (level is the last value furthest from
> the left)
> My rudimentary SQL as come up with this...
> TRANSFORM Sum(tblHistory.previousminutes) AS SumOfpreviousminutes
> SELECT tblHistory.dtmTime
> FROM tblHistory
> GROUP BY tblHistory.dtmTime
> PIVOT tblHistory.status;
> The only thing wrong with this is it lists all entries for 12/24/2005
> instead of one row for 12/24...
> Can anyonoe suggest a corrrection
> Regards
> John Kostenbader|||"John Kostenbader" <john@.kostenbader.com> wrote in message
news:UqadnVFCWKiDih3fRVn-ow@.rcn.net...
> I've posted in plenty of groups in the past without this rudeness...I'm
> very sorry to the gentleman who thought I posted in the wrong group and I
> apologize to the gentleman who believes me an armature and still uses
> "tbl" (it happens to be a table scheme I'm comfortable with and use
> regularly
> I've got news for you...I am an armature looking for some assistance. I
> believe that is the original intent of such newsgroups.
> Thank you for the intention of your posts

Don't worry abou the ISO-Nazis. Although they would like to prosecute
offenders, ISO standards are not legally binding and you may name your
tables as you please. If they had their way, they would be laying down
standards for the naming of children and have secret police to re-educate
those who used non-standard names.|||GROUP BY tblHistory.dtmTime

That'd be a datetime.
And you're sorta totalling by second.
You need to do something like format(tblHistory.dtmTime, "yyyymmdd")
to get all the stuff for one day totalled together.

John Kostenbader wrote:
> I have tried getting this right from within an Access Grid but
without
> success. My table looks like this:
> 12/24/2004 10:50:00 AM | CC | Level 2 | Heigle, Terry | 230
> 12/24/2004 3:00:00 PM | CC| Level 3 | Jost, Karen | 310
> 12/24/2004 7:00:00 PM | CC | Level 3 | Jost, Karen | 240
> 12/24/2004 10:00:00 PM | CC | Level 4 | Jost, Karen | 180
> 12/24/2004 11:00:00 PM | CC | Level 2 | Smith, Bob | 60
>
> My ultimate output would be a crosstab sort of statment that would
have the
> four uniqe levels as columns and the dates as rows with a summation
of the
> amount of time at each level (level is the last value furthest from
the
> left)
> My rudimentary SQL as come up with this...
> TRANSFORM Sum(tblHistory.previousminutes) AS SumOfpreviousminutes
> SELECT tblHistory.dtmTime
> FROM tblHistory
> GROUP BY tblHistory.dtmTime
> PIVOT tblHistory.status;
> The only thing wrong with this is it lists all entries for 12/24/2005

> instead of one row for 12/24...
> Can anyonoe suggest a corrrection
> Regards
> John Kostenbader|||John Kostenbader wrote:
> I've posted in plenty of groups in the past without this
rudeness...I'm very
> sorry to the gentleman who thought I posted in the wrong group and I
> apologize to the gentleman who believes me an armature and still uses
"tbl"
> (it happens to be a table scheme I'm comfortable with and use
regularly
> I've got news for you...I am an armature looking for some assistance.
I
> believe that is the original intent of such newsgroups.

He was assisting you by pointing you to some documentation, and
suggesting places where you should rethink your schema. With regard to
the tbl prefix - I think that Hungarian notation has fallen out of
favour in recent years, and the reasoning seems pretty good to me.

> My table looks like this:
> > 12/24/2004 10:50:00 AM | CC | Level 2 | Heigle, Terry | 230
> > 12/24/2004 3:00:00 PM | CC| Level 3 | Jost, Karen | 310

You would do well to learn about normalization, try googling it. With
an improved design, you will avoid many problems in the future.

TKA|||John Kostenbader (john@.kostenbader.com) writes:
> I've posted in plenty of groups in the past without this rudeness...I'm
> very sorry to the gentleman who thought I posted in the wrong group and
> I apologize to the gentleman who believes me an armature and still uses
> "tbl" (it happens to be a table scheme I'm comfortable with and use
> regularly
> I've got news for you...I am an armature looking for some assistance. I
> believe that is the original intent of such newsgroups.

And my pointer to an Access newsgroup was an attempt to assist you. It
does happen that people post to this newsgroup when they should have had
posted to an Access newsgroup. This is a newsgroup for SQL Server, where
many don't know Access, and while both SQL Server and Access uses something
they both call SQL, there are considerable differences. For instance,
the Transform function is not in SQL Server.

My suggestion that should include CREATE TABLE etc, was also an attempt
to assist you. You see, if you don't tell us what you want, you can't
get it. It may seem to rude to point out that I don't like guessing what
you want. But the story is that I spend some time per day answering posts
in this newsgroups (and in some other places). If I can spend some time N
on a well-stated problem, where I can even can test a solution, or spend
the same time to try to understand what you want to achieve, guess what
is my pick.

Sure, I could have left your post unanswered, but assuming that you want
assistance, I posted my note so that you can help us to help you.

Remember, that in these newsgroups, you never get less than what you pay
for.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp