SELECT DISTINCTROW "01C" AS dummy, Buildings.BuildingNumber,
UCASE(Buildings.BuildingName) AS BuildingName,
Buildings.MasterPlanCode, Buildings.UniformBuildingCode,
Buildings.FunctionalCategoryCode, Buildings.OwnershipCode,
Buildings.ConditionCode, Format$([BasicGrossArea],"0000000") AS
dBasicGrossArea, Format$([CoveredUnenclosedGrossArea],"0000000") AS
dCoveredUnenclosedGrossArea,
IIf(Month([DateOccupancy])>9,Month([DateOccupancy]),"0" &
Month([DateOccupancy])) & Year([DateOccupancy]) AS dDateOccupancy,
Buildings.YearConstructed, Format$([NumberLevels],"00") AS
dNumberLevels, Format$([UnrelatedGrossArea],"0000000") AS
dUnrelatedGrossArea, Buildings.YearLatestImprovement,
UCASE(Buildings.Address) AS Address, Buildings.CityCode,
CityCodes.CountyCode, Format$([Circulation],"0000000") AS dCirculation,
Format$([PublicToiletArea],"0000000") AS dPublicToiletArea,
Format$([Mechanical],"0000000") AS dMechanical,
Format$([Custodial],"0000000") AS dCustodial
FROM CityCodes INNER JOIN Buildings ON CityCodes.CityCode =
Buildings.CityCode
ORDER BY "01C", Buildings.BuildingNumber, Buildings.BuildingName;
Please if anyone can help me in Converting the above given Access Query
to Sql Server. I don't know which function to use for format$, IIF. I
would really appreciate your suggestions.
I have the following query created in MS Access that I would like to convert to MS SQL Server, but I can't get the IF logic to work properly, any help you can provide would be greatly appreciated.
Dear All, i am making small web application using asp.net, C# ,sql2000. i want a about regarding how to access view or table from other server to local server. i have base database where there is a view which need to access in my database of local server.that is how to make select query to access view in other server Please help thanks
I would like to be able to view the complete SQL command that is sent from a Microsoft Access query which has linked SQL Server tables through ODBC, and the query also has some VBA functions manipulating and filtering data.I tried using the SQL Profiler to view the sql command send to SQL Server but all I see is the simple query format and some of the fields. I don't see the fields that have a VBA function such as trim(fieldname) etc in the column. And If I use a VBA fucntion in the Criteria then nothing shows up in SQL Profiled under the TextData column.How can I see the complete SQL command sent to SQL Server?
I am being asked to convert this access query into sql server 2000. Access query SELECT Left(Trim([Notes_Primary_Key]),InStr(Trim([Notes_Primary_Key])," ")) AS PcnPID, Trim([Notes_Secondary_Key]) AS PcnTicketNum FROM tri_offnotes;
I'm new to SQL and am not familiar with which function replaces the InStr access function.
First off, I apologise if this is classed as off topic as it's concerning access but I couldn't see another forum that was better suited for this question.
I'm trying to run this query in Access. I designed it in SQL Management Studio with a test database that I set up and it runs fine. However, when I tried to run it in access I get a syntax error on the subquery bit. Can anyone tell me what I'm doing wrong here?
UPDATE tblInvoice SET tblInvoice.PeriodID = (SELECT p.PeriodID FROM tblPeriod p INNER JOIN tblInvoice i ON i.TransDate BETWEEN p.PeriodStartDate AND p.PeriodEndDate WHERE i.InvoiceID = tblInvoice.InvoiceID)
SELECT tbl1.evenID, Sum(IIf(IsNull([evpeDateCompleted]),0,1)) AS Completions, Count(tbl2.evpeID) AS Attendees, IIf(Count([evpeID])=0,"No Bookings",IIf(Count([evpeID])=Sum(IIf(IsNull([evpeDateCompleted]),0,1)),"Closed",IIf(Sum(IIf(IsNull([evpeDateCompleted]),0,1))>0,"Closing","In Progress"))) AS Status FROM tbl1 LEFT JOIN tbl2 ON tbl1.evenID=tbl2.evpeEvenID GROUP BY tbl1.evenID;
Here is what I have, but I'm not confident it is correct: CASE WHEN (PTicketNum = '0' OR PTicketNum IS NULL) THEN 'M999Z' else PTicketNum END AS Ticket
I'm trying to convert an Access database application to an ASP.NET application with SQL Server 2005 as the database backend. However, I'm having trouble converting some of the queries to SQL stored procedures. Here's an example Access query that I'm trying to convert into working SQL:
SELECT DISTINCTROW EXISTS (SELECT AccountCodeID FROM AccountCodes WHERE CodeID = 22 AND AccountCodes.AccountID = Accounts.AccountID) AS FullTimeInfo, EXISTS (SELECT AccountCodeID FROM AccountCodes WHERE CodeID = 24 AND AccountCodes.AccountID = Accounts.AccountID) AS ShortTermInfo, EXISTS (SELECT AccountCodeID FROM AccountCodes WHERE CodeID = 10 AND AccountCodes.AccountID = Accounts.AccountID) AS GeneralInfo, Accounts.* FROM Accounts INNER JOIN AccountCodes ON Accounts.AccountID = AccountCodes.AccountID WHERE (((Accounts.SummitID)=@SummitID) AND ((AccountCodes.CodeID)=10 Or (AccountCodes.CodeID)=22 Or (AccountCodes.CodeID)=24)) ORDER BY Accounts.LastName, Accounts.FirstName
My understanding is that EXISTS can only be used in the WHERE clause in SQL. Any suggestions on how to properly rewrite this?
Good morning, I'm trying to create a view on SQL Server based on a query that i use on Access. The query returns detailed data from a table based on ID's selected and saved into another table.
the thing is, on access it returns back 5 records<which is a correct output> , while on SQL. its returning only one record. what could be the issue here ?
I was able to create a view and convert it to excel. Now I want to it to schedule it for everyday and then email the excel file as an attachment to couple of people.
Would SSRS be an option? where I can create a report of the view and schedule it?
Does any know the process I need to follow?
Do I have to uses SSIS ? and then set it up as SQl server job?
Sorry if this has been answered before. Could not find any answers. OS Vista Business, SQL Server 2005 Express. I have a CSV file which I imported to Access only 100 records with 8 fields. Then ran Tools >> Database Utilities >> Upsizing Wisard. The result was I exported the table field headers but not the data. Is there any method on how I can get the field headers and data into SQL Server Express?
I need to convert on existing Access database to SQL Server database. If I convert though sql DataTransformation, it taking only data from the Acccess Database not all the constraints like Primary Key, Default Value, Relations .........
what's the easiest way to do this. i have 5 tables within the mdb filei tried the "easiest way" shown in a website but it does not work.the way i did it was on access(2007) > database tools tab > sql server buttonit gave me an error when it ask for an login IDi do not have any password/id..please helpthanks
Hi, on a migration from access 97 to SQL Server 7.0 columns in access like 'hyperlink' are converted to datatype 'text' on the SQL Server 7.0. Now I miss the function in the access application I had before with fields like 'hyperlink'. Now I can't start e.g. a word document with a click and I have to type in the hyperlink manualy. Does anyone had the same problem an possibly have a resolve? Thanks.
I am translating some of my Access queries to SQL views. In one of those, I had a very convenient function called "IIF" (e.g. IIf(IsNull([Remark]),"NULL","NOT NULL").
How is this function called in the MS SQL Server 2000? Apparently I cannot use either "IIF" nor "CASE" in the query/view.....
Our business application is running SQL Server in a remote data center and we had been using MS Access from a local PC to occasionally fix data problems in the data base. As the tables continue to grow, it's painfully obvious MS Access just can't handle the volume of data. One of our tables we need to get into is about 200,000 rows, another is over 100,000 rows.
We need to be able to change individual rows, and perform search and replace commands across one or more columns in select tables.
What software can we install on local desktops to allow viewing and editing of SQL table data?
I downloaded the Microsoft SQL Server Migration Assistant for Access 4 weeks ago. Today, I tried this program for the first time. I added one Database with 3 Access 2003 Tables to the Access Metadata Explorer. I right clicked Databaes on the Access Metadata pane and did not see the "Convert Schema" highlighted!!!??? What is wrong with the program? How can I make it highlighted? Please help and advise.
I have a view that give me the data of all the batched. Now I am using a query on view to get a single batched data. when I am using direct query it was taking 0 sec but when I am using Through view "select * from myView where batched=2" then its taking 30 mnt.
quote:Run the view for orders shipped from January 1, 2003 and June 30, 2003, formatting the shipped date as MON DD YYYY.
creating the view:
CREATE VIEW vw_orders AS SELECT o.order_id, c.customer_id, c.name AS 'customer_name', c.city, c.country, o.shipped_date FROM customers c INNER JOIN orders o ON c.customer_id = o.customer_id
running the view:
SELECT * FROM vw_orders WHERE shipped_date between '01/01/2003' and '06/30/2003' ORDER BY 'customer_name', 'country'
