Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2010
    Posts
    15

    The specified field could refer to more than one table in the from clause of your sql

    I can not figure out how to fix the problem I have with the access database. If anyone could help it would be greatly appreciated. Here is my macro I use for the parameters and I am also going to include the 2 queries I use to produce the form. I will also give the SQL to produce the form. Thanks

    Macro
    Case 2
    stLinkCriteria = "([CalExpires]>Now()) And ([Tester_Info.TNRCC_Reg_Date]>Now())"
    stLabel = "Current Backflow Tester Information"

    Queries
    SUB1
    SELECT Tester_Info.TesterID, ([LastName] & ", " & [FirstName]) AS Tester, Tester_Info.TNRCC_Reg_Date AS State, Calibration_Dates.Calibration_Date, ([Calibration_Dates]![Calibration_Date]+365) AS [Good Until]
    FROM Tester_Info INNER JOIN Calibration_Dates ON Tester_Info.TesterID = Calibration_Dates.TesterID
    WHERE (((Tester_Info.TNRCC_Reg_Date)>=Now()) AND ((Calibration_Dates.Calibration_Date)<=Now()) AND ((([Calibration_Dates]![Calibration_Date]+365))>=Now()))
    ORDER BY ([LastName] & ", " & [FirstName]);

    SUB2

    SELECT Todays_Qualified_Testers_Sub1_qry.TesterID, Max(Todays_Qualified_Testers_Sub1_qry.Calibration_ Date) AS [Date]
    FROM Todays_Qualified_Testers_Sub1_qry
    GROUP BY Todays_Qualified_Testers_Sub1_qry.TesterID;

    Form SQL

    SELECT Tester_Info.*, Todays_Qualified_Testers_Sub2_qry.TesterID AS T FROM Tester_Info LEFT JOIN Todays_Qualified_Testers_Sub2_qry ON Tester_Info.TesterID = Todays_Qualified_Testers_Sub2_qry.TesterID;


    PLEASE HELP

  2. #2
    Join Date
    Oct 2009
    Posts
    340
    well I didn't look at your code (there's only so much work I myself will lend for free !!) but in general based on your title: If there is uncertainty on a field name then you add the table name into the query to be clear i.e. tablename.fieldname

    one can include the table name in anywhere in an sql statement...

    to trouble shoot you have to fire each of those sql statement separately...

  3. #3
    Join Date
    Jan 2010
    Posts
    15
    just fixed it thanks for the advice

Posting Permissions

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