Results 1 to 10 of 10
  1. #1
    Join Date
    Jul 2011
    Posts
    47

    Unanswered: "Join expression not supported" SQL

    Hi,

    I've got help from someone to come up with this SQL code for MS Access,
    when i try to run it i get, "Join expression not supported"

    The code is:
    SELECT 1 as SEQ, C.FamilyID, C.FileNumber, C.FirstName, C.Surname,
    LAL.Code, LAL.Cost, LAL.Minutes_, LAL.Items, LAL.BilledTotal, LAL.All_
    , NULL as Code, NULL as Credit
    FROM tblClientDetails as C
    INNER JOIN tblLegalAidLedger as LAL ON
    C.FamilyID = LAL.FamilyID
    UNION ALL
    SELECT 2 as SEQ, C.FamilyID, C.FileNumber, C.FirstName, C.Surname,
    NULL, NULL, NULL, NULL, NULL, NULL, LAFL.Code, LAFL.Credit
    FROM ClientDetails as C
    INNER JOIN tblLegalAidFiancialLedger AS LAFL ON
    C.FamilyID = LAFL.FamilyID AND
    LAFL.Code = 'PA'
    ORDER BY FamilyID, SEQ;
    Any help will be greatly appreciated.

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Unfortunately the Jet Engine that comprises the SQL interpretor used by Access can only handle a (rather large) subset of the SQL language, and some complex expressions or joins cannot be processed.

    There is no magic recipe, but usually the solution consists in:

    a) Dividing the complex expression in several simpler queries and use a "parent" query to assemble the results yielded by these simpler queries.
    Code:
    SELECT... FROM QueryA
    INNER JOIN 
    SELECT... FROM QueryB
    ON QueryA.xxx = QueryB.yyy
    ...
    AND/OR

    b) Using subqueries inside a single query:
    Code:
    SELECT ... FROM (
        SELECT... FROM...
    ) AS a INNER JOIN (
        SELECT ... FROM...) AS b
      ON a.xxx = b.yyy
    ...
    AND/OR

    c) Finding an alternative way to get the same result set with an expression that the Jet Engine can handle. You can, for instance, replace a JOIN operation by one or several WHERE conditions.
    Have a nice day!

  3. #3
    Join Date
    Jul 2011
    Posts
    47
    Quote Originally Posted by Sinndho View Post
    Unfortunately the Jet Engine that comprises the SQL interpretor used by Access can only handle a (rather large) subset of the SQL language, and some complex expressions or joins cannot be processed.

    There is no magic recipe, but usually the solution consists in:

    a) Dividing the complex expression in several simpler queries and use a "parent" query to assemble the results yielded by these simpler queries.
    Code:
    SELECT... FROM QueryA
    INNER JOIN 
    SELECT... FROM QueryB
    ON QueryA.xxx = QueryB.yyy
    ...
    AND/OR

    b) Using subqueries inside a single query:
    Code:
    SELECT ... FROM (
        SELECT... FROM...
    ) AS a INNER JOIN (
        SELECT ... FROM...) AS b
      ON a.xxx = b.yyy
    ...
    AND/OR

    c) Finding an alternative way to get the same result set with an expression that the Jet Engine can handle. You can, for instance, replace a JOIN operation by one or several WHERE conditions.
    Hi Sinndho,

    Thanks for your reply, unfortunately my SQL ability is next to nil. Could you please help me assemble this into MS Access format or can you please direct me to website that can assist me?

    Also, on a separate note, can you direct me to a book/website that can teach me MS Access SQL?

    Thanks

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Please post the full definitions for all the tables involved in the queries, as well as some sample data they contain. I can see some possible solutions but I cannot be sure without knowing precisely what we're dealing with, and they will need some testing anyway.
    Have a nice day!

  5. #5
    Join Date
    Jul 2011
    Posts
    47
    The problem:
    I have 3 tables:

    1) tblclientdetails(FamilyID, First Name, Surname,Status, Typeofwork)
    2) tbllegalaidledger(Code, Minutes, Items, Billed Total,All, FamilyID)
    3) tbllegalaidfiancialledger (Code, Credit, FamilyID)
    FamilyID is a foregin key in the last two tables.

    I need the query to search the tbllegalaidfinancialledger in the CODE for "PA" and display the credit for that, if a particular person does not have a "PA" in tbllegalaidfinacialledger i need to query just to show nothing but still show that person. (i.e. IIf([tbllegalaidfiancialledger.Code]="PA",[Credit],"0"))


    A little bit more info(including the upload):

    tblclientdetails has a lot more fields, I need all of them. I didn't put them all in.
    The records need to be searched using a criteria. i.e. [Status]="active" and the [Typeofwork]="legalaid"

    there maybe 1 or a a couple of thousands of records related to one record(from tblclientdetails) in tbllegalaidledger.

    in the third table, this is where I have an issue, there may or may not be a related recorded. If there is a related record, I need the actual value "Credit" to be shown(there may be a few). If there is none, I need a "0". Also, the "Code" field has a criteria "PA", i.e. i only need the fields where [Code] = "PA"

    Also, the other problem I have had if i managed to get the some part of it working. I kept getting each "Credit" field which was related repeating more one 10 times.

    There is sample data about the three tables, and the result that i require. I have attached a compressed spreadsheet.

    Please feel free to ask for any more information.

    Regards
    Attached Files Attached Files

  6. #6
    Join Date
    Jul 2011
    Posts
    47
    I have managed to come up with the following:
    But i have a strange problem: when i run the query it is fine, when I filter out certain value for [Credit] I get a random symbol instead of a number "〠?" and when I try to open the report or sometimes run the query I get "Data mis match in criteria". Can you help please?

    SELECT tblClientDetails.FamilyID, tblClientDetails.FileNumber, tblClientDetails.FirstName, tblClientDetails.Surname, tblClientDetails.Status,tblClientDetails.Typeofwor k,
    tblLegalAidLedger.Code, tblLegalAidLedger.Cost, tblLegalAidLedger.Minutes, tblLegalAidLedger.Items, IIf([tblLegalAidLedger.Code] In ("AT","PR","AD","ATC","WA","TR"),([cost]/60)*[Minutes],IIf([tblLegalAidLedger.Code] In ("LO","TE","LI"),[Items]*[cost])) AS [All], tblLegalAidLedger.BilledTotal, NULL as [1Code] , NULL as [Credit]
    FROM tblClientDetails INNER JOIN tblLegalAidLedger
    ON tblClientDetails.FamilyID = tblLegalAidLedger.FamilyID
    WHERE (((tblClientDetails.Status)="Active")
    AND ((tblClientDetails.Typeofwork)="Legal Aid"))
    UNION ALL SELECT tblClientDetails.FamilyID, tblClientDetails.FileNumber, NULL, NULL,
    NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, tbllegalaidfiancialledger.Credit
    FROM tblClientDetails LEFT JOIN tbllegalaidfiancialledger
    ON tblClientDetails.FamilyID = tbllegalaidfiancialledger.FamilyID
    ORDER BY tblClientDetails.FileNumber;

  7. #7
    Join Date
    Jul 2011
    Posts
    47
    Further to my post before, I think I have narrowed it down further.

    There seems to be a problem here:
    Code:
    NULL as [Credit]
    Credit is a Currency field, does it have any bearing on it?

  8. #8
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Sorry but I cannot find any "tblLegalAidFiancialLedger" table in your example. There is one set of data for a "tblclientdetails" table (which is simply named "ClientDetails" in the query you submitted, at least I suppose so), and two sets of data that are both named "tbllegalaidledger".

    1. Why do you use aliases when assembling the first data set ("FROM tblClientDetails as C" and "INNER JOIN tblLegalAidLedger as LAL")? This is useless and you could simplify and use:
    Code:
    SELECT tblclientdetails.FamilyID, 
           tblclientdetails.FileNumber, 
           tblclientdetails.FirstName, 
           tblclientdetails.Surname, 
           tbllegalaidledger.Code, 
           tbllegalaidledger.Cost, 
           tbllegalaidledger.Minutes, 
           tbllegalaidledger.Items, 
           tbllegalaidledger.[Billed Total], 
           tbllegalaidledger.[All]
    FROM   tblclientdetails INNER JOIN 
           tbllegalaidledger ON tblclientdetails.FamilyID = tbllegalaidledger.FamilyID;
    Also notice that "All" is a reserved word that should not be used to name a column. Following the same path of thoughts, you should refrain from using spaces (e.g. "Billed Total") or other non alphanumeric characters in the names (tables, columns, controls, variables, etc.) in your database.


    2. Always in the first data set:

    a) What's the meaning of "SELECT 1 as SEQ"? This will simply create a column named "SEQ" with all rows containing the value '1'. Is it for being able to distinguish what comes from the first data set from what comes from the second? If it's for sorting, I'm not sure it will always work with a UNION query.

    b) You cannot use "NULL as Code" because there is already a column named "Code" in the selected columns: "LAL.Code".

    c) About the same "NULL as Code" as well as about "NULL as Credit", what the purpose of creating two empty columns (i.e. columns containing a Null value) in the data set? Is it to be able to perform the UNION ALL operation, that requires the same amount of columns?

    d) You wrote: "LAL.BilledTotal" while it should be: "LAL.[Billed Total]"


    3. The same issues are true for the second data set. Moreover I guess that one of the problem Access encounters when trying to execute the query is that you try to mix an INNER JOIN operation with a WHERE clause: "INNER JOIN tblLegalAidFiancialLedger AS LAFL ON C.FamilyID = LAFL.FamilyID AND LAFL.Code = 'PA'". This is syntactically incorrect in SQL and should be:
    Code:
    SELECT tblclientdetails.FamilyID, 
           tblclientdetails.FileNumber, 
           tblclientdetails.FirstName, 
           tblclientdetails.Surname, 
           tbllegalaidFinancialledger.Code, 
           tbllegalaidFinancialledger.Credit
    FROM   tblclientdetails INNER JOIN 
           tbllegalaidFinancialledger ON tblclientdetails.FamilyID = tbllegalaidFinancialledger.[Family ID]
    WHERE  tbllegalaidFinancialledger.Code='PA'
    ORDER BY tblclientdetails.FamilyID;
    In this second selection set, you wrote "ON C.FamilyID = LAFL.FamilyID" while the column in what I suppose to be the table "tblLegalAidFinancialLedger", the column name is: "Family ID". Also notice that the name of a table is misspelled: "tblLegalAidFiancialLedger"

    4. Finally and though awkward from my view point, this corrected query works:
    Code:
    SELECT 1 as SEQ, 
           C.FamilyID, 
           C.FileNumber, 
           C.FirstName, 
           C.Surname, 
           LAL.Code, 
           LAL.Cost, 
           LAL.Minutes, 
           LAL.Items, 
           LAL.[Billed Total], 
           LAL.All, 
           NULL as Code1, 
           NULL as Credit
    FROM   tblClientDetails as C INNER JOIN 
           tblLegalAidLedger as LAL ON C.FamilyID = LAL.FamilyID
    UNION ALL
    SELECT 2 as SEQ, 
           C.FamilyID, 
           C.FileNumber, 
           C.FirstName, 
           C.Surname, 
           NULL, 
           NULL, 
           NULL, 
           NULL, 
           NULL, 
           NULL, 
           LAFL.Code, 
           LAFL.Credit
    FROM   TBLClientDetails as C INNER JOIN 
           tblLegalAidFinancialLedger AS LAFL ON C.FamilyID = LAFL.[Family ID]
    WHERE  LAFL.Code = 'PA'
    ORDER BY FamilyID, SEQ;
    Have a nice day!

  9. #9
    Join Date
    Jul 2011
    Posts
    47
    Hi Sinndho,

    Firstly I would like to thank you for the help you have given and all the hard-work you have put in.
    Secondly, I would like to apologise for the delayed response. I am an A-level student who is helping help his fathers firm with this, I have just been busy with tests and assignments.

    Sorry but I cannot find any "tblLegalAidFiancialLedger" table in your example.
    Sorry, that is my bad. The last table should have been named tbllegalaidfiancialledger.

    Why do you use aliases when assembling the first data set
    As I explained before, I got someone to code this in SQL for me. I do not know why they used aliases.

    a) What's the meaning of "SELECT 1 as SEQ"? This will simply create a column named "SEQ" with all rows containing the value '1'. Is it for being able to distinguish what comes from the first data set from what comes from the second? If it's for sorting, I'm not sure it will always work with a UNION query.
    c) About the same "NULL as Code" as well as about "NULL as Credit", what the purpose of creating two empty columns (i.e. columns containing a Null value) in the data set? Is it to be able to perform the UNION ALL operation, that requires the same amount of columns?
    As I mentioned before, I did not create the code. However, I assume that your explanations are the actual reason behind it.

    I have gone through your post thoroughly, and I am pleased to say that I have learnt a lot from you. Thank you for that.

    The code you came up with worked GREAT! Thank-you for all your help.

  10. #10
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You more than welcome!
    Have a nice day!

Posting Permissions

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