Solved Alternative to IN clause

[Kayleigh]

Hi
I have the following query as a form recordsource. It run fairly quickly on Jet but now that I have moved to SQL server it crashes the DB. If I would convert to PT it would mean the form is not editable. Are there any alternatives?
This is the SQL Server version:

SELECT * FROM tblorders
WHERE ([fldOrderID] IN (
SELECT DISTINCT (dbo.tblOrders.fldOrderID)
FROM (dbo.tblOrders INNER JOIN (
SELECT dbo.tblAddress.fldAddressID, concat([fldAAddress1], '  ', [fldastreet], ', ', [fldacity], ', ', [fldapostcode]) AS cfAddress,
IIf([fldCBusiness] Is Null, concat([fldClastname], ', ', [fldcfirstname]),[fldCbusiness]) AS cfClient, dbo.tblClients.fldCPhone1, dbo.tblClients.fldCEmail1, dbo.tblClients.fldClientID,
dbo.tblClients.fldCTradeRetailID, dbo.tblAddress.fldAContactName, dbo.tblAddress.fldAContactPhone
FROM dbo.tblClients INNER JOIN dbo.tblAddress ON dbo.tblClients.fldClientID = dbo.tblAddress.fldAClientID
) lkpqryAddress1 ON dbo.tblOrders.fldOAddressID = lkpqryAddress1.fldAddressID) INNER JOIN dbo.tblOrderDetails ON dbo.tblOrders.fldOrderID = dbo.tblOrderDetails.fldODOrderID
WHERE (((dbo.tblOrderDetails.fldODOrdered) Is Not Null) AND ((dbo.tblOrderDetails.fldODReceived) Is Null))));

 

[theDBguy]

Have you tried using a View and then linking to it?

 

[Kayleigh]

Will a view be updated automatically ?

 

[Minty]

Have you pasted that into SSMS and run it to see if it gives you a more meaningful error.

I can't see anything obviously wrong, although prefixing every field with fld would drive me mad typing it everytime...

 

[Pat Hartman]

Not sure you even need the outer query. Try removing it. Also not sure you need the join to Address at this point either. I'd need the database to sort this out but the query is way more complicated than it needs to be. You have a second subselect also.

If you are doing this with Access, break the process into three separate queries and join the queries with inner joins.

 

[theDBguy]

Will a view be updated automatically ?

You make a view updateable.

 

[Pat Hartman]

Views are like queries. They act like tables in most situations. I'm pretty sure they are updateable by default as long as you include the necessary PKs.

 

[Kayleigh]

Thanks for that advice. I've tried it and it seems to work very successfully!

 

[theDBguy]

Thanks for that advice. I've tried it and it seems to work very successfully!

Not sure which advice that is, but glad to hear you got it sorted out. Good luck with your project.

 

[Pat Hartman]

It is helpful to people who find this thread if you mention which advice helped but we're glad regardless

 

[Kayleigh]

I implemented views. Also appreciate the info about how it updates!