Reply:

help with query 03-19-10
sql server 2008 sp1 I can't seem to get this query right. I need a way to read a table and for each person, show the person's highest value and the person's last entered value to get a result set like this person_id high last ------------- ----- ----- 1 40 20 2 30 15 here is a sample schema create table test (person_id int, enter_value int, enter_date datetime) insert into test values (1, 30, '2001-01-01') insert into test values (1, 40, '2002-01-01') insert into test values (1, 10, '...

How do I add a field to a query and make a calculation?
I have a database with fees paid field and in a query I need to add a 15% rebate if the student has paid the fee already. On the query in the criteria line for the fee paid field I have entered the criteria >0 to select students that have already paid. If they have paid already they get a 15% discount. I then tried to create a new field called [REBATE] and entered in the criteria section [Fee Paid]*0.15 to get the answer but I ma getting nothing showing. Any suggestions please? Terry Hollands -- Hello Terry. "Terry Hollands" wrote > I have a database w...

Combo box needs to change query when other combox changes
Process: After selecting the first combo box, the second combo box need to be populated with a particular query based on info from the first. 1st Combo box: Type (there are 4 different types) 2nd Combo box: Query need to change based on Type (there are 4 queries). What you are describing is called Cascading Combo Boxes. This link should help: http://www.fontstuff.com/access/acctut10.htm MDS wrote: >Process: After selecting the first combo box, the second combo box need to be >populated with a particular query based on info from the first. > >1st Combo box: Type (there are 4 di...

HELP! How to extract information in inbox/sentbox into a text file
Can any one tell me how to extract the information of new mails in m inbox/sentbox, including the sender name, message subject and messag time, into a text file ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com ...

Extracting the data according the number of cell (at specific range) #2
I have a minor Excel formula problem. In my example has following data, cell A5 shows "apple", A6 shows "salt", A7 shows "sugar" and A8 shows "fish". On another hand, cell B3 is the "criteria" data showing 3. My question is to form an Excel formula which can extract the data from cell "A1" and then extract the 3rd data, which is "sugar" (that is the result I want). Althought the formula is counting from A1, but the excel formula know the data after "apple" and "salt" is "sugar" (cell fro...

Alias query
We are using the Microsoft POP3 Connector under SBS2003. I have set the default account for undelivereble mail to me (eg mike@domain.co.uk) I have set up a user (eg account name fred) and his e-mail address is fred.user@domain.co.uk. I also set him up with an alias of info@domain.co.uk (by adding a second smtp address). For some reason, the mails sent to fred.user@domain.co.uk were getting through to his mailbox, but those to info@domain.co.uk came through to me. I also set up 2 more aliases - fred@domain.co.uk and anyone@domain.co.uk - fred@ works OK, but anyone@ get's bounced a...

Createdon Timezone query
We are utilising CRM to send auto updates on open CRM cases and also recording these updates within the CRM case, as part of this we are including the 'createdon' entry. The only problem is that this generates an entry that lists the following: Created on: 10/10/2007 4:20 AM (GMT-08:00) Pacific Time (US & Canada); Tijuana Now the case was actually created 10/10/2007 PM (GMT +08:00) Perth. The servers are set correctly, the Users PC is set correctly, and the timestamp for the actual record is set correctly as: 10/10/2007 7:20 PM by System, Administrator As we are using the ...

help with a query... 08-17-10
I need to create a complex query which gets data from 3 SQL tables. Table 1 - Department Dept_Id (int) primary key Dept_Name (vchar) Include_Unavailable (bit) Table 2 - Employee Employ_Id (int) primary key Dept_Id (int) foreign key Employ_Name (vchar) Table 3 - Schedule Sched_Id (int) primary key Employ_Id (int) foreign key Date (datetime) My query is as follows: SELECT Department.Dept_Name, Employee.Employ_Name FROM Department, Employee, Schedule WHERE ?? In my query I want all departments and employees listed who work on a particular date....

I don't understanding what makes this query work
I inherited a .mdb that has no documentation. One query contains a number of fields, one, "Last Name" is sorted in ascending order and has for criteria <"iq". I do not understand where 'iq' comes from. The query breaks a list of names into 2 parts, 11 names and another query that uses >"iq" produces the remaining 12 names. The purpose of the query is to print a specific report containing those names (and related data). The report for the 2 separate queries is different, hence the need to break it at that point. If I remove the criteria then the...

Save DB query results as XML file
I am trying to query a SQL Server database, retrieve the results as XML, and save them to a file. I was trying to use the SqlXmlCommand, SqlXmlCommandType.XPath and an xsd to query the database, and load the results into a XmlTextReader. This works fine. But then I get stuck. I don't want to DO anything with the data besides putting it into a file. Should I be loading the results into a stream, instead? Then load the stream into a string, to an XmlDocument, then .Save(...)? Or might a XmlTextWriter have a role in here somewhere? Can anyone give me a hint on how I should appr...

Variable In Query For Report
Ok, what a subject title. :-) I am not sure what to call it, but here is what I want to do.... Query: AllOrders, about 9 fields, and 1 of them is determining what to pull out. It uses the BoatID (numeric) to get a list of all orders. Example: BoatId=5 Report: AllOrdersByBoat; pulling data from the query AllOrders. Problem: If I want to print all orders for BoatId=9, I have to go change the query to BoatId=9, then resave it. Then print the report. We are trying to go away from the Query for every boat strategy we first implemented. Right now there is a query for each and every boat ...

