Results 1 to 2 of 2
  1. #1
    pdanes is offline Advanced Beginner
    Windows Vista Access 2007
    Join Date
    Sep 2019

    Match set of multiple Access records to SQL Server

    I had a SQL Server stored procedure doing a lookup using an XML-format view. It worked, but it was kind of slow and unnecessarily resource-intensive. I reformulated it for better performance, but the cost was making it dynamic SQL. A sample run is here:

    Select SC1.SkupinaID	From SouhrnyCloveks SC1 
    		inner join TableOfCloveks TC1 
    			on SC1.ClovekAutoID = TC1.ClovekAutoID 
    	inner join SouhrnyCloveks SC2
    		on SC1.SkupinaID = SC2.SkupinaID
    		inner join TableOfCloveks TC2
    			on SC2.ClovekAutoID = TC2.ClovekAutoID
    	inner join SouhrnyCloveks SC3
    		on SC2.SkupinaID = SC3.SkupinaID
    		inner join TableOfCloveks TC3
    			on SC3.ClovekAutoID = TC3.ClovekAutoID
    	inner join SouhrnyCloveks SC4
    		on SC3.SkupinaID = SC4.SkupinaID
    		inner join TableOfCloveks TC4
    			on SC4.ClovekAutoID = TC4.ClovekAutoID
    Where TC1.BPAuthorAbbreviation = '(' and SC1.Poradi = 1
    AND   TC2.BPAuthorAbbreviation = 'Crantz' and SC2.Poradi = 2
    AND   TC3.BPAuthorAbbreviation = ')' and SC3.Poradi = 3
    AND   TC4.BPAuthorAbbreviation = 'Vill.' and SC4.Poradi = 4
    And (Select MAX(Poradi) from SouhrnyCloveks Where SkupinaID = SC1.SkupinaID) = 4
    The number of joins (4 here) is not fixed. In theory, it can be any number. In practice, the max in this database is currently 12, and the overwhelming majority of cases are 4 or less. What I'm looking for is the group ID for the SET of records that meet the listed conditions. That ID will be unique - there is code to check that the identical set of records will not be saved twice, so these queries return either the one unique ID of the matching set of records, or nothing, if such a set does not exist.

    I have read that passing a table as a parameter is not possible in Access, the way it is in Is there a way to make a local Access table with these values and match the ENTIRE SET as a whole to a SQL Server table? I can build a normal join, but that will give me all records that match any of these conditions, not just the ones where all conditions match. For instance, there are 1338 records that match the first criterion, of TC1.BPAuthorAbbreviation = '(' and SC1.Poradi = 1.

    i would have to pull all these superfluous records back across my ODBC link and assemble the sets, then check to see if any of the sets meets all of my conditions, just to get that one or none ID number. That's horribly complicated, inefficient, and quite likely very slow. (I'm not going to even bother trying to code it all to test the speed - that's just not how I want this to work.)

    Is there a way to make the Access - SQL Server join match up only the entire set of records, or am I going to be stuck with building dynamic T-SQL to do this?

  2. #2
    Ajax is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    I have read that passing a table as a parameter is not possible in Access, the way it is in
    it's not possible in access nor - it is a limitation of sql server SP's.

    you can pass parameters to an SP which could be used to dictate how the sql is constructed. However from what your sql looks like, you have a recursive requirement - sql server has functionality for handling recursion.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 7
    Last Post: 07-04-2017, 09:26 AM
  2. Replies: 3
    Last Post: 07-16-2012, 08:32 PM
  3. Replies: 5
    Last Post: 01-24-2012, 06:19 PM
  4. Replies: 12
    Last Post: 10-15-2010, 10:03 AM
  5. How does access match records?
    By johnmerlino in forum Access
    Replies: 4
    Last Post: 10-05-2010, 08:51 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
Other Forums: Microsoft Office Forums - Senior Forums