Update Query (Access Vs. SQL Server)

Jul 23, 2005

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"));

View 1 Replies


ADVERTISEMENT

UPDATE Query Handles Differently In SQL Server And MS Access?

Dec 17, 2007

Hello,

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.

View 3 Replies View Related

Update Query In Ms-access Doesn't Workin C#, But Does Work In Ms-access

Apr 18, 2007

Hi,

I have an application that uses following code:



Code Snippet







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);

SelectProjectID.Parameters.AddWithValue("@Project", m_Project);

try
{
//open the connection
conn.Open();

OleDbDataReader Dataset = SelectProjectID.ExecuteReader();

while (Dataset.Read())
{
m_ProjectID = (int)Dataset["ProjectID"];
}

Dataset.Close();
}

//Some usual exception handling
catch (OleDbException e)
{
throw (e);
}

//
//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);

SelectActionID.Parameters.AddWithValue("@Action", m_Action);

try
{
OleDbDataReader Dataset = SelectActionID.ExecuteReader();

while (Dataset.Read())
{
m_ActionID = (int)Dataset["ActionID"];
}

Dataset.Close();
}

//Some usual exception handling
catch (OleDbException e)
{
throw (e);
}



//
//
//
OleDbCommand Query = new OleDbCommand("UPDATE [tblEntry] SET [tblEntry].[Weeknumber] = @Week,"
+ " [tblEntry].[Year] = @Year, [tblEntry].[Date] = @Date, [tblEntry].[Project] = @ProjectID, [tblEntry].[Action] = @ActionID,"
+ " [tblEntry].[Hours Spent] = @Time, [tblEntry].[Comments] = @Comment WHERE (([tblEntry].[ID]) = @ID)", conn);

Query.Parameters.AddWithValue("@ID", m_ID);
Query.Parameters.AddWithValue("@Week", m_Week);
Query.Parameters.AddWithValue("@Year", m_Year);
Query.Parameters.AddWithValue("@Date", m_Date);
Query.Parameters.AddWithValue("@ProjectID", m_ProjectID);
Query.Parameters.AddWithValue("@ActionID", m_ActionID);
Query.Parameters.AddWithValue("@Time", m_Time);
Query.Parameters.AddWithValue("@Comment", m_Comment);

try
{
Query.ExecuteNonQuery();
}

//Some usual exception handling
catch (OleDbException e)
{
throw (e);
}

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.

What am I overseeing here?
--Pascal

View 13 Replies View Related

Update SQL 2000 Query (converting An Old Access 2k Query To SQL)

Mar 30, 2006

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

View 2 Replies View Related

Access Update Query

Apr 4, 2007

I have an Access update query that is not working.

Here's my SQL:UPDATE tablename SET tablename.HeartAttackDOD = " "
WHERE(((tablename.HeartAttackDOD)=#1/1/2001#) AND ((tablename.HeartAttack)="2"));

It sets HeartAttackDOD = " " even if HeartAttack = "1" and I only want it to set HeartAttackDOD = " " if HeartAttack = "2".

Can anyone help?

View 1 Replies View Related

ACCESS To MSSQL Update Query

Oct 24, 2005

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 ));"

View 8 Replies View Related

Update Query In MS-ACCESS Don't Working + C#

Nov 24, 2007

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.

I'm using VS.NET 2005 & MS ACCESS 2003.

please help me...its urgent.

i'm including the code below :

*****************************************************************

string provider = "Provider = Microsoft.Jet.OleDb.4.0; Data Source = db1.mdb;";
string sql = "update Table1 set Password = 'modify' where ID = 'abhi'";

try
{
OleDbConnection oc = new OleDbConnection(provider);
OleDbCommand od = new OleDbCommand(sql, oc);
oc.Open();

od.ExecuteNonQuery();

oc.Close();
}
catch (Exception exp)
{
return exp.Message;
}

*****************************************************************

My table contains two field : "ID" & "Password", both are of String type.

*****************************************************************

thanks in advance

View 1 Replies View Related

Can I Access MS Access Table In A Select Query Of SQL Server

Nov 29, 2006

Is there a way to specify a MS Access table (or query object) in the select query of SQL Server.

Ex.:

MSAccessTable (in file.mdb)



