Subquery works..2 questions to learn more

Thanks to Allen Browne, and general reading of the other Posts,
I have a Top 5 Aggregate Query working, but I'd like to learn more.

1....
If I use the name of the Aggregate Query in the Subquery, it runs, but
much slower than making that Aggregate Query a Table (which I've
read before, while it works, is discouraged).  Is this observation I've
noted the way it is when using an Aggregate Query as a Subquery?

2..
If the Top 5 results are 7, 6, 5, 4, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 
3, 3, 3
then all the 3s are displayed...they are a tie for 5th place...any way to 
display only one 3...although the other "3" corresponding data has Mgmt Info.

TIA - Bob

0
Utf
12/5/2007 12:34:00 AM
access 16762 articles. 3 followers. Follow

3 Replies
779 Views

Similar Articles

[PageSpeed] 54

Congrats, Bob: you will find subqueries very useful (as wel as somewhat 
frustrating), so it will prove a good learning experience.

Re your specific questions:

1) In general, JET does tend to execute subqueries slower than most other 
approaches (such as JOINs, stacked queries, or temp tables.) So while 
subqueries have a wide range of uses, they are not the answer to everything.

If there is an easy, efficient solution that does not require a temp table, 
you will want to go that way. However, there are many cases where a temp 
table makes good sense. In general, you use a temp table if performance has 
become a real barrier, or if you need to reuse the results from the temp 
table.

More on solving performance issues:
    http://allenbrowne.com/subquery-02.html#Performance

2) To solve the fact that JET returns more than the TOP N results when there 
are ties, give it some way to decide between equals. The simplest way to do 
this is to add the primary key field to the ORDER BY clause. More info:
    http://allenbrowne.com/subquery-01.html#TopN

-- 
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.

"Bob Barnes" <BobBarnes@discussions.microsoft.com> wrote in message
news:F07C8567-E11C-4BD1-A1AC-9585C31299A6@microsoft.com...
> Thanks to Allen Browne, and general reading of the other Posts,
> I have a Top 5 Aggregate Query working, but I'd like to learn more.
>
> 1....
> If I use the name of the Aggregate Query in the Subquery, it runs, but
> much slower than making that Aggregate Query a Table (which I've
> read before, while it works, is discouraged).  Is this observation I've
> noted the way it is when using an Aggregate Query as a Subquery?
>
> 2..
> If the Top 5 results are 7, 6, 5, 4, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 
> 3,
> 3, 3, 3
> then all the 3s are displayed...they are a tie for 5th place...any way to
> display only one 3...although the other "3" corresponding data has Mgmt 
> Info.
>
> TIA - Bob
> 

0
Allen
12/5/2007 2:26:28 PM
Allen - thank you.

I'm using a Make Table as it runs so much faster.

The "Top 5" results give very beneficial info, and, after discussion w/ the 
Client, the "tie for 5th Place" DOES need to show any other tie-values.

As always, you, and the other fine Newsgroup contributors, merit a High-Five.

Thanks again - Bob

"Allen Browne" wrote:

> Congrats, Bob: you will find subqueries very useful (as wel as somewhat 
> frustrating), so it will prove a good learning experience.
> 
> Re your specific questions:
> 
> 1) In general, JET does tend to execute subqueries slower than most other 
> approaches (such as JOINs, stacked queries, or temp tables.) So while 
> subqueries have a wide range of uses, they are not the answer to everything.
> 
> If there is an easy, efficient solution that does not require a temp table, 
> you will want to go that way. However, there are many cases where a temp 
> table makes good sense. In general, you use a temp table if performance has 
> become a real barrier, or if you need to reuse the results from the temp 
> table.
> 
> More on solving performance issues:
>     http://allenbrowne.com/subquery-02.html#Performance
> 
> 2) To solve the fact that JET returns more than the TOP N results when there 
> are ties, give it some way to decide between equals. The simplest way to do 
> this is to add the primary key field to the ORDER BY clause. More info:
>     http://allenbrowne.com/subquery-01.html#TopN
> 
> -- 
> 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.
> 
> "Bob Barnes" <BobBarnes@discussions.microsoft.com> wrote in message
> news:F07C8567-E11C-4BD1-A1AC-9585C31299A6@microsoft.com...
> > Thanks to Allen Browne, and general reading of the other Posts,
> > I have a Top 5 Aggregate Query working, but I'd like to learn more.
> >
> > 1....
> > If I use the name of the Aggregate Query in the Subquery, it runs, but
> > much slower than making that Aggregate Query a Table (which I've
> > read before, while it works, is discouraged).  Is this observation I've
> > noted the way it is when using an Aggregate Query as a Subquery?
> >
> > 2..
> > If the Top 5 results are 7, 6, 5, 4, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 
> > 3,
> > 3, 3, 3
> > then all the 3s are displayed...they are a tie for 5th place...any way to
> > display only one 3...although the other "3" corresponding data has Mgmt 
> > Info.
> >
> > TIA - Bob
> > 
> 
> 
0
Utf
12/5/2007 10:39:00 PM
Okay, sounds like you have things sorted out.

