Query design question?

Query1 retrieves data for 2,000 records from a series of linked tables that 
have about 100,000 recordset

Query2 retrieves data for 5,000 records from another series of linked tables 
that has about a 100,000 recordset as well.

Query1 and Query2 are linked on a customer ID (e.g.)

Should there be a simple join between Query1 and Query2 on Customer ID, or 
would there there be a benefit to limiting the records in Query2 first? 


0
JimP
4/5/2007 1:52:12 PM
access.queries 6343 articles. 1 followers. Follow

1 Replies
964 Views

Similar Articles

[PageSpeed] 34

Yes, SQL Server app - have an old stopwatch, but didn't know if some general 
guidelines applied.

"Jerry Whittle" <JerryWhittle@discussions.microsoft.com> wrote in message 
news:F25894A6-1087-4617-BC28-008FA676DA72@microsoft.com...
>I have an old-fashion tool in my desk drawer to answer such questions: a
> stopwatch. When it comes to queries, accuracy first then worry about
> performance.
>
> It's also possible that using Showplan might help decide. Google showplan
> for info on it.
>
> Now on a large, multi-user database such as Oracle, I'd also pay attention
> to things like disk hits and costs. If making one query fast slows down 99
> other users, that's not a good thing. Are these linked tables in something
> like SQL server? You might want to check what happens there.
> -- 
> Jerry Whittle, Microsoft Access MVP
> Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
>
> "JimP" wrote:
>
>> Query1 retrieves data for 2,000 records from a series of linked tables 
>> that
>> have about 100,000 recordset
>>
>> Query2 retrieves data for 5,000 records from another series of linked 
>> tables
>> that has about a 100,000 recordset as well.
>>
>> Query1 and Query2 are linked on a customer ID (e.g.)
>>
>> Should there be a simple join between Query1 and Query2 on Customer ID, 
>> or
>> would there there be a benefit to limiting the records in Query2 first? 


0
JimP
4/6/2007 12:08:01 PM
Reply:

Similar Artilces:

Business Portal Hierachy Question
I have created a hierachy which is suppose to function for the Executive level as a dual approval. For example when we reach the 25k level for purchasing I would like to have both the CFO and CEO approve these requistions. So far the requistions have been going in a circle. I have the CFO reporting directly to the Requistion Manager as default and the CEO reporting to the CFO as a default with the CFO under the CEO incase a request comes to this position first. How to I make it so that the default is to go to the CEO then the CFO finally to the RA? Without the round. ...

WSE 3.0 X.509 General Question
I'm migrating my app to WSE 3.0. In the past I used WSE 1.0 and 2.0 to encrypt my requests and responses using a private shared key, and not using X.509 due to perceived difficulties therein. With each release of WSE, however, I find myself having to re-implement my encryption scheme to make it work, since it's not using the recommended X.509 certs. Is it possible to use X.509 in a "private" environment, i.e. closed network (no internet), between one server and one or more clients? I'm assuming this would require the IIS Web Server to act as a Certificate Author...

SQL query with Many-To-Many relationship???
I am trying to write an SQL statement that is giving me some trouble, and I was hoping that somebody here could lend me a hand. CONTEXT I am working in Access 2007. The tables concerned are the Students, Classes and StudentsAndClasses. The relationship of Students to Classes is many-to-many, i.e. each student can attend several classes and each class can have several students. StudentsAndClasses table is used to help define this relationship and works something like a class roster. The Students table has StudentID as its primary key. The Classes table has ClassID as its primary key. The St...

Option Group question 03-19-08
I have an option group with 3 choices and next to each choice I have a textbox ( 3 in all). I imagine this is simple, but I'd like to have for whatever checkbox in the optiongroup that is chosen the textbox to the right of it be enabled, while the other two are disabled. optiongroup name is OptGroupFreight optionvalue 1 is check52 textbox is FreightTLQuantity optionvalue 2 is check54 textbox is FreighthalfLTLQuantity optionvalue 3 is check56 textbox is FreightthirdLTLQuantity Thanks in advance. Ryan You might use something like this in the current event of your form; Me.FreightTLQuant...

