How to convert SQL Server Query into Access

Question

I am working in Access 2010 and am stuck with a poor table design.

The table has multiple columns that may contain a name I need to find.

ie Company, Company DBA1,Company DBA2,DBA3,DBA4,DBA5 etc... Company DBA12

The best that I have come up with is: (assuming I am looking for jbl)

      Select Company ,ClientNumber  
      From tblClient 
      Where Company = 'jbl' or DBA1='jbl' or DBA2='jbl'... DBA12='jbl'

While it works it is horribly slow!!!

I have found that if I were able to use SQLServer (which I cannot) I could use a Stored Procedure like this:

BEGIN
    SET NOCOUNT ON;

    declare @Sql    varchar(max)

    set @sql = 

                'select  Company ,ClientNumber 
                 from dbo.tblClient
                 where isnull([Company],'''') + isnull( [Company DBA],'''') + isnull( [Company DBA2],'''') + isnull( [Company DBA3],'''') + isnull( [Company DBA4],'''') + isnull( [Company DBA5],'''') + isnull( [Company DBA6],'''') + isnull( [Company DBA7],'''') + isnull( [Company DBA8],'''') + isnull( [Company DBA9],'''') + isnull( [Company DBA10],'''') + isnull( [Company DBA11],'''') + isnull( [Company DBA12],'''') like ''%'+@Look4+'%'' 
     order by company'

     print @sql
     exec (@sql)
END

This is quite fast.

So I am wondering if there is away to create a similar query or Stored procedure in MS Access 2010?

I personally love the flexibilty of Dynamic SQL like you showed in your example.... is doing a VBA proc in Access an option. I have never actually written on but I understand it is the alternative. W — logixologist
– logixologist, Commented Aug 13, 2014 at 17:27

ludwigmace · Accepted Answer · 2014-08-13 17:44:07Z

1

A direct translation into Access would be:

select * from tblClient
where company & dba1 & dba2 & dba3 like '*jbl*'

EDIT: To make an exact match, you could do:

select * from tblClient
where '|' & company & '|' & dba1 & '|' & dba2 & '|' & dba3 & '|' like '*|' & 'jbl' & '|*'

edited Aug 13, 2014 at 17:44

answered Aug 13, 2014 at 17:27

ludwigmace

7124 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

logixologist Over a year ago

if the & operator is really saying AND than that would be incorrect. And technically the OP said Company = 'jbl'... why force him to use a LIKE

ludwigmace Over a year ago

In Access SQL, "&" is the concatenation operator. So this is just building a string. And I don't think Text columns can be NULL, so if you're looking for "jbl" then 'TimeWarnerCablejblTWC' like '*jbl*' will work.

Fionnuala Over a year ago

What if the company is called, say, 'OR', you will have multiple false matches.

ludwigmace Over a year ago

Indeed if you're searching for something generic you'll have problems, but that's a risk the OP took with his SQL Server code so I figured OP would be fine with that in the Access version.

Mcp Over a year ago

It appears that this just might be the best solution! It is extremely quick compared to my previous query.

|

Fionnuala · Accepted Answer · 2014-08-13 17:58:11Z

1

You would be better off using a union query to at least make the table look normal:

  SELECT q.Company, q.ClientNumber, q.DBA 
  FROM 
  (SELECT Company, ClientNumber, DBA1 As DBA FROM tblClient 
  UNION All
   SELECT Company, ClientNumber, DBA2 As DBA FROM tblClient 
  ....) As q
  Where q.Company = "jbl" or q.DBA="jbl"

You can, of course, save the normalized version as a query and then use that 'view' in other queries, which would give you:

  PARAMETERS SearchString Text;
  SELECT Company, ClientNumber, DBA 
  FROM ANormalTable
  WHERE Company = SearchString Or DBA = SearchString

Note that once you add WHERE that does not specifically include null, all nulls are ignored.

Reference: http://office.microsoft.com/en-ie/access-help/combine-the-results-of-several-select-queries-by-using-a-union-query-HA010206109.aspx

edited Aug 13, 2014 at 17:58

answered Aug 13, 2014 at 17:21

Fionnuala

91.4k7 gold badges121 silver badges155 bronze badges

3 Comments

Mcp Over a year ago

Ill see if I can give that a try, do you think it will run faster?

Mcp Over a year ago

Can you elaborate on the last part?? I'm not that Access Savvy! How to make it a view

Fionnuala Over a year ago

No, I do not think it will run faster. To make a 'view' just save a union query and then use it it future queries.

Collectives™ on Stack Overflow

How to convert SQL Server Query into Access

2 Answers 2

6 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related