col1
col2

a1
a2

b1
b2

SQL query in SQL Server:

SELECT col1, col2 into SqlTable from [file.mdb].MSAccessTable;

Thanks,

View 3 Replies View Related

Update SQL Server From Access

Jul 20, 2005

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

View 1 Replies View Related

Primary Key In Datarow After Update Works In Access Not In Sql Server

Feb 15, 2005

Heys

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

thx a million in advance!

View 1 Replies View Related

UPDATE Into JOINed Table - Access Vs. SQL Server Syntax

Sep 13, 2007

I am trying to get a SQL statement to work with both Access 2000 and SQL Server 2000.

The statement that works in SQL Server is:

---
UPDATE [myTable2]

SET
[myTable2].[FieldA] = 'Hello',
[myTable2].[FieldB] = 2,
[myTable2].[FieldC] = 'xxx',
[myTable2].[FieldD] = 0

FROM [myTable1] INNER JOIN
(myTable2 INNER JOIN [myTable3]
ON [myTable2].[FieldX]=[myTable2].[FieldY])
ON [myTable1].[FieldZ]=[myTable2].[FieldY]

WHERE ([myTable2].[FieldY]=1)
And ([myTable3].[FieldZ]='xxx');
---


(names have been changed to protect the innocent)


The statement that works in Access is:

---
UPDATE [myTable1] INNER JOIN
(myTable2 INNER JOIN [myTable3]
ON [myTable2].[FieldX]=[myTable2].[FieldY])
ON [myTable1].[FieldZ]=[myTable2].[FieldY]

SET
[myTable2].[FieldA] = 'Hello',
[myTable2].[FieldB] = 2,
[myTable2].[FieldC] = 'xxx',
[myTable2].[FieldD] = 0

WHERE ([myTable2].[FieldY]=1)
And ([myTable3].[FieldZ]='xxx');
---


It seems that neither will accept the other format. Can anyone suggest how I can rearrange the statement so that it works in both?

View 2 Replies View Related

UPDATE SQL Statement In Excel VBA Editor To Update Access Database - ADO - SQL

Jul 23, 2005

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

View 1 Replies View Related

MS Access Query That I Would Like To Convert To MS SQL Server Query

Jun 28, 2005

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.


Code:

SELECT Customer.Last_Name, Customer.First_Name, Customer.Middle_Name, Customer.Address, Customer.City, Customer.Region, Customer.Postal_Code, Customer.Country, Orders.Order_ID, Customer.Customer_ID, Employees.LastName, Employees.FirstName, Order_Line.Item_ID, Item.[Long Description], Order_Line.Units_Purchased, Order_Line.Price, Order_Line.Discount, CCur(Order_Line.Price*[Units_Purchased]*(1-[Discount])/100)*100 AS ExtendedPrice, Store.Store_Address, Store.Store_City, Store.Store_State, Store.Store_Zip_code, Store.Store_Phone_Number, Store.Store_Fax_Number, Item.Taxable_Nontaxable,
IIf(Item.Taxable_Nontaxable=Yes,([ExtendedPrice]*Tax_Table.Tax_Rate),([ExtendedPrice]*0)) AS Tax_Amt,
Tax_Table.Tax_Rate

View 3 Replies View Related

UPDATE Query In SQL Server

Jul 20, 2005

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

View 8 Replies View Related

SQL Query From Access To SQL Server

Sep 25, 2007

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

View 1 Replies View Related

New Values Ignored In SQL Server UPDATE Query

Nov 4, 2004

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.

First, the html:

