Solved

Timeserial on sql statement question

Posted on 2009-07-14
3
326 Views
Last Modified: 2013-11-27
Good Day Gurus,
I'm basically stuck on a query involving a timeserial operation on an sql statement:

"SELECT DISTINCT FORMAT(Main_Records.Participation_Date, 'MMMM'), " & _
           "FORMAT(Main_Records.Participation_Date, 'YYYY'),  timeserial(0, count3, 0) ,  (count1 * 60) + count2 AS count3,  " & _
           "(SELECT SUM(M.Hours) FROM Main_Records M WHERE M.User = '" & fOSUserName() & "' AND " & _
           "FORMAT(M.Participation_Date, 'MMMM') = FORMAT(Main_Records.Participation_Date, 'MMMM') AND " & _
           "FORMAT(M.Participation_Date, 'YYYY') = FORMAT(Main_Records.Participation_Date, 'YYYY')) as count1, " & _
           "(SELECT SUM(M.Minutes) FROM Main_Records M WHERE M.User = '" & fOSUserName() & "' AND " & _
           "FORMAT(M.Participation_Date, 'MMMM') = FORMAT(Main_Records.Participation_Date, 'MMMM') AND " & _
           "FORMAT(M.Participation_Date, 'YYYY') = FORMAT(Main_Records.Participation_Date, 'YYYY')) as count2 FROM " & _
           "Main_Records  group by FORMAT(Main_Records.Participation_Date, 'MMMM'), FORMAT(Main_Records.Participation_Date, 'YYYY')  order BY 1 ASC"

Now I already isolated the issue with this statement which is the 'timeserial(0, count3, 0)' field.  Now when doing a straight mathematical operation on the alias 'count3', like count3 + 1,  the statement works.  But if I do something like a timeserial, or a dateadd operation using the alias, the statement fails.  Now, I'm assuming both timeserial and dateadd don't work with aliases? Any Ideas?  Thanks!
0
Comment
Question by:jad0083
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 24856630
>timeserial(0, count3, 0) ,  (count1 * 60) + count2 AS count3
so, you reuse the same column name as alias.

note: when you use count3 anywhere in the query, it remains the value of the table column, and not the result of the expression above.

apart from that: what are the values of count3?
timeserial might not take any value >= 60 ...
0
 

Author Comment

by:jad0083
ID: 24856932
@angelll
Well, in the end, after seeing how messy the actual statement is, I just rewrote the whole damn thing, eliminating the nested selects in the process and from your advise, just minimized the alias usage.  And it worked!  Below is the final statement that I came up with that worked, thanks!


"SELECT FORMAT(Main_Records.Participation_Date, 'MMMM') AS D1a, FORMAT(Main_Records.Participation_Date, 'MM') AS D1b, " & _
           "FORMAT(Main_Records.Participation_Date, 'YYYY') AS D2, IIF(CStr(countb) = '00', counta & ' hour(s)', " & _
           "counta & ' hour(s) ' & countb & ' minute(s)'), LEFT(countx, INStr(county, '.') -1) AS counta, " & _
           "RIGHT(countx, IIF(LEN(countx) - INStr(county, '.') < 0, 0, LEN(countx) - " & _
           "INStr(county, '.'))) AS countb, IIF(INStr(countx, '.') = 0, countx & '.', countx) AS county, " & _
           "CStr(FORMAT(TIMESERIAL(0, count1, 0), 'HH.MM')) AS countx, ((SUM(Main_Records.Hours) * 60) + " & _
           "SUM(Main_Records.Minutes)) AS count1 FROM Main_Records WHERE Main_Records.User = '" & fOSUserName & "' " & _
           "GROUP BY FORMAT(Main_Records.Participation_Date, 'MMMM'), FORMAT(Main_Records.Participation_Date, 'MM'), " & _
           "FORMAT(Main_Records.Participation_Date, 'YYYY') ORDER BY 3 DESC, 2 DESC"

Open in new window

0
 

Author Closing Comment

by:jad0083
ID: 31603590
Thanks!
0

Featured Post

Tutorials alone can't teach real engineering

So we built better training tools.

-Hands-on Labs
-Instructor Mentoring
-Scenario-Based Tests
-Dedicated Cloud Servers

All at your fingertips. What are you waiting for?

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

691 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question