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
ADVERTISEMENT
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
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
Jan 30, 2008
I'm having trouble converting this access query into a sql 2000 query.
Your help would be appreciated:
IIf(nz([PTicketNum],"M999Z")="0","M999Z",IIf(Trim(nz([PTicketNum],"M999Z"))="","M999Z",nz([PTicketNum],"M999Z")))
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
View 1 Replies
View Related
Aug 17, 2007
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.
View 2 Replies
View Related
May 9, 2008
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)
Thanks in advance,
Andy.
View 3 Replies
View Related
Apr 10, 2007
how to convert this access query to sql query
IIf([Total Bunches] > 0, Production * 1000 / [Total Bunches], 0) as Name2
SUM(IIf(BlockInYield = -1, [SIZE], 0)) as Name1
IIf(BlockInYield = TRUE, IIf(TC_M > 0, TC_M, TC_DENS *[SIZE]), 0) as Name
please......
View 3 Replies
View Related
Aug 21, 2013
I am trying to convert a code from access Db to sql code?
II(Left([dbo_ClaimLosstype].[dscr],4)="Liab","D_Liab","D_Property"),
View 4 Replies
View Related
Apr 2, 2008
Here is the access query
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;
coding is my passion
View 2 Replies
View Related
Mar 18, 2008
I having trouble converting this access query to sql 2000, can anyone offer advice? The where clause is troubling me.
---Access Query---
SELECT DISTINCT Procedures.PatientNumber,
rocedures.TicketNumber,
Procedures.Counter, Procedures.DateOfService, Policies.PatientNumber, Policies.CarrierCode,
Policies.EffectiveDate, Policies.Employer, Policies.FirstName, Policies.GroupNumber, Policies.LastName,
Policies.MiddleInitial, Policies.PolicyHolderBirthdate, Policies.PolicyNumber,
Policies.PrimaryPolicyIndicator, Policies.TerminationDate, Procedures.ProcedureCode,
Procedures.PrimaryPolicyCarrierCode
FROM Procedures LEFT JOIN Policies ON (Procedures.Counter = Policies.Counter)
AND (Procedures.PrimaryPolicyCarrierCode = Policies.CarrierCode)
AND (Procedures.PatientNumber = Policies.PatientNumber)
WHERE (((Procedures.DateOfService)>=[effectivedate]
And (Procedures.DateOfService)
<=IIf(IsNull([terminationdate]),Now(),[terminationdate])))
OR (((IsNull([procedures].[primarypolicycarriercode]))<>False))
View 2 Replies
View Related
Jun 6, 2008
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?
Jon
View 1 Replies
View Related
Sep 11, 2007
I'm going through and restructuring a database scheme and was wondering if, after I make a query (view), how can I convert that into a table?
Thanks
View 4 Replies
View Related
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
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
Mar 18, 2015
How to convert float to timestamp in single select query
E.g., i have float as 1.251152515236 ,
I want to convert this to datetime and from datetime to timestamp... i.e. 26:11:00
View 1 Replies
View Related
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
Jul 23, 2005
Can someome please advise what the equivalent query would be inMicrosoft SQL Server ... I've tried a number of combinations with nosuccess ... Thanks, Ralph Noble (ralph_noble@hotmail.com)================UPDATE INVENTORYINNER 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
View Related
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
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
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
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
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
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
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
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
Apr 6, 2006
I'm currently upgrading from Access to Sql Server, and I have a broken query that I cannot find a fix for.
The original query:
SELECT DISTINCT DownloadedNames.*FROM DownloadedSkims INNER JOIN (DownloadedNames INNER JOIN DownloadedInfo ON DownloadedNames.DNID = DownloadedInfo.DIDnID) ON DownloadedSkims.DM_ID = DownloadedInfo.DI_DM_IDWHERE DM_ID=23 AND (dWeek BETWEEN '6/26/2006' AND '9/18/2006')ORDER BY DIID, dWeek
I get the error "Msg 145, Level 15, State 1, Line 2
ORDER BY items must appear in the select list if SELECT DISTINCT is specified" when I test the query.
If anyone knows what changes I can make to the query, you'd really be helping me out.
Chris
View 1 Replies
View Related
Jan 10, 2007
Hello,
I have attached a Sql Server table in an Access database.
This wasn't a problem, I can open my attached table from Access, use it in queries.
Now I'm trying to open it from an asp page, for example :
SQL="SELECT * FROM myAttachedTable"
Set Rs= server.createobject("ADODB.Recordset")
Rs.Open SQL, myAccessConnectionString , 3, 4
I get :
Microsoft OLE DB Provider for ODBC Drivers error '80004005'
[Microsoft][Pilote ODBC Microsoft Access] ODBC -- failed connection to 'My SQL Server'.
again if I enter the same sql query directly in Access it all works.
What am I missing ?
Thanks a lot
View 5 Replies
View Related
Jul 12, 2006
Hello,
I have an Access 2003 application that uses a query to supply sorted data to a combobox on a form. This bit works fine.
I've upsized to SSE and have now got a few learning opportunities! Can anybody suggest how I can get the same functionality in the upsized/SSE version? Do I need a stored procedure or a view (or something else) to deliver sorted data? As you can tell, what I know about SQL Server wouldn't cover a postage stamp! Any recommendations for good books on the subject?
TIA for any help.
Chris
View 4 Replies
View Related
Mar 7, 2007
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
View 1 Replies
View Related
Oct 17, 2013
I have a SQL Server database running on a local PC which will eventually be scaled up once everything is working.
The Database takes data from an Access database, then the SQL Server aggregates this data into several other tables.
I have used a trigger to run this in SQL Server, once a table in SQL Server is appended with a specific value.
I have tested the trigger to do a simple task, and this works.
I have tested the aggregation query which create 18 seperate tables as well. It takes around 25 minutes to run. These are huge tables
When I use Access to append the final value to start the SQL Server trigger it freezes and eventually times out. I assume this is because it is running the 25minute trigger, and Access has to wait until this is completed before it can proceed.
I was hoping it would trigger SQL Server to run the trigger, then Access could go off and do something else!
View 10 Replies
View Related
Mar 2, 2006
Hi everybody,
I do not know if this is the correct area to post this topic? So, How to access
different sql server with query analyzer? Usually, when to install sql server, it
access the database server locally installed, now I like to access other sql server
within a domain using query analyzer. How to configure this in order I could use
query analyzer to access other sql server within a domain? Thanks in advanced.
den2005
View 1 Replies
View Related
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
May 28, 2008
Hi Friends,
I have created the procedure in sql server 2005 for retriving email addresses from table based on date_expiry and concatinating all email addresses in to @tolist as shown below
Declare @tolist varchar(8000)
set @tolist = ''SELECT @tolist = @tolist + ';' + COALESCE(email, '')
FROM awc_register
WHERE DATEDIFF(day, date_expiry, GETDATE())='3'print @tolist
set @tolist = substring(@tolist, 2, len(@tolist))
and I passed this @tolist to another procedure which should send mails for email addresses present in @tolist variable.
Problem is I need to send the mail to each email address separatley.( not bulkly)
Thanks,
View 1 Replies
View Related
