Hi all,
Let me first setup how i accomplished this particular query, and then you guys can help me with improving it.
I currently have a search form which has 4 combo_boxes each based on a query
----
* Combo1 =
The table EMY looks like this
The Primary field has check boxes as Yes or No
-------
* Combo2 =
The table for VC looks like this
---
* Combo3 =
the Table for Office Type Looks like this
----
* Combo4 =
The table for grpoffic looks like this
What i do next is i use i try to search through two tables using these combo box fields. So the query for my search is
Sosx table looks like this
I think the part thats taking a long time is when it does a pattern search. Is there anyway to optimize this so it doesnt take too much time?
Keep in mind that Sosx table has around 1.4 million entries
I know this is kind of a long problem, but i will be really grateful if someone can help me out.
Thanks in advance
Let me first setup how i accomplished this particular query, and then you guys can help me with improving it.
I currently have a search form which has 4 combo_boxes each based on a query
----
* Combo1 =
Code:
"SELECT [EMY].mmmyy, [EMY].Description, [EMY].Primary, [EMY].[Pattern Mask] FROM [EMY]; "
The table EMY looks like this
Code:
[U]ID[/U] [U]mmmyy[/U] [U]Description[/U] [U]Primary[/U] [U]Pattern Mask[/U]
15 feb03 February 03 Primary Yes 033
16 apr03 April 03 Election No 03??4
17 mar04 March 04 Primary Yes 041
18 nov04 November 04 Election No 04??2
The Primary field has check boxes as Yes or No
-------
* Combo2 =
Code:
"SELECT [VC].mmmyy, [VC].[Voter Code], [Voter Code].[Voter Code Description], [VC].[Voter Code Pattern Mask]
FROM [VC]
WHERE ((([VC].mmmyy)=[I]forms![SOS Report Generator]!Combo1[/I])); "
The table for VC looks like this
mmmyy Voter Code Voter Code Description Voter Code Pattern Mask
nov02 * All *02??2*
nov02 N Did not vote *02??2N*
nov02 V Voted *02??2V*
mar02 * All *021*
mar02 D Voted Democrat *021D*
mar02 L Voted Libertarian *021L*
mar02 N Did not vote *021N*
mar02 P Voted Non-Partisan *021P*
mar02 R Voted Republican *021R*
mar02 V Voted *021V*
apr03 * All *03??4*
apr03 N Did not vote *03??4N*
apr03 V Voted *03??4V*
feb03 * All *033*
feb03 D Voted Democrat *033D*
feb03 N Did not vote *033N*
feb03 P Voted Non-Partisan *033P*
feb03 R Voted Republican *033R*
nov04 * All *04??2*
nov04 N Did not vote *04??2N*
nov04 V Voted *04??2V*
mar04 * All *041*
mar04 D Voted Democrat *041D*
mar04 L Voted Libertarian *041L*
mar04 N Did not vote *041N*
mar04 P Voted Non-Partisan *041P*
mar04 R Voted Republican *041R*
mar04 V Voted *041V*
---
* Combo3 =
Code:
"SELECT [Office Type].type, [Office Type].description
FROM [Office Type]
ORDER BY [Office Type].description;"
the Table for Office Type Looks like this
Code:
[U]type[/U] [U]description[/U]
05 Township
10 Municipality
15 Elementary School
20 High School
25 College
----
* Combo4 =
Code:
"SELECT grpoffic.TYPE, grpoffic.OFFICE
FROM grpoffic
GROUP BY grpoffic.TYPE, grpoffic.OFFICE
HAVING (((grpoffic.TYPE)=forms![SOS Report Generator]![I]Combo3[/I])); "
The table for grpoffic looks like this
Code:
[U]PRECINCT[/U] [U]G[/U] [U]CODE[/U] [U]TYPE[/U] [U]OFFICE[/U]
7000001 0 02-0010-100 05 B Township
7000001 0 03-0030-100 10 Village of B
What i do next is i use i try to search through two tables using these combo box fields. So the query for my search is
Code:
SELECT Sosx.Certnum, Sosx.Township, Sosx.Ward, Sosx.Precinct, Sosx.[Reg Date], Sosx.[Name], Sosx.Feb07, Sosx.Apr07, Sosx.Mar06, Sosx.Nov06, Sosx.Feb05, Sosx.Apr05, Sosx.Mar04, Sosx.Nov04, Sosx.Feb03, Sosx.Apr03, Sosx.[Voting History Combined]
FROM Sosx INNER JOIN grpoffic ON (Sosx.[Precinct Combined] = grpoffic.PRECINCT) AND (Sosx.Group = grpoffic.G)
WHERE (((Sosx.[Voting History Combined]) Like [forms]![SOS Report Generator]![[U][B]Combo2[/B][/U]]) AND ((grpoffic.OFFICE)=[forms]![SOS Report Generator]![[I][B]Combo4[/B][/I]]))
ORDER BY Sosx.[Precinct Combined];
Sosx table looks like this
Code:
Certnum Township City Ward Precinct Name Feb07 Apr07 Mar06 Nov06 Feb05 Apr05 Mar0 Nov04 Feb0 Apr03 Voting History Combined Precinct Combined
00000001 96 00 01 John Doe F 4 N N N N N V N N 073N4N061N2N053N4NN041N2V033N4N 9600013
I think the part thats taking a long time is when it does a pattern search. Is there anyway to optimize this so it doesnt take too much time?
Keep in mind that Sosx table has around 1.4 million entries
I know this is kind of a long problem, but i will be really grateful if someone can help me out.
Thanks in advance