I'm surprised at your comment that the Make Table runs faster. I never use a 
Make Table query in a production database, any only very rarely when 
developing. I find it much more reliable to set up the target table exactly 
right, and I don't expect this to execute any slower:
    db.Execute "DELETE FROM MyTempTable;", dbFailOnError
    db.Execute "INSERT INTO ...

-- 
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.

"Bob Barnes" <BobBarnes@discussions.microsoft.com> wrote in message
news:070BF5CA-E40E-4909-9B38-7440E3125C8A@microsoft.com...
> Allen - thank you.
>
> I'm using a Make Table as it runs so much faster.
>
> The "Top 5" results give very beneficial info, and, after discussion w/ 
> the
> Client, the "tie for 5th Place" DOES need to show any other tie-values.
>
> As always, you, and the other fine Newsgroup contributors, merit a 
> High-Five.
>
> Thanks again - Bob
>
> "Allen Browne" wrote:
>
>> Congrats, Bob: you will find subqueries very useful (as wel as somewhat
>> frustrating), so it will prove a good learning experience.
>>
>> Re your specific questions:
>>
>> 1) In general, JET does tend to execute subqueries slower than most other
>> approaches (such as JOINs, stacked queries, or temp tables.) So while
>> subqueries have a wide range of uses, they are not the answer to 
>> everything.
>>
>> If there is an easy, efficient solution that does not require a temp 
>> table,
>> you will want to go that way. However, there are many cases where a temp
>> table makes good sense. In general, you use a temp table if performance 
>> has
>> become a real barrier, or if you need to reuse the results from the temp
>> table.
>>
>> More on solving performance issues:
>>     http://allenbrowne.com/subquery-02.html#Performance
>>
>> 2) To solve the fact that JET returns more than the TOP N results when 
>> there
>> are ties, give it some way to decide between equals. The simplest way to 
>> do
>> this is to add the primary key field to the ORDER BY clause. More info:
>>     http://allenbrowne.com/subquery-01.html#TopN
>>
>> "Bob Barnes" <BobBarnes@discussions.microsoft.com> wrote in message
>> news:F07C8567-E11C-4BD1-A1AC-9585C31299A6@microsoft.com...
>> > Thanks to Allen Browne, and general reading of the other Posts,
>> > I have a Top 5 Aggregate Query working, but I'd like to learn more.
>> >
>> > 1....
>> > If I use the name of the Aggregate Query in the Subquery, it runs, but
>> > much slower than making that Aggregate Query a Table (which I've
>> > read before, while it works, is discouraged).  Is this observation I've
>> > noted the way it is when using an Aggregate Query as a Subquery?
>> >
>> > 2..
>> > If the Top 5 results are 7, 6, 5, 4, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 
>> > 3,
>> > 3,
>> > 3, 3, 3
>> > then all the 3s are displayed...they are a tie for 5th place...any way 
>> > to
>> > display only one 3...although the other "3" corresponding data has Mgmt
>> > Info.
>> >
>> > TIA - Bob 

0
Allen
12/6/2007 1:58:12 AM
Reply:

Similar Artilces:

Historical Excel question re: statistical capabilities
Does anyone know approximately when Excel started shipping with statistical functions built in- or know how I can find out? In the pre-spreadsheet days of my computing past, I used BASIC to code statistical functions necessary for my profession. As spreadsheets became more advanced, we all transitioned to using the advanced functionality. My question is when did this occur? Did Excel always have its current complement of statistical functions? Thanks slingsh0t@hotmail.com shared this with us in microsoft.public.excel.misc: > Does anyone know approximately when Excel started shipping...

