SQL PT QRY - how to select all records/ criteria? (1 Viewer)

[mattkorguk]

Good morning All,
We are moving from our current Access FE/BE set to an SQL data BE.
We would also like to move some of the more 'power hungry' queries over to the SQL side to try and speed things up a bit.
Basically, we have a cont. form which displays your list of clients (1000+ possibly) and we've provided criteria option in the form of text boxes and drop down lists. These work ok in Access but can sometimes take a while to populate.
Below is one of the PT queries I've been working on, and it works well, although only when someone has selected a criteria for EACH search option, this is not very helpful. In the current format we can use "*" as a wildcard search, but I'm unsure of the option in SQL.
Also, when the criteria has been updated, is it possible to then refresh the query/ form somehow as me.requery / me.refresh don't seem to work.

SELECT dbo.[case].[Case Id], dbo.[case].Administrator, dbo.[case].[Corres Addr1], _
dbo.[case].[Corres Addr2],dbo.[case].[Corres PCode], dbo.[case].[Sales Rep], dbo.[client].[Title], _
dbo.[client].[Surname],dbo.[client].[Forename],dbo.[case].[Case Status], dbo.[Parties].[Co Name], _
[qryCase-Activity-Count].MtgCt, [qryCase-Activity-Count].FSCt,[qryCase-Activity-Count].GICt, _
dbo.[case].[Customer Type], dbo.[case].Folio _
FROM ((dbo.[case] _
LEFT JOIN dbo.[Parties] _
ON dbo.[case].[Sales Rep] = dbo.[Parties].[Party Id]) _
LEFT JOIN [qryCase-Activity-Count] _
ON dbo.[case].[Case Id] = [qryCase-Activity-Count].[Case]) _
LEFT JOIN dbo.[client] ON dbo.[case].[Case Id] = dbo.[client].[Case Id] _
WHERE (((dbo.[case].Administrator) =N'HH') AND ((dbo.[case].[Corres PCode]) =N'se1') _
AND ((dbo.[client].[Surname]) =N'bond*')AND ((dbo.[case].[Case Status]) =N'In Progress') _
AND ((Parties.[Co Name]) =N'D Bush'Or (dbo.[Parties].[Co Name]) Is Null) _
AND (([qryCase-Activity-Count].MtgCt) = 1) AND (([qryCase-Activity-Count].FSCt) = 1) _
AND (([qryCase-Activity-Count].GICt) = 2) AND ((dbo.[case].[Customer Type]) =N'New')) _
ORDER BY dbo.[client].[Surname], dbo.[client].[Forename];

Any pointers, as always greatly appreciated.
Thanks.

 

[gemma-the-husky]

a technique with stuff like this is generally to try and limit the size of the dataset before the selection

so eg, make the subform unbound, select a customer first, then bind the form (declare the record source) to avoid having ot return 1000's of unnecessary rows


depends what you need to see, of course

 

[mattkorguk]

Ah, thanks for that. It's a little tricky as the initial form should be an Alpha list of clients, basically all of them, and then they can drill down into the list using the filters.
I'll put forward your suggestion though, if it's speed they're after, that sounds a good route to take.

 

[gemma-the-husky]

i think it perhaps depends what you need/have to see before you select the criteria

i think possibly the joined subquery

[qryCase-Activity-Count].

may be leading to the delay, as running this join for a large number of customers is going to take some time - Is this count necessary before customer selection? - and in any event make sure you have correct indexes by customer on the case file, to make sure the query is processed effectively

 

[mattkorguk]

The counts are for the various product groups, so I guess they're only required after a selection has been made.
But if someone wanted to view all clients, I can't seem to find a solution for these counts. I'd like the results to return all clients and their product counts, the using the "*" in access.
I might be missing something here, could I get away with removing the WHERE clause for the counts altogether?! I'm guessing that's right.
And then only add the WHERE clause if a selection is made.

I think the coding behind this page is going to be rather long. Is this again only going to slow things down?

Could there be another way around this? I'm sure others would have come across this when converting Dbs??!

 

[mattkorguk]

All resolved now, I'll post the code on Monday.