Solved

SQL to MS Access conversion #2

Posted on 2007-04-10
3
198 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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Viewers will learn how the fundamental information of how to create a table.

828 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