Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Search results for query: *

  1. JustATheory

    Need to removed

    Greetings, I have a column that displays weight, I need to strip out "lb" and convert it to a numeric or decimal (3,1). the column needs to be averaged, all is fine until the conversion, and I keep getting an error. Msg 8115, Level 16, State 8, Line 96 Arithmetic overflow error converting...
  2. JustATheory

    Hyperlinks with difference address refernces

    Greetings, I a macro that creates worksheets based on a list it also creates a list of hyperlinks to each worksheet and a hyperlink in the worksheet back to the Table of Contents. I don’t want the hyperlink in each worksheet refer to Cell A1. I want each worksheet’s hyperlink to tie back to the...
  3. JustATheory

    Convert Date of Birth to Age, results a negative number

    Greetings, I'm able to convert a birthdate to age and all is fine except for years in the early 1900s, 1912 - 1920 for instance. I need to correct this, what do I need to add to the query string? All help is greatly appreciated! Thanks, JustATheory
  4. JustATheory

    max value based on two columns

    Greetings, I'm stuck on a simple query to find max values, I'm looking for the max number in col1 based on the values in col2. I keep getting all values. I thought it was as simple as: select max(col1), col2 from table; I've tried subqueries with a similar result. Help is always greatly...
  5. JustATheory

    Concatenate with parentheses around a column

    I have a concatenation problem. I can get the left parentheses to display but not the right. Here is what should happen, if column 2 is populated the concatenate col1 with parens around col2. If col2 is null then concatenate col1 with parens around a dash. It functions properly for placing...
  6. JustATheory

    SQL Server 2008 Query to poulate from previous record

    Greetings, I’m working with SQL Server 2008 and need to know if there is a way to populate a field with the value from the same field in the previous record? In the table below I need to populate the empty fields with the value in the previous records: Record 2 would also be SQL, Records 4...
  7. JustATheory

    SQL Server 2008 errors during export to Excel

    Greetings, The Export wizard was working fine until I realized that the Excel file limits were at the old 66k records instead of the 1million available in 2007. I needed to add Office 2007 compatibility components and now it errors and stops the export completely. The files are of the 2007...
  8. JustATheory

    Large table receives 802 error Insufficient Memory in Buffer Pool

    Greetings, I'm running SQL Server 2005, I have a database with one large table and I continue to receive this error (802) on simple queries. I'm asking for all fields there are only 8, by a date range, like 1 week and it bombs. It has over 700 million records, it resides on a 2TB eSATA...
  9. JustATheory

    database tagged suspect error 926

    Greetings, I'm running a stand alone version of SQL Server 2005, and was setting up an index when, the database was taged suspect with an error 926. How is the best way to remove this tag, is it possible to do this without resorting from backup? Any help is greatly appreciated. Thanks...
  10. JustATheory

    SQL Pivot query using case instead of PIVOT

    Greetings, Is it possible to use CASE to create a two column pivot, PIVOT is not an option with the database I'm working with. I need the count of Txn and the sum of Amount. Is it possible without Union All? Original Table Account Txn Amount 123 111 $10.00 456 222 $15.00 789 333 $20.00 234...
  11. JustATheory

    Query to remove non-numeric charcters

    Greetings, I need to remove all non-numeric characters from a field using an SQL query, the remaining characters will be numeric. I'm creating a new table with this process and was wondering if there is quick way to do this, possible a funcction. I did something like this using VBA years...
  12. JustATheory

    Timestamp DataType

    Greetings, For other data types I can run this query no problem, I’m having trouble with the “timestamp” data type. I’m looking for the record between max and min values based on timestamp value; there will always be one record between the two. For date_time I’ll write a having clause: Having...
  13. JustATheory

    max, min, total of a record

    Is there a way to run a total, max or min on a record? Account Total Val1 Val2 Val3 123 10 0 10 0 456 3 2 0 1 789 4 2 2 0 The Total column is correct but I want to identify records that have multiple entries for that total. I would identify records 2 and 3. I was thinking running a max or...
  14. JustATheory

    Pivot Query Columns to Records

    Greetings, Is there a way to do a simple pivot query to list columns as records. Here is my example of table and result table OriginalTable CatA CatB CatC Vol 1 2 3 Vol 2 3 4 Vol 5 6 7 ResultTable Vol CatA 6 CatB 9 CatC 18 Thanks in advance, JustATheory
  15. JustATheory

    Rank Account Transaction by Year and Week

    Greetings, I have a table that lists Account and Date of Transaction, and I need to add two columns YearRank that lists sequentially each time a transaction occurs by account by year, and then it's rank within a week’s timeframe. I did this years ago in Access and can’t find the code, as I...
  16. JustATheory

    Count of Count subquery

    Hi, I need to do a count of a count field in one query, it's just not happening. Basic idea: Table Account State 123 CA 456 CA 789 HI 122 WA Selct State, count(state)CNT from Table Group by STATE; Result: State CNT CA 2 HI 1 WA 1 What I...
  17. JustATheory

    sum(case when regexp_instr(...)

    Greetings, I'm having trouble with this expression: sum(case when regexp_instr(FieldName, 'Text') > 0 then 1 else 0 end) as NewFieldName I want to read FieldName and sum the count of times 'Text' shows up and place that value in NewFieldName. 'Text' can show up in the string 0-n times, in...
  18. JustATheory

    Excel VBA Mutually Exclusive cells in a row dynamic

    Greetings, I've written code for mutually exclusive cells in a row, but they are static and only in one row. I need to have this work for all rows in a range, and would like it to be dynamic so that adding a column wouldn't be an issue. Has anyone done this? Any help is welcomed. Thanks, Andy
  19. JustATheory

    Select a Generic Range VBA Excel 2007

    I have this piece of code that fills in blanks within a range and I'd like it to be generic so that I can add it to a button. I'd like it to run for the current worksheet and the current range, I'm stuck on that part. Thanks, Andy For Each c In Worksheets("Worksheet").Range("FillBlankTop"...
  20. JustATheory

    VBA Excel 2007 and MS-Query

    Greetings, In 2003, I was able to record a query and place it within a macro and it ran everytime. In 2007, I get an error 9, due to an .ODBC connection. Is there something in 2007, that I should switch on or off. It also records much more information, is there an area to switch to a...

Part and Inventory Search

Back
Top