How Do I Query For Particular Criteria In Every Field In The Db?
I need to query a table for a record only if there is a certain text string in ANY OF THE TABLE'S fields. I know of using the WHERE clause with logical OR (e.g. - WHERE field1 = 'xyz' or field2 = 'xyz' or ...) but there is a limit of 40 expressions with an Access database, and I have 102 fields to look through! That's my dillema. I know I may need to go to MSSQL or Oracle, but I'd rather stay with Access. Is there a syntactical way to express the query I need? Or do I have to look somewhere other than Access for my database needs? Thanks for the advice!
View Complete Forum Thread with Replies
Related Forum Messages:
Need Help: Count A Field Based On Field Criteria
Hi, I can't seem to get my head around this, hopefully someone can help. In my query I have a few fields, which I would like to get the number of rows based on certain criteria. For example I have a gender field, and I would like to get the number of females or males from the query. I have been trying the following method, which uses the Sum and Iif functions: Code Block Sum(IIf(Fields!gender.Value = "Female", 1, 0)) This does bring back a figure, but it's not correct. My query brings back 46 rows, yet the expression only brings back a figure of 24. I have even tried doing a query using CASE statements, bringing back just one row with all the correct figures in. But if I then add a textbox, with the expression '=Fields!gender.Value' it also brings back 24, yet the fields actual value in the query is 46. What is causing this problem? Is there a way around this? Thanks
View Replies !
Select Same Field Twice For Different Criteria
I need a little insight on how to select the same field from the same table, but for different criteria. here are example tables... Categories CATSUBCATNAME 10MainTitle 11SubTitle #1 12SubTitle #2 20Section 21Section #1 DataTable CATSUBCATINFO 11Detail Information for subtitle #1 12Detail information for subtitle #2 desired result would be: MainTitle, SubTitle #1, Detail Information for subtitle #1 MainTitle, SubTitle #2, Detail Information for subtitle #2 Select c1.Name, c2.Name, d.info from DataTable d, Categories c1, Categories c2 where c1.CAT = d.CAT and c2.CAT = d.CAT and c2.SUBCAT = d.SUBCAT
View Replies !
Criteria; Search Any Word In Field
I am trying to set up a query that will allow the user to input a string, and the search will match ANY word in that string. Currently, I have it configured so that the search will only match the exact string that the user inputs. I have google searched for the answer, but no luck yet. Any ideas?
View Replies !
WHERE Clause Not Picking Up 2nd Field Criteria
Hello, I have this SP that works, except I need to add another field value for the WHERE clause. As you can see I have "WM" but I need to add "PR", and those two are definitely in the table field. I've tried a variety of syntax arrangements using the AND operator, the OR operator, the & operator, just a comma between the two, nothing between the two. Can someone please show me what I'm doing wrong. It fileters for "WM" fine, but I also need it to filter in the WHERE clause for "PR". Here is the SP: CREATE procedure spDemoSchedule (@beginDate varchar(10), @endDate varchar(10), @storeNum int) AS SELECT Progstats.[Program#], Progstats.KCKOFF, Progstats.ProgramName, Progstats.Parent, Store.[Str#], Store.Status, Progstats.Dept, Store.[Program#], Product.[Item#], Product.[Item] FROM Progstats INNER JOIN Product ON Progstats.[Program#] = Product.[Program#] INNER JOIN Store ON Progstats.[Program#] = Store.[Program#] WHERE Progstats.KCKOFF BETWEEN @beginDate AND @endDate AND Store.[Str#]=@storeNum AND Progstats.CLASS="WM" GO TIA, Bruce Wexler
View Replies !
Looking For Field Name From Table On Select Criteria
Hari writes "Sub:- looking for Field name from table on select criteria dear friends I have an table [assosories] which have 100 fields. initial 10 fields have some date, varchar, int types rest 90 are bool type. for a single row I need to know the field name which have true vaules in rest 90 fields I need to insert this field name into another table as a row. Thanks HARI"
View Replies !
Count Same Field Mutiple Times With Different Criteria
Is it possible to count the same field with different criteria. It would be something like this. car_table car_id car_name car_brand So you would execute a statement which would count(car_brand) with two different criteria. I am not sure if this is possible or if there is another way to approach it.
View Replies !
Getting Details From A Row That Satisfies A Group Criteria In A Different Field
I know only enough SQL to be dangerous, but I'm stumped by this general task. I need to know the method for selecting info from several fields of the records that satisfy a group function criteria on one of the fields. Example: Table contains Employees, products, amounts. If I group on Employee, and product, I can find the total amount of each product's sales for that employee. create view prod_sales_by_emplyoee select employee product SUM(amount) as empl_prod_total from sales group by employee, product But if I want to know which product made each employee the most money, I don't know how to do that, because the MAX function works on each field individually, not by row. From the above view, (or a table created from it) I want to know how to identify, for each employee, the product they made the most money selling, and what proportion of their total sales that product accounted for. Then, I'd like to use those results to eliminate the top tier, and find their second place product, etc. I can do this in Access by sorting the table produced from the above view by empl_prod_total in decending order, joining that to a view that groups by employee only and provides a total, then using a grouped query on the joined data, I can use the "FIRST" function to find the values in each field of the record with the highest sales for that employee. However SQL doesn't have the FIRST function in its aggregate functions. I suspect there's a standard way SQL does this, but it wasn't in my class. HELP!
View Replies !
Prompt For User Input In Criteria Field Of View
In Access, I use [Enter Date] in the Criteria field of the Query. I tried the same thing in SQL Server in the Criteria field of the View and it does not recognize this. Is there a comparable command in SQL to get user input into the Criteria field of a view?
View Replies !
Inflated Number Of Records Returned When Adding Bit Field Criteria
When querying a bit field, I am encountering a problem with MS SQLServer returning a larger number of records for a table than theactual number of records that exist within that table.For example, my customer table has 1 million unique records, so theresults of the following query are as such:select count(customer_nbr) from customer = 1,000,000There is bit field in the customer table that denotes whether acustomer has placed an order with us called. That flag is calledorder_flagIf I run the following query:select count(customer_nbr) from customer where order_flag = 1The result is 3,000,000 records.There is no logical way that this is possible, as my table onlycontains 1,000,000 unique records and the number of customers with anorder should be a subset of this.If a run the above query with a distinct before customer number, I getthe results I want:select count(distinct customer_nbr) from customer where order_flag = 1600,000 records.So while I can get to the answer I want, I have no idea why I amreturning incorrect values if I don't select distinct.Can anyone help? I checked microsoft support and message boards buthaven't seen anything.I should note that the bit field is indexed.I am not sure if that isthe problem or not.
View Replies !
Best Practice Question: JOIN Criteria Vs. WHERE Criteria
For example, consider the following queries: DECLARE @SomeParam INT SET @SomeParam = 44 SELECT * FROM TableA A JOIN TableB B ON A.PrimaryKeyID = B.ForeignKeyID WHERE B.SomeParamColumn = @SomeParam SELECT * FROM TableA A JOIN TableB B ON A.PrimaryKeyID = B.ForeignKeyID AND B.SomeParamColumn = @SomeParam Both of these queries return the same result set, but the first query filters the results in the WHERE clause whereas the the second query filters the results in the JOIN criteria. Once upon a time a DBA told me that I should always use the syntax of the first query (WHERE clause). Is there any truth to this, and if so, why? Thanks.
View Replies !
Using Results From One Query As Criteria For Next
I have a database with some over normalized tables in it. The best I can do with one query is get the file ID. In the second query I want to get all the file names, based on all the fileID's I got from the first query. How would I go about doing this?
View Replies !
Query Criteria Format
I have a text box that is used to submit stock symbols that are to be saved in a sql table. The symbols are to be separated by a space or a comma (I don't know which, yet). I want to retrieve the symbols later to be used in a query, but I don't know how to get the symbols in the proper string format for the query, eg The symbols are stored in the tables as: A B C D The query string criteria would look like: IN('A', 'B', 'C', 'D') The IN('A', 'B', 'C', 'D') citeria would be the values in the @Symbol variable in this SPROC SELECT a_Name_Symbol.Symbol, a_Financials.Revenue FROM a_Financials INNER JOIN a_Name_Symbol ON a_Financials.Symbol = a_Name_Symbol.Symbol WHERE (a_Name_Symbol.Symbol @Symbol) ORDER BY a_Name_Symbol.Symbol Is there a slick (ie easy) way to change the contents entered in the text box (A B C D) into IN('A', 'B', 'C', 'D') ? Thanks, Paul
View Replies !
Criteria Search Query
I have four criterias in my .aspx page. They are "First Name, Last Name, Title, Year". I have a Book and an Author table. The Author table would contain all the author's information and the Book table contains all the books information such as title, publisher, subject, and so on. So here's what I'm trying to do. I want to write a transaction statement that will query the four criterias above if the criteria textbox is not black. So for example, when the user click the submit button, all the four criteria fields are filled except Year. That means the query would search the Author and Book tables for "First Name, Last Name, Title" but not "Year" for any potential matches. I also wanted to use "Like" instead of "=" for a wider search. Actually I'm trying to create a store procedure that will accept those four criterias and search the tables based on those criterias. Any help is appreciated.
View Replies !
Slow Query Due To Criteria
I have a query in access that is running extremely slow and I'm trying to find a better way to write it. It appears to be the criteria statement that is causing the lag. Is there a better way to write this? And unfortunately I have to keep it in access. query: SELECT DISTINCT "JBC" AS ClientCode, PaymentDetail.PatientNumber, Procedures.CaseNumber, IIf(IsNull([TicketNumber]),Procedures.patientnumber & Year(Procedures.dateofservice) & Month(Procedures.dateofservice) & Day(Procedures.dateofservice),Procedures.ticketnumber) AS ClaimNumber, PaymentDetail.PaymentCounter, PaymentDetail.TransAmount, Payments.PaymentDate, PaymentDetail.AccountingDate, PaymentDetail.TransDate, payments.PaymentType & "-" & Adjustments.adjustmentcode AS CombinedPmtType, Payments.PaymentType, Payments.PaymentCode, Adjustments.AdjustmentCode, PaymentCodes.BriefDescription, PaymentCodes.LongDescription, Payments.CarrierCode, Payments.Remarks, Procedures.ProcedureCode, Procedures.DateOfService, PaymentDetail.DetailCounter FROM ((((PaymentDetail LEFT JOIN Procedures ON (PaymentDetail.PaymentCounter = Procedures.Counter) AND (PaymentDetail.AccountingDate = Procedures.AccountingDate) AND (PaymentDetail.PatientNumber = Procedures.PatientNumber)) LEFT JOIN Payments ON (PaymentDetail.TransDate = Payments.AccountingDate) AND (PaymentDetail.TransCounter = Payments.Counter) AND (PaymentDetail.PatientNumber = Payments.PatientNumber)) LEFT JOIN PaymentCodes ON Payments.PaymentCode = PaymentCodes.PaymentCode) LEFT JOIN Adjustments ON (PaymentDetail.TransDate = Adjustments.AccountingDate) AND (PaymentDetail.PatientNumber = Adjustments.PatientNumber)) LEFT JOIN AdjustmentCodes ON Adjustments.AdjustmentCode = AdjustmentCodes.AdjustmentCode WHERE (((Procedures.CaseNumber)=0) AND ((PaymentDetail.TransAmount)<>0) AND ((Payments.PaymentType) Is Null) AND ((PaymentDetail.DetailCounter)=2)) OR (((Procedures.CaseNumber)=0) AND ((PaymentDetail.TransAmount)<>0) AND ((Payments.PaymentType) Is Null) AND ((PaymentDetail.DetailCounter)=5) AND ((AdjustmentCodes.InsuranceIndicator)="N"));
View Replies !
Query Criteria / Parameter For A Whole Project
I have a large collection of 'projects', i.e., RS reports for each of our customers. Each project is effectivley a library of maybe up to 15 reports. They are usually modified slightly according to the customer's requirements. Most of the reports use a variery of SQL tables / views and to ensure the customer only sees theor data. I am currently changing the query criteria for each report in the project each time I deply a new library / set. This is time consuming and open to the risk that I mis-key the criteria for one or more report. The criteria is always the customer's account number and is common across most of the database tables. E.g: WHERE (LIVE_INVOICESUMMARY_CRREPONLY.IS_SUCODE = 'DH166'... Is there any easy way to set this criteria at the project level, rather than at individual report level? Can it be done by getting the SQL query to refer to something in the custom code? I know I can use parameters, but again, these have to be set per individual report. I'm on SQL2000
View Replies !
Update Data To A Table From The Sum Of A Field From Another Table Based On Some Criteria
Hello Friends, I have two tables, And also I have Sample data in them. create table X (y int, m int, v int) insert into X select 2007,1,5 insert into X select 2007,1,3 insert into X select 2007,2,9 insert into X select 2007,2,1 select * from X Create table Y (fy int, fm int, v int) insert into Y select 2007,1,0 insert into Y select 2007,2,0 insert into Y select 2007,3,0 select * from X select * from Y I want to update the Table Y with the Sum of the Fields V from X based on the Criteria Y.fy = X.y and Y.fm = X.m Using temporary table cannot be done. Thanks in Advance, Babz
View Replies !
Access Query Against SQL Server Works Only Without Criteria
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 Replies !
Can’t Understand The Query Execution Interval, For Every One Criteria It Takes Extra 60 Seconds. Help Please?,
Can€™t understand the Query execution interval, for every one criteria it takes extra 60 seconds. Help please?, Query A SELECT TOP 15 * FROM CMD4 AS T1 WHERE T1.ST IN (SELECT max(T2.ST) FROM CMD4 AS T2 WHERE T1.SERIAL=T2.SERIAL AND T2.sd='20060817') AND sd='20060817' --AND STOCKNAME like 'A%' ORDER BY D_CUM desc; The above query take 2 seconds to return result Query B SELECT TOP 15 * FROM CMD4 AS T1 WHERE T1.ST IN (SELECT max(T2.ST) FROM CMD4 AS T2 WHERE T1.SERIAL=T2.SERIAL AND T2.sd='20060817') AND sd='20060817' AND STOCKNAME like 'A%' ORDER BY D_CUM desc This query same as above but " AND STOCKNAME like 'A%'" Criteria only added but it takes 01 minute 43 seconds to return result. why it is like that where i need to adjust the query i have no idea. please help
View Replies !
SQL Select Query Need For Following Criteria. Please Help, Retrieve Records With Independent Price And Its Total Volume Per Min
Time Price Volume 090048 510 6749 090122 510 2101 090135 510 1000 090204 505 2840 090213 505 220 090222 505 1260 090232 505 850 090242 505 200 090253 510 1200 090313 510 570 090343 510 250 090353 510 160 Criteria Retrieve records with independent price and its total volume per minute SELECT SUBSTRING(st,1,4) AS Ttime,d_price AS Price,SUM(l_cum) AS Volume FROM cmd4 WHERE sd='20060717' AND serial='0455' GROUP BY SUBSTRING(st,1,4),d_price,l_cum Result of the above query: - Time Price Volume 0900 510 6749 0901 510 1000 0901 510 2101 0902 505 200 0902 505 220 0902 505 850 0902 505 1260 0902 505 2840 0902 510 1200 0903 510 160 0903 510 250 0903 510 570 THE FOLLOWING RESULT STILL NOT A TOTAL FOR A MINUTE] E.G 0901 510 1000 + 0901 510 2101 = 0901 510 3101 <- I NEED THIS Can any one advice or give me tips over this. Please
View Replies !
Max Of Date Field In Query
I have a table with date fielde.g. SrNo Date 1 1-MAR-2008 2 3-Mar-2008 3 7-Mar-2008 4 10-Mar-2008 5 15-Mar-2008 I need a query to find out Max date which is less than '8-Mar-2008' . i.e i need single output '7-Mar-2008'anybody helpThanks
View Replies !
How To Split The Field In The Sql Query?
I have the field LocationID (string)which has values like "AZ001","AZ002","IN002","IN004" first 2 will be always alphabets and remaining 3 will be numbers, I want to split it like "AZ" ," 001" "AZ","002" "IN" "002" "IN" "004" now i will populate dropdownlist with unique values of "AZ" "IN" according to first dropdownlist how i will populate second dropdownlist? So how to write sql query for splitting? and then populating the dropdownlist ?
View Replies !
SQL DateTime Field Query
I was wondering if someone could help me here with some reporting I'm trying to do. For website visits, I currently use getdate() to have SQL insert the date and time of the visit in the table. I was wondering, is there a way to grab just the month from the field? I would like to chart this and I need to group the visits by month. I'm not sure how I would go about filtering just the month out of the entire date and time fields.
View Replies !
Empty Field Query
Hi Friends, I have one query that i have to Replace the Empty Value of a filed with some other value how can i do in SQL?? ID Phone Name 1 122 abc 2 xyz 3 444 mmm 4 525 ccc 5 nvb Now i want replace the blank (Empty) filed with some charaters Numbers how can i do that?? any Help Ashu
View Replies !
Use Calculated Field In Same Query
Right now I have one view that grabs records and sums up related records etc.... and returns a result. So basically it has the ID number and the number I calculated. THen I have another view that takes that number and performs calculations on it into three different columns. Is there any way to make these two view into one without a lot of repetative statements? Here is an example: SELECT (tblTest.Quantity * tblTest.Price) as SubTotal, SubTotal * 1.06 as Total Obviously that doesn't work, but what could I do to get that basic thing to work? Thanks!
View Replies !
SQL Query: Missing Field Value
Suppose in a table, there is some data in a field: 1, 2, 3, 5, 6, 8, 9. I need a sql query which will list the missing numbers: 4,7 (Missing digits) Query: Select * From table1 field ------ 1 2 3 5 6 8 9 Expected query:?? filed1 ------ 4 7 Please help me to get a query which will provide my expected data. Thanks. /Fakhrul(mfhossain@gmail.com)
View Replies !
Query Detail As A Field.
I have two tables(Order and OrderDetail) of master-detail relationship. I have a nchar field in the detail table called itemno. I want to query like: Select Order.OrderNo, Order.Date, SUM(OrderDetail.ItemNo) as ItemNos,.... From Order Inner Join OrderDetail on Order.OrderID=OrderDetail.OrderID Where Order.OrderID=10 so that the resulting field ItemNos will become a string in format "ItemNo01, ItemNo02, ItemNo03,...." How can I write this query in T-SQL? Thanks
View Replies !
Really Simple Query Needs Another Field
I have written this query in SQL select * from wce_contact where (Mail3Date <= '2007-09-25') AND NOT (Mail1Date is NULL ) AND NOT (Mail2Date is NULL ) AND (Mail4Date is NULL ) AND (Mail5Date is NULL ) AND (Mail6Date is NULL ) AND (Mail7Date is NULL ) AND (Mail8Date is NULL ) AND (Mail9Date is NULL ) AND (Mail10Date is NULL ) AND (Mail11Date is NULL ) AND (Mail12Date is NULL ) AND (Mail13Date is NULL ) AND (Mail14Date is NULL ) AND (Mail15Date is NULL ) AND (Mail16Date is NULL) Little bit of background info - We have a database of over 500k websites with email addresses. We use email marketing to contact these sites and everytime they get mailed i need to fill in the MailDate field. Obv Mail5Date means they have been emailed 5 times etc The above qry is trying to find all contacts that have been mailed 3 times and havent been mailed in the last 42 days. I also have an IDStatus field that allows me not to use the contacts that have opted out our mailing or website shutdown etc I try adding this to the bottom of the query AND (IDstatus is Null) But the query runs and finds 0 contacts which i know isnt the case, as when i use our front end database application it produces results. I was wondering if anybody could possibly help me? Thanks!
View Replies !
Sort On Field Constructed In Query
I want to sort on a field that I construct during a query....how can I solve this?Basically what i am doing is selecting mediafiles with comments in descending order. (so mediafile with most comments on top)Here's my (not working) query, because it throws the error that the mediaComments column is unknown....SELECT *FROM(select ROW_NUMBER() OVER (ORDER BY mediaComments DESC) as RowNum,m.title,m.usercode,mediaComments=(select count(*) from MediaComments where mediaid=m.mediaid)FROM Media m WHERE m.usercode>0group by m.title,m.usercode) as InfoWHERE RowNum between @startRowIndex AND (@startRowIndex + @maximumRows-1)
View Replies !
Query To Sum The Same Field Twice In The Select Statement
Hello friends , I have table (MoneyTrans) with following structure [Id] [bigint] NOT NULL, [TransDate] [smalldatetime] NOT NULL, [TransName] [varchar](30) NOT NULL, -- CAN have values 'Deposit' / 'WithDraw' [Amount] [money] NOT NULL I need to write a query to generate following output Trans Date, total deposits, total withdrawls, closing balance i.e. Trans Date, sum(amount) for TransName='Deposit' and Date=TransDate , sum(amount) for TransName=Withdraw and Date=TransDate , Closing balance (Sum of deposit - sum of withdraw for date < = TransDate ) I am working on this for past two days with out getting a right solution. Any help is appreciated Sara
View Replies !
DISTINCT SELECT Query With More Than One Field
Hello, I want to do a DISTINCT SELECT query with more than one field, for example a ID field with a Type field, as if both fields make the primary key, like (ID 1 ,Type 1) ,( ID 1, Type 2) and (ID 2, Type 1) is ok but not (ID 1, Type1) and (ID 1,Type 1) if its not possible to do a distinct with more than one then what other techniques are possible to get the duplicate data out. the reason why I want to use distinct is that I can use that query to export that data to where both of these fields make the primary key. Thanks in advance
View Replies !
SQL Query - Search Field For String
Hi, I have a SQL server 2005 database with a series of multiple fields. One of the fields has a array of strings seperated by semi-colons like so: Red;Green;Blue My question is, how can i run a query on all of the fields that have the value of say Green in it. Note that these values vary in different order and numbers. Thanks
View Replies !
Help! - Query On Time Field In SQL Server - How?
Hi,I have an orders database and I need to be able to write a querythat groups sales by:1. Date2. Time of day (both am and pm)I currently have a date/time field named "Submitted" that storesjust the date.However, how do I store the time?I know I can store the date/time in the "Submitted" field Icreated. However, because of the time being stored in that field, Ican't execute an easy query such as:select * from database where submitted = '2/10/05'(reason being, the time portion makes each "Submitted" fieldrecord unique, so just the date won't be recognized)I realize there probably is a much better way to do this in theSQL query language that I am not aware of.Two questions:1. Based on what I'm trying to do -- query based on date and groupby number of sales for each hour of the day (am and pm) -- what is thebest approach to store the time?2. What does the query look like that querys by date and groupsorders by time of day (am and pm)?Thanks for your help.J
View Replies !
How To Query By Keyword For Image Field.
If the data type of field is "varchar",we can use "like" to query if it hassome substring.Such as "where custom.valuevariant like '%Verizon%' ", it will query out allrecords that contains string "Verizon".But how to do when data type of field custom.valuevariant is "image"?Thanks
View Replies !
Referencing A Calculated Field In A Query
I would have a question, if it is possible to reference a calculated field in T-SQL query. The following expression does not work in SQLExpress 2005 SELECT Qty, UnitPrice, Tax, Qty*UnitPrice as Expr1, Expr1*(1.0 + Tax) AS Expr2 This problem has occurred after upsizing to SQLExpress from Access XP/Jet Engine. Since Access does not have any issue with the expression above, the SQLExpress does not even accept it. The only way how to avoid the issue in the SQLExpress seems probably to be - Duplicate some calculations (i.e. expand every expression with duplicating some mathematic operations with some performance loss) - Utilize computed columns if possible (no idea on performance impact here) Am I right or is there any other way how to reference a calculated field? Any suggestion is greatly welcomed! Thanks in advance.
View Replies !
Help - Updating A Field In Query Analyzer
Hi All, I'm trying to create a script that updates a field in a table, based on data in another table. It should be simple, but I'm doing something wrong. Here's the code: USE DBMyDatabase UPDATE TblToBeUpdated SET IDField=TblOther.IDNew WHERE IDField=TblOther.IDOld SELECT Pk, IDField FROM TblToBeUpdated What am I doing wrong? The error code I get is: Server: Msg 107, Level 16, State 3, Line 1 The column prefix 'TblOther' does not match with a table name or alias name used in the query. Server: Msg 107, Level 16, State 1, Line 1 The column prefix 'TblOther' does not match with a table name or alias name used in the query. Thanks. Henry
View Replies !
Query/select The First 10 Characters In A Field
I'm comparing two tables and need to compare the first 8 characters of one field in table A to the first 8 characters in another field in table B. So instead of where 'John Smith' = 'John Smith' it would compare where 'John Smi' = 'John Smi' I know I've done this before but can't find a good reference. It's something like this when calling a query within a WHILE loop: SELECT * FROM blah WHERE FID = '".$row['FID']."' AND LEFT('TRACK', 8) = LEFT('".$row['TRACK']."',8) Am I close? Any help would be appreciated.
View Replies !
Query To See If An Int Field Starts With A Certain Number
How would I write a query on a table containing a column of ints, where I want to retrieve the rows where that int value starts with a number? I know that you can do this with strings by using "....WHERE thisfield LIKE ('123%')", but if 'thisfield' is an int, how would I do this? Thanks!
View Replies !
Need Help With Update Query To Populate New Field
Hi folks: I have added a new field to an established table, and am having trouble figuring out how to populate its values: Two tables are involved: Jobs and Parts There is a one-to-one relationship between each JobID and its PartID Each Part has a PartPrice. Now I have added to the Jobs table a JobPrice field. Whenever a new Job is created, JobPrice takes the current value of its Part's PartPrice. Each Job's JobPrice remains constant for historical purposes, while the PartPrice may fluctuate at my client's whim. The trouble is that the Jobs table is 10k+ records large, and I need to fill the JobPrice values. I am at a loss. I know how to commit the update one record at a time: UPDATE Jobs SET JobPrice = (SELECT PartPrice FROM Parts WHERE (PartID = [the part in question])) WHERE (JobID = [the job in question]) My SQL knowledge is limited to basic statements that I use in my .NET work, and I rarely create anything in Management Studio more elaborate than what you see above. Many thanks for your time, Matt
View Replies !
How Do I Use A Parameter In A Dataset Field Value Using An MDX Query?
Let me simplify. The value of my dataset field Dimension_1 is [SOB Year].[Yr Id].[Yr Id].[MEMBER_CAPTION] I want to use parameters for SOB Year and Yr Id like I do on the query. But if I use ="["& Parameters!dimension_1_table.Value &"].["& Parameters!dimension_1_column.Value &"].[" & ["& Parameters!dimension_1_column.Value &"]" it returns null for the values. I don't know how to make these parameters I am passing in parameters for the table and column to build an MDX query here is my query. ="SELECT NON EMPTY { [Measures].[Liability Amt] } ON COLUMNS, NON EMPTY { (["& Parameters!dimension_1_table.Value &"].["& Parameters!dimension_1_column.Value &"].["& Parameters!dimension_1_column.Value &"].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM [SOB Clean] CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS" Anyone know what value I need to use on the dataset field to access the column? Thanks, Roger
View Replies !
SQL Query Not Returning Identity Field - Sometimes
Hello, I have a C# application that adds records to a SQL Server database using a query something like this one: INSERT INTO table_name (first_name, last_name, date_added) ('john', 'smith', '1/1/2005 12:00:00pm') ; SELECT SCOPE_IDENTITY() AS [Scope_Identity] This works fine unless there's already a John Smith in the database. When that happens, Scope_Identity is null even though the date_added is different. About half the time the record is added even though Scope_Identity is null. I've added code to notify me when this happens, but it's a pain in the neck to re-run my import utility for individual records. (The table I'm adding to does have a autonumbered key field) Thanks in advance!
View Replies !
UPDATE Query On Partial Field
How would I accomplish update just a part of a field? I have 500+ records in a table that got written as \share 01234.jpg, \share 01235.jpg...\share 01734.jpg but the records should have ended with .tif instead. Now the application cannot find the correct image because the extension is incorrect. How can I use the Update statement to just change the extension on these records? Thanks.
View Replies !
Condition On Query Created Field
Hi all, i have a query as below that creates a count in the field Total. I wanted to then be able to say only show me where there are more than 2 incidents. But i can't get it to recognise my created field. Any advice? Thanks. select a.vchcompanyname, count(*) as total from company a inner join incident b on b.iownerid = a.icompanyid where b.iincidentcategory = 1 and a.icompanytypecode = 102165 --and total > 2 (Wont recognise Total) group by b.iownerid, a.icompanyid, a.vchcompanyname order by 2 desc
View Replies !
Increment An ID Field In Sql Query String
Hello.. can anyone help me with this query string? String SQL = "INSERT Employee(Employee ID, UserName, JobRole, Department, Level, Email)(SELECT max(EmployeeID) + 1 FROM Employee) AS Employee ID, VALUES(EmployeeID, '" + newUserName + "', '" + newJobRole + "', '" + newDept + "', '" + newLevel + "', '" + newEmail + "')"; I am trying to insert values into a table, but i have an Employee ID field, which needs incrementing. How can i do this through my SQL query string? Is this possible? As it can't accept a NULL value. Thanks, Sandy
View Replies !
|
TRACKER
