If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > PC based Database Applications > Microsoft Access > The specified field could refer to more than one table in the from clause of your sql

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #2 (permalink)  
Old
Registered User
 
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...
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
Join Date: Jan 2010
Posts: 15
just fixed it thanks for the advice
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On