Difficult function question for columns
I would like the create a column that performs the following function: [If column A=1, then display number in cell B10; If Column A=2, then display number in cell B11, If column A=3; then display number in cell B12] If it is of any importance, in the above example B10:12 have a =SUMIF/COUNTIF function in them. Thanks in advance. AAA1986 wrote: > I would like the create a column that performs the following function: > > [If column A=1, then display number in cell B10; If Column A=2, then display > number in cell B11, If column A=3; then display number in cell B1...

Excel Question: How to Delete All Blank Rows from a worksheet?
Hi, I'm brand new, and that is my question. I just spent 2 hour laboriously mousing along deleting row by row, every other row thinkin there's gotta be a better way. (I could've done it of course if the were adjacent, but they were interspersed with my good data). I'm little behind the times: agile with keystrokes but less so with mous [think I was quicker in Lotus DOS! (actually "TWIN")]. But I do play mean piano -- Message posted from http://www.ExcelForum.com Check out: http://www.cpearson.com/excel/deleting.htm#DeleteBlankRows "DaveThePianoGuy >...

Help needed with database design
Hi Heres hoping someone can help me with a problem. I need to design a small database to hold stock parts. This part is well within my abilities and is quite simple. eg Part_no Location description Price etc. The problem I have is that I need to be able to create some master parts which will make a kit composed of individual parts. The users need to be able to choose a master part which will return a list of the component parts and details such as price etc. On the same order the individual parts may also be ordered in addition to the master part. Also some of the individual parts will...

Disapearing Query Results!
Hello Dear I have some Combo Box's on a form that extract data from queries When queries run,the results show to the user that is not necessary! Is it possible to hide query results from user? Thanks Best Regards The question is unclear. Do you want to show the results of a query in the form? Are you seeing query results in data sheet view and don't want to? If this is the case, then you must be executing the query in some manner and opening the query. We cannot see your application and cannot see what you are doing. John Spencer Access MVP 2002-2005, 2007-2...

Questions about Strange ReCalc Behavior
I'm currently struggling with an MOAS (that's "Mother of All Spreadsheets") that someone else at work has given to me. It's 28 MB and takes several seconds to re-calculate. I'm in the process of trying to diagnose ways in which I can make it more efficient (Ultimately I'll need to run it as part of a Monte Carlo simulation--25,000 recalcs, so every second is important). I've run into a couple of peculiar Excel behaviors. Can anyone explain these? 1) I can do an F9 recalc on any sheet. However, there are a couple of sheets (out of maybe 15) which, if I...

Silly Question on Contacts
One of my users is complaining that the default 'Display As' field in Outlook 2003 is Full Name (email address) and that when he prints a distribution list it looks messy and replicates data. I'm probably going to have to write a script that will correct his existing addresses (he has over 5000), but I just wondered if there was a way to fine tune the Display As formatting? Many thanks for any help Simon M ...

"relative reference" question
I'm new to the whole macro thing and am looking for a simple way to understand the "relative reference" when recording a macro. Can someone shed some light on that? also, my macro takes a spreadsheet of 10 columns and x rows and formats it. How would I build the macro so that no matter the number of rows, the macro will still work? would i have to write code for this or will the record macro option work? Any advice would be appreciated. Thanks Hi there, Your whole intent is unclear. Can you specify the Ranges/Addresses to which you are referring? Give some examples...

bar graph question
Hello everyone, I have data with 5 variables for 10 countries, and I have made horizontal bar graph for each variable (so 5 graphs). This data i linked to another page, and it is divided by variable within the sheet For example, the format is: Country Variable 1 US ### China ### India ### UK ### Country Variable 2 US ### China ### India ### UK ### There is a macro that sorts each of this mini-tables, and then eac graph draws from its respective variable table. The same 10 countrie are in each table, but they are not in the sam...

Design..
Hi, I've learnt c++ and mfc and im building a program that is using both, but i,mm having a problem combining all the code together. this is my first serious progect using them, and im having a problem in code design and it goes as follows: how can if i have file "a.cpp" that is connected to a "a.h" file(c++ class files) and i have file "f.cpp" and "f.h" how can i make f which is the main program use the class i have in "a". thanks. > I've learnt c++ and mfc and im building a program that is using both, MFC is written is C...

Highlight keywords in result of perameter query
I have a query perameter "*" & [Keyword Search] & "*" the returns the records based on the keyword. Works great. Is there a way the keyword to be highlighted or change color in the results? Not that I know of. Especially in a query. -- John Spencer Access MVP 2002-2005, 2007-2008 Center for Health Program Development and Management University of Maryland Baltimore County .. "Ryan Tisserand" <RyanTisserand@discussions.microsoft.com> wrote in message news:34DD4D45-C312-4204-B53E-B22300A0613B@microsoft.com... >I have a query perameter &qu...

Create Email From Access W/Information From Form or Query in
I have designed Databases for many years now, but never attempted this: I need to know how to create an email with information located in the active form or off of a query. I would like to have the email automatically generated with the To Address, The Subject Line and the Body of the Text. I would like that email to open but not to send until the operator has a chance to make modifications. (one record per email). -- Mark Matzke "mmatzke@comcast.net" <mmatzkecomcastnet@discussions.microsoft.com> wrote in message news:F3F01720-2676-4DE6-A881-0E640CAF4CB5@microsoft.com...

Perplexing Chart Question
I went here http://support.microsoft.com/kb/183446 and followed the instructions exactly to create a chart in a new sheet that automatically updates for 2007--it worked perfectly. When I tried to do the same thing with existing data I could never get it to work, and I tried ten times. I have no idea what I'm doing wrong. Any suggestions? Hi Jim, I haven't check Microsoft step by step instructions but if the data range you are working with grows or shrinks in the vertical direction, there is a simpler way to handle this. What the web site is suggesting is using dynamic ran...

How can I add a warning meassage to a macros designed to delete/insert a Row?
every time when a row (or a cell in a certain range) in a shared worksheet is to be deleted or inserted, I want a warning message. But how? Thanks. Zean ...

Design for a service business
What is the best way to design a database for my service business? Invoices are sent out just once a month and the customers pretty much stays the same from month to month. Each customer has a customer number and each invoice has an invoice number (the invoice number is really just an abbreviation of the month and year attached to the customer number). Should I have a seperate table for each month or do it some other way? Presently, using different database software, I create a different database file for each month. Many thanks. 1st off, and I don't mean to scare you, but de...

Open Query Design In SQL view
Is it possible to open the query design window directly to the SQL View in existing queries or in creating new queries? Presently it is two or three clicks to get there. If the existing query was saved in SQL view, opening it subsequently in Design view should open it in the SQL view. I'm not aware of any way to have new queries automatically open in SQL view. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "tcb" <tbenson@mn.rr.com> wrote in message news:1190637257.981140.239390@19g2000hsx.googlegroups.com... > Is it possible t...

Easy Questions Here
I'm looking at a customer that wants to replace exisitng POS. Small retial business operates out of a mall cart. Sells custom novelties. Will POS be a good fit (Of course it would)? Any HCL to look at I can't seem to find one? Optionals like Merchant Services other that whats advertised? Why is this NOT on MSDN downloads (Universal)???? Is RMS COM based or managed code? I'm mostly interested in integration. I'm BizTalk ceritfied so I do most of my work in integration. OK MVPs these are easy. -- Michael Krok President Mallard Solutions LLC Microsoft Certified Partne...

Good question! No answer. US to UK dates Pocket Version
can the date format on pocket version money 2004 be changed from the US mm/dd/yy to the UK dd/mm/yy format????? Grateful for your answer. I posted an answer to this earlier today. -- Regards Bob Peel, Microsoft MVP - Money Hints/Tips http://support.microsoft.com/default.aspx?scid=fh;EN-GB;mny UK Wishes/Suggestions mnyukwsh@microsoft.com "william" <anonymous@discussions.microsoft.com> wrote in message news:1399101c3f8bb$9bcc0dc0$a101280a@phx.gbl... > > can the date format on pocket version money 2004 be > changed from the US mm/dd/yy to the UK dd/...

Question about Mapping
I add a new mapping from Product(discription) to Incident(discription) and then publish and reset iis. However, when new Case and select a product, I cannot see the data from product discription to the incident discription. Can anybody help me ?????? Only 1 mapping will take effect when a record is created and they only impact the record creations, not updates. When you add a Product to a case, the case has already been created, so the map is not used. Matt Parks MVP - Microsoft CRM ---------------------------------------- ---------------------------------------- On Fri, 3 Dec 2004 07:3...

Newbie question Pulling data from one sheet to another based on
Okay, Forgive me ahead of time, I really don't know much about Excel. I can work with templates and create simple formulas but alot of what I read here comes across as "Excelspeak" to me. Here is what I want to do: I have a price list sheet created. This price list has cells labled by name and then "duration", basically for every item there are four prices, based on duration of a rental or sale. I want to create a quote program that will pull data from the price list based on the quantities entered in the quote program. For example Price list is "System A, des...

How do I temporarily limit values in a query field
For testing purposes, I want to restrict the query to a range of numerical values e.g. <4999. I get error messages when I try to type <4999 in the criteria field. The field contains an expression does have an alias if that makes a difference? e.g. Incurred: IIf([COV]="GENL" ...,...,...) That's what I get for trying trying to abbreviate things ... Field: Incurred: IIf([COV]="GENL" Or [COV]="AUTOLIAB" Or [COV]="D&O" Or [COV]="EPL",Nz([CURRES],0)+Nz([PDLOSS],0)+Nz([PDLegal],0)+NZ([PDO],0),IIf([COV]="AUTOPHY",Nz([CURRE...

what is the query to list records with a certain field empty?
what is the query to list records with a certain field empty? Put Is Null in the criteria row of the query -- Wayne Manchester, England. "aa" wrote: > what is the query to list records with a certain field empty? > > > Thanks a lot ...