?
Solved

improve query

Posted on 2012-09-04
19
Medium Priority
?
308 Views
Last Modified: 2012-09-05
I have utilised a query from an ee expert to give me a list of [mvris code] that has a link to any [abicode] pulled up by qrytransmissionEventsforreview.

the query works but is slow, can it be improved?

It is unfortunate that the abi codes could reside in 1 of 4  columns in abitomvris table.

SELECT QryTransmissionEventsforReview.AbiCode, tClient.TRANSMISSION_TYPE, AbiToMvris.[MVRIS CODE], SMMT.TRANSMISSION
FROM tClient INNER JOIN QryTransmissionEventsforReview ON tClient.abiCode = QryTransmissionEventsforReview.AbiCode, AbiToMvris INNER JOIN SMMT ON AbiToMvris.[MVRIS CODE] = SMMT.[MVRIS CODE]
WHERE (((IIf(IsNull([abi1]),[closestmatchabi1],[abi1]))=[QryTransmissionEventsforReview.AbiCode])) OR (((IIf(IsNull([insecom]),[Insecom_ClosestMatch],[insecom]))=[QryTransmissionEventsforReview.AbiCode]))
GROUP BY QryTransmissionEventsforReview.AbiCode, tClient.TRANSMISSION_TYPE, AbiToMvris.[MVRIS CODE], SMMT.TRANSMISSION
ORDER BY QryTransmissionEventsforReview.AbiCode;

Open in new window

ee-example.PNG
0
Comment
Question by:PeterBaileyUk
  • 8
  • 6
  • 5
19 Comments
 
LVL 25

Expert Comment

by:lwadwell
ID: 38363263
Try this ... I assumed abi1, closestmatchabi1, insecom and Insecom_ClosestMatch were the 4 fields.
SELECT QryTransmissionEventsforReview.AbiCode, tClient.TRANSMISSION_TYPE, AbiToMvris.[MVRIS CODE], SMMT.TRANSMISSION
FROM tClient 
INNER JOIN QryTransmissionEventsforReview ON tClient.abiCode = QryTransmissionEventsforReview.AbiCode
INNER JOIN AbiToMvris ON (((IIf(IsNull([abi1]),[closestmatchabi1],[abi1]))=[QryTransmissionEventsforReview.AbiCode])) OR (((IIf(IsNull([insecom]),[Insecom_ClosestMatch],[insecom]))=[QryTransmissionEventsforReview.AbiCode]))
INNER JOIN SMMT ON AbiToMvris.[MVRIS CODE] = SMMT.[MVRIS CODE]
GROUP BY QryTransmissionEventsforReview.AbiCode, tClient.TRANSMISSION_TYPE, AbiToMvris.[MVRIS CODE], SMMT.TRANSMISSION
ORDER BY QryTransmissionEventsforReview.AbiCode;

Open in new window

0
 

Author Comment

by:PeterBaileyUk
ID: 38363273
it must be almost there its failed on syntax.
ee-example.PNG
0
 
LVL 25

Expert Comment

by:lwadwell
ID: 38363285
take 2.
SELECT QryTransmissionEventsforReview.AbiCode, tClient.TRANSMISSION_TYPE, AbiToMvris.[MVRIS CODE], SMMT.TRANSMISSION
FROM ((tClient 
INNER JOIN QryTransmissionEventsforReview ON tClient.abiCode = QryTransmissionEventsforReview.AbiCode)
INNER JOIN AbiToMvris ON (IIf(IsNull([abi1]),[closestmatchabi1],[abi1])=[QryTransmissionEventsforReview.AbiCode]) 
OR (IIf(IsNull([insecom]),[Insecom_ClosestMatch],[insecom])=[QryTransmissionEventsforReview.AbiCode]))
INNER JOIN SMMT ON AbiToMvris.[MVRIS CODE] = SMMT.[MVRIS CODE]
GROUP BY QryTransmissionEventsforReview.AbiCode, tClient.TRANSMISSION_TYPE, AbiToMvris.[MVRIS CODE], SMMT.TRANSMISSION
ORDER BY QryTransmissionEventsforReview.AbiCode;

Open in new window

0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:PeterBaileyUk
ID: 38363299
it didnt like the bracketing, which i removed now it says join expression not supported maybe its just not possible to create the join on an iif expression.
0
 
LVL 25

Expert Comment

