Can someome please advise what the equivalent query would be in
Microsoft SQL Server ... I've tried a number of combinations with no
success ... Thanks, Ralph Noble (ralph_noble@hotmail.com)
================
UPDATE INVENTORY
INNER JOIN SALES ON (INVENTORY.BAR_CODE = SALES.BAR_CODE)
AND (INVENTORY.PRODUCT_NBR = SALES.PRODUCT_NBR)
SET INVENTORY.DATE_PURCHASED = "20050127"
WHERE (((SALES.SOLD)="20050127"));
I am converting old MS Access queries to T-SQL and ran into a problem. The results of the same update queries returned different results. The idea is to subtract each of the amounts of Table2 from Table1:
Source sample tables and content: Table1 ID Amount 1 100
Table2 ID Amount 1 10 1 20 1 30
In Access (Orginal source): UPDATE Table1 INNER JOIN Table2 ON Table1.ID = Table2.ID SET Table1.Amount = Table1.Amount - Table2.Amount
In T-SQL (Converted): UPDATE Table1 SET Table1.Amount = Table1.Amount - Table2.Amount FROM Table1 INNER JOIN Table2 ON Table1.ID = Table2.ID
Syntax for T-SQL is different from Access. When both queries are ran on their respective database, Table1.Amount in access became 40 (100 - 10 - 20 - 30), but Table1.Amount in SQL became 90 (100 - 10).
It looks as if in T-SQL it only ran one row? Or it could be that in T-SQL, updates written to the database in batches, hence why Table1.Amount was not updated for all update instances? Any help would be greatly appreciated.
using System; using System.Collections.Generic; using System.Text; using System.Data; using System.Data.OleDb; using System.Collections;
namespace TimeTracking.DB { public class sql { OleDbConnection conn;
// //the constructor for this class, set the connectionstring // public sql() { DBConnectionstring ConnectToDB = new DBConnectionstring(); conn = ConnectToDB.MyConnection(); }
// // // public void UpdateEntry(int ID, string Week, string Year, string Date, string Project, string Action, string Time, string Comment) { int m_ID = ID; int m_Week = (Convert.ToInt32(Week)); int m_Year = (Convert.ToInt32(Year)); string m_Date = Date; string m_Project = Project; int m_ProjectID = new int(); string m_Action = Action; int m_ActionID = new int(); Single m_Time = (Convert.ToSingle(Time)); string m_Comment = Comment;
// //get the project ID from the database and store it in m_ProjectID // OleDbCommand SelectProjectID = new OleDbCommand("SELECT tblProject.ProjectID FROM tblProject" + " WHERE (((tblProject.Project) LIKE @Project))", conn);
// //get the action ID from the database and store it in m_ActionID // OleDbCommand SelectActionID = new OleDbCommand("SELECT tblAction.ActionID FROM tblAction" + " WHERE (((tblAction.Action) LIKE @Action))", conn);
finally { //close the connection if (conn != null) { conn.Close(); } } } } }
Code Snippet
The update statement is not working in my application, no error in C# and no error in ms-access. When I paste the update query into the ms-access query tool and replace the parameter values (@....) with real values, is will update the record.
Hello, I have the following query in Access 2000 that I need to convertto SQL 2000:UPDATE tblShoes, tblBoxesSET tblShoes.Laces1 = NullWHERE (((tblShoes.ShoesID)=Int([tblBoxes].[ShoesID])) AND((tblBoxes.Code8)="A" Or (tblBoxes.Code8)="B"))WITH OWNERACCESS OPTION;The ShoesID in the tblShoes table is an autonumber, however the recordsin the tblBoxes have the ShoesID converted to text.This query runs ok in Access, but when I try to run it in the SQLServer 2000 Query Analizer I get errors because of the comma in the"UPDATE tblShoes, tblBoxes" part. I only need to update the tblShoesfield named Laces1 to NULL for every record matching the ones in thetblBoxes that are marked with an "A" or an "B" in the tblBoxes.Code8field.Any help would be greatly appreciated.JR
I run the following statement from an update query in access but I can't find the way to run this same query in MSSQL. Please give me some ideas how to modify and run this in MSSQL.
Thank you
"UPDATE DISTINCTROW ZipToTerr, leadsUS SET leadsUS.Terr = [ZipToTerr]![TerrNum] WHERE ((([ZipToTerr].[BU]='W') AND (([ZipToTerr].[ZipFrom])<=[zip]) And (([ZipToTerr].[ZipTo])>=[zip])) And (([leadsUS].[terr]) = 1 ));"
hi guys, my prblem is that I cannot update sql query against MS ACCESS using C#. Everytime wen i do so, i got an exception (at runtime) as 'UPDATE syntax not correct'. I don find any error in my 'update' syntax.
I can successfully run other dbase operations like insertion, deletion & all.; except Updation.
But, i can successfully run the same update query in the 'sql query tab' of MS ACCESS, and is executed successfully.
Hello,I'm in need of a little assitance using MSAccess with an SQL Server 2000 database backend.I currently have an application populates an Access database,but I would like to be able to send this data directly to the SQLServer also. Potential solution include the use of ADO/ADOX to make aconnection, a Pass-through/Update query to send the datato an identical database schema on the SQL Server, andstored procedures to make everything efficient. I don't have muchexperience with any of these technologies, but would greatlyappreciate any help and /or code snippets to get things moving.Thanks,Daryl
a while back i had to do a project with an access database, one of the biggest problems i had back then was gettting the primary key of a datarow you had just inserted into the database.
After a long set of trial and error i came up with the following:
- add the tablemappings of a table - call the dataadapte.fillschema method
then after inserting a new row into the database the primary key gets filled in automatically!
now thing is
i was hoping to duplicate this in sql server
but it doesn't seem to work at all
so after i insert a row into my datatable and update it the row is in the database but in vb the datarow primary key is not filled in! anyone have an idea?
prefereabely one that does not resort to stored procedures with return parameters etc
Hello,I am trying to update records in my database from excel data using vbaeditor within excel.In order to launch a query, I use SQL langage in ADO as follwing:------------------------------------------------------------Dim adoConn As ADODB.ConnectionDim adoRs As ADODB.RecordsetDim sConn As StringDim sSql As StringDim sOutput As StringsConn = "DSN=MS Access Database;" & _"DBQ=MyDatabasePath;" & _"DefaultDir=MyPathDirectory;" & _"DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;" &_"PWD=xxxxxx;UID=admin;"ID, A, B C.. are my table fieldssSql = "SELECT ID, `A`, B, `C being a date`, D, E, `F`, `H`, I, J,`K`, L" & _" FROM MyTblName" & _" WHERE (`A`='MyA')" & _" AND (`C`>{ts '" & Format(Date, "yyyy-mm-dd hh:mm:ss") & "'})"& _" ORDER BY `C` DESC"Set adoConn = New ADODB.ConnectionadoConn.Open sConnSet adoRs = New ADODB.RecordsetadoRs.Open Source:=sSql, _ActiveConnection:=adoConnadoRs.MoveFirstSheets("Sheet1").Range("a2").CopyFromRecordset adoRsSet adoRs = NothingSet adoConn = Nothing---------------------------------------------------------------Does Anyone know How I can use the UPDATE, DELETE INSERT SQL statementsin this environement? Copying SQL statements from access does not workas I would have to reference Access Object in my project which I do notwant if I can avoid. Ideally I would like to use only ADO system andSQL approach.Thank you very muchNono
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.
In VBA, I constructed the following to update all records intblmyTable with each records in tblmyTableTEMP having the sameUniqueID:UPDATEtblMyTable RIGHT JOIN tblMyTableTEMP ON tblMyTable.UniqueID =tblMyTableTEMP.UniqueIDSETtblMyTable.myField = tblMyTableTEMP.myField,tblMyTable.myField2 = tblMyTableTEMP.myField2,tblMyTable.myField3 = tblMyTableTEMP.myField3How is this done in a SQL Server Stored Procedure?Any help is appreciated.lq
HiI'm trying to convert an SQL Query from Access to SQL Server. TheAccess Query goes like this:SELECT Format(EntryDate, 'ddd mm dd') AS [Day]FROM JournalEntriesThis query returns the name of the day followed by month and date (Suaug 21)What would this be like in SQL Server ? What style id do I have to useto get the correct format ?RegardsRune
I am new to both ASP.net and this forum. I have seen some posts close to this, but none address this problem.
I have a SQL Server database on JOHN1 called 'siu_log' with a table called 'siu_log'. It has two fields: Scenarios char[20] and Machines char[20].
I have been adapting code from Build Your Own ASP.NET Website in C# & VB.NET by Zac Ruvalcaba to learn the language. Much of what you will see is his work adapted for my use.
Sub dg_Update(ByVal s As Object, ByVal e As DataGridCommandEventArgs) Dim strMachineName, strScenarioName As String Dim intResult As Integer strScenarioName = Trim(scenariosDataGrid.DataKeys(e.Item.ItemIndex)) strMachineName = CType(e.Item.FindControl("txtMachine"), TextBox).Text cmd = New SqlCommand("UPDATE siu_log SET Machines=@Machine " & _ "WHERE Scenarios=@Scenario", conn) cmd.Parameters.Add("@Machine", strMachineName) cmd.Parameters.Add("@Scenario", strScenarioName) conn.Open() intResult = cmd.ExecuteNonQuery() resultLabel.Text = "The result was " & intResult & "." conn.Close() scenariosDataGrid.EditItemIndex = -1 BindData() End Sub
The problem is the strMachineName variable always contains the previous contents of the text box -- not the new one. This makes the UPDATE query just push the old data back into the table.
Hello all. I have 2 tables members1 and members2. members1 have a field called directory_services_idmembers2 also has a field directory_services_id and another one called employee_id I need to update directory_services_id in members1 to the value employee_id in members2 Where members1.directory_services_id = members2.directory_services_id I dont want to update all the records. Only those records in members1 that have a match on directory_services_id in members2. So if there are 100 records that match on directory_services_id then i want to update only those 100 and not all the records.This is the query that I have so far.Update members1 M1 Set directory_services_id = (Select member_custom20 From members2 M2 Where M1.directory_services_id = M2.directory_services_id)Where M1.directory_services_id IN (Select directory_services_id From M2)And the error I am getting isServer: Msg 170, Level 15, State 1, Line 1Line 1: Incorrect syntax near 'M1'.Server: Msg 156, Level 15, State 1, Line 3Incorrect syntax near the keyword 'Where'.Please help. Thank you.
I have two tables in an inner join. I'm detailing the tables with some of their fields below. These tables are in a database I'm creating to manage backup tapes. Most importantly, this database will inform me when backup tapes which have already been used can be recycled (e.g. after all the jobs on the tape are over 28 days old). I want to write something which will look at each tape in turn and, if all related backup jobs on that tape are aged, the tape status will be changed from Active to Spare.
Tapes --TapeNo --Status (Spare / Assigned)
Jobs --JobNo --Name --Description --TapeNo --AgedJob (BIT field indicating whether or not the job has aged)
Each tape can have 0, 1 or many jobs and each job can be on more than one tape.
Anyway, I have the tables and relationsips set up and they're ok. Again, what I'm struggling with is how I take each tape and look at all its jobs and, if all have aged, change the Status for the tape to Spare. I'm using SQL Server 2000 (Access 2003 as front end) and am pretty new to SQL. I was thinking this could be done by using some kind of update query and subquery, but I'm stumped. Could someone please help ?
Membertable1 has SSN, Address1, Address2 as columns. Membertable2 has SSN, Name, Address1, Address2 as columns. For each SSN in Membertable2 I want to update the Address1 and Address2 from Membertable1 table Address columns if it is available.
Since it is SQL Server 2005, thinking of any 2005 features. SQL Statements only. Thank you, Gish
Hi,I'm trying to convert MS Access 97 .mdb application to Access 2003 .adpapplication with SQL Server as Backend.I'm having trouble converting Access Query into SQL Query. The Query isgiven below:~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~~~SELECT DISTINCTROW Buildings.BuildingNumber,First(Buildings.BuildingName) AS FirstOfBuildingName,First(OwnershipCodes.OwnershipCode) AS FirstOfOwnershipCode,First(OwnershipCodes.OwnershipDesc) AS FirstOfOwnershipDesc,First(CityCodes.CityName) AS FirstOfCityName,First(CountyCodes.CountyName) AS FirstOfCountyName,First(Buildings.Address) AS FirstOfAddress,First(Buildings.YearConstructed) AS FirstOfYearConstructed,First(Buildings.DateOccupancy) AS FirstOfDateOccupancy,First(Buildings.NumberLevels) AS FirstOfNumberLevels,First(Buildings.BasicGrossArea) AS FirstOfBasicGrossArea,Sum(Rooms.AssignableSquareFeet) AS SumOfAssignableSquareFeet,First(Buildings.UnrelatedGrossArea) AS FirstOfUnrelatedGrossArea,First(Buildings.SpecialArea) AS FirstOfSpecialArea,First(Buildings.CoveredUnenclosedGrossArea) ASFirstOfCoveredUnenclosedGrossAreaFROM CountyCodes INNER JOIN (OwnershipCodes INNER JOIN (ConditionCodesINNER JOIN ((CityCodes INNER JOIN Buildings ON CityCodes.CityCode =Buildings.CityCode) LEFT JOIN Rooms ON Buildings.BuildingNumber =Rooms.BuildingNumber) ON ConditionCodes.ConditionCode =Buildings.ConditionCode) ON OwnershipCodes.OwnershipCode =Buildings.OwnershipCode) ON CountyCodes.CountyCode =CityCodes.CountyCodeGROUP BY Buildings.BuildingNumber;~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~~~Please can any one tell me substitue for First Function in Acess to SQLFunction.Any help is appreciated.Thanks,S
Hello,I have one simple query joining two tables with left outer join on 3fields and using MIN on two fields. These two tables have lot of dataabout 3 mil in total. I am trying to migrate db from MS Access to SQL2005. I get about 689000 rows in SQL Server, vs 863000 rows in MSAccess.SELECT T1.[MON], T1.[ANUM], T2.[ANUM], MIN ( T1.[OCD]), MIN(T1.[STATE]), T1.COUNTFROM T1 INNER JOIN T2 ON T1.MON = T2.MON AND T1.[OCD] = T2.[OCD] ANDT1.[STATE] = T2.[STATE]WHERE T1.[REASON] <'SOMETHING' AND T2.[REASON] <'SOMETHING'GROUP BY T1.[MON], T1.[ANUM], T2.[ANUM], T1.COUNTHAVING T1.[MON] <'-' AND T1.[ANUM] <'-'I have about 30 queries to migrate and I am sort of stuck. Does anyone have any idea ?JB
I am trying to upsize a database to SQL server (on which I am a novice). InAccess as part of a much more complex query I had the following (from sqlview)SELECTIIf(InStr([ItemName],"*")>0,Left([ItemName],InStr([ItemName],"*")-1),[ItemName]) AS ShortName FROM corp_infoWhich gives a return value for the whole of ItemName if there is no star init, or the portion up to the star if there is a starI am having a nightmare trying to get an equivalent in SQL server. I'veworked out that Instr is charindex in sql and can adjust for that, but can'twork out how to get a conditional select statement working.It may well be obvious, but any help much appreciated. Thanks.Robin Hammondwww.enhanceddatasystems.com
I set up an Excel spreadsheet for several users that queries a SQL Server. I provided the spreadsheet to the users and set it to refresh when ever it is open. The query is working fine. The problem is the users are prompted with a SQL Server login screen every time they open the file. The login screen shows the server name, the username and the password. All they have to do is press 'OK' and the screen goes away. Why are they being prompted with this screen? What can I do to eliminate the users from even seeing this window?
I also receive this window whenever I open the file and I created it on the workstation where i'm opening it.
Thought I would post here in case anybody can give some information.
Here is the background information:
I have 2 tables (stores and sales) from the Pubs database in Sql Server 2000 copied down to a SQL Server CE database. There is no foreign key/primary key relationship between the 2 tables in the CE database.
Here are the update queries that cause the error:
UPDATE st SET st.zip = 66668 FROM stores st INNER JOIN sales sa ON st.stor_id = sa.stor_id AND st.stor_id = 6380
Update stores SET stores.zip = 55555 FROM sales, stores WHERE stores.stor_id = 6380 AND stores.stor_id = sales.stor_id
Here is the error message that is generated when I run the query (Param 0 and Param 1 change according to what column and line the FROM clause is in):
Error: 0x80040e14 DB_E_ERRORSINCOMMAND Native Error: (25501) Description: There was an error parsing the query. [Token line number,Token line offset,,Token in error,,] Interface defining error: IID_ICommand Param. 0: 2 Param. 1: 1 Param. 2: 0 Param. 3: FROM Param. 4: Param. 5:
I ran the 2 queries in SQL Query Analyzer in SQL Server 2000 and they worked just fine. I also created 2 new tables (stores1 and sales1) in SQL Server 2000 using the Select Into clause. The new tables were created from the sales table and stores table in the Pubs database. The new tables had no foreign key/primary key relationship.
I ran the queries again in Query Analyzer against the new tables and the queries produced no errors.
I am trying to Write an update string for individual partID's. I wrote this query below but it isn't populating the time to test.
SELECT 'UPDATE Parts SET = [TimeToTest]' + ' ' + Convert(varchar, (select test From [dbo].[db_PartsCats] as c Join Parts As P on P.category = C.CatID Where PartID = 48727)) + ' ' + 'WHERE PartID = ' + CONVERT(varchar, P.PartID) From Parts As P Where FRID = 0 And TimeToTest = 0 and TimeToInstall = 0 and TimeToProgram = 0 And TimeToTrain = 0 And manufacturer = 187 Order By categoryMy results:
Should get UPDATE Parts SET = [TimeToTest] 0.5000 WHERE PartID = 48871 But getting Nulls instead
I have run into a perplexing issue with how to UPDATE some rows in my table correctly.I have a Appointment table which has Appointment Times and a Appointment Names. However the Name is only showing on the Appt start Time line. I need it to show for its duration. So for example in my DDL Morning Appt only shows on at 8:00 I need it to show one each line until the next Appt Starts and so on. In this case Morning Appt should show at 8:00,8:15, 8:30.
Im using an ADP to connect to a SQL Sqever DB.In access it was really easy to sayInner join on table1 and table2 and update columnA from table1 withcolumnC from table2 where table1.key = table2.key and table2 columnB =1 and table2 columnD = 4I have tried all manner of beasts to get this thing to work..UPDATE dbo.GIS_EVENTS_TEMPSET FSTHARM1 =(SELECT HARMFULEVENTFROM HARMFULEVENTWHERE (HARMFULEVENT.CRASHNUMBER = GIS_EVENTS_TEMP.CASEID)AND(HARMFULEVENT.UNITID = 1 AND HARMFULEVENT.LISTORDER = 0))This almost works but ignors the 'HARMFULEVENT.UNITID = 1 ANDHARMFULEVENT.LISTORDER = 0' part which is really importantAny Help would be great....
Gary writes "I'm imported the query from Access (where it worked perfectly) to SQL View where the "Iif" statements caused an issue.
Here's a SMALL portion of the original: SELECT tblTransactionBuyer.TransBuyerID, tblDefaults.CompID, ([TransBuyerPropSalePrice]*([DefBasePercentVA])/100) AS LoanBaseVA, [TransBuyerPropSalePrice]* (IIf([TransBuyerPropSalePrice]>=[DefLoanMinJumbo],[DefFundingJumboVA], IIf([TransBuyerVaUsed]="Yes",[DefFundingUsedVA],[DefFundingVA])))/100 AS VAFunding, ......
Below, are my changes so far, but I'm stuck. SELECT tblTransactionBuyer.TransBuyerID, tblDefaults.CompID, ([TransBuyerPropSalePrice]*([DefBasePercentVA])/100) AS LoanBaseVA,
[TransBuyerPropSalePrice]* If([TransBuyerPropSalePrice]>=[DefLoanMinJumbo],[DefFundingJumboVA], If([TransBuyerVaUsed]="Yes",[DefFundingUsedVA],[DefFundingVA]/100)) AS VAFunding, ........
It appears that I may not be able to put an IF into a SQL SELECT. If not, how can I get the same effect ?
Thanks. Gary
Listed Below is the Full Access Query:
SELECT tblTransactionBuyer.TransBuyerID, tblDefaults.CompID, tblTransactionBuyer.TransBuyerEntryDate, qryUserAlphaList.UserName, qryUserAlphaList.UserAreaCode, qryUserAlphaList.UserPhone, tblTransactionBuyer.TransBuyerName, tblTransactionBuyer.TransBuyerVaEligible, tblTransactionBuyer.TransBuyerPropAddress, tblTransactionBuyer.TransBuyerPropCity, tblTransactionBuyer.TransBuyerPropSalePrice, ([TransBuyerPropSalePrice]*([DefBasePercentFHA])/100) AS LoanBaseFHA, ([TransBuyerPropSalePrice]*([DefBasePercentCONV])/100) AS LoanBaseCONV, ([TransBuyerPropSalePrice]*([DefBasePercentVA])/100) AS LoanBaseVA, ([TransBuyerPropSalePrice]*([DefBasePercentJumbo])/100) AS LoanBaseJumbo, [LoanBaseFHA]*[DefMipFHA]/100 AS MipFHA, [LoanBaseCONV]*[DefMipCONV]/100 AS MipCONV, [LoanBaseJumbo]*[DefMipJumbo]/100 AS MipJumbo, [TransBuyerPropSalePrice]*(IIf([TransBuyerPropSalePrice]>=[DefLoanMinJumbo],[DefFundingJumboVA],IIf([TransBuyerVaUsed]="Yes",[DefFundingUsedVA],[DefFundingVA])))/100 AS VAFunding, [LoanBaseFHA]-[DownOverMinFHA]+[MipFHA] AS LoanTotalFHA, [LoanBaseCONV]-[DownOverMinCONV]+[MipCONV] AS LoanTotalCONV, [LoanBaseJumbo]+[MipJumbo]-[DownPaymentJumbo] AS LoanTotalJumbo, [LoanBaseVA]+[VAFunding]-[DownPaymentVA] AS LoanTotalVA, [TransBuyerPropSalePrice]*([DefMinDownPayFHA]/100) AS MinDownFHA, [TransBuyerPropSalePrice]*[DefMinDownPayCONV]/100 AS MinDownCONV, [TransBuyerPropSalePrice]*[DefMinDownPayVA]/100 AS MinDownVA, [TransBuyerPropSalePrice]*[DefMinDownPayJumbo]/100 AS MinDownJumbo, tblTransactionBuyer.TransBuyerIntRate, tblTransactionBuyer.TransBuyerLoanYears, tblTransactionBuyer.TransBuyerHazardIns, tblTransactionBuyer.TransBuyerFloodIns, tblTransactionBuyer.TransBuyerClosingDate, IIf(([TransBuyerConvDownPayPercent]/100)*[TransBuyerPropSalePrice]>[TransBuyerConvDownPayCash],([TransBuyerConvDownPayPercent]/100)*[TransBuyerPropSalePrice],[TransBuyerConvDownPayCash]) AS ConvDownPay, tblDefaults.DefBuyAppraisal, tblDefaults.DefBuyCreditReport, tblDefaults.DefBuyJumboUnderwriting, tblDefaults.DefBuyVAUnderwriting, tblDefaults.DefBuyFHAUnderwriting, tblDefaults.DefBuyCONVUnderwriting, tblDefaults.DefBuyJumboTaxService, tblDefaults.DefBuyVATaxService, tblDefaults.DefBuyFHATaxService, tblDefaults.DefBuyCONVTaxService, tblDefaults.DefBuyFloodCert, tblDefaults.DefBuyAbstractTitle, tblDefaults.DefBuyMiscFedex, tblDefaults.DefBuyRecording, tblDefaults.DefBuySurvey, tblDefaults.DefBuyAttorney, tblDefaults.DefBasePercentFHA, tblDefaults.DefBasePercentCONV, tblDefaults.DefBasePercentJumbo, tblDefaults.DefBasePercentVA, [TransBuyerPropSalePrice]*[DefPropTaxRate] AS PropTax, [LoanBaseFHA]*[DefBuyOrigFHA]/100 AS OrigFHA, [LoanBaseCONV]*[DefBuyOrigCONV]/100 AS OrigCONV, [LoanBaseVA]*[DefBuyOrigVA]/100 AS OrigVA, [LoanBaseJumbo]*[DefBuyOrigJumbo]/100 AS OrigJumbo, [DefTitleInsDollar]*([TransBuyerPropSalePrice]/[DefTitleInsPerX]) AS TitleInsurance, [OrigFHA]+[DefBuyAppraisal
Hello everybody I need to a solution to my problem asap. My problem is that i have a sql server 2005 script which is working fine and returning me the correct query. However the same script in access does not return the required query. The Sql server script is as follows:
UPDATE Transactions SET Transactions.EndProductDescription = TB.EndDescription FROM Transactions INNER JOIN (SELECT Distinct EndDescription, ProdBatchNo FROM ((SELECT EndProductCode FROM EndProduct WHERE (Product1 IN (SELECT ProductCode FROM Transactions WHERE (ProdBatchNo = '06122007/000002')))) AS TB1 INNER JOIN (SELECT EndProductCode FROM EndProduct WHERE (Product2 IN (SELECT ProductCode FROM Transactions WHERE (ProdBatchNo = '06122007/000002')))) AS TB2 ON TB1.EndProductCode = TB2.EndProductCode) INNER JOIN (SELECT EndProductCode, EndDescription, ProdBatchNo FROM EndProduct, Transactions WHERE (ProdBatchNo = '06122007/000002') AND (Product3 IN (SELECT ProductCode FROM Transactions WHERE (ProdBatchNo = '06122007/000002')))) AS TB3 ON TB2.EndProductCode = TB3.EndProductCode) AS TB ON Transactions.ProdBatchNo = TB.ProdBatchNo WHERE Transactions.EndProductDescription = ''
So when i put this query in access same as it is i do not obtain the required results. Can someone please help me urgently on converting the above sql script into access query?
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") ASdBasicGrossArea, Format$([CoveredUnenclosedGrossArea],"0000000") ASdCoveredUnenclosedGrossArea,IIf(Month([DateOccupancy])>9,Month([DateOccupancy]),"0" &Month([DateOccupancy])) & Year([DateOccupancy]) AS dDateOccupancy,Buildings.YearConstructed, Format$([NumberLevels],"00") ASdNumberLevels, Format$([UnrelatedGrossArea],"0000000") ASdUnrelatedGrossArea, 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 dCustodialFROM CityCodes INNER JOIN Buildings ON CityCodes.CityCode =Buildings.CityCodeORDER BY "01C", Buildings.BuildingNumber, Buildings.BuildingName;Please if anyone can help me in Converting the above given Access Queryto Sql Server. I don't know which function to use for format$, IIF. Iwould really appreciate your suggestions.Thanks,
Getting a weird error while trying out a query from Access 2003 on aSQL Server 2005 table.Want to compute the amount of leave taken by an emp during the year.Since an emp might be off for half a day (forenoon or afternoon), havethe following computed field:SessionOff: ([ForenoonFlag] And [AfternoonFlag])The query works fine when there's no criterion on SessionOff.However, when I try to get the records where the SessionOff equals 0, Iget the following error:~~~~~ODBC--call failed. [Microsoft][SQL Native Client][SQL server]Incorrect syntax near the keyword 'NOT'. (#156)~~~~~I checked the SQL of the Access query, but there's no NOT anywhere init:~~~~~SELECT tblWorkDateAttendance.*FROM tblWorkDate INNER JOIN tblWorkDateAttendance ONtblWorkDate.WorkDate = tblWorkDateAttendance.WorkDateWHERE (((([ForenoonFlag] And [AfternoonFlag]))=0) AND((tblWorkDateAttendance.WorkDate)<Date()) AND((Year([tblWorkDate].[WorkDate]))=Year(Date())) AND((Weekday([tblWorkDate].[WorkDate])) Between 2 And 6) AND((tblWorkDate.HolidayFlag)=False));~~~~~What gives?