<%@ Page Language="vb" AutoEventWireup="false" Codebehind="WebForm1.aspx.vb" Inherits="SIU_Assign.WebForm1"%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML>
<HEAD>
<title>SIU Interface Scenario Assignments</title>
<meta content="Microsoft Visual Studio .NET 7.1" name="GENERATOR">
<meta content="Visual Basic .NET 7.1" name="CODE_LANGUAGE">
<meta content="JavaScript" name="vs_defaultClientScript">
<meta content="http://schemas.microsoft.com/intellisense/ie5" name="vs_targetSchema">
</HEAD>
<body>
<form id="Form1" method="post" runat="server">
<asp:datagrid id="scenariosDataGrid" runat="server" CellPadding="4" AutoGenerateColumns="False"
OnUpdateCommand="dg_Update" OnCancelCommand="dg_Cancel" OnEditCommand="dg_Edit" DataKeyField="Scenarios">
<ItemStyle BackColor="#00DDDD" ForeColor="#000000" />
<HeaderStyle BackColor="#003366" ForeColor="#FFFFFF" />
<Columns>
<asp:EditCommandColumn EditText="Edit" CancelText="Cancel" UpdateText="Update" />
<asp:BoundColumn DataField="Scenarios" HeaderText="Scenario" ReadOnly="True" />
<asp:TemplateColumn>
<HeaderTemplate>
Machine
</HeaderTemplate>
<ItemTemplate>
<%# Container.DataItem("Machines") %>
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox ID="txtMachine" Runat=server Text='<%# Container.DataItem("Machines") %>' />
</EditItemTemplate>
</asp:TemplateColumn>
</Columns>
</asp:datagrid>
<asp:Label id="resultLabel" runat="server"></asp:Label></form>
</body>
</HTML>


Then the CodeBehind:

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.

Any suggestions?

Thanks!
John

View 5 Replies View Related

SQL Server Update Query Help -- URGENT!!!

Oct 10, 2005

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.

View 2 Replies View Related

Use Update Query In SQL Server 2000 ?

Sep 15, 2006

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 ?


--
Paul Anderson

View 3 Replies View Related

Update Query Of SQL Server 2005…….

Apr 25, 2008

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

View 8 Replies View Related

Access To SQL Server Query Translation

Aug 17, 2006

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

View 3 Replies View Related

Why Same Query Results In Two Different # In SQL Server Vs MS Access

Feb 20, 2008

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

View 4 Replies View Related

Converting IIF In Access Query To SQL Server

Jul 20, 2005

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

View 1 Replies View Related

Using An Excel Query To Access SQL Server

Jun 25, 2006

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.

Thank you for any input or suggestions.

View 4 Replies View Related

SQL Server CE Update Query Causing Errors

Jan 14, 2005

Hello all,

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.

Any suggestions?

Thank you,
Aaron B

View 1 Replies View Related

SQL Server 2012 :: Sub-Query For Update Statement

Feb 14, 2014

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

View 5 Replies View Related

SQL Server 2008 :: Update Table Query

Mar 11, 2015

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.

CREATE TABLE #TEST ( ApptTime TIME, ApptName VARCHAR (20), DURATION TINYINT)
INSERT INTO #TEST VALUES ('8:00', 'Morning Appt', 45), ('8:15', NULL, NULL),('8:30', NULL,NULL),('8:45', 'Brunch Appt', 45),('9:00', NULL,NULL),('9:15', NULL,NULL),
('9:30', 'Afternoon Appt', 30),('9:45', NULL,NULL),('10:00', NULL,NULL).

View 3 Replies View Related

?HLEP..Sql Server UPDATE INNER JOIN QUERY ?????..

Jul 23, 2005

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....

View 4 Replies View Related

Trouble Translating Query From Access To SQL Server

Feb 16, 2006

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

View 1 Replies View Related

Convert Query From Access To Sql Server 2000

Aug 10, 2007

How do I write this query that was written in access to sql?

SELECT Max(tri_InsrTran.claimnum) AS MaxOfclaimnum, tri_InsrTran.trannum
FROM tri_InsrTran
GROUP BY tri_InsrTran.trannum;

View 2 Replies View Related

SQL Server To Access Query URGENT Help Plzzzz...

Dec 13, 2007

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?

View 2 Replies View Related

Convert Access Query To SQL Server View

May 17, 2006

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,

View 8 Replies View Related

Access Query Against SQL Server Works Only Without Criteria

Jun 23, 2006

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?

View 4 Replies View Related

Serial Number In Query, In MS Access And SQL Server

Jan 16, 2008

im trying to run a query, my first column should be serial number based on results, how can i do that for example

qry = "Select products, sum(prod_price), sum(prod_sellingprice) from sales group by products"

or

qry = "Select products, sellingperson from sales where seldate=#" & date() & "#"

i want first column of both queries as serial number based on results of query.

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved