473,372 Members | 947 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,372 software developers and data experts.

Converting an access query to SQL

347 100+
I have the following query that was written by someone else that I need help converting to a SQL query, can anyone assist me with this?

Thank you

Expand|Select|Wrap|Line Numbers
  1. SELECT [1_1Employee].Last_First_Name, [1_1Employee].Employee_Number, 
  2. format((([Timestamp]-(([LoggedIn]/1000)/60))/1440)+1,"mm/dd/yy") AS [Date], 
  3. format((([Timestamp]-(([LoggedIn]/1000)/60))/1440)+1,"dddd") AS [Day], 
  4. Sum(([LoggedIn]/1000/60)) AS LogIn, Sum(([OnTime]/1000/60)) AS OnTime1,
  5. Format((([Timestamp]-(([LoggedIn]/1000)/60))/1440)+1,"yy/mm/dd") AS Da INTO 1_1ScratchPad
  6.  
  7. FROM 1_1Employee INNER JOIN dbo_mOpLogout ON [1_1Employee].Employee_Login = dbo_mOpLogout.Opname
  8.  
  9. GROUP BY [1_1Employee].Last_First_Name, [1_1Employee].Employee_Number, 
  10. Format((([Timestamp]-(([LoggedIn]/1000)/60))/1440)+1,"mm/dd/yy"),
  11. Format((([Timestamp]-(([LoggedIn]/1000)/60))/1440)+1,"dddd"),
  12. Format((([Timestamp]-(([LoggedIn]/1000)/60))/1440)+1,"yy/mm/dd")
  13.  
  14. HAVING (((Format$((([Timestamp]-(([LoggedIn]/1000)/60))/1440)+1,"yy/mm/dd")) 
  15. Between [Start (YY/MM/DD)] And [End (YY/MM/DD)]));
  16.  
Aug 26 '10 #1
11 2724
NeoPa
32,556 Expert Mod 16PB
Sure.

Where specifically do you need help?

What have you tried already and what results (or errors) have you had?
Aug 26 '10 #2
dougancil
347 100+
Well I'm not really sure on the syntax differences here. The "format" command is the first thing that I've stumbled into.

So here is where I'm starting from
Expand|Select|Wrap|Line Numbers
  1. SELECT [1_1Employee].Last_First_Name, [1_1Employee].Employee_Number, 
  2. format((([Timestamp]-(([LoggedIn]/1000)/60))/1440)+1,"mm/dd/yy") AS [Date], 
  3. format((([Timestamp]-(([LoggedIn]/1000)/60))/1440)+1,"dddd") AS [Day], 
  4. Sum(([LoggedIn]/1000/60)) AS LogIn, Sum(([OnTime]/1000/60)) AS OnTime1,
  5. Format((([Timestamp]-(([LoggedIn]/1000)/60))/1440)+1,"yy/mm/dd") AS Da INTO 1_1ScratchPad
  6.  
  7. FROM 1_1Employee INNER JOIN dbo_mOpLogout ON [1_1Employee].Employee_Login = dbo_mOpLogout.Opname
  8.  
  9. GROUP BY [1_1Employee].Last_First_Name, [1_1Employee].Employee_Number, 
  10. Format((([Timestamp]-(([LoggedIn]/1000)/60))/1440)+1,"mm/dd/yy"),
  11. Format((([Timestamp]-(([LoggedIn]/1000)/60))/1440)+1,"dddd"),
  12. Format((([Timestamp]-(([LoggedIn]/1000)/60))/1440)+1,"yy/mm/dd")
  13.  
  14. HAVING (((Format((([Timestamp]-(([LoggedIn]/1000)/60))/1440)+1,"yy/mm/dd")) 
  15. Between [Start (YY/MM/DD)] And [End (YY/MM/DD)]));
  16.  
and here is the error I have so far:

Server: Msg 195, Level 15, State 10, Line 2
'format' is not a recognized function name.
Server: Msg 170, Level 15, State 1, Line 3
Line 3: Incorrect syntax near 'Timestamp'.
Server: Msg 170, Level 15, State 1, Line 5
Line 5: Incorrect syntax near 'Timestamp'.
Server: Msg 170, Level 15, State 1, Line 10
Line 10: Incorrect syntax near 'Timestamp'.
Server: Msg 170, Level 15, State 1, Line 11
Line 11: Incorrect syntax near 'Timestamp'.
Server: Msg 170, Level 15, State 1, Line 12
Line 12: Incorrect syntax near 'Timestamp'.
Server: Msg 170, Level 15, State 1, Line 14
Line 14: Incorrect syntax near 'Format'.
Server: Msg 170, Level 15, State 1, Line 14
Line 14: Incorrect syntax near 'Timestamp'.

and I know that the incorrect syntax has to be the format command that is in there.
Aug 26 '10 #3
NeoPa
32,556 Expert Mod 16PB
Doug, I'll deal with the format issue, but this is not what I was asking for. If you have a problem with your format command I don't want to have to swim through 15 odd lines of questionable SQL to find it. That may be your eventual aim, but I asked for specific questions deliberately. If you can work with me then I can work with you.

Anyway, T-SQL, the language used in SQL Server, has a couple of functions that do the job of formatting data from one type to another. These are CAST() and CONVERT(). Be warned. They are markedly different from Jet SQL (The SQL used in Access). There are various options available in CONVERT() (I would expect this to be the more appropriate for you on this one), too many to list here. One of the problems is that they are a finite list. They don't have the flexibility you'd be used to with Format() I'm afraid.

Context-sensitive Help works well from Management Studio. Type CONVERT and press F1 to see the relevant page with full details appear before you. 101 for mm/dd/yyyy, 103 for dd/mm/yyyy, 111 for yyyy/mm/dd, etc.
Aug 26 '10 #4
dougancil
347 100+
Neo,

Specifically where I need help is as follows. I didn't write this query and while I vaguely understand what it does, I need to try to simplify it, as well as making it as clean and efficient as possible while still performing it's task. This is the first time that I've had to recode someone elses work and since I'm not the DBA here, and I'm not that well versed in SQL this is taking me a considerable amount of time. At this point, I havent tried anything because before I step into that realm, I want to see what I'm up against. I have two more blocks of queries like this that I'll need to reformat from Access to SQL after this. If this can be converted relatively easily from Access to SQL then I'm all for that but if I have to go back and rebuilt the query from scratch, then that's what I'll need to look at.
Aug 26 '10 #5
NeoPa
32,556 Expert Mod 16PB
Most things in T-SQL work, more or less, as they do in Jet SQL. There are of course exceptions.

I'm not aware of anything around that can do the job (or possibly even most of it) for you. That doesn't mean they're not out there, or even that no-one here would be able to point you to them. It's just never been my requirement to find them or work that way.

Unfortunately for you, you seem to have been lumbered with SQL that, in my estimation at least, has a high level of incompatibility. I can't say how you would approach this, but I would pick it off one concept at a time. I very well may end up recoding the whole thing from scratch, but I wouldn't say that should be necessary. You should be able to fix one bit at a time until all the issues are resolved. It may be a little laborious, but that's your situation as I see it.

I'm happy to help with specifics, such as "How the f*$! do I translate something like Format()??!?", but I'll leave you with the process of fixing all you don't need specific help with. Does that seem fair?
Aug 26 '10 #6
dougancil
347 100+
Neo,

So by looking at least at the query as I have it right now, which do you think would be the best way to approach this, with cast or convert?

Thanks

Doug
Aug 30 '10 #7
ck9663
2,878 Expert 2GB
Use CONVERT on those that requires formatting and use CAST on those that simply needs direct data conversion. You're also going to need this function or this function to get the day portion of your date expression.

Remember, once formatted, your date might no longer be a date datatype but a formatted string/character type.

Good Luck!!!

~~ CK
Aug 30 '10 #8
dougancil
347 100+
CK or Neo,

