Solved

Timeserial on sql statement question

Posted on 2009-07-14
3
322 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
  • 2
3 Comments
 
LVL 142

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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

Suggested Solutions

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

778 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