Question on anatomy of a query

I was looking at an article by Plamen Ratchev (which I had printed a while 
ago but can't find it online anymore) where he states that the first thing a 
query does if there are 2 tables involved is create a Cartesian Product 
(cross Join) between each table. Then the ON filter is applied.

If Table A has 20,000,000 rows and Table B has 30,000,000 rows - the system 
would read all the rows and put all the combinations together to get 
600,000,000,000,000 rows?  Then apply the ON filter???

Where would it put the data it just read?

Does it read all this into a temporary table in memory?

Then when it applies the ON predicate, does it them move the rows that match 
to another temporary table?

And if an outer join, move the other rows to the 2nd temporary table?

Thanks,

Tom


0
tshad
8/11/2010 4:55:35 AM
sqlserver.programming 1873 articles. 0 followers. Follow

15 Replies
1180 Views

Similar Articles

[PageSpeed] 54

tshad (tfs@dslextreme.com) writes:
> I was looking at an article by Plamen Ratchev (which I had printed a
> while ago but can't find it online anymore) where he states that the
> first thing a query does if there are 2 tables involved is create a
> Cartesian Product (cross Join) between each table. Then the ON filter is
> applied. 
> 
> If Table A has 20,000,000 rows and Table B has 30,000,000 rows - the
> system would read all the rows and put all the combinations together to
> get 600,000,000,000,000 rows?  Then apply the ON filter??? 
 
That is what would happen if there wasn't an optimizer.

Keep in mind that SQL is a *declarative* language. You tell what want. The 
DBMS figures out how to perform the operation in the most efficient manner.

But in order to get you what you want, you need to understand the rules
the computer works according to, and those are the rules than Plamen 
describes. 

The optimizer is free to recast the computation order as long as the result 
is guaranteed to be the same.


-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
0
Erland
8/11/2010 7:46:49 AM
You're probably talking about the following article: 
http://pratchev.blogspot.com/2007/05/anatomy-of-query.html .

In this article, Platmen make first the explicit note that this is the 
logical processing of a query and that it doesn't necessarily reflect it's 
physical processing.

-- 
Sylvain Lafontaine, ing.
MVP - Access
Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
Independent consultant and remote programming for Access and SQL-Server 
(French)


"tshad" <tfs@dslextreme.com> wrote in message 
news:%23KfoxFROLHA.4424@TK2MSFTNGP04.phx.gbl...
>I was looking at an article by Plamen Ratchev (which I had printed a while 
>ago but can't find it online anymore) where he states that the first thing 
>a query does if there are 2 tables involved is create a Cartesian Product 
>(cross Join) between each table. Then the ON filter is applied.
>
> If Table A has 20,000,000 rows and Table B has 30,000,000 rows - the 
> system would read all the rows and put all the combinations together to 
> get 600,000,000,000,000 rows?  Then apply the ON filter???
>
> Where would it put the data it just read?
>
> Does it read all this into a temporary table in memory?
>
> Then when it applies the ON predicate, does it them move the rows that 
> match to another temporary table?
>
> And if an outer join, move the other rows to the 2nd temporary table?
>
> Thanks,
>
> Tom
>
> 


0
Sylvain
8/11/2010 3:10:41 PM
"Sylvain Lafontaine" <sylvainlafontaine2009@yahoo.ca> wrote in message 
news:eKpqgdWOLHA.4120@TK2MSFTNGP02.phx.gbl...
> You're probably talking about the following article: 
> http://pratchev.blogspot.com/2007/05/anatomy-of-query.html .
>
Actually it is only the first part of the article.

The one I had copied was much longer with examples.

One of the examples had to do placing a filter as part of the ON clause or 
as part of the WHERE clause and that it makes a difference in an outer join, 
which I assume would not matter in an inner join.

> In this article, Platmen make first the explicit note that this is the 
> logical processing of a query and that it doesn't necessarily reflect it's 
> physical processing.

But that is what is confusing.  Why would you say that logically you put all 
the combinations together and then take the ones that match.  Logically, I 
would think you would say that both tables are read and only the ones that 
match are put into a temporary table.

Just curious.  I like the article but that part kind of threw me.

Thanks,

Tom

>
> -- 
> Sylvain Lafontaine, ing.
> MVP - Access
> Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
> Independent consultant and remote programming for Access and SQL-Server 
> (French)
>
>
> "tshad" <tfs@dslextreme.com> wrote in message 
> news:%23KfoxFROLHA.4424@TK2MSFTNGP04.phx.gbl...
>>I was looking at an article by Plamen Ratchev (which I had printed a while 
>>ago but can't find it online anymore) where he states that the first thing 
>>a query does if there are 2 tables involved is create a Cartesian Product 
>>(cross Join) between each table. Then the ON filter is applied.
>>
>> If Table A has 20,000,000 rows and Table B has 30,000,000 rows - the 
>> system would read all the rows and put all the combinations together to 
>> get 600,000,000,000,000 rows?  Then apply the ON filter???
>>
>> Where would it put the data it just read?
>>
>> Does it read all this into a temporary table in memory?
>>
>> Then when it applies the ON predicate, does it them move the rows that 
>> match to another temporary table?
>>
>> And if an outer join, move the other rows to the 2nd temporary table?
>>
>> Thanks,
>>
>> Tom
>>
>>
>
> 


0
tshad
8/11/2010 4:10:56 PM
"Erland Sommarskog" <esquel@sommarskog.se> wrote in message 
news:Xns9DD1637D0EA91Yazorman@127.0.0.1...
> tshad (tfs@dslextreme.com) writes:
>> I was looking at an article by Plamen Ratchev (which I had printed a
>> while ago but can't find it online anymore) where he states that the
>> first thing a query does if there are 2 tables involved is create a
>> Cartesian Product (cross Join) between each table. Then the ON filter is
>> applied.
>>
>> If Table A has 20,000,000 rows and Table B has 30,000,000 rows - the
>> system would read all the rows and put all the combinations together to
>> get 600,000,000,000,000 rows?  Then apply the ON filter???
>
> That is what would happen if there wasn't an optimizer.
>
Why?

Why wouldn't it just read both tables and take the rows that match the ON 
filter?

> Keep in mind that SQL is a *declarative* language. You tell what want. The
> DBMS figures out how to perform the operation in the most efficient 
> manner.
>
> But in order to get you what you want, you need to understand the rules
> the computer works according to, and those are the rules than Plamen
> describes.
>
> The optimizer is free to recast the computation order as long as the 
> result
> is guaranteed to be the same.
>

I agree that that is what happens which is why it is confusing when thinking 
about how joins work.  I have always been told that you do the joins left to 
right and that each join becomes the left condition of the next join.

For example,

Select *
From tableA a
join tableB b on a.id = b.id
join tablec c on b.id = c.id
join tabled d on c.id = d.id
where something.

In this case, a is joined with b, b is joined with the result of a and b, d 
is joined with the results of a and b and c.

If the optimizer changed the order this is done then couldn't you possibly 
have a different result, especially if dealing with left joins or there is 
multiple criteria in one of the joins.

Thanks,

Tom

>
> -- 
> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
>
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx 


0
tshad
8/11/2010 4:18:17 PM
When you have more of a background, you might want to read some ANSI/
ISO Standards. They are dull, boring and complicated and in a strange
language we called "Standard-speak" when i was on the committee. One
of the technical terms is "effectively" which means that we are about
to describe a way to do something (like that CROSS JOIN thing) which
will have some effect. But any process that produces that same result
is just as good.

I work out a full SELECT statement in SQL FOR SMARTIES as per the
formal process just to show how this could be done.
0
CELKO
8/11/2010 6:00:32 PM
This is important when you want to understand the old ANSI syntax for the 
Outer Join: *= and =*.

Contrary to what many people think, *= is not identical with a Left Join and 
=* is not identical with a Right Join.  If you have a single *= or =* in 
your query then yes, remplacing it with a modern Left or Right Join will 
give you the same result.  However, if you have more than one then replacing 
them with their corresponding Left or Right Join can give a vastly different 
result.

This is because all the Cross Join are done before all the *= and =* are 
applied at the same time in a single operation after that.  In the case of 
the modern Left and Right Join, all the joins are performed consecutively 
(one after the other); so when you have more than one join, in many cases, 
you'll get a different result between a combination of multiples *= and/or 
=* in comparaison to a combination of the modern Left and Right join.

In the case of modern outer joins, there is no need to go into this logical 
application of a Cross Join but in the case of the older Ansi outer joins, 
yes, it is.

-- 
Sylvain Lafontaine, ing.
MVP - Access
Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
Independent consultant and remote programming for Access and SQL-Server 
(French)


"tshad" <tfs@dslextreme.com> wrote in message 
news:e4QbH$WOLHA.4424@TK2MSFTNGP04.phx.gbl...
>
> "Sylvain Lafontaine" <sylvainlafontaine2009@yahoo.ca> wrote in message 
> news:eKpqgdWOLHA.4120@TK2MSFTNGP02.phx.gbl...
>> You're probably talking about the following article: 
>> http://pratchev.blogspot.com/2007/05/anatomy-of-query.html .
>>
> Actually it is only the first part of the article.
>
> The one I had copied was much longer with examples.
>
> One of the examples had to do placing a filter as part of the ON clause or 
> as part of the WHERE clause and that it makes a difference in an outer 
> join, which I assume would not matter in an inner join.
>
>> In this article, Platmen make first the explicit note that this is the 
>> logical processing of a query and that it doesn't necessarily reflect 
>> it's physical processing.
>
> But that is what is confusing.  Why would you say that logically you put 
> all the combinations together and then take the ones that match. 
> Logically, I would think you would say that both tables are read and only 
> the ones that match are put into a temporary table.
>
> Just curious.  I like the article but that part kind of threw me.
>
> Thanks,
>
> Tom
>
>>
>> -- 
>> Sylvain Lafontaine, ing.
>> MVP - Access
>> Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
>> Independent consultant and remote programming for Access and SQL-Server 
>> (French)
>>
>>
>> "tshad" <tfs@dslextreme.com> wrote in message 
>> news:%23KfoxFROLHA.4424@TK2MSFTNGP04.phx.gbl...
>>>I was looking at an article by Plamen Ratchev (which I had printed a 
>>>while ago but can't find it online anymore) where he states that the 
>>>first thing a query does if there are 2 tables involved is create a 
>>>Cartesian Product (cross Join) between each table. Then the ON filter is 
>>>applied.
>>>
>>> If Table A has 20,000,000 rows and Table B has 30,000,000 rows - the 
>>> system would read all the rows and put all the combinations together to 
>>> get 600,000,000,000,000 rows?  Then apply the ON filter???
>>>
>>> Where would it put the data it just read?
>>>
>>> Does it read all this into a temporary table in memory?
>>>
>>> Then when it applies the ON predicate, does it them move the rows that 
>>> match to another temporary table?
>>>
>>> And if an outer join, move the other rows to the 2nd temporary table?
>>>
>>> Thanks,
>>>
>>> Tom
>>>
>>>
>>
>>
>
> 


0
Sylvain
8/11/2010 6:03:06 PM
tshad (tfs@dslextreme.com) writes:
> Why?
> 
> Why wouldn't it just read both tables and take the rows that match the ON 
> filter?
 
But how it read them? How would it know which fits together? Logically the
only way to do this is to create all combinations and then filter.

Oh, in practice the optimizer does it another way, but here is the matter
of finding a neutral description.

But as Celko points out, the SQL standards are written in a very formalistic
language, and they are defintely not trivial to understand.

-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

0
Erland
8/11/2010 9:18:28 PM
"Erland Sommarskog" <esquel@sommarskog.se> wrote in message 
news:Xns9DD1ED1996428Yazorman@127.0.0.1...
> tshad (tfs@dslextreme.com) writes:
>> Why?
>>
>> Why wouldn't it just read both tables and take the rows that match the ON
>> filter?
>
> But how it read them? How would it know which fits together? Logically the
> only way to do this is to create all combinations and then filter.
>

Makes sense.

Thanks,

Tom

> Oh, in practice the optimizer does it another way, but here is the matter
> of finding a neutral description.
>
> But as Celko points out, the SQL standards are written in a very 
> formalistic
> language, and they are defintely not trivial to understand.
>
> -- 
> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
>
> Links for SQL Server Books Online:
> SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
> SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
> SQL 2000: 
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
> 


0
tshad
8/12/2010 12:01:48 AM
"--CELKO--" <jcelko212@earthlink.net> wrote in message 
news:696d9d0a-8610-4272-8013-bc885429afaf@x21g2000yqa.googlegroups.com...
> When you have more of a background, you might want to read some ANSI/
> ISO Standards. They are dull, boring and complicated and in a strange
> language we called "Standard-speak" when i was on the committee. One
> of the technical terms is "effectively" which means that we are about
> to describe a way to do something (like that CROSS JOIN thing) which
> will have some effect. But any process that produces that same result
> is just as good.
>
> I work out a full SELECT statement in SQL FOR SMARTIES as per the
> formal process just to show how this could be done.

I'll have to check that out when I get home.

Thanks,

Tom 


0
tshad
8/12/2010 12:03:27 AM
And what about the question of the JOINS:

I agree that that is what happens which is why it is confusing when thinking
about how joins work.  I have always been told that you do the joins left to
right and that each join becomes the left condition of the next join.

For example,

Select *
From tableA a
join tableB b on a.id = b.id
join tablec c on b.id = c.id
join tabled d on c.id = d.id
where something.

In this case, a is joined with b, b is joined with the result of a and b, d
is joined with the results of a and b and c.

If the optimizer changed the order this is done then you could possibly
have a different result, especially if dealing with left joins or there is
multiple criteria in one of the joins.

Thanks,

To
"tshad" <tfs@dslextreme.com> wrote in message 
news:%23Yn7AGbOLHA.5700@TK2MSFTNGP04.phx.gbl...
>
> "Erland Sommarskog" <esquel@sommarskog.se> wrote in message 
> news:Xns9DD1ED1996428Yazorman@127.0.0.1...
>> tshad (tfs@dslextreme.com) writes:
>>> Why?
>>>
>>> Why wouldn't it just read both tables and take the rows that match the 
>>> ON
>>> filter?
>>
>> But how it read them? How would it know which fits together? Logically 
>> the
>> only way to do this is to create all combinations and then filter.
>>
>
> Makes sense.
>
> Thanks,
>
> Tom
>
>> Oh, in practice the optimizer does it another way, but here is the matter
>> of finding a neutral description.
>>
>> But as Celko points out, the SQL standards are written in a very 
>> formalistic
>> language, and they are defintely not trivial to understand.
>>
>> -- 
>> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
>>
>> Links for SQL Server Books Online:
>> SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
>> SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
>> SQL 2000: 
>> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>>
>
> 


0
tshad
8/12/2010 6:25:14 AM
tshad (tfs@dslextreme.com) writes:
> For example,
> 
> Select *
> From tableA a
> join tableB b on a.id = b.id
> join tablec c on b.id = c.id
> join tabled d on c.id = d.id
> where something.
> 
> In this case, a is joined with b, b is joined with the result of a and
> b, d is joined with the results of a and b and c. 
> 
> If the optimizer changed the order this is done then you could possibly
> have a different result, especially if dealing with left joins or there is
> multiple criteria in one of the joins.
 
No. An inner join is just like addition and multiplication a commutative
operator. Meaning that "A JOIN B" <=> "B JOIN A".

With left and right joins this is obviously not the case.

-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
0
Erland
8/12/2010 7:24:21 AM
You are probably mixing up the two articles, for logical query
processing (http://pratchev.blogspot.com/2007/05/anatomy-of-
query.html) and for outer joins (http://pratchev.blogspot.com/2009/06/
outer-joins.html). As it has been already said, logical and physical
processing differ a lot. Logical is more like the description of the
logical flow to extract data, physical is how it is really done by the
query engine. The separation of the cross join and matching on join
predicates is done to separate the different steps logically. But when
executed in reality some predicates may be applied much earlier before
the join.

--
Plamen Ratchev
http://www.SQLStudio.com
0
Plamen
8/12/2010 3:07:07 PM
"Erland Sommarskog" <esquel@sommarskog.se> wrote in message 
news:Xns9DD25FAE1B0D7Yazorman@127.0.0.1...
> tshad (tfs@dslextreme.com) writes:
>> For example,
>>
>> Select *
>> From tableA a
>> join tableB b on a.id = b.id
>> join tablec c on b.id = c.id
>> join tabled d on c.id = d.id
>> where something.
>>
>> In this case, a is joined with b, b is joined with the result of a and
>> b, d is joined with the results of a and b and c.
>>
>> If the optimizer changed the order this is done then you could possibly
>> have a different result, especially if dealing with left joins or there 
>> is
>> multiple criteria in one of the joins.
>
> No. An inner join is just like addition and multiplication a commutative
> operator. Meaning that "A JOIN B" <=> "B JOIN A".

Right

but would:
A JOIN B
B JOIN C
C JOIN D

be the same thing as
A JOIN B
C JOIN D
B JOIN C

If so, the 3rd join IS NOT joining against the previous result set.

In otherwords, would the first example be:

(A JOIN B
B JOIN C) a1
D JOIN a1

Where D is being joined with the result of the 1st 2 joins so only joining 
with the rows in C that matched with the rows in B that matched with the 
rows in A.

This could give less rows that the original set than if D were JOIN'd with 
the whole table of C.

And if result sets were joined with each other then the 2nd example would 
have the results of A JOIN B join'd with the result set of C JOIN B.

Of course, if previous result sets are not used that this is all mute and it 
doesn't matter what the order is.

Thanks,

Tom
>
> With left and right joins this is obviously not the case.
>
> -- 
> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
>
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx 


0
tshad
8/12/2010 3:17:24 PM
If you have the same join predicates then the results of the multiple
inner joins (no matter how you group or rearrange them) will be always
the same. Try it with example and see.

The order of tables in JOIN matters only when you deal with outer
joins (because of the added outer rows).

--
Plamen Ratchev
http://www.SQLStudio.com
0
Plamen
8/12/2010 3:51:09 PM
In article <eMsN3FjOLHA.5700@TK2MSFTNGP04.phx.gbl>, tshad
<tfs@dslextreme.com> wrote:

> Right
> 
> but would:
> A JOIN B
> B JOIN C
> C JOIN D
> 
> be the same thing as
> A JOIN B
> C JOIN D
> B JOIN C
> 
> If so, the 3rd join IS NOT joining against the previous result set.

The above syntax isn't close to valid (no join conditions, no commas to
indicate cross joins), but any way you look at it, the results are the
same (although performance could vary)


select *
from A 
  inner join B on a.x = b.x
  inner join C on b.y = c.y
  inner join D on c.z = d.z

Previous results are used at each step.

or 

select * 
from A 
  inner join B on a.x = b.x
  inner join (select * C inner join D on c.z = d.z) CJ on b.y = CJ.y

Result of A/B combined with results C/D on condition b.y=c.y  Because
of the b.y=c.y join condition it doesn't matter how many c/d records
there are, all except the ones that match with b are discarded, because
the c/d condition is the same either way, you get the same number of
records in the end.

The only way it would make a difference is if you meant 

select * 
from A 
  cross join B
  cross join C 
  cross join D
  cross join B
  cross join C


Where you have cartesian joins, and include B and C twice.  If you
don't include tables more than once, then cross join or inner join or a
mixture of the two, as long as the same conditions are applied you'll
end up with the same result set.

-- 
J.B. Moreno
0
J
8/13/2010 3:25:14 AM
Reply:

Similar Artilces:

Windows 7 Mail Question
Does Windows 7 have an included email application? If so, does it support IMAP, POP, SMTP, EXCHANGE and HTTP email configurations? Thank you. -Frank YES "Frankster" <frank@SPAM2TRASH.com> wrote in message news:ze6dnYRTr8IDfqXWnZ2dnUVZ_hOdnZ2d@giganews.com... > Does Windows 7 have an included email application? If so, does it support > IMAP, POP, SMTP, EXCHANGE and HTTP email configurations? > > Thank you. > > -Frank Windows 7 does not include an email program. I use the free Windows Live Mail,(http:\download.live.com) but ther...

SMALL Function question
Hi All, First off, many thanks that assisted me in getting my golf score sheet running properly with the MATCH & INDEX stuff...it works great. I have a problem with the SMALL function...I have a list of 5 lowest scores returned from a range called 'calcutta'. In the adjacent cell I have the coresponding name returned ato match the score with the player. The problem is with ties. If two players hve the same score the SMALL function returns the first one it finds in each cell. I use it like this G1=SMALL(CALCUTTA,1) G2=SMALL(CALCUTTA,2) ETC.... "Monte Comeau" <...

Query Text in the Mid of a memo
I am trying to query off a memo field [Description] the text that follows "User Name: " until the first space the "User Name: " is not in the same position nor is the text of the user name a fixed size. I am working with an InStr function but I don't know how to stop at the " " after the name. Thanks so much for any help. AngieSD wrote: >I am trying to query off a memo field [Description] the text that follows >"User Name: " until the first space the "User Name: " is not in the same >position nor is the text of the user n...

Local OLAP cubes in Excel/MS Query
Hi Can someone please recommend some good book or web page about creating local OLAP cubes using MS Query. I have some data in Access that I want to work with using cubes. Kind regards IgorM http://wang.se/en/CreateOLAPCube.html/ Ed Ferrero Hi Thanks for response. Unfortunately I still don't know if the show-details-after-double-click feature is available in pivottables linked to OLAP cubes. I tried all three connection types. With the two first options (rebuild at once and rebuild when needed) I still cannot make the pivot table to create a sheet on the fly and provide detai...

Installation Question
I would like to install Vista on a Macintosh I have. It's a 250 g hard drive, but I've already used up 150 g. Is there a way not to put on all that extra junk that comes with Windows? I've already bought it a year and a half ago, the macintosh hard drive was replaced. Thank you On 12 Jul 2010, safety123@aol.com wrote in microsoft.public.windows.vista.general: > I would like to install Vista on a Macintosh I have. > It's a 250 g hard drive, but I've already used up 150 g. > > Is there a way not to put on all that extra junk that comes with ...

VBA speed question.
I want to know that A() and B() which faster... dim arr(99) as long dim arrB(9) as long sub A() arr(arrB()) = arr(arrB()) + 1 end sub sub B() dim X as long X = arrB() arr(X) = arr(X) + 1 end sub ...

Newbie question: finding aliases and forwards
If I have an address such as services@somewhere.com how could I figure out where (which user) that address exists at in Exchange or where it forwards to (if it forwards versus belonging to a user)? Thanks. just go to Active Directory Users and computers and do a find with a filter by the users email Address "Joe Blow" wrote: > If I have an address such as services@somewhere.com > how could I figure out where (which user) that address exists at in Exchange > or where it forwards to (if it forwards versus belonging to a user)? > Thanks. > > > ...

Simple charting question
I don't believe I'm having so much trouble with this. I'm trying to make a graph with Temperature (Celesius) as the X-axis and Baseball Speed (Km/hr) as the Y-axis. When I highlight the columns below and try to make a bar graph - 0 44.1 1 47 3 44.7 4 43.5 5 50.2 13 59.9 I get two series plotted or shown seperately. I would like to get one of the following: ...

2003 Money (Can I -) question
Is it possible to change or preset defaults for the drop down fields used to create new recurring bill under [Bills & Deposits]? For example; [Write Check] is the default in the payment method. I want it to be [Print Check]. [Monthly] is default for the frequency and I want [Only Once]. I would like to be able to set my most used selections as defaults. Thanks - Andy Nope. "Andy" <a1cotton@knology.net> wrote in message news:079b01c392c3$6cfe0290$a301280a@phx.gbl... > Is it possible to change or preset defaults for the drop > down fields used to create new...

View query results in MsgBox
I have developed qryQuickLook that brings up rptQuickLook when cmdQuickLook is clicked, everything works great but I would rather see the results (never more than 10 line items) in a MsgBox than have a seperate report window open up. Is there a way to do this? Thanks Dave On Nov 23, 11:00 pm, Duane Hookom <duanehookom@NO_SPAMhotmail.com> wrote: > Why "MsgBox"? You can display the results in a dialog form with a command > button or two. > > -- > Duane Hookom > Microsoft Access MVP > > > > "maces...@gmail.com" wrote: > > I have deve...

Question about OleSetClipboard return CO_E_NOTINITIALIZED error!
When I using Clipboard function, for example below codes, SetClipboard() always is failed.I track into it and find OleSetClipboard return CO_E_NOTINITIALIZED. I depend on its tip and add Coinitialize() in. but It strill is wrong. who can help me? COleDataSource* pOds = new COleDataSource(); if (pOds == NULL) return FALSE; pOds->CacheGlobalData(nFormat,hMem); pOds->SetClipboard(); Michael F wrote: > When I using Clipboard function, for example below codes, > SetClipboard() always is failed.I track into it and find > OleSetClipboard return...

Great Plains Question 10-21-03
Can anyone tell me how Great Plains can be set up for 802.11b wireless scanners? Is the session just a regular Telnet session or is there some funky emulation? Also, is there any modules in place within Great Plains that allow portable RF scanners to operate? Given the fact that the portable scanner may only have a 16 x 21 line display. Or is it up to an integrator to set this up. please send this query to microsoft.public.greatplains "Kevin" <anonymous@discussions.microsoft.com> wrote in message news:00d301c39803$d049b0c0$a601280a@phx.gbl... > Can anyone tell...

XMLSPY Question
Altova posted an announcement regarding XMLSPY 2008 stating it can handle much larger files. So how large is large? Does it load the entire XML document into memory, or does it stream, like xmlreader? I read it has spyware so am reluctant to download the trial version. I did download Stylus Studio and it appears to be a DOM based system. I transform very large xml documents for my clients and some of these files are over 500Mb. I even did noe that was almost 3Gb. I wote some tools with xmlreader/writer that split the files, but requires some hard-coding for each one. sure would be nice to...

looping question
I've used a few looping type macros until now with no problems. However, I'm completely stumped when i need to get excel to count things for me>>> I have 5 row of data in each column (for example). I want to get all the data to be in column A each set below the next. so I need to refer to B1:B5 and move it to A6:A10. Then repeat to move C1:C5 to A11:A15 etc..etc... I need to move all 5 rows in each case regardless of content (number letter, blank etc..) I just come up against a brick wall when it comes to all this i=i+1 stuff, refering to ranges and telling excel to move ...

Report based on parameter query
I have a report that is based on an ordinary parameter query (i.e. right now it pops up dialogs asking for three values that it needs for its where clause). I use this query in several places, so I don't want to make the parameters dependent on specific form controls, which is the only information I've been able to find on google on feeding parameters in a report query's Recordsource. What I actually want to happen is to make a button in the main switchboard which, when pressed, will look up those values in a different query I already have and then use those to populate th...

Billing/Statement Question w/Solomon Integration
Here is our company's structure: Our Corporate Office is being ran by Solomon, we have retail locations that will be running RMS for the retail counter sales.....We have many customers that not only call on us for service work, but also will come into the retail store to purchase products and services....When they make the purchase at the retail location, I would like them to be able to put their sale "on account", and once a month we can then bill them for their purchases. At the month end, I will need to be able to generate a statement that shows their activity for the ...

Multiple Unrelated Queries In One Report or Form?
Hello guys, I've been tasked with generating a report for my boss that provides an overview of our core database statistics. This includes the total number of orders placed each month and the total value of orders placed each month. I also need to provide the same information for purchase orders, quotations and discrepancy reports. Each individual query is a piece of cake on its own but how can I display all of this information either in a form or preferably in a nicely formatted report? The queries are all completely unrelated so there's no way I can somehow joi...

Query to Compare Actual and Budget
One table defines budget (each one has a unqiue ID) Another table defines employees (each one has a unique ID) Another table defines tasks (each one has a unique ID) Another table records budgeted time by project, task, employee Another table records actual time by project, task, employee I created one query which will total up the budgeted time by project and employee (so if an employee was assigned multiple tasks on the same project, you'll just get one total for that employee for that project). I created another query which will total up actual time byt project and employee (so if an e...

Repost-Edit and save query criteria
Sorry to repost this, but I was not sure I would get help with this again and it would get lost in the list. As suggested, I created a table called ExcludeShows1 with the ShowID and Year fields. I have posted the SQL with those changes to the query. When I tested this initially, it appeared to work. Then I tried testing the queries using last year's show data for 2009. No data is being returned for 2009. Not sure what is happening that it is not returning 2009 data. SELECT Shows.ShowID, Entries.Place, IIf([Entries]>1,([Entries]-[Place])*0.5,0) AS Points1, IIf([...

How to update query memo field
I have an old db that I need to import a long notes field from. My new table is existing. I have managed to get the notes from the old db into a comma separated text file with just an SSN and the notes. I can import this into a table that is structured with just an SSN field and a memo field. I'm trying to run an update query to fill in the notes fields of the people already in the new table. When I run the update query it says it's going to update x number of records, but doesn't. I tried changing both the old and new tables to text fields as an experiment and then it works, but o...

QS Hook Function Question
Based on the surfpro.dll example, I have roughed out a prospective function that I hope will allow me to initiate a membership style control over POS transactions. I'm not 100% sure how to call a QSBridge FireEvent -- ClearCustomer as the POS requires a Customer to complete a sale, so I figure no customer, or take away the customer, no sale... right? In VB6 this morning, the Date function, literally returned 6/28/2005. Great! If the Customer Expiration Date is stored in CustomDate1, then a quick subtraction and a few conditionals should do it. The subtraction returned an integer calc of t...

One More Calendar Control Question
Seems as if there are many questions about the ActiveX calendar control and many warnings on not to use them. I have used them with some success, but some issues just make me scratch my head. I am currently using it to have the user select a start date and an end date to run a report. The form opens and the calendars are set to the current date. If the user clicks anywhere on the dates, the start or end date changes accordingly. The problem I have is when the user selects a different month or year, none of the dates are depressed. The user has to select a new date within the sel...

query against AD
EXCEL 2003 1. data, import external data, new database query. Is there a way to query a AD ? ...

sumproduct question please
Hi everyone I have a list of names in column a - I then have data in various other columns which is copied from other worksheets (Columns B, F and J are names) I want to sum how many time a name in column a is listed in the others so have entered as: =SUMPRODUCT(($B$2:$B$500=A2)*($F$2:$F$500=A2)*($J$2:$J$500=A2)) But this is not calculating correctly - can someone pls advise what step I'm missing?? -- Thanks as always Lise Try =COUNTIF(A:A,A1)+COUNTIF(F:F,A1)+COUNTIF(J:J,A1) -- Jacob "Lise" wrote: > Hi everyone > > I have a list ...

Excel, ODBC and creating queries
Does anyone know of a way to create a query based on values or formula in a spreadsheeet? Thanks in advance, J -- Message posted from http://www.ExcelForum.com From reading between the lines of your post, I am guessing that you have an Access Database with which you want to create a query based on a spreadsheet. If this is the case, you will need to open your database and in the tables section of your databse , right mouse click in the area showing your table names, and select "Link Tables" from the menu. Once you have followed the rest of the steps in the link wizard, a...