IN SELECT Query Criteria

I think I read somewhere that using an "IN SELECT..." type of query will 
speed up your query performance in Access 2003 better than linking the two 
tables by their common field. Is that correct? If so, what is the proper 
syntax for creating this type of query criteria? 

My scenario is that I have a very long list (1000's) of employees and a 
select group of managers (about 30). I only want the employees whose manager 
is in my table of selected managers.  I should end up with a list of <200.

Is this a good way to use the "IN Select.." as my criteria?
-- 
THX cs
0
Utf
9/26/2007 10:51:01 PM
access.queries 6343 articles. 1 followers. Follow

9 Replies
558 Views

Similar Articles

[PageSpeed] 49

I think you are talking about using a subquery rather than a join?

If so, the information you have been given is incorrect.
A join will almost always execute faster than a subquery in JET.

If you want to pursue subqueries, here's a starting point:
    http://allenbrowne.com/subquery-01.html

-- 
Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Cydney" <Cydney@discussions.microsoft.com> wrote in message
news:B7B6AE5B-C863-41BB-B028-D7BF5459EBA0@microsoft.com...
>I think I read somewhere that using an "IN SELECT..." type of query will
> speed up your query performance in Access 2003 better than linking the two
> tables by their common field. Is that correct? If so, what is the proper
> syntax for creating this type of query criteria?
>
> My scenario is that I have a very long list (1000's) of employees and a
> select group of managers (about 30). I only want the employees whose 
> manager
> is in my table of selected managers.  I should end up with a list of <200.
>
> Is this a good way to use the "IN Select.." as my criteria?
> -- 
> THX cs 

0
Allen
9/27/2007 2:49:24 AM
On Sep 26, 11:51 pm, Cydney <Cyd...@discussions.microsoft.com> wrote:
> I think I read somewhere that using an "IN SELECT..." type of query will
> speed up your query performance in Access 2003 better than linking the two
> tables by their common field. Is that correct? If so, what is the proper
> syntax for creating this type of query criteria?
>
> My scenario is that I have a very long list (1000's) of employees and a
> select group of managers (about 30). I only want the employees whose manager
> is in my table of selected managers.  I should end up with a list of <200.
>
> Is this a good way to use the "IN Select.." as my criteria?

Using northwind: which employees haven't processed an order?

SELECT E1.EmployeeID
FROM Employees AS E1
WHERE NOT EXISTS (
SELECT *
FROM Orders AS O1
WHERE O1.EmployeeID = E1.EmployeeID);

SELECT E1.EmployeeID
FROM Employees AS E1
WHERE E1.EmployeeID NOT IN (
SELECT E1.EmployeeID
FROM Orders AS O1
WHERE O1.EmployeeID = E1.EmployeeID);

SELECT E1.EmployeeID
FROM Employees AS E1 LEFT JOIN Orders AS O1
ON O1.EmployeeID = E1.EmployeeID
WHERE O1.EmployeeID IS NULL;

All three queries are semantically equivalent; a smart optimizer will
recognise this and use the best plan regardless. How Jet handles each
requires detailed knowledge of its optimizer, which I do not possess.
I can tell you that the NOT EXISTS construct *could* outperform the
NOT IN one because it can 'short circuit' as soon as it finds a match
(you only need to find one for EXISTS to return true), as happens with
SQL Server (I read); what Jet *actually* does may not even be
documented. The LEFT JOIN would definitely (I think <g>) be slower in
the event, albeit unlikely, of the columns in the ON clause being
nullable.

Personally, I find the NOT EXISTS the most the intuitive and the LEFT
JOIN one the least intuitive, and that's what I would use to make my
choice. Minimising maintenance time is more important to me than run
time performance, all other things being equal.

In testing the above the LEFT JOIN ran in 11 milliseconds, the other
two in 10 milliseconds. OK, so I'm only using 10 employees and 900
orders i.e. not far off yours. My point is that performance is not a
factor here because I'm only interested in absolute performance rather
than relative performance.

Try googling the exact phase "premature optimization is the root of
all evil".

Jamie.

--


0
Jamie
9/27/2007 11:31:42 AM
Jamie,

I think you missed the point.  He wants to identify those records in table 
A, where the Manager_ID is in Table B.

SELECT tbl_Employees.* 
FROM tbl_Employees
INNER JOIN tbl_Managers
ON tbl_Employees.Manager_ID = tbl_Managers.Manager_ID

Dale
-- 
Email address is not valid.
Please reply to newsgroup only.


"Jamie Collins" wrote:

> On Sep 26, 11:51 pm, Cydney <Cyd...@discussions.microsoft.com> wrote:
> > I think I read somewhere that using an "IN SELECT..." type of query will
> > speed up your query performance in Access 2003 better than linking the two
> > tables by their common field. Is that correct? If so, what is the proper
> > syntax for creating this type of query criteria?
> >
> > My scenario is that I have a very long list (1000's) of employees and a
> > select group of managers (about 30). I only want the employees whose manager
> > is in my table of selected managers.  I should end up with a list of <200.
> >
> > Is this a good way to use the "IN Select.." as my criteria?
> 
> Using northwind: which employees haven't processed an order?
> 
> SELECT E1.EmployeeID
> FROM Employees AS E1
> WHERE NOT EXISTS (
> SELECT *
> FROM Orders AS O1
> WHERE O1.EmployeeID = E1.EmployeeID);
> 
> SELECT E1.EmployeeID
> FROM Employees AS E1
> WHERE E1.EmployeeID NOT IN (
> SELECT E1.EmployeeID
> FROM Orders AS O1
> WHERE O1.EmployeeID = E1.EmployeeID);
> 
> SELECT E1.EmployeeID
> FROM Employees AS E1 LEFT JOIN Orders AS O1
> ON O1.EmployeeID = E1.EmployeeID
> WHERE O1.EmployeeID IS NULL;
> 
> All three queries are semantically equivalent; a smart optimizer will
> recognise this and use the best plan regardless. How Jet handles each
> requires detailed knowledge of its optimizer, which I do not possess.
> I can tell you that the NOT EXISTS construct *could* outperform the
> NOT IN one because it can 'short circuit' as soon as it finds a match
> (you only need to find one for EXISTS to return true), as happens with
> SQL Server (I read); what Jet *actually* does may not even be
> documented. The LEFT JOIN would definitely (I think <g>) be slower in
> the event, albeit unlikely, of the columns in the ON clause being
> nullable.
> 
> Personally, I find the NOT EXISTS the most the intuitive and the LEFT
> JOIN one the least intuitive, and that's what I would use to make my
> choice. Minimising maintenance time is more important to me than run
> time performance, all other things being equal.
> 
> In testing the above the LEFT JOIN ran in 11 milliseconds, the other
> two in 10 milliseconds. OK, so I'm only using 10 employees and 900
> orders i.e. not far off yours. My point is that performance is not a
> factor here because I'm only interested in absolute performance rather
> than relative performance.
> 
> Try googling the exact phase "premature optimization is the root of
> all evil".
> 
> Jamie.
> 
> --
> 
> 
> 
0
Utf
9/27/2007 4:56:02 PM
Thank you for the information (everyone!).

I researched the links and SQL's you all provided and will use the 
old-fashioned "join" instead of the subquery to gather my subset of employees 
in. I just thought maybe I could come up with a better way of resolving the 
query.

However.. in my searching out the answers, I did find a lot of new things to 
ponder too! Thanks! cs


"Allen Browne" wrote:

> I think you are talking about using a subquery rather than a join?
> 
> If so, the information you have been given is incorrect.
> A join will almost always execute faster than a subquery in JET.
> 
> If you want to pursue subqueries, here's a starting point:
>     http://allenbrowne.com/subquery-01.html
> 
> -- 
> Allen Browne - Microsoft MVP.  Perth, Western Australia
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
> 
> "Cydney" <Cydney@discussions.microsoft.com> wrote in message
> news:B7B6AE5B-C863-41BB-B028-D7BF5459EBA0@microsoft.com...
> >I think I read somewhere that using an "IN SELECT..." type of query will
> > speed up your query performance in Access 2003 better than linking the two
> > tables by their common field. Is that correct? If so, what is the proper
> > syntax for creating this type of query criteria?
> >
> > My scenario is that I have a very long list (1000's) of employees and a
> > select group of managers (about 30). I only want the employees whose 
> > manager
> > is in my table of selected managers.  I should end up with a list of <200.
> >
> > Is this a good way to use the "IN Select.." as my criteria?
> > -- 
> > THX cs 
> 
> 
0
Utf
9/27/2007 5:13:01 PM
On Sep 27, 5:56 pm, Dale Fye <dale....@nospam.com> wrote:
> I think you missed the point.  He wants to identify those records in table
> A, where the Manager_ID is in Table B.
>
> SELECT tbl_Employees.*
> FROM tbl_Employees
> INNER JOIN tbl_Managers
> ON tbl_Employees.Manager_ID = tbl_Managers.Manager_ID

["He"? I once knew a Cyndney with a 'C' and it was a derivative of
'Lucinda'.]

The OP asked, "I think I read somewhere that using an "IN SELECT..."
type of query will speed up your query performance in Access 2003
better than linking the two tables by their common field." I think
it's clear they were asking for a comparison of syntax constructs as
regard performance, which is what I delivered, I feel.

Are you saying that in my examples I should have used IN rather than
NOT IN, EXISTS rather than NOT EXISTS, etc? And to think *I* get
accused of nick picking... <g>.

Seriously, in my experience I use NOT EXISTS far more often then
EXISTS, therefore found it easier to dream up a NOT EXISTS example on
the spot, plus the equivalent using a JOIN has more issues because it
requires an OUTER JOIN for which Jet is not SQL-92 compliant hence
handling nullable columns is problematic (see http://tinyurl.com/yunof4).

Jamie.

--



0
Jamie
9/28/2007 7:25:18 AM
Jamie,

I think your examples were a great explaination of the Not Exists and Outer 
Joins to select items from one data set where something doesn't exist in 
another.  I guess I just read the OP differently and assumed that they 
wanted to select from one list where the value is found in the other list. 
Your examples might have been confusing in that context.

Dale

"Jamie Collins" <jamiecollins@xsmail.com> wrote in message 
news:1190964318.892378.273750@y42g2000hsy.googlegroups.com...
> On Sep 27, 5:56 pm, Dale Fye <dale....@nospam.com> wrote:
>> I think you missed the point.  He wants to identify those records in 
>> table
>> A, where the Manager_ID is in Table B.
>>
>> SELECT tbl_Employees.*
>> FROM tbl_Employees
>> INNER JOIN tbl_Managers
>> ON tbl_Employees.Manager_ID = tbl_Managers.Manager_ID
>
> ["He"? I once knew a Cyndney with a 'C' and it was a derivative of
> 'Lucinda'.]
>
> The OP asked, "I think I read somewhere that using an "IN SELECT..."
> type of query will speed up your query performance in Access 2003
> better than linking the two tables by their common field." I think
> it's clear they were asking for a comparison of syntax constructs as
> regard performance, which is what I delivered, I feel.
>
> Are you saying that in my examples I should have used IN rather than
> NOT IN, EXISTS rather than NOT EXISTS, etc? And to think *I* get
> accused of nick picking... <g>.
>
> Seriously, in my experience I use NOT EXISTS far more often then
> EXISTS, therefore found it easier to dream up a NOT EXISTS example on
> the spot, plus the equivalent using a JOIN has more issues because it
> requires an OUTER JOIN for which Jet is not SQL-92 compliant hence
> handling nullable columns is problematic (see http://tinyurl.com/yunof4).
>
> Jamie.
>
> --
>
>
> 


0
Dale
9/28/2007 10:40:16 AM
On Sep 28, 11:40 am, "Dale Fye" <dale....@nospam.com> wrote:
> I guess I just read the OP differently and assumed that they
> wanted to select from one list where the value is found in the other list.

Fair enough :)

Jamie.

--


0
Jamie
9/28/2007 2:51:36 PM
Actually.... You are both correct. 
I was needing to know which syntax performed best. I think Dale responded to 
that appropriately. However, I also was looking for (as Jamie indicated) the 
best way to get a subset of Employees whose manager exists in my smaller list 
of managers. I had thought that using the subquery IN Select... as my 
criteria would help with performance. Your response that using a JOIN is as 
fast if not faster, which answered my question.

I'm sorry, Jamie, but your examples were a bit confusing at first, as Dale 
mentioned, but I understood the result of your findings which led me to (I 
think) make the proper choice anyway. 

Thanks gentlemen.
-- 
THX cs


"Dale Fye" wrote:

> Jamie,
> 
> I think your examples were a great explaination of the Not Exists and Outer 
> Joins to select items from one data set where something doesn't exist in 
> another.  I guess I just read the OP differently and assumed that they 
> wanted to select from one list where the value is found in the other list. 
> Your examples might have been confusing in that context.
> 
> Dale
> 
> "Jamie Collins" <jamiecollins@xsmail.com> wrote in message 
> news:1190964318.892378.273750@y42g2000hsy.googlegroups.com...
> > On Sep 27, 5:56 pm, Dale Fye <dale....@nospam.com> wrote:
> >> I think you missed the point.  He wants to identify those records in 
> >> table
> >> A, where the Manager_ID is in Table B.
> >>
> >> SELECT tbl_Employees.*
> >> FROM tbl_Employees
> >> INNER JOIN tbl_Managers
> >> ON tbl_Employees.Manager_ID = tbl_Managers.Manager_ID
> >
> > ["He"? I once knew a Cyndney with a 'C' and it was a derivative of
> > 'Lucinda'.]
> >
> > The OP asked, "I think I read somewhere that using an "IN SELECT..."
> > type of query will speed up your query performance in Access 2003
> > better than linking the two tables by their common field." I think
> > it's clear they were asking for a comparison of syntax constructs as
> > regard performance, which is what I delivered, I feel.
> >
> > Are you saying that in my examples I should have used IN rather than
> > NOT IN, EXISTS rather than NOT EXISTS, etc? And to think *I* get
> > accused of nick picking... <g>.
> >
> > Seriously, in my experience I use NOT EXISTS far more often then
> > EXISTS, therefore found it easier to dream up a NOT EXISTS example on
> > the spot, plus the equivalent using a JOIN has more issues because it
> > requires an OUTER JOIN for which Jet is not SQL-92 compliant hence
> > handling nullable columns is problematic (see http://tinyurl.com/yunof4).
> >
> > Jamie.
> >
> > --
> >
> >
> > 
> 
> 
> 
0
Utf
9/28/2007 3:25:00 PM
The reference in http://tinyurl.com/yunof4 is right about the non standard 
way Jet handles outer join ON clause implying just one table (or no table), 
but that is *not* a 'problem' for the outer join used as a "not in" 
equivalence. Further more, NOT IN is poorly optimized in Jet, and an outer 
join on the equivalent statement is preferred if runtime execution is of 
some concern.



Vanderghast, Access MVP


"Jamie Collins" <jamiecollins@xsmail.com> wrote in message 
news:1190964318.892378.273750@y42g2000hsy.googlegroups.com...
> On Sep 27, 5:56 pm, Dale Fye <dale....@nospam.com> wrote:
>> I think you missed the point.  He wants to identify those records in 
>> table
>> A, where the Manager_ID is in Table B.
>>
>> SELECT tbl_Employees.*
>> FROM tbl_Employees
>> INNER JOIN tbl_Managers
>> ON tbl_Employees.Manager_ID = tbl_Managers.Manager_ID
>
> ["He"? I once knew a Cyndney with a 'C' and it was a derivative of
> 'Lucinda'.]
>
> The OP asked, "I think I read somewhere that using an "IN SELECT..."
> type of query will speed up your query performance in Access 2003
> better than linking the two tables by their common field." I think
> it's clear they were asking for a comparison of syntax constructs as
> regard performance, which is what I delivered, I feel.
>
> Are you saying that in my examples I should have used IN rather than
> NOT IN, EXISTS rather than NOT EXISTS, etc? And to think *I* get
> accused of nick picking... <g>.
>
> Seriously, in my experience I use NOT EXISTS far more often then
> EXISTS, therefore found it easier to dream up a NOT EXISTS example on
> the spot, plus the equivalent using a JOIN has more issues because it
> requires an OUTER JOIN for which Jet is not SQL-92 compliant hence
> handling nullable columns is problematic (see http://tinyurl.com/yunof4).
>
> Jamie.
>
> --
>
>
> 


0
Michel
9/28/2007 5:02:55 PM
Reply:

Similar Artilces:

Query-based list w/1 Exception
I've created a query based distribtuion list which works fine yet out of the 1000 or so mailboxes it gives me I'd like to exclude 10. Is there a way? (Basically I have checked only "Users with Exchange mailbox") Make those 10 recipients members of a distribution group, and add the following to the query: (&(your current query)(!memberOf=distinguished Name of the distribution group)) Alternatively, if there's a common value of a particular attribute like location/city/department, et al, use that to exclude them. -- Bharat Suneja MVP - Exchange www.zenprise.com ...

Run report on multi selected records.
I try to make a report for a custom entity that works like the Account Summary: let's say that it's Customer's Inquiry Summary for entity Customer's Inquiry I made the report so that I'm able to run the report on Customer's Inquiry Form. But when I select multi records in the list of Customer's Inquiry and run report, only one record is show in one page of the the report. I would like to have the selected records appear in the report with each record on each page. How can I do this? ...

Combo box list selection based on earlier selection by user
HI, I have a form that has a combo box 1 (Product) that reads a the Product table and has a unique id attached to each product (RO#). There is a second combo box 2 that is RO#. Now what I would like to do is when a user selects a Product in combo box 1, the system should lookup the Product selected from combo box 1 and upload the corresponding RO# in the combo box 2 list. Now I am using both combo boxes as Unbound and in the combo box 2 I have the two columns Product and RO# - in the Criteria row of RO# I have the following: Like IIf([Forms]![xa_f_lookup]![RO#]=" ",[Forms]![x...

Can't select objects in Excel 2007
I'm trying to create a form in Excel 2007, and although I can manipulate, copy & paste controls (check boxes, etc) I can't select multiple objects to align them, etc. I've checked on-line help (and have followed the instructions), and other websites, but no one seems to have indicated a problem. It is extremely cumbersome and I need a solution. Any ideas? Thanks in advance, Jim Berglund I know this is about a year too late, but it may help others who have stumbled across this page looking for similar help. On the Home tab, in the Editing group, click Find & Select....

Lot Selection not Showing when I select PO on Receiving Entry
Usually, the Lot selection screen would pop up when I select a PO at the Line Detail of the Receivings Entry, now it does not! And GP will let items post without a lot number! It seems as if a setting has changed. Any ideas anyone? Any help is appreciated. Thanks, R Are you sure the item is marked to track lots? This wiould be set item by item on the Item Maintenance Options window. -- Richard L. Whaley Author / Consultant / MVP 2006-2008 Documentation for Software Users Get our Free Tips and Tricks Newsletter and check out our books at http://www.AccoladePublications.com &q...

simple query not working
Hi: Trying to set up a simple query, and can't figure out why its not working. Hope someone can help me think a little more clearly. Trying to link two tables: 1)tblServices, with fields 'ClientID#', 'DateofSvc', 'SvcCode' 2)tblCodes, with fields 'SvcCode', and 'SvcDescription' Join is 'SvcCode' Query design is to show ClientID, DateofSvc, SvcCode, and SvcDescription. SQL is "SELECT tblSERVICES.[ClientID#], tblSERVICES.DateofSvc, tblCodes.SvcCode, tblCodes.Description FROM tblSERVICES INNER JOIN tblCodes ON tblSERVICES.SvcCode = tblCodes...

Sumif with two criteria #2
I need to add column c if column a and column b both meet my criteria. example A B C 1 X Y 3 2 S Y 1 3 X Y 2 4 X F 3 5 F Y 3 X AND Y 5 On Thu, 30 Oct 2003 13:06:29 -0800, "danperez@joannstores.com" <anonymous@discussions.microsoft.com> wrote: >I need to add column c if column a and column b both meet >my criteria. > >example > A B C >1 X Y 3 >2 S Y 1 >3 X Y 2 >4 X F 3 >5 F ...

Select contact then hangs
Hi, please help. I have a user that when she selects a contact using To: in a new email, after selecting 2, it closes outlook??. Spec is W2kSP4\office2k with all windows updates and office updates installed. Profile has been deleted and re-created, plus i have set up personal folders and moved contacts into there, and set that folder as a default, but makes no difference. Please help. ...

Selecting rows from various sheets
Hi all, I have several sheets with a few columns in each. One colum in each sheet has a unique value. Most of these unique values are th same in each sheet but not all. Is there any way that I can set up new sheet that will have the unique value in column 1 and th corresponding column values frommeach sheet beside it. For example Sheet 1 aa 1 1 1 bb 2 2 2 cc 3 3 3 Sheet 2 aa 4 4 cc 5 5 Result aa 1 1 1 4 4 bb 2 2 2 cc 3 3 3 5 5 Thanks alot, Sha -- sha ----------------------------------------------------------------------- shav's Profile: http...

null value pass from form into query
For some reason I can't seem to get this to work and was hoping posting here would speed up finding the resolution. I have a form that the users a specifying a value from based on a list box. The users press a button on the form after doing the selection and it executes a query with criteria based on the value from the field; the query is using [Forms]![frmBatchesNeedingIndexing]![Contract].[Value] as the critera. This works fine when a users specifies a value. The problem I'm experiencing is I need this list box to have the capability to have a null value passed to the que...

select text in Calendar
I'm working on a calendar in Publisher. I'm adding text into a cell by spacing down 1 point, then typing in 8 points. Doing fine until April. At that time, I can only select cells (whole days). It will not let me select the text. Help is no help. Any hints out there. Did I accidentally select something to do this? Bonnie Have you added a picture to the cell or a shape? If you have send it to the back, make the cell no fill and try again. -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "GoBonnieGo" <GoB...

macro to filter data based on selected data
Hi, I am looking for a macro that requests input of a reference code and then a date and then filters the data in my spreadsheet based on this criteria. I am using excel 2007. Any help with this will be greatly appreciated. You can probably record a macro while doing it manually and then clean it up and add input boxes to ask the question or input your variable in a cell and use that. If all else fails, If desired, send your file to my address below. I will only look if: 1. You send a copy of this message on an inserted sheet 2. You give me the newsgroup and the ...

Link select with a blast
I have Win 7 Home Premium, x64. Quite often, when I select a link, a mouse pointer changes to a palm with a blast. I understand, the palm is the standard mouse pointer for link select, but what is the blast? What it indicates? -- Ilya Zeldes Fort Myers, Florida Hi Ilya, I suspect that this is being caused by a tracking program. Run something like Ad-Aware and see if it indicates a tracking company. If so, you can delete it, however, it might prevent you from accessing that particular website. Dave "Ilya Zeldes" wrote in message news:7A6FF9B1-5512-44...

Counting number of records based on criteria
Hello, (Try again to get the question in the google-groups. Perhaps not pushed on the send button). For counting records as expression in a report in ms.access I'm using =count(*) which shows all records. Now I also want to know how many of them has registered a specific data in a specific field. I'm trying to use =count([Field1]="999") to count how many of the selection reported has registered data 999. This option isn't work. Can somebody give me the correct solution. thanks, Johan The 999 needs to go in the criteria for the query driving the...

Select Case in a Change Event
Hi, I am trying to use a worksheet Change Event to trap changes to specific columns. depending on which column is chnaged by the user, the value in another ciolumn will be altered; so, changes in either column O or P will update column Q, changes in either column T or U will update column V and changes in column Z or AA will update column AB. What I can't figure out is the Select Case bit (where I've got the shouted question below). Here's what I've put together, any comments would be much appreciated. Private Sub Worksheet_Change(ByVal Target As Range) ...

Select Records based another table...
This may be very elementary, but i can't wrap my brain around it. I have tried to dummy it down as much as i can for myself, with plans of adapting it into something working. I have three tables that look like the following: Tbl 1: ProdID A B C 1 $1 $2 $3 2 $4 $5 $6 3 $7 $8 $9 Tbl 2: RmNum ProdID 1 3 1 2 2 1 2 2 Tbl 3: RmNum Style 1 A 2 C Relationships are: [tbl1 ProdID] ------ [tbl2 ProdID] [tbl2 RmNum] ...

Multi-Select List Box Does Not Retain Selected Items
I created an Outlook Form in 2003. But when I select items in the list box they do not remain select when I select another control on the form. Any suggestions? Did you bind the list box to a keywords field? -- Sue Mosher, Outlook MVP Author of Microsoft Outlook Programming - Jumpstart for Administrators, Power Users, and Developers http://www.outlookcode.com/jumpstart.aspx "kaykay" <anonymous@discussions.microsoft.com> wrote in message news:276dd01c4638d$bd5af590$a501280a@phx.gbl... > I created an Outlook Form in 2003. But when I select > items i...

Controling queries displayed when clicking Load from Query button?
I want to be able to control the queries that are displayed when the Load from Query toolbar button is displayed. Can anyone tell me how the filter box list gets populated? Thanks. Paco ...

VBA
I am very new to VBA and access and have a question regarding a SELECT statement. I am going to run a select (recordset - so I can loop through and process each record) but on my SELECT I want to make sure I return at least 20 rows, otherwise I am going to change my WHERE clause to include more rows. Is there an easy way to do this or do I need to just do an inital SELECT COUNT(*) and then decide which WHERE clause to use?? Do you have an example I can look at? Thank you in advance! AJ On Fri, 14 Dec 2007 17:14:00 -0800, AJ <AJ@discussions.microsoft.com> wrote: I would not first ...

column query
Greetings I am trying to return a value in a column of a table based on a value in a cell in the same row but different column. For example, column A is a list of names, column B is a list of scores. In a separate area i want to display the highest score with the name of the person that achieved it. I've tried using MAX to display the highest score but cannot think of a way to show who this value relates to in column A. Does this make sense? Many thanks for any help Assuming: col A = Names, col B = scores data from row2 down and there's *no ties* for the maximum score If you h...

Fetch the query in the report
Dear Friends, Do we get any technical or functional information regarding the system generated reports. I've one more doubt in MS CRM 3.0, when we create account and Opportunity entities in the system, it automatically creates the reports like Account Distribution and Account Overview, actually I want that which query is getting executed in the report. If anyone have acheive this please help me if possible. With Regards, Jeetu T. Hi Jeetu, These are predefined reports shipped with MS CRM 3.0. You would get more information in MOC or you can Google for it. Answer to your question, the...

Drop down list quicker selection
We are using Excel 2003 We created a drop down list using the Data\Validation process, which works fine. When the arrow within a cell is clicked on to show the drop down list, how can you quickly select one of the many names in the list just by typing the first character of a name within the list. At the moment, we have to scroll up and down the list to select a particular name, which in itself isn't a problem, but slows down the process of finding the right name. Can the names within the list be alphabetically grouped when inputting the first character from the keyboard, and spee...

Selected fields to update
I need to have the ability, in OL'07, when I try to copy in a contact and am told there is a duplicate, to be able to select which fields I want updated and which not. Is that possible built-in or add-in? -- Boris You may find this of interest - ContactGenie DataPorter 2.0 - specifically handles updating of pre-existing contacts allowing you to define which fields you want to import/update from your input data file (custom forms/fields supported). Features: http://www.contactgenie.com/cg20features.htm Download" http://www.contactgenie.com/cg20dlorder.htm Karl _...

Change Form/Report Query Based on User Selection
I have a form and report based on a query. I need to change the query based on what the user selects on a form. Using VBA in MS Access 2002, can I change a single field in an existing query from GROUP BY to LAST? This process would be initiated by a click event on a form. Thanks in advance for any help. I would say only if you are willing to construct the entire query string in VBA and then apply that string as the source of the report. John Spencer Access MVP 2002-2005, 2007-2009 The Hilltop Institute University of Maryland Baltimore County Wayne wrote: > I have a form ...

How do I find the currently selected Scenario?
Hi, Maybe this is a simple thing, but I don't seem to be able to track it down. I have an Excel sheet with a couple of Scenarios defined. I am trying to implement some VBA functions where I need to know which Scenario is currently selected. Does anyone know how I determine the active Scenario from VBA? Any help will be greatly appreciated. Thanks in advance. Best regards. hi, (?) > Maybe this is a simple thing, but I don't seem to be able to track it down. > I have an Excel sheet with a couple of Scenarios defined. > I am trying to implement some VBA functi...