by:lwadwell
ID: 38363319
OK then ... use an inline view to create the value as columns before the join
SELECT QryTransmissionEventsforReview.AbiCode, tClient.TRANSMISSION_TYPE, v.[MVRIS CODE], SMMT.TRANSMISSION
FROM tClient 
INNER JOIN QryTransmissionEventsforReview ON tClient.abiCode = QryTransmissionEventsforReview.AbiCode
INNER JOIN (SELECT [MVRIS CODE]
                 , IIf(IsNull([abi1]),[closestmatchabi1],[abi1]) as M1
                 , IIf(IsNull([insecom]),[Insecom_ClosestMatch],[insecom]) as M2
            FROM AbiToMvris) as v ON    (v.M1=[QryTransmissionEventsforReview.AbiCode]) 
                                     OR (v.M2=[QryTransmissionEventsforReview.AbiCode])
INNER JOIN SMMT ON v.[MVRIS CODE] = SMMT.[MVRIS CODE]
GROUP BY QryTransmissionEventsforReview.AbiCode, tClient.TRANSMISSION_TYPE, v.[MVRIS CODE], SMMT.TRANSMISSION
ORDER BY QryTransmissionEventsforReview.AbiCode;

Open in new window

0
 

Author Comment

by:PeterBaileyUk
ID: 38363721
ok now its back to the error again.. i will see if i can create a cutdown db and post it.
error.PNG
0
 
LVL 25

Expert Comment

by:lwadwell
ID: 38363776
even an empty db will allow me to test the syntax.
0
 
LVL 41

Expert Comment

by:ralmada
ID: 38363799
what are you trying to do with this?

WHERE (((IIf(IsNull([abi1]),[closestmatchabi1],[abi1]))=[QryTransmissionEventsforReview.AbiCode])) OR (((IIf(IsNull([insecom]),[Insecom_ClosestMatch],[insecom]))=[QryTransmissionEventsforReview.AbiCode]))

Open in new window


It seems something like this

SELECT 	QryTransmissionEventsforReview.AbiCode, 
	tClient.TRANSMISSION_TYPE, 
	AbiToMvris.[MVRIS CODE], 
	SMMT.TRANSMISSION
FROM ((tClient 
INNER JOIN QryTransmissionEventsforReview ON tClient.abiCode = QryTransmissionEventsforReview.AbiCode)
INNER JOIN AbiToMvris ON QryTransmissionEventsforReview.AbiCode = NZ([abi1], [closestmatchabi1]) OR
			 QryTransmissionEventsforReview.AbiCode = NZ([insecom], [insecom_closestmatch]))
INNER JOIN SMMT ON AbiToMvris.[MVRIS CODE] = SMMT.[MVRIS CODE]
GROUP BY QryTransmissionEventsforReview.AbiCode, 
	tClient.TRANSMISSION_TYPE, 
	AbiToMvris.[MVRIS CODE], 
	SMMT.TRANSMISSION
ORDER BY QryTransmissionEventsforReview.AbiCode;

Open in new window



Or you might want to simplify the AbiToMvris join even further to this:

INNER JOIN AbiToMvris ON QryTransmissionEventsforReview.AbiCode = NZ([abi1], [closestmatchabi1], [insecom], [insecom_closestmatch])

Open in new window

0
 

Author Comment

by:PeterBaileyUk
ID: 38364045
I simplified the db and have uploaded, i tried to create your example but it wont let me save a query that it says has an error.
EEExampledb.zip
0
 
LVL 41

Expert Comment

by:ralmada
ID: 38364086
i tried to create your example but it wont let me save a query that it says has an error.  

Are you referring to my last comment?

What's the error message you get?

btw, you can use distinct instead of group by.

Also pay special attention to the brackets there:

SELECT DISTINCT	QryTransmissionEventsforReview.AbiCode, 
	tClient.TRANSMISSION_TYPE, 
	AbiToMvris.[MVRIS CODE], 
	SMMT.TRANSMISSION
FROM (((tClient 
INNER JOIN QryTransmissionEventsforReview ON tClient.abiCode = QryTransmissionEventsforReview.AbiCode)
INNER JOIN AbiToMvris ON QryTransmissionEventsforReview.AbiCode = NZ([abi1], [closestmatchabi1]) OR
			 QryTransmissionEventsforReview.AbiCode = NZ([insecom], [insecom_closestmatch]))
INNER JOIN SMMT ON AbiToMvris.[MVRIS CODE] = SMMT.[MVRIS CODE])
ORDER BY QryTransmissionEventsforReview.AbiCode;

Open in new window

0
 
LVL 41

Expert Comment

by:ralmada
ID: 38364175
AND using IIF

SELECT DISTINCT	QryTransmissionEventsforReview.AbiCode, 
	tClient.TRANSMISSION_TYPE, 
	AbiToMvris.[MVRIS CODE], 
	SMMT.TRANSMISSION