Extract Date from line of text
Is there any way I can extract a date from a cell containing a line o text??? Eg: A1: "16-02-04-Received & Completed" How can I extract the date "16-02-04" & display it in Cell B1?? -- Message posted from http://www.ExcelForum.com Hi if your date is always at the beginning of the string and always in the format "DD-MM-YY" try the following in B1 =LEFT(A1,8) -- Regards Frank Kabel Frankfurt, Germany > Is there any way I can extract a date from a cell containing a line of > text??? > > Eg: > A1: "16-02-04-Received & Completed&...

Querying Dates
Hello, I have a query with information in it and then one of the fields is a date field. I would like to further query it so that it only takes the information for the people that are before a certain date. Everything after that date I dont want. For ex: name address date Jane Doe Whatever St. 12/3/08 Doe Jane Who Knows St 12/15/09 I would like to quere out the people that have a date before 12/10/09. I tried when I do my query to just put under the date field <12/10/09, but that didnt work for some reason. It's seems sim...

sp_send_dbmail with @query from tampdb
Hi, I have written the followng statement in order to send all rows from a temp table stored in the tempdb area. When I run this I get an error message that says "Invalid object name 'tempdb.dbo.EmailDataCollector". My goal here is to send via email the rows of this table to various people. The rows are to be embedded in the body of the email. I can't figure out why this is not working and any help would be appreciated. DECLARE @Msg2 VARCHAR(5000) EXEC hbe01.msdb.dbo.sp_send_dbmail @profile_name = 'SQL_Admin', @recipients = 'xxxx@xxx.com&#...

Local Data Queries Stored? Client or Server?
I have 3 complex queries I need to deploy to 35 users. After creating the 5th one from scratch, I figured the data's gotta be stored somewhere! I looked around in the registry and couldn't find any clues. Where are the local data queries stored? I'm thinking I could copy the queries (update for any unique GUIDs, of course). Thanks. It is stored on server -- Regards, MS CRM Certified Professional http://microsoftcrm3.blogspot.com Chat with me on MSN / Gmail / Skype : ID Is :.. mscrmexpert@gmail.com "Avogadro" wrote: > I have 3 complex queries I need to ...

Shared Workbook Query
Hi, I have set up a shared workbook for 20+ users. I am aware that share workbooks in Excel (I have version 2002) have limited functionality an that you cannot for example merge cells. However, if there are merged cells in another worksheet and someon wants to copy across text (which includes merged cells) into the share workbook it removes the merged formatting. At present I have to take the share off the workbook and then cop across the data (which includes merged cells) then reshare the workboo so that the formatting is maintained. Is there any other way around this problem so that peo...

