I am using a form to populate a query. The query is looking at around 1.4 Million records to date, every week this will only increase. Currently when I run my query from the form via a command button, the query will take about 8-10 min to run. Is there a better way to do this? I will post the SQL of the query, and if something else is needed let me know. -
SELECT [Generic Material].CLAIM_NBR, [Generic Material].DETAIL_NBR, [Generic Material].CLAIM_TYPE_DESCR, [Generic Material].[CLAIM DATE], [Generic Material].[SHIP DATE], [Generic Material].PROD_CODE, InStr([Forms]![Form1]![txtsee].[Text],[Prod_Code]) AS ProdCodeParam, [Generic Material].PROD_CODE_DESCR, [Generic Material].RFC_CODE, [Generic Material].PROD_GRP, [Generic Material].RFC_FAILURE_DESCR, [Generic Material].RFC_FAILURE_MAT_IND, [Generic Material].RFC_FAILURE_LBR_IND, [Generic Material].RFC_LEVEL_ONE_DESCR, [Generic Material].RFC_LEVEL_TWO_DESCR, [Generic Material].RFC_LEVEL_THREE_DESCR, [Generic Material].RFC_LEVEL_FOUR_DESCR, [Generic Material].RFC_SHORT_DESCR, [Generic Material].BU_ID, [Generic Material].PART_PART_NBR, [Generic Material].PART_DESCR, [Generic Material].SUBMIT_OFFICE_NAME, [Generic Material].PARENT_SERIAL_NBR, Mid([ORDERED_MODEL],1,38) AS [Model Number], [Generic Material].COST_CENTER, [Generic Material].ACCOUNT, [Generic Material].TASK_CODE, [Generic Material].TASK_DESCR, [Generic Material].SALES_ORDER_PRIME, [Generic Material].[DATE ENTERED], [Generic Material].[DATE APPROVED], [Generic Material].[DATE STARTED], [Generic Material].[DATE FAILED], [Generic Material].DETAIL_QTY, [Generic Material].SumOfEXP_TYPE_AMOUNT, [Generic Material].CLAIM_JOB_NAME
-
FROM [Generic Material]
-
WHERE ((([Generic Material].[CLAIM DATE]) Between [Forms]![Form1]![cmbcd] And [Forms]![Form1]![cmbcde]) AND (([Generic Material].[SHIP DATE]) Between [Forms]![Form1]![cmbsd] And [Forms]![Form1]![cmbed]) AND ((InStr([Forms]![Form1]![txtsee].[Text],[Prod_Code]))>0 Or (InStr([Forms]![Form1]![txtsee].[Text],[Prod_Code])) Is Null) AND (([Generic Material].RFC_LEVEL_ONE_DESCR) Like ("*" & [Forms]![Form1]![cmbrfcl1dscr] & "*")) AND (([Generic Material].RFC_SHORT_DESCR) Like ("*" & [Forms]![Form1]![cmbrfcsd] & "*")) AND (([Generic Material].PART_PART_NBR) Like ("*" & [Forms]![Form1]![cmbpn] & "*")) AND ((Mid([ORDERED_MODEL],1,38)) Like ("*" & [Forms]![Form1]![cmbm1] & [Forms]![Form1]![cmbm2] & [Forms]![Form1]![cmbm3] & [Forms]![Form1]![cmbm4] & [Forms]![Form1]![cmbm5] & [Forms]![Form1]![cmbm6] & [Forms]![Form1]![cmbm7] & [Forms]![Form1]![cmbm8] & [Forms]![Form1]![cmbm9] & [Forms]![Form1]![cmbm10] & [Forms]![Form1]![cmbm11] & [Forms]![Form1]![cmbm12] & [Forms]![Form1]![cmbm13] & [Forms]![Form1]![cmbm14] & [Forms]![Form1]![cmbm15] & [Forms]![Form1]![cmbm16] & [Forms]![Form1]![cmbm17] & [Forms]![Form1]![cmbm18] & [Forms]![Form1]![cmbm19] & [Forms]![Form1]![cmbm20] & [Forms]![Form1]![cmbm21] & [Forms]![Form1]![cmbm22] & [Forms]![Form1]![cmbm23] & [Forms]![Form1]![cmbm24] & [Forms]![Form1]![cmbm25] & [Forms]![Form1]![cmbm26] & [Forms]![Form1]![cmbm27] & [Forms]![Form1]![cmbm28] & [Forms]![Form1]![cmbm29] & [Forms]![Form1]![cmbm30] & [Forms]![Form1]![31] & [Forms]![Form1]![32] & [Forms]![Form1]![33] & [Forms]![Form1]![34] & [Forms]![Form1]![35] & [Forms]![Form1]![36] & [Forms]![Form1]![37] & [Forms]![Form1]![38] & "*")) AND (([Generic Material].TASK_CODE) Like ("*" & [Forms]![Form1]![cmbtc] & "*")) AND (([Generic Material].TASK_DESCR) Like ("*" & [Forms]![Form1]![cmbtd] & "*")) AND (([Generic Material].[DATE STARTED]) Between [Forms]![Form1]![cmcsd1] And [Forms]![Form1]![cmcsd2]) AND (([Generic Material].[DATE FAILED]) Between [Forms]![Form1]![cmcfd1] And [Forms]![Form1]![cmbfd2]));
-
5  1587  
I'm a little concerned about your brackets. Access will add further brackets but can you copy and paste this in exactly as it is and see if there is any improvement.
Mary -
SELECT CLAIM_NBR, DETAIL_NBR, CLAIM_TYPE_DESCR,
-
[CLAIM DATE], [SHIP DATE], PROD_CODE,
-
InStr([Forms]![Form1]![txtsee].[Text],[Prod_Code]) AS ProdCodeParam,
-
PROD_CODE_DESCR, RFC_CODE, PROD_GRP,
-
RFC_FAILURE_DESCR, RFC_FAILURE_MAT_IND,
-
RFC_FAILURE_LBR_IND, RFC_LEVEL_ONE_DESCR,
-
RFC_LEVEL_TWO_DESCR, RFC_LEVEL_THREE_DESCR,
-
RFC_LEVEL_FOUR_DESCR, RFC_SHORT_DESCR, BU_ID,
-
PART_PART_NBR, PART_DESCR, SUBMIT_OFFICE_NAME,
-
PARENT_SERIAL_NBR,
-
Mid([ORDERED_MODEL],1,38) AS [Model Number],
-
COST_CENTER, ACCOUNT, TASK_CODE, TASK_DESCR,
-
SALES_ORDER_PRIME, [DATE ENTERED], [DATE APPROVED],
-
[DATE STARTED], [DATE FAILED], DETAIL_QTY,
-
SumOfEXP_TYPE_AMOUNT, CLAIM_JOB_NAME
-
FROM [Generic Material]
-
WHERE ([CLAIM DATE] Between [Forms]![Form1]![cmbcd] And [Forms]![Form1]![cmbcde])
-
AND ([SHIP DATE] Between [Forms]![Form1]![cmbsd] And [Forms]![Form1]![cmbed])
-
AND (InStr([Forms]![Form1]![txtsee].[Text],[Prod_Code])>0 Or InStr([Forms]![Form1]![txtsee].[Text],[Prod_Code]) Is Null)
-
AND (RFC_LEVEL_ONE_DESCR
-
Like '*' & [Forms]![Form1]![cmbrfcl1dscr] & '*')
-
AND (RFC_SHORT_DESCR)
-
Like '*' & [Forms]![Form1]![cmbrfcsd] & '*')
-
AND (PART_PART_NBR Like '*' & [Forms]![Form1]![cmbpn] & '*')
-
AND (Mid([ORDERED_MODEL],1,38)
-
Like '*' & [Forms]![Form1]![cmbm1] &
-
[Forms]![Form1]![cmbm2] & [Forms]![Form1]![cmbm3] & [Forms]![Form1]![cmbm4] & [Forms]![Form1]![cmbm5] & [Forms]![Form1]![cmbm6] & [Forms]![Form1]![cmbm7] & [Forms]![Form1]![cmbm8] & [Forms]![Form1]![cmbm9] & [Forms]![Form1]![cmbm10] & [Forms]![Form1]![cmbm11] & [Forms]![Form1]![cmbm12] & [Forms]![Form1]![cmbm13] & [Forms]![Form1]![cmbm14] & [Forms]![Form1]![cmbm15] & [Forms]![Form1]![cmbm16] & [Forms]![Form1]![cmbm17] & [Forms]![Form1]![cmbm18] & [Forms]![Form1]![cmbm19] & [Forms]![Form1]![cmbm20] & [Forms]![Form1]![cmbm21] & [Forms]![Form1]![cmbm22] & [Forms]![Form1]![cmbm23] & [Forms]![Form1]![cmbm24] & [Forms]![Form1]![cmbm25] & [Forms]![Form1]![cmbm26] & [Forms]![Form1]![cmbm27] & [Forms]![Form1]![cmbm28] & [Forms]![Form1]![cmbm29] & [Forms]![Form1]![cmbm30] & [Forms]![Form1]![31] & [Forms]![Form1]![32] & [Forms]![Form1]![33] & [Forms]![Form1]![34] & [Forms]![Form1]![35] & [Forms]![Form1]![36] & [Forms]![Form1]![37] & [Forms]![Form1]![38] & '*')
-
AND (TASK_CODE Like '*' & [Forms]![Form1]![cmbtc] & '*')
-
AND (TASK_DESCR Like '*' & [Forms]![Form1]![cmbtd] & '*')
-
AND ([DATE STARTED] Between [Forms]![Form1]![cmcsd1] And [Forms]![Form1]![cmcsd2])
-
AND ([DATE FAILED] Between [Forms]![Form1]![cmcfd1] And [Forms]![Form1]![cmbfd2]);
-
That worked very well, thank you for your help.
That worked very well, thank you for your help.
No problem.
Mary
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Trev |
last post by:
Which is more efficient:
Select * from table1 where id in (select id from table2)
or
Select * from table1 where exists(select * from table2 where
table2.id=table1.id)
|
by: Koen |
last post by:
Hi all,
At work I created a database which is really helpful. The database is used
by approx 15 users. Everything worked great, until I added some
'scoreboard' forms and reports. I get the...
|
by: Bob Alston |
last post by:
Some more, rather specific Access performance questions. IN a split
front-end & back-end Access/Jet ONLY LAN situation, and with all query
criteria fields and join fields indexed:
1. Is is...
|
by: Brian Wotherspoon |
last post by:
I have a table with data that is refreshed regularly but I still need to
store the old data. I have created a seperate table with a foreign key
to the table and the date on which it was replaced. ...
|
by: CJM |
last post by:
I have an ASP/ADO application querying an SQL Server DB. I want know the
most efficient way to determine if more than one row is returned from a
query. If more than one row is returned, the user...
|
by: laredotornado |
last post by:
Hi, I'm using PHP 4.3. I have 15 pages in which I need to take the
content of the BODY and put it in a little table ...
<table>
<tr><td colspan="3"><img src="header.gif"></td></tr>
<tr>...
|
by: teser3 |
last post by:
I would like to know if it is possible to do a pattern match or better
way to condense my below query in Access 2003.
Basically I need to query the average with all fields in TableOne that
end...
|
by: Mark |
last post by:
Hey folks, I'm looking at making the following query more efficient
potentially using the ranking functions and I'd like some advice from
the gurus. The purpose of the following is to have a...
|
by: Ken Fine |
last post by:
This is a question that someone familiar with ASP.NET and ADO.NET DataSets
and DataTables should be able to answer fairly easily. The basic question is
how I can efficiently match data from one...
|
by: joshai |
last post by:
Hi, I'm pretty new to the php/mysql world and am building an article database for a website with multiple content types. I have an entry screen built that allows the site owner to enter articles,...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
| |