"TOOLS" menu missing from menu bar #2
"TOOLS" menu is completely missing from the menu bar in Publisher 2003. How do I put it back? Might try a "detect and repair" Meanwhile, right-click a toolbar, click customize, scroll down to built-in menus, drag the tools menu to your toolbar. -- Mary Sauer MS MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "Anil Gupta" <AnilGupta@discussions.microsoft.com> wrote in message news:5C7ADC1E-A6E9-4F01-8C06-01B92AC7B97B@microsoft.com... > "TOOLS" menu is completely missing from the menu bar in Publisher 20...

Creating a Combination Bar Chart with 2 Lines
I am trying to create a bar chart with 2 line series. The Y-axis for the vertical bars would be on the left and the Y-axis for the 2 line series would be on the right (3 series were defined). The horizontal axis for all series are calendar months (Jan, Feb, etc.) Every time I setup the chart my chart only displays a single line, ignoring the other line series. The vertical bars come out fine. Any thoughts on what I am doing wrong? Thanks Bill Falzone Don't use the built in types. Make the chart using all columns. Select a series that you want to change (click on it right in th...

Vista, MS Access 2003 and MS ADO Ext 6.0 vs 2.x
I am running into an issue with MS Access 2003 installed on Vista. Apparantly in the MS ADO Ext 6.0 version either (format, round, or sum) is not in that DLL. However, when I link to v2.8 of the DLL, I don't have an issue. I am getting the following error message when running my query. "Function is not available in expressions in query expressions." Does anyone have other workarounds? I am distributing this application in an MDE format, so I was wondering if the end users may experience issues, if I link to my version of msadox.dll instead of the installed version. I tried s...

Printing Question
I have a report that I have been doing in Excel but is better done in Access. However, the report is too large to print on a single page and that is a necessity. With Excel, I just adjusted the scaling. I can't seem to do that with Access I've also tried exporting from Access to Excel, but the calculated fields are not exported. Any suggestions on how I can print the report on one page? Sorry, when I posted, it said there was an error and that I should try posting later. "randlesc" wrote: > I have a report that I have been doing in Excel but is bett...

URGENT!!! Problem with row data being truncated in a copy worksheet sub #2
Dave, Thanks for responding. I tried this but I could not get it t work in conjunction with the entire module. It dies right afte copying and PasteSpecial Values It does not kill the temp file or loa the newly created sheet into an e-mail. Any Ideas -- Doctor ----------------------------------------------------------------------- DoctorV's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=514 View this thread: http://www.excelforum.com/showthread.php?threadid=26863 You may want to post your current procedure. DoctorV wrote: > > Dave, Thanks for respon...

Cannot Start Outlook #2
I have just run the auomatic windows update on my local PC and now Outlook 2000 will not start. It just hangs at the splash screen. Outlook is configured to connect to Exchange. Other PCs on the same Lan which have not been updated can still connect. The updates installed were 824141, 823182, 826232, 825119, 828035, 828749. I have unistalled all of these updates and Outlook still will not start. Any ideas? Does it start if you run this command from the command line, go to Start > Run > type "outlook /safe" without the quotes but with the space before the Forward slash &...

2 Email domains w/2 Act. Dir. Domains - Same Forest
Hello, Scenario: We've had domain1.com (AD) setup and running in exchange 2003 for a year now w/mixed win2k and win2k3 servers - email domain name is the same, domain1. OWA works great and haven't had any problems. Our company is branching out into a new business segment and I've created a new active dir. domain within the same forest, domain2.local. It is NOT a child domain, but does sit under the same forest. I need to configure my front end/back end exchange 2003 boxes to do the following: -receive email for both email domains (domain1.com and domain2.com) -let users...

Search Multiple Worksheets #2
Is there a way to search trough multiple worksheets for a specific value? Other posts have mentioned to use VBA, but I have never used that before. If anyone can give me some advice on using that or a type of formula to perform that can search multiple worksheets. Thank You There may be other ways but, while holding down the ctrl key select each of the worksheet tabs you want to search in then select Edit|Find from the menu. Type in the value you want and it will go to the first instance of that value. Now if you are wanting to preserve a specifice value for reference or ???, then...

