Solved

SQL to MS Access conversion #2

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

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
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…
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.

773 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