Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL to MS Access conversion #2

Posted on 2007-04-10
3
Medium Priority
?
202 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
[X]
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
3 Comments
 
LVL 93

Accepted Solution

by:
Patrick Matthews earned 1600 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 400 total points
ID: 18885178
and Month(mm, ah.ended) = Month(Now())

should be

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

Featured Post

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

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

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
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…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

721 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