Question about "Float Invalid Operation"
I've got an app written in C++, in some c'tor I wish to set a member variable as "quiet not a number" #define QNAN (HUGE/HUGE) class CTest : public ... { double m_dTest; } CTest::CTest() { m_dTest = QNAN; // mark } Runs fine under NT based Windows. A customer however installed the app at a Win98 machine and it crashed with some 0xC0000090 "Float Invalid Operation" error at the marked line. So here are my questions about this: 1. Does Win9x handle HUGE/HUGE other than NT based systems? 2. Is it likely that the error occurs only at...

Noob Question For Selecting Multiple Fields On A Form
Hello... In versions earlier than 2007 I would be able to go to the Menu Bar and click Edit -> Select All to select all fields on the form. Now, my question is... where in 2007 did the put that functionality? If Microsoft removed it from there... where did they recreate it? Thanks! Squirrel "SQLSQUIRREL" <SQLSQUIRREL@discussions.microsoft.com> wrote in message news:333547A1-9C6A-422B-9CD5-97D79D6037DF@microsoft.com... > Hello... > > In versions earlier than 2007 I would be able to go to the Menu Bar and > click Edit -> Select All to se...

print area #2
i cannot clear print area. when i select the print area and 'file', 'print area',the options are not available. any suggestions? When you use this command, Excel creates a range "print_area". Try deleting that range. Stan Scott New York City "bronxxbabe" <anonymous@discussions.microsoft.com> wrote in message news:78dc01c476a2$31509280$a401280a@phx.gbl... > i cannot clear print area. when i select the print area > and 'file', 'print area',the options are not available. > any suggestions? Perhaps you have two or more ...

HTTP Error 401.2
Hi, I have installed MS CRM 1.2 on my Domain controller running windows 2003 standard server and email router on exchange 2003. My local ip subnet is 172.0.0.x With ms vpn connection, I can access to ms crm by using outlook 2003 or internet explorer by opening the default ms crm page remotely. However, if i connect to ms crm with different subnet 10.0.0.x, i cannot connect to ms crm. 10.0.0.x subnet is routed to 172.0.0.x, where my users normally login email from the exchange 2003. What puzzle me is why i can authenticate from exchange 2003 but not ms crm server on using the same...

backup same set of servers from 2 DPM 2007 servers
I am changing the DPM server 2007. However, I would like to take this opportunity to revise all protection groups so that I can restore bare-metal any server from a single lTO-4 tape if possible. During this "transitional period," I would like to leave the existing backup settings on the older DPM server to continue to run while adding jobs to the new DPM server. Looks like to me that I have to re-install the agent on all servers so that they can be recognized by the new DPM server. IS there a better way to do this if my plan is possible at all? Thanks Bill ...

Mailbox Manager questions
Hello, We are running Exchange 2003, sp1. Is there a tutorial somewhere that shows how to specificy groups/users for the mailbox management selection. When I get into the window to select users, it doesn't select them the way I'm asking. i.e. I try and select a security group and it selects a few users. I also can't seem to select multiple users correctly. Lastly, we would like to archive old Calendar items. The problem seems to be that if we archive recurring meetings that they will get deleted on future items. Thanks, Sun ...

Urgent Question Please Help: Credit Card Setup Problem
We have an urgent question regarding credit card setup/cash receipts/bank rec We understand that in Great Plains when setting up a Credit Card to be used by a Customer, it must be setup as a "Bank Card", not a "Charge Card", in order for the transaction to hit Bank Rec. However, we mistakenly setup all the Credit Cards as "Charge Cards", therefore after posting Credit Card Cash Receipt transactions in Receivables, the transaction does not appear in Bank Rec for us to perform Bank Deposits. Please Please Help!!! Is there any way to "fix" those pos...

moving publisher #2
I bought a computer with publisher bundled (no cd). Now I want to put it on my new laptop. How do I move it from the desktop computer to the laptop???? Cliff without the source CD...you don't. -- ~~~~~~~~~~~~~~~~~~ Rob Giordano Microsoft MVP Expression "Cliff" <Cliff@discussions.microsoft.com> wrote in message news:336C1CBE-2A51-4B62-B83D-46C35C557B74@microsoft.com... > I bought a computer with publisher bundled (no cd). Now I want to put it > on > my new laptop. How do I move it from the desktop computer to the > laptop???? > > Cliff I...