Ok here's a small query that I'm trying to run:

Expand|Select|Wrap|Line Numbers
  1. SELECT        Opname, LoggedIn, OnTime, OpNumber
  2. FROM            mOpInterval
  3. cast (Timestamp(([LoggedIn]/1000)/60))/1440)+1,"mm/dd/yy")
  4.  
What I'm needing to do is to pull that information from the mOpInterval table (which is in minutes) and convert them into a more "readable" format. Also it's my way of finding which information to pull from the table. Any thoughts or ideas? I know this isn't very well worded so I know that what I'm trying to retrieve may not be that clear.
Sep 2 '10 #9
NeoPa
32,556 Expert Mod 16PB
Doug, do you mean :
Expand|Select|Wrap|Line Numbers
  1. SELECT Opname
  2.      , LoggedIn
  3.      , CONVERT(nvarchar(10),[LoggedIn],101) AS strLoggedIn
  4.      , OnTime
  5.      , OpNumber
  6. FROM   mOpInterval
And doesn't this just take us back to post #4 where this was covered previously?
Sep 6 '10 #10
dougancil
347 100+
Neo,

Actually that worked because I found out that in order to do most of my queries, I had to import data into a table from a .csv file. I'm in the process of working through this at the moment. What my next step is is the step preceeding this. That step will give me the starting date for this query. I'll post more information as I get it.


Thank you,

Doug
Sep 7 '10 #11
NeoPa
32,556 Expert Mod 16PB
That sounds like good news, though the next step being the preceding one sounds a little Alice-in-Wonderland in a funny sort of way.

Let us know when you have more to say :D
Sep 7 '10 #12

Sign in to post your reply or Sign up for a free account.

Similar topics

9
by: wiredog | last post by:
I am struggling rewriting my query from MS Access' IIF, Then to SQL Servers TSQL language. I am hoping some one can give me some guidance. I believe I have the first portion of the query correct...
17
by: chicha | last post by:
Hey people, I have to convert MS Access 2000 database into mysql database, the whole thing being part of this project I'm doing for one of my faculty classes. My professor somehow presumed I...
0
by: Timppa | last post by:
Hi, I'm converting ACCESS 2000 database to SQL Server. I have .adp project. In the .mdb I have form where I'll insert rows into two different tables using docmd.GoToRecod ,,acNewRec. In .adp...
8
by: s_wadhwa | last post by:
SELECT DISTINCTROW "01C" AS dummy, Buildings.BuildingNumber, UCASE(Buildings.BuildingName) AS BuildingName, Buildings.MasterPlanCode, Buildings.UniformBuildingCode,...
1
by: wintonsl | last post by:
Would anyone know how to convert this Access Query to SQL Server Query? Format(((+++)/(IIf(>0,1,0)+IIf(>0,1,0)+IIf(>0,1,0)+IIf(>0,1,0))),"Standard") What this query is doing in Access is...
5
by: silversubey | last post by:
I have a query that works in Access. It pulls time in a format that is understood by Quickbooks. We are upgrading our access-based program to MSDE. I have tried 3rd party converters and searched...
6
by: jsacrey | last post by:
Hey everybody, got a secnario for ya that I need a bit of help with. Access 97 using linked tables from an SQL Server 2000 machine. I've created a simple query using two tables joined by one...
1
by: LSGKelly | last post by:
Hi all...I need to convert an EOMONTH statement into an Access Query. The current formula looks like this: =EOMONTH(B16,(-1)) B16 now = ProposedEffDate Is there a function in Access that...
1
by: wassimdaccache | last post by:
Hello I am trying converting a query in MSACCESS 2007 into *.csv File to upload them on the web server MYSQL database using PHPMYADMIN Actually I am a little bit confused because there are...
10
by: Anthony97 | last post by:
This is a problem I've been fighting through for the last month. I've been tasked with converting access 2007 queries to SQL Server 2005. I have been able to convert a number of queries associated...
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.