changing some sequel code into a query

[smiley0q0]

i am not too familiar with sqlx code, and i'm trying to change a few things. i was given a database which has a lot of vba code, and very few queries. just to help me understand what it is doing and how i can tweek it, i'm trying to change some of the code into a query i'm more familiar with. here is the code...

sqlx = "SELECT dbo_tblTime.SSN AS SSNumber, dbo_tblTime.reportDate AS repDate, Sum(datediff('n',dbo_tblTime.timeIn,dbo_tblTime.timeOut)) AS TotalHours," & _
"dbo_tblTime.PayCode AS PayCode, Min(dbo_tblTime.timeIn) AS TimeIn, Min(dbo_tblTime.timeOut) AS TimeOut" & _
&quot; FROM dbo_tblTime WHERE ((dbo_tblTime.SSN)= '&quot; & SSN & &quot;') AND ((dbo_tblTime.reportDate)>= #&quot; & CDate(reqdate) & &quot;# and (dbo_tblTime.reportDate)<#&quot; & CDate(reqdate) + 7 & &quot;#)&quot; & _
&quot; AND ((dbo_tblTime.PayCode)=531) GROUP BY dbo_tblTime.SSN, dbo_tblTime.reportDate, dbo_tblTime.PayCode; &quot;

there is code before and after, but this is the part i need to change. i'm not sure whether or not it's even possible, but if it is i'd sure like some help.

there are 2 times in the dbo_tblTime TimeIn and TimeOut
this one takes the difference of the 2, but there should be 2 of each, a TimeIn from the beginning of their shift, a TimeOut when they go to lunch, a TimeIn when they come back from lunch and a TimeOut when they leave for the day. i need the difference between these 2 times. and be able to pull each of them. unfortunatly i can't email a copy, beacause of security reasons, (social security numbers) sorry.

i don't think i made any sense, but if i did ... thank you.
Smiley

 

[UncleJack]

Copy the query SQL code to the clipboard. Leave out the quote marks. Start a new query and close the &quot;Add Table&quot; window. A button will appear on your toolbar that says &quot;SQL&quot; on it. Click this button and paste the SQL code into the window. Now click the Design button and you will see the design view of the query.

Not all SQL queries can be viewed this way, but most can. It may be easier to modify the query in QBE view rather than SQL if you're not real familiar with SQL code.

After you've modified the query you can save it as a named query and then call it from code (DoCmd.OpenQuery) or go back to the SQL view, copy it back to the clipboard and paste the SQL back into your code.

Uncle Jack

 

[MichaelRed]

Then again, perhaps you really shouldn't be changing the table structure and the code. Normalization rules / thoughts SUGGEST that a single record should relate to a single entity, wihich -in this instance- should really be a 'visit' (single occurance of the in-out pair). If you go on down the path to in/out for X, thenX will rapidly multiply (in additionn to Lunch, you will add Business, morning break, afternoon break, official compaany errand ...). If you stick with the single in/out the whole process will -in the end- be much easier to manage.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over