How format data to add zeros to alpha numeric data in query
Need to add zeros to data like MCRD-472 to show MCRD-00472 and like NBC-2 to show NBC-00002 and like NBC-M14 to show NBC-00M14. To Query text box. If YourField always has a dash which you want to follow with filler zeros then this -- Left([YourField], InStr([YourField], "-")-1) & Right("00000" & Mid([YourField], InStr([YourField], "-")+1) -- Build a little, test a little. "CS" wrote: > Need to add zeros to data like MCRD-472 to show MCRD-00472 and like NBC-2 to > show NBC-00002 and like NBC-M14 to show NBC-00M14...

Extract data (not in table) from Word to Excel
I'm trying to find a way to tag free text in Word and then auto extract all the tagged text into an Excel spreadsheet. Does anyone have any suggestions? Tag the text by bolding it. Go to Edit, Replace and replace bold text with Find What Text/// (the field shows ^&///). Remove text that is not bold by replacing bold text with nothing. Replace /// with paragraph marks. Change Bold to Not Bold. Copy what you have left to Excel. If there is already bold text in the document you can choose another font that is not in the document, or you can use Marching Black Ants. "hellokit...

Query Def not recognized
Query Def not recognized Hello, Using Access =9203=85 I=92ve created a qdf in VBA and I=92d like to use it as part of another query=85 My problem, is that the qdf is not being recognized; i.e., when the query runs, I get a parameter box asking for a value? Here=92s some of my code: =91=92=92=92=92=92=92=92=92=92=92=92=92=92=92=92 Set dbs =3D CurrentDb Dim strQDFname As String strQDFname =3D "myQryDef" 'query def name With dbs Set qdf =3D .CreateQueryDef(strQDFname, strQDFsql) qdf.Close End With Set qdf =3D Nothing =...

Using a query as base for report
Hello, I have created a table where I run a query picking the columns I want in the query. From the query, I create a report. My question is this: Whenever I open the report, I want it to show only the open items. Example: My query has 10 columns, when ALL the fields are populated, the record is complete and does not need to be in the report (i.e. open items report). If the 10 fields are NOT all completed, it's still considered opened and will be on the report when it is run. I think I'm thinking too much about it and making it sound harder to myself. Can you please...

Query Problem!
How would I query this ChargeDate,ChargeItem,ChargeAmount [ChargeDate] >Date()-365 [ChargeItem,ChargeAmount] No Duplicates , 2 fields together No Duplicate Thanks if you can Help On Wed, 15 Aug 2007 17:44:55 +1200, "Bob V" <rjvance@ihug.co.nz> wrote: >How would I query this >ChargeDate,ChargeItem,ChargeAmount >[ChargeDate] >Date()-365 >[ChargeItem,ChargeAmount] No Duplicates , 2 fields together No Duplicate SELECT DISTINCT ChargeItem, ChargeAmount FROM [someunspecifiedtablename] WHERE [ChargeDate] > DateAdd("yyyy", -1, Date()) If you wan...

Query question.
I need to know how to format a query to produce columns of "best fit" width. Specifically in a Xtab query. Please help, Frank What do you mean by 'best fit width'? The width of a column is really a display issue on whatever form or report will show the results. If you are saying you need to know the maximum length of the data in any column of the entire table, you'd need to take a 2-pass approach where the first pass calculates the max length of each column and the second pass formats the data. -- Dorian "Give someone a fish and they eat fo...

Priv.edb query utility
Recently I ran across a utility on technet somewhere that you can run against the priv.edb for exchange 2003, and it can spitback how much of a percentage that each users mailbox is compromised of specific attachment formats, by extension name, not by querying the attachments mime information. It spat back graphical information in pie chart format, to show for example: user 1's mailbox is 15% compromised of mpegs, and avis etc It can also spit back the total % of the overall priv.edb is comprised of 15% specific formats(in our case, movie file formats, mpeg, avi etc). Does anyone...

Excel 2007 & 2003 Data Queries Q
I hope below will make sense. I'm trying to create a Data Query from an External source (using Excel 2007). I've done this many times under Excel 2003. I've managed to pull in the data, but it appears as a table. My Excel 2003 queries were not pulled in like a table. Is this how 2007 does it now? Also when I want to refresh the data, I usually run below Range("A1").Select Selection.QueryTable.Refresh BackgroundQuery:=False But under the Table query in Excel I have to run below Range("I1").Select Selection.ListObject.QueryTable.Refresh Backgro...

Multiple make table queries in one?
I have a single table in my database containing school names, usernames, and password for user accounts. I am trying to find an easy way of exporting out the data into an Excel spreadsheet, using 1 tab for each school. Now, I have the code for a make table query which works perfectly. Example code: SELECT [School Name], [Student ID], [Password] INTO [Excel 8.0;Database=C:\StudentIDs.xls].EDS FROM [DPS STUDENT] WHERE [School Name]="Edison"; SELECT [School Name], [Student ID], [Password] INTO [Excel 8.0;Database=C:\StudentIDs.xls].WOW FROM [DPS STUDENT] WHERE [Sc...