FROM (((tClient 
INNER JOIN QryTransmissionEventsforReview ON tClient.abiCode = QryTransmissionEventsforReview.AbiCode)
INNER JOIN AbiToMvris ON (QryTransmissionEventsforReview.AbiCode = IIF(isnull([abi1]), [closestmatchabi1], [abi1])) OR
			 (QryTransmissionEventsforReview.AbiCode = IIF(isnull([insecom]), [insecom_closestmatch], [insecom])))
INNER JOIN SMMT ON AbiToMvris.[MVRIS CODE] = SMMT.[MVRIS CODE])
ORDER BY QryTransmissionEventsforReview.AbiCode;

Open in new window

0
 

Author Comment

by:PeterBaileyUk
ID: 38364312
there must be something strange going on because its now saying join not supported, i copied and pasted to avoid typos.
0
 

Author Comment

by:PeterBaileyUk
ID: 38364317
i am in access 2010
0
 
LVL 41

Expert Comment

by:ralmada
ID: 38364374
which one is giving you that error?

Could you try this as well:

SELECT distinct	QryTransmissionEventsforReview.AbiCode, 
	tClient.TRANSMISSION_TYPE, 
	AbiToMvris.[MVRIS CODE], 
	SMMT.TRANSMISSION
FROM tClient 
INNER JOIN QryTransmissionEventsforReview ON tClient.abiCode = QryTransmissionEventsforReview.AbiCode
INNER JOIN AbiToMvris ON QryTransmissionEventsforReview.AbiCode = nz(AbiToMvris.abi1, AbiToMvris.closestmatchabi1, AbiToMvris.insecom, AbiToMvris.insecom_closetmatch)
INNER JOIN SMMT ON AbiToMvris.[MVRIS CODE] = SMMT.[MVRIS CODE]
ORDER BY QryTransmissionEventsforReview.AbiCode;

Open in new window

0
 

Author Comment

by:PeterBaileyUk
ID: 38364411
the latter 38364374 gives syntax error and 38364175 gives join expression not supported. did it not do the same in the db i posted?
0
 
LVL 41

Expert Comment

by:ralmada
ID: 38364664
Sorry, don't have Access installed on this computer,

For 38364175, There is a bracket issue, try:

SELECT DISTINCT	QryTransmissionEventsforReview.AbiCode, 
	tClient.TRANSMISSION_TYPE, 
	AbiToMvris.[MVRIS CODE], 
	SMMT.TRANSMISSION
FROM ((tClient 
INNER JOIN QryTransmissionEventsforReview ON tClient.abiCode = QryTransmissionEventsforReview.AbiCode)
INNER JOIN AbiToMvris ON (QryTransmissionEventsforReview.AbiCode = IIF(isnull([abi1]), [closestmatchabi1], [abi1])) OR
			 (QryTransmissionEventsforReview.AbiCode = IIF(isnull([insecom]), [insecom_closestmatch], [insecom])))
INNER JOIN SMMT ON AbiToMvris.[MVRIS CODE] = SMMT.[MVRIS CODE]
ORDER BY QryTransmissionEventsforReview.AbiCode;

Open in new window


And for the other one, try adding some brackets there:

SELECT distinct	QryTransmissionEventsforReview.AbiCode, 
	tClient.TRANSMISSION_TYPE, 
	AbiToMvris.[MVRIS CODE], 
	SMMT.TRANSMISSION
FROM ((tClient 
INNER JOIN QryTransmissionEventsforReview ON tClient.abiCode = QryTransmissionEventsforReview.AbiCode)
INNER JOIN AbiToMvris ON QryTransmissionEventsforReview.AbiCode = nz(AbiToMvris.abi1, AbiToMvris.closestmatchabi1, AbiToMvris.insecom, AbiToMvris.insecom_closetmatch))
INNER JOIN SMMT ON AbiToMvris.[MVRIS CODE] = SMMT.[MVRIS CODE]
ORDER BY QryTransmissionEventsforReview.AbiCode;

Open in new window

0
 
LVL 25

Expert Comment

by:lwadwell
ID: 38366154
Using a query ...
Created a query called AbiToMvrisM1M2:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SELECT [MVRIS CODE], NZ([abi1],[closestmatchabi1]) AS M1, NZ([insecom],[Insecom_ClosestMatch]) AS M2
FROM AbiToMvris;