Average speed #2
Hi all Having trouble working out average speed. miles in a1 =14.3 time in B1 = 0:50:23 this cell has been formatted to HH:MM:SS The formula A1/B1 brings back the wrong answer if I alter to A1/(B1*24) still does not work correctly. Advise please. Thanks Jon "jon" <jon@nospam.com> schrieb im Newsbeitrag news:4659eb9a$1_1@glkas0286.greenlnk.net... > Hi all > Having trouble working out average speed. > > miles in a1 =14.3 > time in B1 = 0:50:23 this cell has been formatted to HH:MM:SS > > The formula A1/B1 brings back the wrong answer if I al...

Keeps asking for password #2
I have followed all of the instructions given previously, I have deleted and recreated my e-mail account but Outlook 2002 (Windows XP) still keeps asking me for user ID and password every time it tries to send / recieve. The strange thing is that I have two accounts and this is only a problem with one of them. one of your accounts is set to ask formy outgoing server requires authentication, see under tools ...accounts... send...and then advanced email settings, or just double click on the email accunt , it wil give you the properties, take it from there it should be the settings should ...

Y Axis #2
I have two questions regarding the 'y' axis - in a normal line graph Firstly, how do i get the 'y' axis to appear in the middle of the graph IE to cross the 'x' axis at (0,0) instead of currently (in my graph) (-10,0) Secondly how do i plot the line "X =2" which should just be a vertical line on the graph? I'm using Microsoft Excel 2007 any help would be appreciated. Thanks Hi, Not able to test in xl2007 but from memory it should be the same as xl2003. For a line chart, select the x axis and open the format dialog. On the scale tab set Value (Y) axi...

Printing on 2 sides of a page?
How can I print a 2 page spreadsheet on the front and back of the same sheet of paper? Using Excel 2008. Thanks! It depends on whether your printer supports duplex printing. Check your printer's features to find out. If it does, the setting will be available in the Print dialog. You can also do a "manual duplex" job by printing page 1 (or 'Odd pages'), flipping the paper yourself, then print page 2 (or 'Even pages')... If it's only 2 pages that might be the simplest approach. -- HTH |:>) Bob Jones [MVP] Office:Mac "Rick"...

Question about formula or marco
Hi all, Here is what I need to do: Column B will have a list of first names (John), Column C will have last names (Smith). What kind of formula or macro do I need so that I can have Column A read "jsmith" (all lower case)? Any ideas? Thanks very much. Try this formula in Cell A1:- =LOWER(CONCATENATE(LEFT(B1),C1)) "Gus Jae" <gusjae@verizon.net> wrote in message news:dn8Ue.186$sk2.153@trndny03... > Hi all, > Here is what I need to do: > Column B will have a list of first names (John), Column C will have last > names (Smith). > What kind of formu...

Draft Folder problem #2
I'm running outlook 2000. My draft folder is called draft1. Can't seem to rename it! There isn't a folder named draft so what's happening? Isn't a big problem, but I should be able to solve it and I can't. Help. Thanks in advance Peter You could try to start Outlook from the run line with the /resetfoldernames switch. Start-->Run outlook.exe /resetfoldernames Reference: http://office.microsoft.com/en-gb/assistance/HP010031101033.aspx "Peter Brown" <peter.jennifer@gmail.com> wrote in message news:TErag.449$TF.2645@news-1.opaltelecom.net... &...

Inventory #2
How can I import an excel spreadsheet as inventory into Money...? -- Marie You're talking about the home inventory? It's not possible without using a macro (i.e., there's no import function). IMO, it's not desirable either, as the information is not easily usable in Money. I keep my home inventory in an asset account, which has the huge benefit of being a "normal" Money account. If you need to import the inventory, there are Excel to QIF converters available from third parties. I use the following: http://www.bigredconsulting.com/ -- "Marie" <...

System Cleanup not working
I have a few 2003 enterprise servers which are all on the same Recipient & Mailbox Policy's. Mail does not move to system cleanup folder which is past msg age limit and mail is not being deleted from Deleted Items folder past msg age limit. I checked out the old msgs that should have moved and they meet the requirements noted in http://support.microsoft.com/?id=302804: • PR_MESSAGE_DELIVERY_TIME • PR_CLIENT_SUBMIT_TIME • PR_LAST_MODIFICATION_TIME I up'd the logging on server to max and I see no errors. The time between events 1206 & 1207 for "Cleanup of items pa...