Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2006

    Unanswered: Bad Coding or Bad Access SQL Engine?

    I've been having some consistent problems with coding SQL queries in Access, and after consulting with a friend of mine who has a lot of SQL background and has had the same problems, we've concluded that there must be some significant problems with the Access SQL engine. It goes beyond the differences between Access SQL and other forms of SQL. My friend has years of training and experience in SQL coding for Access, and he's running into the same issues that make no sense.

    For example, if you have a perfectly working query and simply delete one character in SQL view and then add the character again (so the query is exactly like it was before), Access often generates errors when trying to execute the query.

    I'm also finding that it's nearly impossible to code queries of any complexity without Access generating nonsensical errors. I know that there are differences between the Access SQL and SQL coding in other environments, but I'm following Microsoft's own documentation for Access!

    All I need to do is the following:

    1. Query one table and find duplicates based on the first x number of characters in a column.

    2. Group by these duplicates and then look for all records where the duplicates have a full field match in one or more other fields. e.g. All cases where the first 5 characters of a company name are the same and where the city and/or state is the same.

    3. Show me the results in a data table, so I can delete specific records.

    4. Better yet, allow me to remove certain duplicates automatically.

    5. Have the freedom to copy queries over to other databases and then edit them to pull from different tables and fields.

    Can anyone offer any insight as to what kind of SQL scripting to use without causing errors? I've been trying to use the left(string,value) method, but Access seems driven to give me errors about aggregate expressions or syntax errors, even when I take Microsoft's own example and simply edit it for use with my table and field names.

    I've concluded that the Access SQL engine is using coding that is invisible to the user, and it goes haywire when you try to edit anything that is not a very basic query code. Even if you try to copy an existing query and then edit the underlying SQL to simply change points of reference, you're almost guaranteed to get errors. I really hope someone can help.

  2. #2
    Join Date
    Sep 2003
    Show us a/the query that's giving you the errors ... And if possible, tell what error it's giving ...

    I also have TONS of SQL querying experience in Access as many here have (Teddy, pbaldy, r937 - rudy, pootle flump, izyrider, to name a few) and we know that Access does have limitations on its SQL generation ... BUT, it can do very complex SQL operations. So let's see what you've got and maybe we can figure out where things are going wrong ...
    Back to Access ... ADO is not the way to go for speed ...

  3. #3
    Join Date
    Sep 2006
    Okay, let me show you a script that was given to me by an SQL programmer. This is what finally generated the result that I was looking for -- at least for my first query. The problem is that this seems like a very roundabout way of doing things, and Access will not let me edit this query or configure it for any other purpose without giving me an error. If I literally delete just one character from this query and then replace it, so the query is verbatim identical to its original form, Access will not run the query, even though I just ran it with the same coding before I loaded it in SQL view! It generates an error claiming that there is illegal bracketing in the first SELECT sections. It does this even though the coding is identical to what it was when I launched the query!

    SELECT Combined.NursingHomeName, Combined.City, Combined.Street, Combined.State, Combined.ZipCode, Combined.PhoneNumber
    FROM Combined, [Select Left([NursingHomeName],5) as left5NursingHomeName,[City],[State],Count(*) as Counts
    from Combined
    group by Left([NursingHomeName],5),[City],[State]
    having count(*) > 1 ]. AS Dupe
    WHERE Left([Combined.NursingHomeName],5) = left5NursingHomeName and ltrim(rtrim(Combined.City)) = ltrim(rtrim(Dupe.City))
    ORDER BY Combined.NursingHomeName;

    My other attempt was based on this example from Microsoft:

    SELECT Customers.CompanyName, Customers.Address, Customers.City, Customers.ContactName, Customers.Phone, Customers.Fax
    FROM Customers
    WHERE (((Customers.CompanyName) In (SELECT [CompanyName] FROM [Customers] As Tmp GROUP BY [CompanyName],Left([Address],7) ,[City] HAVING Count(*)>1 And Left([Address],7) =Left([Customers].[Address],7) And [City] = [Customers].[City])))
    ORDER BY Customers.CompanyName, Customers.Address, Customers.City;

    I simply changed the table name and field names to work with my particular database, but Access gave me an error claiming that I tried to execute a query without one of my field names being part of an aggregate expression. I want to emphasize that I used this identical format and changed only the table name and the relevant field names. The logic and functions were identical, but I got the error.

    In case this is confusing, please reference my first posting to keep in mind what I'm trying to do here.

  4. #4
    Join Date
    Dec 2002
    Préverenges, Switzerland
    wild guess at the first one...
    have a go inserting a space after the first [
    ...if nothing else, it might change the error msg.

    looking at the second one now.


    looks like one too many closing parenthesis just before the ORDER BY
    LATER STILL remove the extra ) and it wont save!
    i messed around and generated a dozen different errors until i got to:
    SELECT Customers.CompanyName, Customers.Address, Customers.City, Customers.ContactName, Customers.Phone, Customers.Fax
    FROM Customers
    WHERE Customers.CompanyName IN ( SELECT [CompanyName] FROM [Customers] As Tmp GROUP BY [CompanyName],Left([Address],7) ,[City] HAVING Count(*)>1 And Left([Address],7) =Left([Customers].[Address],7) And [City] = [Customers].[City] )
    ORDER BY Customers.CompanyName, Customers.Address, Customers.City;
    the parentheses balance, it saves, but i don't have your tables to test it.
    Last edited by izyrider; 09-15-06 at 13:38. Reason: idea for the second one
    currently using SS 2008R2

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts