Windows XP The specified table could refer to more than one table in the FROM clause of your SQL

A

aaldridge

Joined
Jun 17, 2005
Messages
9
Reaction score
0
Okay, so I have 2 tables, one named ClientInformation, and one named ClientFamilyMembers. ClientInformation includes the following fields:
ClientSSN
LastName
FirstName
MI
DOB
Gender
Address
City
State
Zipcode
PhoneNumber
Notes

ClientFamilyMembers includes the following fields:
ClientSSN
FMSSN
LastName
FirstName
MI
DOB
Gender
Relationship

The two tables are joined with a 1-many join through ClientSSN. Basically, what I'm trying to do is create a query that takes all the fields from ClientFamilyMembers, add a field to calculate the person's age, and also include the fields LastName, FirstName, and MI from ClientInformation. When I try to run the query, I get the error message

"The specified field '[DOB]' could refer to more than one table listed in the FROM clause of your SQL statement."

This is what the query looks like in SQL view:

SELECT ClientFamilyMembers.ClientSSN, ClientFamilyMembers.FMSSN, ClientFamilyMembers.LastName, ClientFamilyMembers.FirstName, ClientFamilyMembers.MI, ClientFamilyMembers.DOB, DateDiff("yyyy",[DOB],Now())+Int(Format(Now(),"mmdd")<Format([DOB],"mmdd")) AS Age, ClientFamilyMembers.Gender, ClientFamilyMembers.Relationship, ClientInformation.LastName, ClientInformation.FirstName, ClientInformation.MI

FROM ClientInformation LEFT JOIN ClientFamilyMembers

ON ClientInformation.ClientSSN = ClientFamilyMembers.ClientSSN;

Please Help!

Amanda
 
aaldridge said:
Okay, so I have 2 tables, one named ClientInformation, and one named ClientFamilyMembers. ClientInformation includes the following fields:
ClientSSN
LastName
FirstName
MI
DOB
Gender
Address
City
State
Zipcode
PhoneNumber
Notes

ClientFamilyMembers includes the following fields:
ClientSSN
FMSSN
LastName
FirstName
MI
DOB
Gender
Relationship

The two tables are joined with a 1-many join through ClientSSN. Basically, what I'm trying to do is create a query that takes all the fields from ClientFamilyMembers, add a field to calculate the person's age, and also include the fields LastName, FirstName, and MI from ClientInformation. When I try to run the query, I get the error message

"The specified field '[DOB]' could refer to more than one table listed in the FROM clause of your SQL statement."

This is what the query looks like in SQL view:

SELECT ClientFamilyMembers.ClientSSN, ClientFamilyMembers.FMSSN, ClientFamilyMembers.LastName, ClientFamilyMembers.FirstName, ClientFamilyMembers.MI, ClientFamilyMembers.DOB, DateDiff("yyyy",[DOB],Now())+Int(Format(Now(),"mmdd")<Format([DOB],"mmdd")) AS Age, ClientFamilyMembers.Gender, ClientFamilyMembers.Relationship, ClientInformation.LastName, ClientInformation.FirstName, ClientInformation.MI

FROM ClientInformation LEFT JOIN ClientFamilyMembers

ON ClientInformation.ClientSSN = ClientFamilyMembers.ClientSSN;

Please Help!

Amanda
Click to expand...
Amanda,

This is the solution, you must simply place the tablename before the field

DateDiff("yyyy",ClientFamilyMembers.DOB,Now())+Int(Format(Now(),"mmdd")<Format(ClientFamilyMembers.DOB,"mmdd"))

Because the field DOB is present in both tables, this is confusing for Access. For this reason you must provide the table name for avoiding problems.
 
Sql Question

I am receiving this error message

"The Specified field [PIPELINE] could refer to more than one table listed in the FROM clause of you SQL statement


Here is my statement


SELECT Format([CALMONTH],"yyyymm") & [PROD] & [PIPELINE] AS ID,
DateAdd("m",-tbl_WADF_MNEMONIC!LAG,qry_WADF_OMS!CALMONTH) AS CALMONTH,
tbl_WADF_MNEMONIC.PROD, tbl_WADF_MNEMONIC.PIPELINE, qry_WADF_OMS.VALUE
FROM tbl_WADF_MNEMONIC INNER JOIN qry_WADF_OMS ON
tbl_WADF_MNEMONIC.MNEMONIC = qry_WADF_OMS.MNEMONIC;

Can anyone tell me why I get the error?????????
 
Read cvo-aalst's message to me. Try inserting the table/query name in the line SELECT Format([CALMONTH],"yyyymm") & [PROD] & [PIPELINE] AS ID,
i.e., try [tblTableName.PIPELINE] instead of just [PIPELINE]
 
Back
Top