query very slow
i have a query that takes about five minutes to run. i think that the delay
is not justified: the table contains around 40000 records and the query is
based on two separate queries whose performance is ok.
below is the sql of my query if anyone could help me please!
SELECT [Quotation-Suppliers-qry].[Order Number],
[Quotation-Suppliers-qry].Description, [Net Price]-[MinOfNet Price] AS Expr4,
[Pricelist-qry-0].[MinOfNet Price], [Quotation-Suppliers-qry].[Net Price], ...How to change the default value in "Filter On" field (Microsoft CRM 3.0)
When I open a contact in CRM, I can view the sales activities
(Opportunities, Quotes, Orders, and Invoices) under "Sales" session. Is
there any way I can change the default value under "Filter On" drop down
For example, when I click on Invoice, I want "Filter on:" drop down list
select "Paid" instead of "Active"
Thanks for your advice.
You do the following:
Settings -> Customizations -> Customize Entities
Select the contact entity
Then you will see a list of views and a form. One of those views is set ...onChange code to automatically select a lookup value
When i create a new case and select a Customer from the lookup value,
i then want the most recent contract to be automatically selected, and
then from this the contract line to be automatically selected.
But i am having problems with trying to write code with the onChange
event to do this because i dont know how to select a value of a lookup
Please help :)
...Criteria in append querie
I want to append only non existing ClientID fields into the
Client ID is not a primairy field because it is a import file from
website(can't put it in primairy key because import program refresh
the table every 5 minutes and replaces all data.)
So i do a append query with this criteria:
=not Exists (SELECT 'KlantID' from [Klant_copy] where
Klant= to be copied table (KlantID fields)
Klant_copy= destination table (also KlantID fields)
Can someone tell me why my append querie criteria does'nt work (to
...Combo Box Search By Query
Ive Created a Dialog Box With some tick box's and a Combo Box.
the Dialog box aint quite working the way i would like it to.
the dialog box will open a report which the report has a record source
of the query.
the tick box on is ment to Included Projects that have Maintenance
assigned. but the tick box is set to a yes or no. and the query looks
at this field
the combo box is the same, the combo lists all the end clients, and it
works if i select a client which is assigned to the project, in which
case i could return 100 Starbucks Coffee.
but lets say i just want to display al...Retrieving Value From Subform Only Works In Break Mode 10-17-07
2550 With Me.subSecurity.Form
2551 If Me.subTrades.Enabled = True Then
2553 curParTotal = Me.subTrades.Form!txtParTotal.Value
2554 !txtParTotal.Value = curParTotal
2556 End If
2559 End With
If I put a break on line 2552 or any line above it, the value
gets retrieved and everything works a-ok.
But with a break on line 2553 or no ...How to make LOOKUP give zero if it can't find the lookup_value?
How to make LOOKUP give zero if it can't find the lookup_value?
I usually use something like this:
Where A,B,C are the arguments of the vlookup. I usually use "" (open
quote, close quote) in place of the 0, to give me a blank response.
Instead of =vlookup(...), use =if(isna(vlookup(...)),0,vlookup(...))
"Dmitry Kopnichev" wrote:
> How to make LOOKUP give zero if it can't find the lookup_value?
Care to post your LOOKUP formula?
Dmitry Kopnichev wrote:
> How to ma...Query based on ComboBox selection
I have a table "Master" with the following fields:
I want to have a form with combo boxes for the above fields and TextBox for
date, and a subform having Query or table type. I want user to be able to
filter the subform query based on the criterias chosen from the combobox and
date enterted in the textbox.
I believe I have managed to explain what I want. Anyhelp would be appreciated.
Message posted via AccessMonster.com
I have just found out that I can use
=Forms!NameOfFor...Editing Data source from imported web query
I've currently got an Excel sheet with a table I've imported from a
web page I've created. This was done using the Data->Import External
Data->New Web Query. The page itself is a CGI script and as such
takes arguments in the address.
I was wondering if anyone knows how I can alter that URL from a cell
in Excel. Specifically I want to be able to change the hostname in a
cell in Excel and the corresponding ?hostname='blah' to change.
Any ideas would be greatly appreciated.
It seems to me that you'll need a macro to do what you're trying to
accomp...Tab Control query problem
I have a tab control on a form with two tabs.
The first tab has drop downs for the month/year and other criteria. It also
has a button to run a query based on all of the criteria displayed on the
The second tab also has drop downs for the month/year and different
criteria. It also has a button to run a query based on all of the criteria
displayed on the form.
My problem is that when I am on the second tab, it picks up the month/year
field from the first tab in the query results. I does pick up all of the
'other criteria' from the second tab. How do I correct this?
...User Defined Query Function Not Working with Multiple Criteria
Using Access 2007 I created a user defined function used in a query
that works fine on single criteria but not two or more.
These work fine:
These don't work:
MyFunction="Open Or Closed"
MyFunction="'Open' Or 'Closed'"
What do I need to type to get this to work?
Unless you let us know something specific about the function itself, it's
going to be difficult to diagnose why it isn't working.
Consider posting the function ... (and wouldn't the function spell out how
I have a table "TblRates" Im want to make a calculation on my report based on
The report total is $35000 I need it to look in the table and bring back
3.75% so I can make my calculation on my report.
AmtLow AmtHigh CommRate
10001 20000 3.25%
20001 30000 3.50%
30001 40000 3.75%
Arlend Floyd wrote:
>I have a table "TblRates" Im want to make a calculation on my report based on
>The report total is $35000 I need it to look in the table and bring back
>3.75% so I can make my calcu...Removing duplicate rows in a query
i have a database that is pretty large and everything is based off a site
i am running a query where i have users first name, last name, email address
and site location. but some of the users are at multiple sites.
how do i create a query to not show duplicate contacts for the user?
Remove site location from the fields that are returned.
Or use a query that returns only one site per individual - Which one do you
SELECT FirstName, LastName, Email, First(Location) as SomeLocation
GROUP BY FirstName, LastName, Email
SELECT FirstName, LastName, E...calculating running total of a field in a query
I have a query that calculates the number of planned and then the completed
innovations by month. I would like to be able to have a running total of the
planned field and the same for the completed, in other words a cumulative
total of each as the year progresses. Below is the sql from the query.
SELECT tblMonth.month_name, qryPlannedOnly.PlannedlCumulativeCount,
qryActualOnly.ActualCumulativeCount, qryActualOnly.dir, tblMonth.month_id
FROM (tblMonth LEFT JOIN qryActualOnly ON tblMonth.month_name =
qryActualOnly.ActualCumulativeMonth) LEFT JOIN qryPlannedOnly ON
tblMonth.month_name = q...price field value varies function of quantity field same record
My product has varying price/unit depending on quantity (i.e 1-3 units =
50$/unit, 4-10 units= 45$/unit etc ). Do I need a new record in product table
for each price structure or is there a way of having a range of numbers in a
table field. Then when client chooses quantity I want to select the correct
price in a query. (i.e. select 5 units, so price is 5 x 45$). That part isnt
difficult i think.
Any help would be positive karma.
One product can have many prices (depending on quantity), so you have a
one-to-many relation between parts and prices.
You need this ProductPrice table in add...Internationalisation queries
1. When entering numbers in Excel using scientific notation in the
English version of Excel, the following convention is used
"[number]E[number]", for example 12E3 (i.e. 12,000). Can someone tell
me whether the "E" character is the same for all languages?
2. Error codes such in Excel such as "#N/A" and "#VALUE", can someone
tell me whether these are the same for all languages? And if not, is
there an easy way to determine what the error strings are in the
language locale used by Excel?
...Pass form parameters to query expression
Hi All -
1. Stand-alone Access 2003 mdb database.
2. Custom dialog form with two textboxes for user input (latitude &
3. btnOK on the form sets Me.Visible = False, but does not close the form.
4. A stored Select query based on an underlying table.
5. A controlling VBA procedure that opens the form, waits until btnOK is
pressed, then continues executing.
Question: Can the form variables ‘latitude’ and ‘longitude’ be passed from
the open form (or from the controlling VBA procedure) to an expression in a
query field of the stored query without using an SQL statement? ...adding SQL Query to make Custom POS button
Is there anyway i can access a customers Purchase History in my database and
write a Query to display specific information that i want
SELECT [Transaction].Time, [Transaction].TransactionNumber,
IsNull(Store.Name, '''') AS StoreName,
INNER JOIN [Transaction] ON TransactionEntry.TransactionNumber =
AND TransactionEntry.StoreID = [Transaction].Stor...Excel 2007 data query parameter issue
I cannot add parameters to external data connection (SQL query) in Excel
I am trying to add an external data connection in Excel 2007. I create a new
connection to my SQL Server. I change the properties to a SQL statement. I
embed a question mark (?) where I need parameters, but the "Parameters"
button remains grayed out and unavailable. The query fails with a "No value
given for one or more parameters." Error.
This applies to queries against a stored procedure or table function.
Frustratingly, I have been occasionally successful in getting the Parameters
butt...Help with lookups and arrays
I have two worksheets, "Pricesheets" and "Orders".
Columns in Pricesheets (2500 rows):
- Customer (text)
- Plant (text)
- SKU (text)
- Standard lengths (text, like this: 48' 50' 60')
Colums in Orders (10,000 rows):
- Customer (Text)
- Plant (text)
- SKU (text)
- length ordered (integer)
For each order (a row in Orders), I want to find the matching row in
Pricesheets (based on Customer, Plant and SKU), then return a 1 if the
length ordered was listed in "standard lengths".
I've tried combinations of array-entered SUM(IF()) and FIND(text, withi...Enter Parameter Value Dialog Box
When I open a form it a dialog box appears stating "Enter Parameter Value
Study #". I have looked at the properties and can't seem to find how to turn
this dialog box off. Would someone know how to turn this off?
...Query calculated field based on another query
this is probably fairly straighforward but I just can't get the right syntax
in building this query:
I have the following table containing time records
person, rDate, rHours (tRecords)
I have a table with rates (tRates)
person, Rate1, Rate2
I have a table with national holidays (tHolidays)
I need to have a query which returns the following:
person, rDate, billed: rHours * if (wDate is found in tholidays then Rate2
any pointers greatly appreciated.
what I should have specified is that i have difficulty in trying to get the
value calculated for the cust...What's Wrong With This Query!
What's wrong with this query? I get an error at ">" sign.
SELECT DOCDATE, DUEDATE, ORTRXAMT as Amount,
IIf(Amount > 100, "Past", "No") as Status
SELECT DOCDATE, DUEDATE, ORTRXAMT as Amount,
Case when Amount > 100 then 'Past'
> What's wrong with this query? I get an error at ">" sign.
> SELECT DOCDATE, DUEDATE, ORTRXAMT as Amount,
> IIf(Amount > 100, "Past", "No") as Status
> from RM20101
...Web Query for list of DVD's
I have a spreadsheet with a list of DVD names in Col A.
I would like to know if it is possible to have a web query that
connects to an external site in which you can type the movie name and
it tells you the main actors of the movie, as well as other facts.
www.imbd.com is the site. you can see i can search for varous movies
and it gives in the movie info in a consistant format.
Ideally i would like to use the web query like a formula, with the dvd
name in col a as the variable and the output being the actors name
from the web, in col B. Because i have 400 dvds, i cant create the
li...automatic query refresh #2
I am using JAVA to retrieve data to an excel sheet.
The data is generated in the excel sheet but when I open that excel sheet a
message comes saying
"queries are used to import external data into an excel sheet but harmful
queries can be used to access confidential information or write info back to
"This workbook contains queries to external data that refresh sutomatically".
Can anyone tell me how to get rid of this message?
how to disable automatic query refresh in excel
One possible answer in crashesgpfs
Microsoft MVP - Exc...