help with query 03-19-10sql 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 changesProcess: 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) #2I 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 queryWe 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 queryWe 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 workI 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 fileI 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 ReportOk, 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 textIs 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 DatesHello,
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 tampdbHi, 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 queryNeed 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 ExcelI'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 recognizedQuery 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 reportHello,
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 utilityRecently 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 QI 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...