Then the main SQL:
~~~~~~~~~~~~~~~~~~
SELECT QryTransmissionEventsforReview.AbiCode, tClient.TRANSMISSION_TYPE, v.[MVRIS CODE], SMMT.TRANSMISSION
FROM ((tClient INNER JOIN QryTransmissionEventsforReview ON tClient.abiCode=QryTransmissionEventsforReview.AbiCode) INNER JOIN AbiToMvrisM1M2 AS v ON v.M1=QryTransmissionEventsforReview.AbiCode OR v.M2=QryTransmissionEventsforReview.AbiCode) INNER JOIN SMMT ON v.[MVRIS CODE]=SMMT.[MVRIS CODE]
GROUP BY QryTransmissionEventsforReview.AbiCode, tClient.TRANSMISSION_TYPE, v.[MVRIS CODE], SMMT.TRANSMISSION
ORDER BY QryTransmissionEventsforReview.AbiCode;

Open in new window


All in one:
SELECT QryTransmissionEventsforReview.AbiCode, tClient.TRANSMISSION_TYPE, v.[MVRIS CODE], SMMT.TRANSMISSION
FROM ((tClient INNER JOIN QryTransmissionEventsforReview ON tClient.abiCode=QryTransmissionEventsforReview.AbiCode) INNER JOIN (SELECT [MVRIS CODE], NZ([abi1],[closestmatchabi1]) AS M1, NZ([insecom],[Insecom_ClosestMatch]) AS M2 FROM AbiToMvris)  AS v ON (v.M1=QryTransmissionEventsforReview.AbiCode) Or (v.M2=QryTransmissionEventsforReview.AbiCode)) INNER JOIN SMMT ON v.[MVRIS CODE]=SMMT.[MVRIS CODE]
GROUP BY QryTransmissionEventsforReview.AbiCode, tClient.TRANSMISSION_TYPE, v.[MVRIS CODE], SMMT.TRANSMISSION
ORDER BY QryTransmissionEventsforReview.AbiCode;

Open in new window


Another thing that might help performance ... the QryTransmissionEventsforReview query may be better as this (it might enable you to avoid the GROUP BY or a DISTINCT in the query above)
SELECT DISTINCT AbiCode
FROM TblTransmission
WHERE AbiCode IN (SELECT v.AbiCode
FROM ((SELECT TblTransmission.AbiCode, MIN(TblTransmission.ChangeYearMonth) as minYM, MAX(TblTransmission.ChangeYearMonth) as maxYM FROM TblTransmission GROUP BY TblTransmission.AbiCode) as v
INNER JOIN TblTransmission as first ON first.ChangeYearMonth = v.minYM and first.AbiCode = v.AbiCode)
INNER JOIN TblTransmission as last ON last.ChangeYearMonth = v.maxYM and last.AbiCode = v.AbiCode
WHERE first.TransmissionPrev <> last.TransmissionChange);

Open in new window

0
 
LVL 41

Accepted Solution

by:
ralmada earned 2000 total points
ID: 38366461
OK, in my suggestion, the alias were missing

SELECT DISTINCT	QryTransmissionEventsforReview.AbiCode, 
	tClient.TRANSMISSION_TYPE, 
	AbiToMvris.[MVRIS CODE], 
	SMMT.TRANSMISSION
FROM (((tClient 
INNER JOIN QryTransmissionEventsforReview ON tClient.abiCode = QryTransmissionEventsforReview.AbiCode)
INNER JOIN AbiToMvris ON QryTransmissionEventsforReview.AbiCode = NZ(AbiToMvris.[abi1], AbiToMvris.[closestmatchabi1]) OR
			 QryTransmissionEventsforReview.AbiCode = NZ(AbiToMvris.[insecom], AbiToMvris.[insecom_closestmatch]))
INNER JOIN SMMT ON AbiToMvris.[MVRIS CODE] = SMMT.[MVRIS CODE])
ORDER BY QryTransmissionEventsforReview.AbiCode;

Open in new window


And actually query QryTransmissionEventsforReview could be simplified to:

SELECT 	v.AbiCode, 
	MIN(v.ChangeYearMonth) as minYM, 
	MAX(v.ChangeYearMonth) as maxYM,
	First(v.TransmissionPrev) as Tprev,
	Last(v.TransmissionChange) as Tchange
FROM (select 	distinct AbiCode, ChangeYearMonth, TransmissionPrev, TransmissionChange from TblTransmission) as v
GROUP BY v.AbiCode
having 	First(v.TransmissionPrev) <> 	Last(v.TransmissionChange);

Open in new window

0
 

Author Closing Comment

by:PeterBaileyUk
ID: 38367288
thank you!
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

621 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question