Error 3079

[johnbu]

I keep getting a 3079 error message "The specified field 'ta_People.DOB' could refer to more than one table listed in the FROM clause of your SQL statement". My SQL statement is



SELECT ta_People.Subject, ta_People.Generation, ta_People.DFA_Link, ta_People.DFA_Page, ta_People.DOB, ta_People.BirthLocation, ta_People.DOD, ta_People.DeathLocation, ta_People.Father, ta_People.Mother, ta_Spouse.SpouseName, ta_Spouse.[Spouse _qv], ta_Spouse.MarriageDate, ta_Spouse.MarriageLocation, ta_Spouse.MarriageStatus, ta_People.SubjectNotes, qu_IssueInfo.IssueName, qu_IssueInfo.DOB, qu_IssueInfo.DOD
FROM (ta_People LEFT JOIN ta_Spouse ON ta_People.Subject=ta_Spouse.Subject) LEFT JOIN qu_IssueInfo ON ta_Spouse.SpouseName=qu_IssueInfo.SpouseName
ORDER BY ta_People.Subject, ta_Spouse.MarriageDate;

If it helps, this query is based on the table ta_people and the query qu_IssueInfo. The query qu_IssueInfo is based on tables ta_people and ta_Issue.

The query itself runs fine but I get the error when I try to open the report based on that query.

Thanks

[orange]

Can you post the sql for the query?

[johnbu]

Here is the SQL for the query the report is based on...

SELECT ta_People.Subject, ta_People.Generation, ta_People.DFA_Link, ta_People.DFA_Page, ta_People.DOB, ta_People.BirthLocation, ta_People.DOD, ta_People.DeathLocation, ta_People.Father, ta_People.Mother, ta_Spouse.SpouseName, ta_Spouse.[Spouse _qv], ta_Spouse.MarriageDate, ta_Spouse.MarriageLocation, ta_Spouse.MarriageStatus, ta_People.SubjectNotes, qu_IssueInfo.IssueName, qu_IssueInfo.DOB, qu_IssueInfo.DOD
FROM (ta_People LEFT JOIN ta_Spouse ON ta_People.Subject=ta_Spouse.Subject) LEFT JOIN qu_IssueInfo ON ta_Spouse.SpouseName=qu_IssueInfo.SpouseName
ORDER BY ta_People.Subject, ta_Spouse.MarriageDate; (same as above)

This is the query ,qu_IssueInfo, that is referenced in that query...

SELECT ta_Issue.IssueName, ta_People.DOB, ta_People.DOD, ta_Issue.SpouseName
FROM ta_People RIGHT JOIN ta_Issue ON ta_People.Subject=ta_Issue.IssueName;

I don't have much experience with SQL. I generally work in the design view of Access.

[orange]

Nothing is jumping out to me, maybe someone else can see the issue.

I don't have acc2007. I have acc2003. If you want to make a copy of your database in mdb format, and post it, I'll take a look further.

[johnbu]

How do I post the database? I don't see anything obvious.

[orange]

You have to hit Reply, then you'll see a GO Advanced button; then manage attachments

[johnbu]

Thanks, here it is. If I did it right. It is pretty basic right now.
Genealogy.mdb

[orange]

I'm looking at your database. You have no relationships defined.
What exactly is the purpose of qu_IssueInfo? Does it do what you intended?
I asked because I see 3 Greene, Shirles Hamilton under SpouseName.

Do you have a description for each table?

[orange]

You should not be using lookups at the Table level. SpouseName on table ta_issue and there may be others.

see http://access.mvps.org/access/lookupfields.htm

[johnbu]

I always set the relationships in the in design view of the query. Is it better to do it otherwise?

qu_IssueInfo is to join the IssueName with their DOB from ta_People. I originally had a DOB in ta_Issue but did not want to have to enter that data in two different places. I would rather put it in ta_people which is the main table for names and info. I combine ta_Issue.IssueName with the corresponding ta_People.DOB with the query qu_IssueInfo. Then combine that query with the table ta_people and ta_Spouse to get the query qu_Families, which the report is based on. The queries themselves do what I wanted, but the report based on qu_Families gives me the error. Hope all that makes sense.

As for descriptions for tables, I have never written it out, but ta_People is the main table for names and their direct information. ta_Spouse lists the spouse(s) for each Subject in ta_people. And ta_Issue lists all children for each spouse. They are all combined to get the query qu_Families.

(I have always been accused of going the long way around.)

[johnbu]

Thanks, they are gone now.

[johnbu]

Don't know if you have found any way to correct the problem in the existing queries, but I found a way around the problem. Instead of building the report's query on ta_Subject, ta_Spouse, and qu_IssueInfo, I first built another query combining ta_Subject and ta_Spouse and then combined that query with qu_IssueInfo in a final query that the report is based on. So now the cursed ta_people.DOB appears nowhere in the report's query sql and the report works fine. Thanks for all your help! And thanks for the link to Access Web!

Any other suggestions on what you have seen?

[orange]

You are welcome. Good luck with your project.

[John_G]

Hi -

For what it's worth, you original problem was that the query qu_IssueInfo contained the table ta_people, which also appeared explicitly in the FROM clause. So, MS Access really was finding ta_people twice in the FROM - once explicitly, and again when it parsed qu_IssueInfo - so ta_people.DOB occurred twice, hence the error.

John

[johnbu]

Thanks. I thought it probably had something to do with that but was having trouble finding a way around it. You can see in post #12 what I ending up doing that finally worked. Thanks for the input. Any idea why the original query would work by itself but not in the report?