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
Solved

Timeserial on sql statement question

Posted on 2009-07-14
3
323 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 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

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

Title # Comments Views Activity
VBA modules import 4 57
database opened as read only 10 32
Menu Macro ‘Action Failed’, Access 2003 7 17
90 days before current date 12 32
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

839 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