Solved

SQL to MS Access conversion #2

Posted on 2007-04-10
3
195 Views
Last Modified: 2008-02-01
Ok, the last posting had a tweaked verson of my SQL code which wasn't right.  Here is the right code from MS SQL.

I have a SQL call that I am trying to convert to MS Access.  What needs to be changed?  The Access database is linking to SQL tables so I didn't think it would make a big deal to keep the same code.  I was wrong.  Help!

select wr.common_name,count(*) as total_tasks, min(started) as start_time,max(ended) as end_time,
sum(datediff(ss,ah.started,ah.ended)) as total_time,
sum(datediff(ss,ah.started,ah.ended))/count(*) as average_time
from (dbo_activity_history ah inner join dbo_work_resource wr on ah.dbo_work_resource_id = wr.id)
where datepart(mm, ah.started) = datepart(mm, getDate())
and datepart(mm, ah.ended) = datepart(mm, getDate())
and datepart(d, ah.started) = datepart(d, getDate())
and datepart(d, ah.ended) = datepart(d, getDate())
group by wr.common_name
order by wr.common_name
 
0
Comment
Question by:farmingtonis
3 Comments
 
LVL 92

Accepted Solution

by:
Patrick Matthews earned 400 total points
ID: 18884526
Hello farmingtonis,

select wr.common_name,count(*) as total_tasks, min(started) as start_time,max(ended) as end_time,
sum(datediff("s",ah.started,ah.ended)) as total_time,
sum(datediff("s",ah.started,ah.ended))/count(*) as average_time
from (dbo_activity_history ah inner join dbo_work_resource wr on ah.dbo_work_resource_id = wr.id)
where Month(ah.started) = Month(Now())
and Month(mm, ah.ended) = Month(Now())
and Day(ah.started) = Day(Now())
and Day(ah.ended) = Day(Now())
group by wr.common_name
order by wr.common_name


Regards,

matthewspatrick
0
 

Author Comment

by:farmingtonis
ID: 18884822
Hmm.  It works kind of.   The (Now()) part isn't working.  I get all date ranges.
0
 
LVL 23

Assisted Solution

by:Christopher Kile
Christopher Kile earned 100 total points
ID: 18885178
and Month(mm, ah.ended) = Month(Now())

should be

and Month(ah.ended) = Month(Now())
0

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

895 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now