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

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
 
Joined
Jun 11, 2005
Messages
28
Reaction score
1
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
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.
 
Joined
Jul 11, 2005
Messages
1
Reaction score
0
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?????????
 
Joined
Jun 17, 2005
Messages
9
Reaction score
0
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]
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top