Spec. field ... could refer to more than one table ...SQL Statemen

H

Honey

I have to track 4 generations of pedigree for horses. I created a pedigree
query using self joins by copying the Horses table and joining the sire and
dam id fields to the Horse ID field in the copied tables. I have changed the
name of the copied tables and everything seems to work just fine. I created
another query to include all of the other horses's information (Micorchip # ,
date of birth, color and markings, etc.) and the owners information uning
the horses table, the owners table and the pedigree query. When I run this
query, it seems fine. In datasheet view, all the info is filled in. I would
like to create a report from this query that I can customize to create the
Registration papers. When I run this report, I get this message: The
specified field 'Horses.[Horse's Name]' could refer to more than one table
listed in the FROM clause of your SQL statement. Please help me get around
this problem. I am using Access 2000. Thank You, Honey.
 
K

KARL DEWEY

Post the FROM clause of your SQL statement. Better still post the complete
SQL.
 
H

Honey

The SQL from the Query? Or you will have to tell me where it is on the report.
Thanks, Honey

KARL DEWEY said:
Post the FROM clause of your SQL statement. Better still post the complete
SQL.
--
KARL DEWEY
Build a little - Test a little


Honey said:
I have to track 4 generations of pedigree for horses. I created a pedigree
query using self joins by copying the Horses table and joining the sire and
dam id fields to the Horse ID field in the copied tables. I have changed the
name of the copied tables and everything seems to work just fine. I created
another query to include all of the other horses's information (Micorchip # ,
date of birth, color and markings, etc.) and the owners information uning
the horses table, the owners table and the pedigree query. When I run this
query, it seems fine. In datasheet view, all the info is filled in. I would
like to create a report from this query that I can customize to create the
Registration papers. When I run this report, I get this message: The
specified field 'Horses.[Horse's Name]' could refer to more than one table
listed in the FROM clause of your SQL statement. Please help me get around
this problem. I am using Access 2000. Thank You, Honey.
Click to expand...
Click to expand...
 
K

KARL DEWEY

Open your report in design view, Click on menu VIEW - Properties. See what
the Record Source says. If it is a SELECT statement that is the SQL. Copy
and paste in a post.
If it has the name of a query then open that query in design view, click on
menu VIEW - SQL View, highlight all, copy, and paste in a post.
--
KARL DEWEY
Build a little - Test a little


Honey said:
The SQL from the Query? Or you will have to tell me where it is on the report.
Thanks, Honey

KARL DEWEY said:
Post the FROM clause of your SQL statement. Better still post the complete
SQL.
--
KARL DEWEY
Build a little - Test a little


Honey said:
I have to track 4 generations of pedigree for horses. I created a pedigree
query using self joins by copying the Horses table and joining the sire and
dam id fields to the Horse ID field in the copied tables. I have changed the
name of the copied tables and everything seems to work just fine. I created
another query to include all of the other horses's information (Micorchip # ,
date of birth, color and markings, etc.) and the owners information uning
the horses table, the owners table and the pedigree query. When I run this
query, it seems fine. In datasheet view, all the info is filled in. I would
like to create a report from this query that I can customize to create the
Registration papers. When I run this report, I get this message: The
specified field 'Horses.[Horse's Name]' could refer to more than one table
listed in the FROM clause of your SQL statement. Please help me get around
this problem. I am using Access 2000. Thank You, Honey.
Click to expand...
Click to expand...
Click to expand...
 
H

Honey

O.K. Karl,
Here it is. It is from the Query.

SELECT Horses.[Horse's Name], Horses.[Registration Number], Horses.[Date of
Birth], Horses.Sex, Horses.[Inbreeding Coefficient], Horses.Breeder,
Horses.[Achievement Level], [Pedigree Query].Sire.[Horse's Name], [Pedigree
Query].Sire.[Registration Number], [Pedigree Query].Sire.[Other Registration
#], [Pedigree Query].Sire.Title, [Pedigree Query].Dam.[Horse's Name],
[Pedigree Query].Dam.[Registration Number], [Pedigree Query].Dam.[Other
Registration #], [Pedigree Query].Dam.Title, [Pedigree Query].[S
Sire].[Horse's Name], [Pedigree Query].[S Sire].[Registration Number],
[Pedigree Query].[S Sire].[Other Registration #], [Pedigree Query].[S
Sire].Title, [Pedigree Query].[S Dam].[Horse's Name], [Pedigree Query].[S
Dam].[Registration Number], [Pedigree Query].[S Dam].[Other Registration #],
[Pedigree Query].[S Dam].Title, [Pedigree Query].[D Sire].[Horse's Name],
[Pedigree Query].[D Sire].[Registration Number], [Pedigree Query].[D
Sire].[Other Registration #], [Pedigree Query].[D Sire].Title, [Pedigree
Query].[D Dam].[Horse's Name], [Pedigree Query].[D Dam].[Registration
Number], [Pedigree Query].[D Dam].[Other Registration #], [Pedigree Query].[D
Dam].Title, [Pedigree Query].[S S Sire].[Horse's Name], [Pedigree Query].[S S
Sire].[Registration Number], [Pedigree Query].[S S Sire].[Other Registration
#], [Pedigree Query].[S S Sire].Title, [Pedigree Query].[S S Dam].[Horse's
Name], [Pedigree Query].[S S Dam].[Registration Number], [Pedigree Query].[S
S Dam].[Other Registration #], [Pedigree Query].[S S Dam].Title, [Pedigree
Query].[S D Sire].[Horse's Name], [Pedigree Query].[S D Sire].[Registration
Number], [Pedigree Query].[S D Sire].[Other Registration #], [Pedigree
Query].[S D Sire].Title, [Pedigree Query].[S D Dam].[Horse's Name], [Pedigree
Query].[S D Dam].[Registration Number], [Pedigree Query].[S D Dam].[Other
Registration #], [Pedigree Query].[S D Dam].Title, [Pedigree Query].[D S
Sire].[Horse's Name], [Pedigree Query].[D S Sire].[Registration Number],
[Pedigree Query].[D S Sire].[Other Registration #], [Pedigree Query].[D S
Sire].Title, [Pedigree Query].[D S Dam].[Horse's Name], [Pedigree Query].[D S
Dam].[Registration Number], [Pedigree Query].[D S Dam].[Other Registration
#], [Pedigree Query].[D S Dam].Title, [Pedigree Query].[D D Sire].[Horse's
Name], [Pedigree Query].[D D Sire].[Registration Number], [Pedigree Query].[D
D Sire].[Other Registration #], [Pedigree Query].[D D Sire].Title, [Pedigree
Query].[D D Dam].[Horse's Name], [Pedigree Query].[D D Dam].[Registration
Number], [Pedigree Query].[D D Dam].[Other Registration #], [Pedigree
Query].[D D Dam].Title, [Pedigree Query].[S S S Sire].[Horse's Name],
[Pedigree Query].[S S S Sire].[Registration Number], [Pedigree Query].[S S S
Sire].[Other Registration #], [Pedigree Query].[S S S Sire].Title, [Pedigree
Query].[S S S Dam].[Horse's Name], [Pedigree Query].[S S S Dam].[Registration
Number], [Pedigree Query].[S S S Dam].[Other Registration #], [Pedigree
Query].[S S S Dam].Title, [Pedigree Query].[S S D Sire].[Horse's Name],
[Pedigree Query].[S S D Sire].[Registration Number], [Pedigree Query].[S S D
Sire].[Other Registration #], [Pedigree Query].[S S D Sire].Title, [Pedigree
Query].[S S D Dam].[Horse's Name], [Pedigree Query].[S S D Dam].[Registration
Number], [Pedigree Query].[S S D Dam].[Other Registration #], [Pedigree
Query].[S S D Dam].Title, [Pedigree Query].[S D S Sire].[Horse's Name],
[Pedigree Query].[S D S Sire].[Registration Number], [Pedigree Query].[S D S
Sire].[Other Registration #], [Pedigree Query].[S D S Sire].Title, [Pedigree
Query].[S D S Dam].[Horse's Name], [Pedigree Query].[S D S Dam].[Registration
Number], [Pedigree Query].[S D S Dam].[Other Registration #], [Pedigree
Query].[S D S Dam].Title, [Pedigree Query].[S D D Sire].[Horse's Name],
[Pedigree Query].[S D D Sire].[Registration Number], [Pedigree Query].[S D D
Sire].[Other Registration #], [Pedigree Query].[S D D Sire].Title, [Pedigree
Query].[S D D Dam].[Horse's Name], [Pedigree Query].[S D D Dam].[Registration
Number], [Pedigree Query].[S D D Dam].[Other Registration #], [Pedigree
Query].[S D D Dam].Title, [Pedigree Query].[D S S Sire].[Horse's Name],
[Pedigree Query].[D S S Sire].[Registration Number], [Pedigree Query].[D S S
Sire].[Other Registration #], [Pedigree Query].[D S S Sire].Title, [Pedigree
Query].[D S S Dam].[Horse's Name], [Pedigree Query].[D S S Dam].[Registration
Number], [Pedigree Query].[D S S Dam].[Other Registration #], [Pedigree
Query].[D S S Dam].Title, [Pedigree Query].[D S D Sire].[Horse's Name],
[Pedigree Query].[D S D Sire].[Registration Number], [Pedigree Query].[D S D
Sire].[Other Registration #], [Pedigree Query].[D S D Sire].Title, [Pedigree
Query].[D S D Dam].[Horse's Name], [Pedigree Query].[D S D Dam].[Registration
Number], [Pedigree Query].[D S D Dam].[Other Registration #], [Pedigree
Query].[D S D Dam].Title, [Pedigree Query].[D D S Sire].[Horse's Name],
[Pedigree Query].[D D S Sire].[Registration Number], [Pedigree Query].[D D S
Sire].[Other Registration #], [Pedigree Query].[D D S Sire].Title, [Pedigree
Query].[D D S Dam].[Horse's Name], [Pedigree Query].[D D S Dam].[Registration
Number], [Pedigree Query].[D D S Dam].[Other Registration #], [Pedigree
Query].[D D S Dam].Title, [Pedigree Query].[D D D Sire].[Horse's Name],
[Pedigree Query].[D D D Sire].[Registration Number], [Pedigree Query].[D D D
Sire].[Other Registration #], [Pedigree Query].[D D D Sire].Title, [Pedigree
Query].[D D D Dam].[Horse's Name], [Pedigree Query].[D D D Dam].[Registration
Number], [Pedigree Query].[D D D Dam].[Other Registration #], [Pedigree
Query].[D D D Dam].Title, Horses.[Microchip #], Horses.[Tongue Tattoo],
Horses.Color, Horses.Markings, Members.[First Name], Members.[Middle
Initial], Members.[Last Name], Members.City, Members.State
FROM (Members INNER JOIN Horses ON Members.[Membership Number] =
Horses.[Owner's membership #]) INNER JOIN [Pedigree Query] ON Horses.[Horse
ID] = [Pedigree Query].[Horse ID]
WHERE (((Horses.[Registration Type])="Registered") AND
((Horses.Breed)="Friesian"))
ORDER BY Horses.[Horse's Name];

Thank You,
Honey


KARL DEWEY said:
Open your report in design view, Click on menu VIEW - Properties. See what
the Record Source says. If it is a SELECT statement that is the SQL. Copy
and paste in a post.
If it has the name of a query then open that query in design view, click on
menu VIEW - SQL View, highlight all, copy, and paste in a post.
--
KARL DEWEY
Build a little - Test a little


Honey said:
The SQL from the Query? Or you will have to tell me where it is on the report.
Thanks, Honey

KARL DEWEY said:
Post the FROM clause of your SQL statement. Better still post the complete
SQL.
--
KARL DEWEY
Build a little - Test a little


:

I have to track 4 generations of pedigree for horses. I created a pedigree
query using self joins by copying the Horses table and joining the sire and
dam id fields to the Horse ID field in the copied tables. I have changed the
name of the copied tables and everything seems to work just fine. I created
another query to include all of the other horses's information (Micorchip # ,
date of birth, color and markings, etc.) and the owners information uning
the horses table, the owners table and the pedigree query. When I run this
query, it seems fine. In datasheet view, all the info is filled in. I would
like to create a report from this query that I can customize to create the
Registration papers. When I run this report, I get this message: The
specified field 'Horses.[Horse's Name]' could refer to more than one table
listed in the FROM clause of your SQL statement. Please help me get around
this problem. I am using Access 2000. Thank You, Honey.
Click to expand...
Click to expand...
Click to expand...
 
K

KARL DEWEY

I do not see a problem but with a query as complex as this there is no telling.
I suggest making a copy and then start taking it apart piece by piece.
Start by removing the order by then the first field of the select as that is
the only places I find that field name.
--
KARL DEWEY
Build a little - Test a little


Honey said:
O.K. Karl,
Here it is. It is from the Query.

SELECT Horses.[Horse's Name], Horses.[Registration Number], Horses.[Date of
Birth], Horses.Sex, Horses.[Inbreeding Coefficient], Horses.Breeder,
Horses.[Achievement Level], [Pedigree Query].Sire.[Horse's Name], [Pedigree
Query].Sire.[Registration Number], [Pedigree Query].Sire.[Other Registration
#], [Pedigree Query].Sire.Title, [Pedigree Query].Dam.[Horse's Name],
[Pedigree Query].Dam.[Registration Number], [Pedigree Query].Dam.[Other
Registration #], [Pedigree Query].Dam.Title, [Pedigree Query].[S
Sire].[Horse's Name], [Pedigree Query].[S Sire].[Registration Number],
[Pedigree Query].[S Sire].[Other Registration #], [Pedigree Query].[S
Sire].Title, [Pedigree Query].[S Dam].[Horse's Name], [Pedigree Query].[S
Dam].[Registration Number], [Pedigree Query].[S Dam].[Other Registration #],
[Pedigree Query].[S Dam].Title, [Pedigree Query].[D Sire].[Horse's Name],
[Pedigree Query].[D Sire].[Registration Number], [Pedigree Query].[D
Sire].[Other Registration #], [Pedigree Query].[D Sire].Title, [Pedigree
Query].[D Dam].[Horse's Name], [Pedigree Query].[D Dam].[Registration
Number], [Pedigree Query].[D Dam].[Other Registration #], [Pedigree Query].[D
Dam].Title, [Pedigree Query].[S S Sire].[Horse's Name], [Pedigree Query].[S S
Sire].[Registration Number], [Pedigree Query].[S S Sire].[Other Registration
#], [Pedigree Query].[S S Sire].Title, [Pedigree Query].[S S Dam].[Horse's
Name], [Pedigree Query].[S S Dam].[Registration Number], [Pedigree Query].[S
S Dam].[Other Registration #], [Pedigree Query].[S S Dam].Title, [Pedigree
Query].[S D Sire].[Horse's Name], [Pedigree Query].[S D Sire].[Registration
Number], [Pedigree Query].[S D Sire].[Other Registration #], [Pedigree
Query].[S D Sire].Title, [Pedigree Query].[S D Dam].[Horse's Name], [Pedigree
Query].[S D Dam].[Registration Number], [Pedigree Query].[S D Dam].[Other
Registration #], [Pedigree Query].[S D Dam].Title, [Pedigree Query].[D S
Sire].[Horse's Name], [Pedigree Query].[D S Sire].[Registration Number],
[Pedigree Query].[D S Sire].[Other Registration #], [Pedigree Query].[D S
Sire].Title, [Pedigree Query].[D S Dam].[Horse's Name], [Pedigree Query].[D S
Dam].[Registration Number], [Pedigree Query].[D S Dam].[Other Registration
#], [Pedigree Query].[D S Dam].Title, [Pedigree Query].[D D Sire].[Horse's
Name], [Pedigree Query].[D D Sire].[Registration Number], [Pedigree Query].[D
D Sire].[Other Registration #], [Pedigree Query].[D D Sire].Title, [Pedigree
Query].[D D Dam].[Horse's Name], [Pedigree Query].[D D Dam].[Registration
Number], [Pedigree Query].[D D Dam].[Other Registration #], [Pedigree
Query].[D D Dam].Title, [Pedigree Query].[S S S Sire].[Horse's Name],
[Pedigree Query].[S S S Sire].[Registration Number], [Pedigree Query].[S S S
Sire].[Other Registration #], [Pedigree Query].[S S S Sire].Title, [Pedigree
Query].[S S S Dam].[Horse's Name], [Pedigree Query].[S S S Dam].[Registration
Number], [Pedigree Query].[S S S Dam].[Other Registration #], [Pedigree
Query].[S S S Dam].Title, [Pedigree Query].[S S D Sire].[Horse's Name],
[Pedigree Query].[S S D Sire].[Registration Number], [Pedigree Query].[S S D
Sire].[Other Registration #], [Pedigree Query].[S S D Sire].Title, [Pedigree
Query].[S S D Dam].[Horse's Name], [Pedigree Query].[S S D Dam].[Registration
Number], [Pedigree Query].[S S D Dam].[Other Registration #], [Pedigree
Query].[S S D Dam].Title, [Pedigree Query].[S D S Sire].[Horse's Name],
[Pedigree Query].[S D S Sire].[Registration Number], [Pedigree Query].[S D S
Sire].[Other Registration #], [Pedigree Query].[S D S Sire].Title, [Pedigree
Query].[S D S Dam].[Horse's Name], [Pedigree Query].[S D S Dam].[Registration
Number], [Pedigree Query].[S D S Dam].[Other Registration #], [Pedigree
Query].[S D S Dam].Title, [Pedigree Query].[S D D Sire].[Horse's Name],
[Pedigree Query].[S D D Sire].[Registration Number], [Pedigree Query].[S D D
Sire].[Other Registration #], [Pedigree Query].[S D D Sire].Title, [Pedigree
Query].[S D D Dam].[Horse's Name], [Pedigree Query].[S D D Dam].[Registration
Number], [Pedigree Query].[S D D Dam].[Other Registration #], [Pedigree
Query].[S D D Dam].Title, [Pedigree Query].[D S S Sire].[Horse's Name],
[Pedigree Query].[D S S Sire].[Registration Number], [Pedigree Query].[D S S
Sire].[Other Registration #], [Pedigree Query].[D S S Sire].Title, [Pedigree
Query].[D S S Dam].[Horse's Name], [Pedigree Query].[D S S Dam].[Registration
Number], [Pedigree Query].[D S S Dam].[Other Registration #], [Pedigree
Query].[D S S Dam].Title, [Pedigree Query].[D S D Sire].[Horse's Name],
[Pedigree Query].[D S D Sire].[Registration Number], [Pedigree Query].[D S D
Sire].[Other Registration #], [Pedigree Query].[D S D Sire].Title, [Pedigree
Query].[D S D Dam].[Horse's Name], [Pedigree Query].[D S D Dam].[Registration
Number], [Pedigree Query].[D S D Dam].[Other Registration #], [Pedigree
Query].[D S D Dam].Title, [Pedigree Query].[D D S Sire].[Horse's Name],
[Pedigree Query].[D D S Sire].[Registration Number], [Pedigree Query].[D D S
Sire].[Other Registration #], [Pedigree Query].[D D S Sire].Title, [Pedigree
Query].[D D S Dam].[Horse's Name], [Pedigree Query].[D D S Dam].[Registration
Number], [Pedigree Query].[D D S Dam].[Other Registration #], [Pedigree
Query].[D D S Dam].Title, [Pedigree Query].[D D D Sire].[Horse's Name],
[Pedigree Query].[D D D Sire].[Registration Number], [Pedigree Query].[D D D
Sire].[Other Registration #], [Pedigree Query].[D D D Sire].Title, [Pedigree
Query].[D D D Dam].[Horse's Name], [Pedigree Query].[D D D Dam].[Registration
Number], [Pedigree Query].[D D D Dam].[Other Registration #], [Pedigree
Query].[D D D Dam].Title, Horses.[Microchip #], Horses.[Tongue Tattoo],
Horses.Color, Horses.Markings, Members.[First Name], Members.[Middle
Initial], Members.[Last Name], Members.City, Members.State
FROM (Members INNER JOIN Horses ON Members.[Membership Number] =
Horses.[Owner's membership #]) INNER JOIN [Pedigree Query] ON Horses.[Horse
ID] = [Pedigree Query].[Horse ID]
WHERE (((Horses.[Registration Type])="Registered") AND
((Horses.Breed)="Friesian"))
ORDER BY Horses.[Horse's Name];

Thank You,
Honey


KARL DEWEY said:
Open your report in design view, Click on menu VIEW - Properties. See what
the Record Source says. If it is a SELECT statement that is the SQL. Copy
and paste in a post.
If it has the name of a query then open that query in design view, click on
menu VIEW - SQL View, highlight all, copy, and paste in a post.
--
KARL DEWEY
Build a little - Test a little


Honey said:
The SQL from the Query? Or you will have to tell me where it is on the report.
Thanks, Honey

:

Post the FROM clause of your SQL statement. Better still post the complete
SQL.
--
KARL DEWEY
Build a little - Test a little


:

I have to track 4 generations of pedigree for horses. I created a pedigree
query using self joins by copying the Horses table and joining the sire and
dam id fields to the Horse ID field in the copied tables. I have changed the
name of the copied tables and everything seems to work just fine. I created
another query to include all of the other horses's information (Micorchip # ,
date of birth, color and markings, etc.) and the owners information uning
the horses table, the owners table and the pedigree query. When I run this
query, it seems fine. In datasheet view, all the info is filled in. I would
like to create a report from this query that I can customize to create the
Registration papers. When I run this report, I get this message: The
specified field 'Horses.[Horse's Name]' could refer to more than one table
listed in the FROM clause of your SQL statement. Please help me get around
this problem. I am using Access 2000. Thank You, Honey.
Click to expand...
Click to expand...
Click to expand...
 
H

Honey

The query for this report is comprised of fields from the Horses table, the
Members table and the Pedigree Query. I created a query from just the horses
table and the members table and created the report fine. As soon as I ad 1
field from the Pedigree query, I have the same problem. Could there be a
problem with the Pedigree query? Is there any other way to pull 4
generations of ancestors other than to create a query of self joins? Very
frustrated. Thanks for any help with this and thank you for the help already
provided.

Honey



KARL DEWEY said:
I do not see a problem but with a query as complex as this there is no telling.
I suggest making a copy and then start taking it apart piece by piece.
Start by removing the order by then the first field of the select as that is
the only places I find that field name.
--
KARL DEWEY
Build a little - Test a little


Honey said:
O.K. Karl,
Here it is. It is from the Query.

SELECT Horses.[Horse's Name], Horses.[Registration Number], Horses.[Date of
Birth], Horses.Sex, Horses.[Inbreeding Coefficient], Horses.Breeder,
Horses.[Achievement Level], [Pedigree Query].Sire.[Horse's Name], [Pedigree
Query].Sire.[Registration Number], [Pedigree Query].Sire.[Other Registration
#], [Pedigree Query].Sire.Title, [Pedigree Query].Dam.[Horse's Name],
[Pedigree Query].Dam.[Registration Number], [Pedigree Query].Dam.[Other
Registration #], [Pedigree Query].Dam.Title, [Pedigree Query].[S
Sire].[Horse's Name], [Pedigree Query].[S Sire].[Registration Number],
[Pedigree Query].[S Sire].[Other Registration #], [Pedigree Query].[S
Sire].Title, [Pedigree Query].[S Dam].[Horse's Name], [Pedigree Query].[S
Dam].[Registration Number], [Pedigree Query].[S Dam].[Other Registration #],
[Pedigree Query].[S Dam].Title, [Pedigree Query].[D Sire].[Horse's Name],
[Pedigree Query].[D Sire].[Registration Number], [Pedigree Query].[D
Sire].[Other Registration #], [Pedigree Query].[D Sire].Title, [Pedigree
Query].[D Dam].[Horse's Name], [Pedigree Query].[D Dam].[Registration
Number], [Pedigree Query].[D Dam].[Other Registration #], [Pedigree Query].[D
Dam].Title, [Pedigree Query].[S S Sire].[Horse's Name], [Pedigree Query].[S S
Sire].[Registration Number], [Pedigree Query].[S S Sire].[Other Registration
#], [Pedigree Query].[S S Sire].Title, [Pedigree Query].[S S Dam].[Horse's
Name], [Pedigree Query].[S S Dam].[Registration Number], [Pedigree Query].[S
S Dam].[Other Registration #], [Pedigree Query].[S S Dam].Title, [Pedigree
Query].[S D Sire].[Horse's Name], [Pedigree Query].[S D Sire].[Registration
Number], [Pedigree Query].[S D Sire].[Other Registration #], [Pedigree
Query].[S D Sire].Title, [Pedigree Query].[S D Dam].[Horse's Name], [Pedigree
Query].[S D Dam].[Registration Number], [Pedigree Query].[S D Dam].[Other
Registration #], [Pedigree Query].[S D Dam].Title, [Pedigree Query].[D S
Sire].[Horse's Name], [Pedigree Query].[D S Sire].[Registration Number],
[Pedigree Query].[D S Sire].[Other Registration #], [Pedigree Query].[D S
Sire].Title, [Pedigree Query].[D S Dam].[Horse's Name], [Pedigree Query].[D S
Dam].[Registration Number], [Pedigree Query].[D S Dam].[Other Registration
#], [Pedigree Query].[D S Dam].Title, [Pedigree Query].[D D Sire].[Horse's
Name], [Pedigree Query].[D D Sire].[Registration Number], [Pedigree Query].[D
D Sire].[Other Registration #], [Pedigree Query].[D D Sire].Title, [Pedigree
Query].[D D Dam].[Horse's Name], [Pedigree Query].[D D Dam].[Registration
Number], [Pedigree Query].[D D Dam].[Other Registration #], [Pedigree
Query].[D D Dam].Title, [Pedigree Query].[S S S Sire].[Horse's Name],
[Pedigree Query].[S S S Sire].[Registration Number], [Pedigree Query].[S S S
Sire].[Other Registration #], [Pedigree Query].[S S S Sire].Title, [Pedigree
Query].[S S S Dam].[Horse's Name], [Pedigree Query].[S S S Dam].[Registration
Number], [Pedigree Query].[S S S Dam].[Other Registration #], [Pedigree
Query].[S S S Dam].Title, [Pedigree Query].[S S D Sire].[Horse's Name],
[Pedigree Query].[S S D Sire].[Registration Number], [Pedigree Query].[S S D
Sire].[Other Registration #], [Pedigree Query].[S S D Sire].Title, [Pedigree
Query].[S S D Dam].[Horse's Name], [Pedigree Query].[S S D Dam].[Registration
Number], [Pedigree Query].[S S D Dam].[Other Registration #], [Pedigree
Query].[S S D Dam].Title, [Pedigree Query].[S D S Sire].[Horse's Name],
[Pedigree Query].[S D S Sire].[Registration Number], [Pedigree Query].[S D S
Sire].[Other Registration #], [Pedigree Query].[S D S Sire].Title, [Pedigree
Query].[S D S Dam].[Horse's Name], [Pedigree Query].[S D S Dam].[Registration
Number], [Pedigree Query].[S D S Dam].[Other Registration #], [Pedigree
Query].[S D S Dam].Title, [Pedigree Query].[S D D Sire].[Horse's Name],
[Pedigree Query].[S D D Sire].[Registration Number], [Pedigree Query].[S D D
Sire].[Other Registration #], [Pedigree Query].[S D D Sire].Title, [Pedigree
Query].[S D D Dam].[Horse's Name], [Pedigree Query].[S D D Dam].[Registration
Number], [Pedigree Query].[S D D Dam].[Other Registration #], [Pedigree
Query].[S D D Dam].Title, [Pedigree Query].[D S S Sire].[Horse's Name],
[Pedigree Query].[D S S Sire].[Registration Number], [Pedigree Query].[D S S
Sire].[Other Registration #], [Pedigree Query].[D S S Sire].Title, [Pedigree
Query].[D S S Dam].[Horse's Name], [Pedigree Query].[D S S Dam].[Registration
Number], [Pedigree Query].[D S S Dam].[Other Registration #], [Pedigree
Query].[D S S Dam].Title, [Pedigree Query].[D S D Sire].[Horse's Name],
[Pedigree Query].[D S D Sire].[Registration Number], [Pedigree Query].[D S D
Sire].[Other Registration #], [Pedigree Query].[D S D Sire].Title, [Pedigree
Query].[D S D Dam].[Horse's Name], [Pedigree Query].[D S D Dam].[Registration
Number], [Pedigree Query].[D S D Dam].[Other Registration #], [Pedigree
Query].[D S D Dam].Title, [Pedigree Query].[D D S Sire].[Horse's Name],
[Pedigree Query].[D D S Sire].[Registration Number], [Pedigree Query].[D D S
Sire].[Other Registration #], [Pedigree Query].[D D S Sire].Title, [Pedigree
Query].[D D S Dam].[Horse's Name], [Pedigree Query].[D D S Dam].[Registration
Number], [Pedigree Query].[D D S Dam].[Other Registration #], [Pedigree
Query].[D D S Dam].Title, [Pedigree Query].[D D D Sire].[Horse's Name],
[Pedigree Query].[D D D Sire].[Registration Number], [Pedigree Query].[D D D
Sire].[Other Registration #], [Pedigree Query].[D D D Sire].Title, [Pedigree
Query].[D D D Dam].[Horse's Name], [Pedigree Query].[D D D Dam].[Registration
Number], [Pedigree Query].[D D D Dam].[Other Registration #], [Pedigree
Query].[D D D Dam].Title, Horses.[Microchip #], Horses.[Tongue Tattoo],
Horses.Color, Horses.Markings, Members.[First Name], Members.[Middle
Initial], Members.[Last Name], Members.City, Members.State
FROM (Members INNER JOIN Horses ON Members.[Membership Number] =
Horses.[Owner's membership #]) INNER JOIN [Pedigree Query] ON Horses.[Horse
ID] = [Pedigree Query].[Horse ID]
WHERE (((Horses.[Registration Type])="Registered") AND
((Horses.Breed)="Friesian"))
ORDER BY Horses.[Horse's Name];

Thank You,
Honey


KARL DEWEY said:
Open your report in design view, Click on menu VIEW - Properties. See what
the Record Source says. If it is a SELECT statement that is the SQL. Copy
and paste in a post.
If it has the name of a query then open that query in design view, click on
menu VIEW - SQL View, highlight all, copy, and paste in a post.
--
KARL DEWEY
Build a little - Test a little


:

The SQL from the Query? Or you will have to tell me where it is on the report.
Thanks, Honey

:

Post the FROM clause of your SQL statement. Better still post the complete
SQL.
--
KARL DEWEY
Build a little - Test a little


:

I have to track 4 generations of pedigree for horses. I created a pedigree
query using self joins by copying the Horses table and joining the sire and
dam id fields to the Horse ID field in the copied tables. I have changed the
name of the copied tables and everything seems to work just fine. I created
another query to include all of the other horses's information (Micorchip # ,
date of birth, color and markings, etc.) and the owners information uning
the horses table, the owners table and the pedigree query. When I run this
query, it seems fine. In datasheet view, all the info is filled in. I would
like to create a report from this query that I can customize to create the
Registration papers. When I run this report, I get this message: The
specified field 'Horses.[Horse's Name]' could refer to more than one table
listed in the FROM clause of your SQL statement. Please help me get around
this problem. I am using Access 2000. Thank You, Honey.
Click to expand...
Click to expand...
Click to expand...
 
K

KARL DEWEY

Could there be a problem with the Pedigree query?
Definitely. Try running it alone.
The problem is probably in a calculated field or expression.
--
KARL DEWEY
Build a little - Test a little


Honey said:
The query for this report is comprised of fields from the Horses table, the
Members table and the Pedigree Query. I created a query from just the horses
table and the members table and created the report fine. As soon as I ad 1
field from the Pedigree query, I have the same problem. Could there be a
problem with the Pedigree query? Is there any other way to pull 4
generations of ancestors other than to create a query of self joins? Very
frustrated. Thanks for any help with this and thank you for the help already
provided.

Honey



KARL DEWEY said:
I do not see a problem but with a query as complex as this there is no telling.
I suggest making a copy and then start taking it apart piece by piece.
Start by removing the order by then the first field of the select as that is
the only places I find that field name.
--
KARL DEWEY
Build a little - Test a little


Honey said:
O.K. Karl,
Here it is. It is from the Query.

SELECT Horses.[Horse's Name], Horses.[Registration Number], Horses.[Date of
Birth], Horses.Sex, Horses.[Inbreeding Coefficient], Horses.Breeder,
Horses.[Achievement Level], [Pedigree Query].Sire.[Horse's Name], [Pedigree
Query].Sire.[Registration Number], [Pedigree Query].Sire.[Other Registration
#], [Pedigree Query].Sire.Title, [Pedigree Query].Dam.[Horse's Name],
[Pedigree Query].Dam.[Registration Number], [Pedigree Query].Dam.[Other
Registration #], [Pedigree Query].Dam.Title, [Pedigree Query].[S
Sire].[Horse's Name], [Pedigree Query].[S Sire].[Registration Number],
[Pedigree Query].[S Sire].[Other Registration #], [Pedigree Query].[S
Sire].Title, [Pedigree Query].[S Dam].[Horse's Name], [Pedigree Query].[S
Dam].[Registration Number], [Pedigree Query].[S Dam].[Other Registration #],
[Pedigree Query].[S Dam].Title, [Pedigree Query].[D Sire].[Horse's Name],
[Pedigree Query].[D Sire].[Registration Number], [Pedigree Query].[D
Sire].[Other Registration #], [Pedigree Query].[D Sire].Title, [Pedigree
Query].[D Dam].[Horse's Name], [Pedigree Query].[D Dam].[Registration
Number], [Pedigree Query].[D Dam].[Other Registration #], [Pedigree Query].[D
Dam].Title, [Pedigree Query].[S S Sire].[Horse's Name], [Pedigree Query].[S S
Sire].[Registration Number], [Pedigree Query].[S S Sire].[Other Registration
#], [Pedigree Query].[S S Sire].Title, [Pedigree Query].[S S Dam].[Horse's
Name], [Pedigree Query].[S S Dam].[Registration Number], [Pedigree Query].[S
S Dam].[Other Registration #], [Pedigree Query].[S S Dam].Title, [Pedigree
Query].[S D Sire].[Horse's Name], [Pedigree Query].[S D Sire].[Registration
Number], [Pedigree Query].[S D Sire].[Other Registration #], [Pedigree
Query].[S D Sire].Title, [Pedigree Query].[S D Dam].[Horse's Name], [Pedigree
Query].[S D Dam].[Registration Number], [Pedigree Query].[S D Dam].[Other
Registration #], [Pedigree Query].[S D Dam].Title, [Pedigree Query].[D S
Sire].[Horse's Name], [Pedigree Query].[D S Sire].[Registration Number],
[Pedigree Query].[D S Sire].[Other Registration #], [Pedigree Query].[D S
Sire].Title, [Pedigree Query].[D S Dam].[Horse's Name], [Pedigree Query].[D S
Dam].[Registration Number], [Pedigree Query].[D S Dam].[Other Registration
#], [Pedigree Query].[D S Dam].Title, [Pedigree Query].[D D Sire].[Horse's
Name], [Pedigree Query].[D D Sire].[Registration Number], [Pedigree Query].[D
D Sire].[Other Registration #], [Pedigree Query].[D D Sire].Title, [Pedigree
Query].[D D Dam].[Horse's Name], [Pedigree Query].[D D Dam].[Registration
Number], [Pedigree Query].[D D Dam].[Other Registration #], [Pedigree
Query].[D D Dam].Title, [Pedigree Query].[S S S Sire].[Horse's Name],
[Pedigree Query].[S S S Sire].[Registration Number], [Pedigree Query].[S S S
Sire].[Other Registration #], [Pedigree Query].[S S S Sire].Title, [Pedigree
Query].[S S S Dam].[Horse's Name], [Pedigree Query].[S S S Dam].[Registration
Number], [Pedigree Query].[S S S Dam].[Other Registration #], [Pedigree
Query].[S S S Dam].Title, [Pedigree Query].[S S D Sire].[Horse's Name],
[Pedigree Query].[S S D Sire].[Registration Number], [Pedigree Query].[S S D
Sire].[Other Registration #], [Pedigree Query].[S S D Sire].Title, [Pedigree
Query].[S S D Dam].[Horse's Name], [Pedigree Query].[S S D Dam].[Registration
Number], [Pedigree Query].[S S D Dam].[Other Registration #], [Pedigree
Query].[S S D Dam].Title, [Pedigree Query].[S D S Sire].[Horse's Name],
[Pedigree Query].[S D S Sire].[Registration Number], [Pedigree Query].[S D S
Sire].[Other Registration #], [Pedigree Query].[S D S Sire].Title, [Pedigree
Query].[S D S Dam].[Horse's Name], [Pedigree Query].[S D S Dam].[Registration
Number], [Pedigree Query].[S D S Dam].[Other Registration #], [Pedigree
Query].[S D S Dam].Title, [Pedigree Query].[S D D Sire].[Horse's Name],
[Pedigree Query].[S D D Sire].[Registration Number], [Pedigree Query].[S D D
Sire].[Other Registration #], [Pedigree Query].[S D D Sire].Title, [Pedigree
Query].[S D D Dam].[Horse's Name], [Pedigree Query].[S D D Dam].[Registration
Number], [Pedigree Query].[S D D Dam].[Other Registration #], [Pedigree
Query].[S D D Dam].Title, [Pedigree Query].[D S S Sire].[Horse's Name],
[Pedigree Query].[D S S Sire].[Registration Number], [Pedigree Query].[D S S
Sire].[Other Registration #], [Pedigree Query].[D S S Sire].Title, [Pedigree
Query].[D S S Dam].[Horse's Name], [Pedigree Query].[D S S Dam].[Registration
Number], [Pedigree Query].[D S S Dam].[Other Registration #], [Pedigree
Query].[D S S Dam].Title, [Pedigree Query].[D S D Sire].[Horse's Name],
[Pedigree Query].[D S D Sire].[Registration Number], [Pedigree Query].[D S D
Sire].[Other Registration #], [Pedigree Query].[D S D Sire].Title, [Pedigree
Query].[D S D Dam].[Horse's Name], [Pedigree Query].[D S D Dam].[Registration
Number], [Pedigree Query].[D S D Dam].[Other Registration #], [Pedigree
Query].[D S D Dam].Title, [Pedigree Query].[D D S Sire].[Horse's Name],
[Pedigree Query].[D D S Sire].[Registration Number], [Pedigree Query].[D D S
Sire].[Other Registration #], [Pedigree Query].[D D S Sire].Title, [Pedigree
Query].[D D S Dam].[Horse's Name], [Pedigree Query].[D D S Dam].[Registration
Number], [Pedigree Query].[D D S Dam].[Other Registration #], [Pedigree
Query].[D D S Dam].Title, [Pedigree Query].[D D D Sire].[Horse's Name],
[Pedigree Query].[D D D Sire].[Registration Number], [Pedigree Query].[D D D
Sire].[Other Registration #], [Pedigree Query].[D D D Sire].Title, [Pedigree
Query].[D D D Dam].[Horse's Name], [Pedigree Query].[D D D Dam].[Registration
Number], [Pedigree Query].[D D D Dam].[Other Registration #], [Pedigree
Query].[D D D Dam].Title, Horses.[Microchip #], Horses.[Tongue Tattoo],
Horses.Color, Horses.Markings, Members.[First Name], Members.[Middle
Initial], Members.[Last Name], Members.City, Members.State
FROM (Members INNER JOIN Horses ON Members.[Membership Number] =
Horses.[Owner's membership #]) INNER JOIN [Pedigree Query] ON Horses.[Horse
ID] = [Pedigree Query].[Horse ID]
WHERE (((Horses.[Registration Type])="Registered") AND
((Horses.Breed)="Friesian"))
ORDER BY Horses.[Horse's Name];

Thank You,
Honey


:

Open your report in design view, Click on menu VIEW - Properties. See what
the Record Source says. If it is a SELECT statement that is the SQL. Copy
and paste in a post.
If it has the name of a query then open that query in design view, click on
menu VIEW - SQL View, highlight all, copy, and paste in a post.
--
KARL DEWEY
Build a little - Test a little


:

The SQL from the Query? Or you will have to tell me where it is on the report.
Thanks, Honey

:

Post the FROM clause of your SQL statement. Better still post the complete
SQL.
--
KARL DEWEY
Build a little - Test a little


:

I have to track 4 generations of pedigree for horses. I created a pedigree
query using self joins by copying the Horses table and joining the sire and
dam id fields to the Horse ID field in the copied tables. I have changed the
name of the copied tables and everything seems to work just fine. I created
another query to include all of the other horses's information (Micorchip # ,
date of birth, color and markings, etc.) and the owners information uning
the horses table, the owners table and the pedigree query. When I run this
query, it seems fine. In datasheet view, all the info is filled in. I would
like to create a report from this query that I can customize to create the
Registration papers. When I run this report, I get this message: The
specified field 'Horses.[Horse's Name]' could refer to more than one table
listed in the FROM clause of your SQL statement. Please help me get around
this problem. I am using Access 2000. Thank You, Honey.
Click to expand...
Click to expand...
Click to expand...
 
H

Honey

When I open the Pedigree query in datasheet view, all the info is there -
looks perfect. Is there a more complete test? and if not - what now?

Honey


KARL DEWEY said:
Definitely. Try running it alone.
The problem is probably in a calculated field or expression.
--
KARL DEWEY
Build a little - Test a little


Honey said:
The query for this report is comprised of fields from the Horses table, the
Members table and the Pedigree Query. I created a query from just the horses
table and the members table and created the report fine. As soon as I ad 1
field from the Pedigree query, I have the same problem. Could there be a
problem with the Pedigree query? Is there any other way to pull 4
generations of ancestors other than to create a query of self joins? Very
frustrated. Thanks for any help with this and thank you for the help already
provided.

Honey



KARL DEWEY said:
I do not see a problem but with a query as complex as this there is no telling.
I suggest making a copy and then start taking it apart piece by piece.
Start by removing the order by then the first field of the select as that is
the only places I find that field name.
--
KARL DEWEY
Build a little - Test a little


:

O.K. Karl,
Here it is. It is from the Query.

SELECT Horses.[Horse's Name], Horses.[Registration Number], Horses.[Date of
Birth], Horses.Sex, Horses.[Inbreeding Coefficient], Horses.Breeder,
Horses.[Achievement Level], [Pedigree Query].Sire.[Horse's Name], [Pedigree
Query].Sire.[Registration Number], [Pedigree Query].Sire.[Other Registration
#], [Pedigree Query].Sire.Title, [Pedigree Query].Dam.[Horse's Name],
[Pedigree Query].Dam.[Registration Number], [Pedigree Query].Dam.[Other
Registration #], [Pedigree Query].Dam.Title, [Pedigree Query].[S
Sire].[Horse's Name], [Pedigree Query].[S Sire].[Registration Number],
[Pedigree Query].[S Sire].[Other Registration #], [Pedigree Query].[S
Sire].Title, [Pedigree Query].[S Dam].[Horse's Name], [Pedigree Query].[S
Dam].[Registration Number], [Pedigree Query].[S Dam].[Other Registration #],
[Pedigree Query].[S Dam].Title, [Pedigree Query].[D Sire].[Horse's Name],
[Pedigree Query].[D Sire].[Registration Number], [Pedigree Query].[D
Sire].[Other Registration #], [Pedigree Query].[D Sire].Title, [Pedigree
Query].[D Dam].[Horse's Name], [Pedigree Query].[D Dam].[Registration
Number], [Pedigree Query].[D Dam].[Other Registration #], [Pedigree Query].[D
Dam].Title, [Pedigree Query].[S S Sire].[Horse's Name], [Pedigree Query].[S S
Sire].[Registration Number], [Pedigree Query].[S S Sire].[Other Registration
#], [Pedigree Query].[S S Sire].Title, [Pedigree Query].[S S Dam].[Horse's
Name], [Pedigree Query].[S S Dam].[Registration Number], [Pedigree Query].[S
S Dam].[Other Registration #], [Pedigree Query].[S S Dam].Title, [Pedigree
Query].[S D Sire].[Horse's Name], [Pedigree Query].[S D Sire].[Registration
Number], [Pedigree Query].[S D Sire].[Other Registration #], [Pedigree
Query].[S D Sire].Title, [Pedigree Query].[S D Dam].[Horse's Name], [Pedigree
Query].[S D Dam].[Registration Number], [Pedigree Query].[S D Dam].[Other
Registration #], [Pedigree Query].[S D Dam].Title, [Pedigree Query].[D S
Sire].[Horse's Name], [Pedigree Query].[D S Sire].[Registration Number],
[Pedigree Query].[D S Sire].[Other Registration #], [Pedigree Query].[D S
Sire].Title, [Pedigree Query].[D S Dam].[Horse's Name], [Pedigree Query].[D S
Dam].[Registration Number], [Pedigree Query].[D S Dam].[Other Registration
#], [Pedigree Query].[D S Dam].Title, [Pedigree Query].[D D Sire].[Horse's
Name], [Pedigree Query].[D D Sire].[Registration Number], [Pedigree Query].[D
D Sire].[Other Registration #], [Pedigree Query].[D D Sire].Title, [Pedigree
Query].[D D Dam].[Horse's Name], [Pedigree Query].[D D Dam].[Registration
Number], [Pedigree Query].[D D Dam].[Other Registration #], [Pedigree
Query].[D D Dam].Title, [Pedigree Query].[S S S Sire].[Horse's Name],
[Pedigree Query].[S S S Sire].[Registration Number], [Pedigree Query].[S S S
Sire].[Other Registration #], [Pedigree Query].[S S S Sire].Title, [Pedigree
Query].[S S S Dam].[Horse's Name], [Pedigree Query].[S S S Dam].[Registration
Number], [Pedigree Query].[S S S Dam].[Other Registration #], [Pedigree
Query].[S S S Dam].Title, [Pedigree Query].[S S D Sire].[Horse's Name],
[Pedigree Query].[S S D Sire].[Registration Number], [Pedigree Query].[S S D
Sire].[Other Registration #], [Pedigree Query].[S S D Sire].Title, [Pedigree
Query].[S S D Dam].[Horse's Name], [Pedigree Query].[S S D Dam].[Registration
Number], [Pedigree Query].[S S D Dam].[Other Registration #], [Pedigree
Query].[S S D Dam].Title, [Pedigree Query].[S D S Sire].[Horse's Name],
[Pedigree Query].[S D S Sire].[Registration Number], [Pedigree Query].[S D S
Sire].[Other Registration #], [Pedigree Query].[S D S Sire].Title, [Pedigree
Query].[S D S Dam].[Horse's Name], [Pedigree Query].[S D S Dam].[Registration
Number], [Pedigree Query].[S D S Dam].[Other Registration #], [Pedigree
Query].[S D S Dam].Title, [Pedigree Query].[S D D Sire].[Horse's Name],
[Pedigree Query].[S D D Sire].[Registration Number], [Pedigree Query].[S D D
Sire].[Other Registration #], [Pedigree Query].[S D D Sire].Title, [Pedigree
Query].[S D D Dam].[Horse's Name], [Pedigree Query].[S D D Dam].[Registration
Number], [Pedigree Query].[S D D Dam].[Other Registration #], [Pedigree
Query].[S D D Dam].Title, [Pedigree Query].[D S S Sire].[Horse's Name],
[Pedigree Query].[D S S Sire].[Registration Number], [Pedigree Query].[D S S
Sire].[Other Registration #], [Pedigree Query].[D S S Sire].Title, [Pedigree
Query].[D S S Dam].[Horse's Name], [Pedigree Query].[D S S Dam].[Registration
Number], [Pedigree Query].[D S S Dam].[Other Registration #], [Pedigree
Query].[D S S Dam].Title, [Pedigree Query].[D S D Sire].[Horse's Name],
[Pedigree Query].[D S D Sire].[Registration Number], [Pedigree Query].[D S D
Sire].[Other Registration #], [Pedigree Query].[D S D Sire].Title, [Pedigree
Query].[D S D Dam].[Horse's Name], [Pedigree Query].[D S D Dam].[Registration
Number], [Pedigree Query].[D S D Dam].[Other Registration #], [Pedigree
Query].[D S D Dam].Title, [Pedigree Query].[D D S Sire].[Horse's Name],
[Pedigree Query].[D D S Sire].[Registration Number], [Pedigree Query].[D D S
Sire].[Other Registration #], [Pedigree Query].[D D S Sire].Title, [Pedigree
Query].[D D S Dam].[Horse's Name], [Pedigree Query].[D D S Dam].[Registration
Number], [Pedigree Query].[D D S Dam].[Other Registration #], [Pedigree
Query].[D D S Dam].Title, [Pedigree Query].[D D D Sire].[Horse's Name],
[Pedigree Query].[D D D Sire].[Registration Number], [Pedigree Query].[D D D
Sire].[Other Registration #], [Pedigree Query].[D D D Sire].Title, [Pedigree
Query].[D D D Dam].[Horse's Name], [Pedigree Query].[D D D Dam].[Registration
Number], [Pedigree Query].[D D D Dam].[Other Registration #], [Pedigree
Query].[D D D Dam].Title, Horses.[Microchip #], Horses.[Tongue Tattoo],
Horses.Color, Horses.Markings, Members.[First Name], Members.[Middle
Initial], Members.[Last Name], Members.City, Members.State
FROM (Members INNER JOIN Horses ON Members.[Membership Number] =
Horses.[Owner's membership #]) INNER JOIN [Pedigree Query] ON Horses.[Horse
ID] = [Pedigree Query].[Horse ID]
WHERE (((Horses.[Registration Type])="Registered") AND
((Horses.Breed)="Friesian"))
ORDER BY Horses.[Horse's Name];

Thank You,
Honey


:

Open your report in design view, Click on menu VIEW - Properties. See what
the Record Source says. If it is a SELECT statement that is the SQL. Copy
and paste in a post.
If it has the name of a query then open that query in design view, click on
menu VIEW - SQL View, highlight all, copy, and paste in a post.
--
KARL DEWEY
Build a little - Test a little


:

The SQL from the Query? Or you will have to tell me where it is on the report.
Thanks, Honey

:

Post the FROM clause of your SQL statement. Better still post the complete
SQL.
--
KARL DEWEY
Build a little - Test a little


:

I have to track 4 generations of pedigree for horses. I created a pedigree
query using self joins by copying the Horses table and joining the sire and
dam id fields to the Horse ID field in the copied tables. I have changed the
name of the copied tables and everything seems to work just fine. I created
another query to include all of the other horses's information (Micorchip # ,
date of birth, color and markings, etc.) and the owners information uning
the horses table, the owners table and the pedigree query. When I run this
query, it seems fine. In datasheet view, all the info is filled in. I would
like to create a report from this query that I can customize to create the
Registration papers. When I run this report, I get this message: The
specified field 'Horses.[Horse's Name]' could refer to more than one table
listed in the FROM clause of your SQL statement. Please help me get around
this problem. I am using Access 2000. Thank You, Honey.
Click to expand...
Click to expand...
Click to expand...
 
T

ThomasJ

Joined
Jun 6, 2008
Messages
4
Reaction score
0
Send me the file and I will fix it for you for free. Or call me on 250 885 2925 Seattle time.
 
M

Michael Gramelspacher

When I open the Pedigree query in datasheet view, all the info is there -
looks perfect. Is there a more complete test? and if not - what now?

Honey


KARL DEWEY said:
Could there be a problem with the Pedigree query?
Click to expand...
Definitely. Try running it alone.
The problem is probably in a calculated field or expression.
--
KARL DEWEY
Build a little - Test a little


Honey said:
The query for this report is comprised of fields from the Horses table, the
Members table and the Pedigree Query. I created a query from just the horses
table and the members table and created the report fine. As soon as I ad 1
field from the Pedigree query, I have the same problem. Could there be a
problem with the Pedigree query? Is there any other way to pull 4
generations of ancestors other than to create a query of self joins? Very
frustrated. Thanks for any help with this and thank you for the help already
provided.

Honey



:

I do not see a problem but with a query as complex as this there is no telling.
I suggest making a copy and then start taking it apart piece by piece.
Start by removing the order by then the first field of the select as that is
the only places I find that field name.
--
KARL DEWEY
Build a little - Test a little


:

O.K. Karl,
Here it is. It is from the Query.

SELECT Horses.[Horse's Name], Horses.[Registration Number], Horses.[Date of
Birth], Horses.Sex, Horses.[Inbreeding Coefficient], Horses.Breeder,
Horses.[Achievement Level], [Pedigree Query].Sire.[Horse's Name], [Pedigree
Query].Sire.[Registration Number], [Pedigree Query].Sire.[Other Registration
#], [Pedigree Query].Sire.Title, [Pedigree Query].Dam.[Horse's Name],
[Pedigree Query].Dam.[Registration Number], [Pedigree Query].Dam.[Other
Registration #], [Pedigree Query].Dam.Title, [Pedigree Query].[S
Sire].[Horse's Name], [Pedigree Query].[S Sire].[Registration Number],
[Pedigree Query].[S Sire].[Other Registration #], [Pedigree Query].[S
Sire].Title, [Pedigree Query].[S Dam].[Horse's Name], [Pedigree Query].[S
Dam].[Registration Number], [Pedigree Query].[S Dam].[Other Registration #],
[Pedigree Query].[S Dam].Title, [Pedigree Query].[D Sire].[Horse's Name],
[Pedigree Query].[D Sire].[Registration Number], [Pedigree Query].[D
Sire].[Other Registration #], [Pedigree Query].[D Sire].Title, [Pedigree
Query].[D Dam].[Horse's Name], [Pedigree Query].[D Dam].[Registration
Number], [Pedigree Query].[D Dam].[Other Registration #], [Pedigree Query].[D
Dam].Title, [Pedigree Query].[S S Sire].[Horse's Name], [Pedigree Query].[S S
Sire].[Registration Number], [Pedigree Query].[S S Sire].[Other Registration
#], [Pedigree Query].[S S Sire].Title, [Pedigree Query].[S S Dam].[Horse's
Name], [Pedigree Query].[S S Dam].[Registration Number], [Pedigree Query].[S
S Dam].[Other Registration #], [Pedigree Query].[S S Dam].Title, [Pedigree
Query].[S D Sire].[Horse's Name], [Pedigree Query].[S D Sire].[Registration
Number], [Pedigree Query].[S D Sire].[Other Registration #], [Pedigree
Query].[S D Sire].Title, [Pedigree Query].[S D Dam].[Horse's Name], [Pedigree
Query].[S D Dam].[Registration Number], [Pedigree Query].[S D Dam].[Other
Registration #], [Pedigree Query].[S D Dam].Title, [Pedigree Query].[D S
Sire].[Horse's Name], [Pedigree Query].[D S Sire].[Registration Number],
[Pedigree Query].[D S Sire].[Other Registration #], [Pedigree Query].[D S
Sire].Title, [Pedigree Query].[D S Dam].[Horse's Name], [Pedigree Query].[D S
Dam].[Registration Number], [Pedigree Query].[D S Dam].[Other Registration
#], [Pedigree Query].[D S Dam].Title, [Pedigree Query].[D D Sire].[Horse's
Name], [Pedigree Query].[D D Sire].[Registration Number], [Pedigree Query].[D
D Sire].[Other Registration #], [Pedigree Query].[D D Sire].Title, [Pedigree
Query].[D D Dam].[Horse's Name], [Pedigree Query].[D D Dam].[Registration
Number], [Pedigree Query].[D D Dam].[Other Registration #], [Pedigree
Query].[D D Dam].Title, [Pedigree Query].[S S S Sire].[Horse's Name],
[Pedigree Query].[S S S Sire].[Registration Number], [Pedigree Query].[S S S
Sire].[Other Registration #], [Pedigree Query].[S S S Sire].Title, [Pedigree
Query].[S S S Dam].[Horse's Name], [Pedigree Query].[S S S Dam].[Registration
Number], [Pedigree Query].[S S S Dam].[Other Registration #], [Pedigree
Query].[S S S Dam].Title, [Pedigree Query].[S S D Sire].[Horse's Name],
[Pedigree Query].[S S D Sire].[Registration Number], [Pedigree Query].[S S D
Sire].[Other Registration #], [Pedigree Query].[S S D Sire].Title, [Pedigree
Query].[S S D Dam].[Horse's Name], [Pedigree Query].[S S D Dam].[Registration
Number], [Pedigree Query].[S S D Dam].[Other Registration #], [Pedigree
Query].[S S D Dam].Title, [Pedigree Query].[S D S Sire].[Horse's Name],
[Pedigree Query].[S D S Sire].[Registration Number], [Pedigree Query].[S D S
Sire].[Other Registration #], [Pedigree Query].[S D S Sire].Title, [Pedigree
Query].[S D S Dam].[Horse's Name], [Pedigree Query].[S D S Dam].[Registration
Number], [Pedigree Query].[S D S Dam].[Other Registration #], [Pedigree
Query].[S D S Dam].Title, [Pedigree Query].[S D D Sire].[Horse's Name],
[Pedigree Query].[S D D Sire].[Registration Number], [Pedigree Query].[S D D
Sire].[Other Registration #], [Pedigree Query].[S D D Sire].Title, [Pedigree
Query].[S D D Dam].[Horse's Name], [Pedigree Query].[S D D Dam].[Registration
Number], [Pedigree Query].[S D D Dam].[Other Registration #], [Pedigree
Query].[S D D Dam].Title, [Pedigree Query].[D S S Sire].[Horse's Name],
[Pedigree Query].[D S S Sire].[Registration Number], [Pedigree Query].[D S S
Sire].[Other Registration #], [Pedigree Query].[D S S Sire].Title, [Pedigree
Query].[D S S Dam].[Horse's Name], [Pedigree Query].[D S S Dam].[Registration
Number], [Pedigree Query].[D S S Dam].[Other Registration #], [Pedigree
Query].[D S S Dam].Title, [Pedigree Query].[D S D Sire].[Horse's Name],
[Pedigree Query].[D S D Sire].[Registration Number], [Pedigree Query].[D S D
Sire].[Other Registration #], [Pedigree Query].[D S D Sire].Title, [Pedigree
Query].[D S D Dam].[Horse's Name], [Pedigree Query].[D S D Dam].[Registration
Number], [Pedigree Query].[D S D Dam].[Other Registration #], [Pedigree
Query].[D S D Dam].Title, [Pedigree Query].[D D S Sire].[Horse's Name],
[Pedigree Query].[D D S Sire].[Registration Number], [Pedigree Query].[D D S
Sire].[Other Registration #], [Pedigree Query].[D D S Sire].Title, [Pedigree
Query].[D D S Dam].[Horse's Name], [Pedigree Query].[D D S Dam].[Registration
Number], [Pedigree Query].[D D S Dam].[Other Registration #], [Pedigree
Query].[D D S Dam].Title, [Pedigree Query].[D D D Sire].[Horse's Name],
[Pedigree Query].[D D D Sire].[Registration Number], [Pedigree Query].[D D D
Sire].[Other Registration #], [Pedigree Query].[D D D Sire].Title, [Pedigree
Query].[D D D Dam].[Horse's Name], [Pedigree Query].[D D D Dam].[Registration
Number], [Pedigree Query].[D D D Dam].[Other Registration #], [Pedigree
Query].[D D D Dam].Title, Horses.[Microchip #], Horses.[Tongue Tattoo],
Horses.Color, Horses.Markings, Members.[First Name], Members.[Middle
Initial], Members.[Last Name], Members.City, Members.State
FROM (Members INNER JOIN Horses ON Members.[Membership Number] =
Horses.[Owner's membership #]) INNER JOIN [Pedigree Query] ON Horses.[Horse
ID] = [Pedigree Query].[Horse ID]
WHERE (((Horses.[Registration Type])="Registered") AND
((Horses.Breed)="Friesian"))
ORDER BY Horses.[Horse's Name];

Thank You,
Honey


:

Open your report in design view, Click on menu VIEW - Properties. See what
the Record Source says. If it is a SELECT statement that is the SQL. Copy
and paste in a post.
If it has the name of a query then open that query in design view, click on
menu VIEW - SQL View, highlight all, copy, and paste in a post.
--
KARL DEWEY
Build a little - Test a little


:

The SQL from the Query? Or you will have to tell me where it is on the report.
Thanks, Honey

:

Post the FROM clause of your SQL statement. Better still post the complete
SQL.
--
KARL DEWEY
Build a little - Test a little


:

I have to track 4 generations of pedigree for horses. I created a pedigree
query using self joins by copying the Horses table and joining the sire and
dam id fields to the Horse ID field in the copied tables. I have changed the
name of the copied tables and everything seems to work just fine. I created
another query to include all of the other horses's information (Micorchip # ,
date of birth, color and markings, etc.) and the owners information uning
the horses table, the owners table and the pedigree query. When I run this
query, it seems fine. In datasheet view, all the info is filled in. I would
like to create a report from this query that I can customize to create the
Registration papers. When I run this report, I get this message: The
specified field 'Horses.[Horse's Name]' could refer to more than one table
listed in the FROM clause of your SQL statement. Please help me get around
this problem. I am using Access 2000. Thank You, Honey.
Click to expand...
Click to expand...
Click to expand...

Maybe you can format the query to help you visualize what is going on. I am not
saying it is the answer to your problem though. Unfortunately, Access will not
preserve the pretty format.

SELECT h.[Horse's Name],
h.[Registration Number],
h.[Date of Birth],
h.Sex,
h.[Inbreeding Coefficient],
h.Breeder,
h.[Achievement Level],
p.Sire.[Horse's Name],
p.Sire.[Registration Number],
p.Sire.[Other Registration #],
p.Sire.Title,
p.Dam.[Horse's Name],
p.Dam.[Registration Number],
p.Dam.[Other Registration #],
p.Dam.Title,
p.[S Sire].[Horse's Name],
p.[S Sire].[Registration Number],
p.[S Sire].[Other Registration #],
p.[S Sire].Title,
p.[S Dam].[Horse's Name],
p.[S Dam].[Registration Number],
p.[S Dam].[Other Registration #],
p.[S Dam].Title,
p.[D Sire].[Horse's Name],
p.[D Sire].[Registration Number],
p.[D Sire].[Other Registration #],
p.[D Sire].Title,
p.[D Dam].[Horse's Name],
p.[D Dam].[Registration Number],
p.[D Dam].[Other Registration #],
p.[D Dam].Title,
p.[S S Sire].[Horse's Name],
p.[S S Sire].[Registration Number],
p.[S S Sire].[Other Registration #],
p.[S S Sire].Title,
p.[S S Dam].[Horse's Name],
p.[S S Dam].[Registration Number],
p.[S S Dam].[Other Registration #],
p.[S S Dam].Title,
p.[S D Sire].[Horse's Name],
p.[S D Sire].[Registration Number],
p.[S D Sire].[Other Registration #],
p.[S D Sire].Title,
p.[S D Dam].[Horse's Name],
p.[S D Dam].[Registration Number],
p.[S D Dam].[Other Registration #],
p.[S D Dam].Title,
p.[D S Sire].[Horse's Name],
p.[D S Sire].[Registration Number],
p.[D S Sire].[Other Registration #],
p.[D S Sire].Title,
p.[D S Dam].[Horse's Name],
p.[D S Dam].[Registration Number],
p.[D S Dam].[Other Registration #],
p.[D S Dam].Title,
p.[D D Sire].[Horse's Name],
p.[D D Sire].[Registration Number],
p.[D D Sire].[Other Registration #],
p.[D D Sire].Title,
p.[D D Dam].[Horse's Name],
p.[D D Dam].[Registration Number],
p.[D D Dam].[Other Registration #],
p.[D D Dam].Title,
p.[S S S Sire].[Horse's Name],
p.[S S S Sire].[Registration Number],
p.[S S S Sire].[Other Registration #],
p.[S S S Sire].Title,
p.[S S S Dam].[Horse's Name],
p.[S S S Dam].[Registration Number],
p.[S S S Dam].[Other Registration #],
p.[S S S Dam].Title,
p.[S S D Sire].[Horse's Name],
p.[S S D Sire].[Registration Number],
p.[S S D Sire].[Other Registration #],
p.[S S D Sire].Title,
p.[S S D Dam].[Horse's Name],
p.[S S D Dam].[Registration Number],
p.[S S D Dam].[Other Registration #],
p.[S S D Dam].Title,
p.[S D S Sire].[Horse's Name],
p.[S D S Sire].[Registration Number],
p.[S D S Sire].[Other Registration #],
p.[S D S Sire].Title,
p.[S D S Dam].[Horse's Name],
p.[S D S Dam].[Registration Number],
p.[S D S Dam].[Other Registration #],
p.[S D S Dam].Title,
p.[S D D Sire].[Horse's Name],
p.[S D D Sire].[Registration Number],
p.[S D D Sire].[Other Registration #],
p.[S D D Sire].Title,
p.[S D D Dam].[Horse's Name],
p.[S D D Dam].[Registration Number],
p.[S D D Dam].[Other Registration #],
p.[S D D Dam].Title,
p.[D S S Sire].[Horse's Name],
p.[D S S Sire].[Registration Number],
p.[D S S Sire].[Other Registration #],
p.[D S S Sire].Title,
p.[D S S Dam].[Horse's Name],
p.[D S S Dam].[Registration Number],
p.[D S S Dam].[Other Registration #],
p.[D S S Dam].Title,
p.[D S D Sire].[Horse's Name],
p.[D S D Sire].[Registration Number],
p.[D S D Sire].[Other Registration #],
p.[D S D Sire].Title,
p.[D S D Dam].[Horse's Name],
p.[D S D Dam].[Registration Number],
p.[D S D Dam].[Other Registration #],
p.[D S D Dam].Title,
p.[D D S Sire].[Horse's Name],
p.[D D S Sire].[Registration Number],
p.[D D S Sire].[Other Registration #],
p.[D D S Sire].Title,
p.[D D S Dam].[Horse's Name],
p.[D D S Dam].[Registration Number],
p.[D D S Dam].[Other Registration #],
p.[D D S Dam].Title,
p.[D D D Sire].[Horse's Name],
p.[D D D Sire].[Registration Number],
p.[D D D Sire].[Other Registration #],
p.[D D D Sire].Title,
p.[D D D Dam].[Horse's Name],
p.[D D D Dam].[Registration Number],
p.[D D D Dam].[Other Registration #],
p.[D D D Dam].Title,
h.[Microchip #],
h.[Tongue Tattoo],
h.Color,
h.Markings,
m.[First Name],
m.[Middle Initial],
m.[Last Name],
m.City,
m.State
FROM (Members AS m
INNER JOIN Horses AS h
ON m.[Membership Number] = h.[Owner's membership #])
INNER JOIN [Pedigree Query] AS P
ON h.[Horse ID] = p.[Horse ID]
WHERE (((h.[Registration Type]) = "Registered")
AND ((h.Breed) = "Friesian"))
ORDER BY h.[Horse's Name];
 
H

Honey

Do you think that a newer version of access would handle this better?

Thanks, Honey


Michael Gramelspacher said:
When I open the Pedigree query in datasheet view, all the info is there -
looks perfect. Is there a more complete test? and if not - what now?

Honey


KARL DEWEY said:
Could there be a problem with the Pedigree query?
Definitely. Try running it alone.
The problem is probably in a calculated field or expression.
--
KARL DEWEY
Build a little - Test a little


:

The query for this report is comprised of fields from the Horses table, the
Members table and the Pedigree Query. I created a query from just the horses
table and the members table and created the report fine. As soon as I ad 1
field from the Pedigree query, I have the same problem. Could there be a
problem with the Pedigree query? Is there any other way to pull 4
generations of ancestors other than to create a query of self joins? Very
frustrated. Thanks for any help with this and thank you for the help already
provided.

Honey



:

I do not see a problem but with a query as complex as this there is no telling.
I suggest making a copy and then start taking it apart piece by piece.
Start by removing the order by then the first field of the select as that is
the only places I find that field name.
--
KARL DEWEY
Build a little - Test a little


:

O.K. Karl,
Here it is. It is from the Query.

SELECT Horses.[Horse's Name], Horses.[Registration Number], Horses.[Date of
Birth], Horses.Sex, Horses.[Inbreeding Coefficient], Horses.Breeder,
Horses.[Achievement Level], [Pedigree Query].Sire.[Horse's Name], [Pedigree
Query].Sire.[Registration Number], [Pedigree Query].Sire.[Other Registration
#], [Pedigree Query].Sire.Title, [Pedigree Query].Dam.[Horse's Name],
[Pedigree Query].Dam.[Registration Number], [Pedigree Query].Dam.[Other
Registration #], [Pedigree Query].Dam.Title, [Pedigree Query].[S
Sire].[Horse's Name], [Pedigree Query].[S Sire].[Registration Number],
[Pedigree Query].[S Sire].[Other Registration #], [Pedigree Query].[S
Sire].Title, [Pedigree Query].[S Dam].[Horse's Name], [Pedigree Query].[S
Dam].[Registration Number], [Pedigree Query].[S Dam].[Other Registration #],
[Pedigree Query].[S Dam].Title, [Pedigree Query].[D Sire].[Horse's Name],
[Pedigree Query].[D Sire].[Registration Number], [Pedigree Query].[D
Sire].[Other Registration #], [Pedigree Query].[D Sire].Title, [Pedigree
Query].[D Dam].[Horse's Name], [Pedigree Query].[D Dam].[Registration
Number], [Pedigree Query].[D Dam].[Other Registration #], [Pedigree Query].[D
Dam].Title, [Pedigree Query].[S S Sire].[Horse's Name], [Pedigree Query].[S S
Sire].[Registration Number], [Pedigree Query].[S S Sire].[Other Registration
#], [Pedigree Query].[S S Sire].Title, [Pedigree Query].[S S Dam].[Horse's
Name], [Pedigree Query].[S S Dam].[Registration Number], [Pedigree Query].[S
S Dam].[Other Registration #], [Pedigree Query].[S S Dam].Title, [Pedigree
Query].[S D Sire].[Horse's Name], [Pedigree Query].[S D Sire].[Registration
Number], [Pedigree Query].[S D Sire].[Other Registration #], [Pedigree
Query].[S D Sire].Title, [Pedigree Query].[S D Dam].[Horse's Name], [Pedigree
Query].[S D Dam].[Registration Number], [Pedigree Query].[S D Dam].[Other
Registration #], [Pedigree Query].[S D Dam].Title, [Pedigree Query].[D S
Sire].[Horse's Name], [Pedigree Query].[D S Sire].[Registration Number],
[Pedigree Query].[D S Sire].[Other Registration #], [Pedigree Query].[D S
Sire].Title, [Pedigree Query].[D S Dam].[Horse's Name], [Pedigree Query].[D S
Dam].[Registration Number], [Pedigree Query].[D S Dam].[Other Registration
#], [Pedigree Query].[D S Dam].Title, [Pedigree Query].[D D Sire].[Horse's
Name], [Pedigree Query].[D D Sire].[Registration Number], [Pedigree Query].[D
D Sire].[Other Registration #], [Pedigree Query].[D D Sire].Title, [Pedigree
Query].[D D Dam].[Horse's Name], [Pedigree Query].[D D Dam].[Registration
Number], [Pedigree Query].[D D Dam].[Other Registration #], [Pedigree
Query].[D D Dam].Title, [Pedigree Query].[S S S Sire].[Horse's Name],
[Pedigree Query].[S S S Sire].[Registration Number], [Pedigree Query].[S S S
Sire].[Other Registration #], [Pedigree Query].[S S S Sire].Title, [Pedigree
Query].[S S S Dam].[Horse's Name], [Pedigree Query].[S S S Dam].[Registration
Number], [Pedigree Query].[S S S Dam].[Other Registration #], [Pedigree
Query].[S S S Dam].Title, [Pedigree Query].[S S D Sire].[Horse's Name],
[Pedigree Query].[S S D Sire].[Registration Number], [Pedigree Query].[S S D
Sire].[Other Registration #], [Pedigree Query].[S S D Sire].Title, [Pedigree
Query].[S S D Dam].[Horse's Name], [Pedigree Query].[S S D Dam].[Registration
Number], [Pedigree Query].[S S D Dam].[Other Registration #], [Pedigree
Query].[S S D Dam].Title, [Pedigree Query].[S D S Sire].[Horse's Name],
[Pedigree Query].[S D S Sire].[Registration Number], [Pedigree Query].[S D S
Sire].[Other Registration #], [Pedigree Query].[S D S Sire].Title, [Pedigree
Query].[S D S Dam].[Horse's Name], [Pedigree Query].[S D S Dam].[Registration
Number], [Pedigree Query].[S D S Dam].[Other Registration #], [Pedigree
Query].[S D S Dam].Title, [Pedigree Query].[S D D Sire].[Horse's Name],
[Pedigree Query].[S D D Sire].[Registration Number], [Pedigree Query].[S D D
Sire].[Other Registration #], [Pedigree Query].[S D D Sire].Title, [Pedigree
Query].[S D D Dam].[Horse's Name], [Pedigree Query].[S D D Dam].[Registration
Number], [Pedigree Query].[S D D Dam].[Other Registration #], [Pedigree
Query].[S D D Dam].Title, [Pedigree Query].[D S S Sire].[Horse's Name],
[Pedigree Query].[D S S Sire].[Registration Number], [Pedigree Query].[D S S
Sire].[Other Registration #], [Pedigree Query].[D S S Sire].Title, [Pedigree
Query].[D S S Dam].[Horse's Name], [Pedigree Query].[D S S Dam].[Registration
Number], [Pedigree Query].[D S S Dam].[Other Registration #], [Pedigree
Query].[D S S Dam].Title, [Pedigree Query].[D S D Sire].[Horse's Name],
[Pedigree Query].[D S D Sire].[Registration Number], [Pedigree Query].[D S D
Sire].[Other Registration #], [Pedigree Query].[D S D Sire].Title, [Pedigree
Query].[D S D Dam].[Horse's Name], [Pedigree Query].[D S D Dam].[Registration
Number], [Pedigree Query].[D S D Dam].[Other Registration #], [Pedigree
Query].[D S D Dam].Title, [Pedigree Query].[D D S Sire].[Horse's Name],
[Pedigree Query].[D D S Sire].[Registration Number], [Pedigree Query].[D D S
Sire].[Other Registration #], [Pedigree Query].[D D S Sire].Title, [Pedigree
Query].[D D S Dam].[Horse's Name], [Pedigree Query].[D D S Dam].[Registration
Number], [Pedigree Query].[D D S Dam].[Other Registration #], [Pedigree
Query].[D D S Dam].Title, [Pedigree Query].[D D D Sire].[Horse's Name],
[Pedigree Query].[D D D Sire].[Registration Number], [Pedigree Query].[D D D
Sire].[Other Registration #], [Pedigree Query].[D D D Sire].Title, [Pedigree
Query].[D D D Dam].[Horse's Name], [Pedigree Query].[D D D Dam].[Registration
Number], [Pedigree Query].[D D D Dam].[Other Registration #], [Pedigree
Query].[D D D Dam].Title, Horses.[Microchip #], Horses.[Tongue Tattoo],
Horses.Color, Horses.Markings, Members.[First Name], Members.[Middle
Initial], Members.[Last Name], Members.City, Members.State
FROM (Members INNER JOIN Horses ON Members.[Membership Number] =
Horses.[Owner's membership #]) INNER JOIN [Pedigree Query] ON Horses.[Horse
ID] = [Pedigree Query].[Horse ID]
WHERE (((Horses.[Registration Type])="Registered") AND
((Horses.Breed)="Friesian"))
ORDER BY Horses.[Horse's Name];

Thank You,
Honey


:

Open your report in design view, Click on menu VIEW - Properties. See what
the Record Source says. If it is a SELECT statement that is the SQL. Copy
and paste in a post.
If it has the name of a query then open that query in design view, click on
menu VIEW - SQL View, highlight all, copy, and paste in a post.
--
KARL DEWEY
Build a little - Test a little


:

The SQL from the Query? Or you will have to tell me where it is on the report.
Thanks, Honey

:

Post the FROM clause of your SQL statement. Better still post the complete
SQL.
--
KARL DEWEY
Build a little - Test a little


:

I have to track 4 generations of pedigree for horses. I created a pedigree
query using self joins by copying the Horses table and joining the sire and
dam id fields to the Horse ID field in the copied tables. I have changed the
name of the copied tables and everything seems to work just fine. I created
another query to include all of the other horses's information (Micorchip # ,
date of birth, color and markings, etc.) and the owners information uning
the horses table, the owners table and the pedigree query. When I run this
query, it seems fine. In datasheet view, all the info is filled in. I would
like to create a report from this query that I can customize to create the
Registration papers. When I run this report, I get this message: The
specified field 'Horses.[Horse's Name]' could refer to more than one table
listed in the FROM clause of your SQL statement. Please help me get around
this problem. I am using Access 2000. Thank You, Honey.
Click to expand...
Click to expand...

Maybe you can format the query to help you visualize what is going on. I am not
saying it is the answer to your problem though. Unfortunately, Access will not
preserve the pretty format.

SELECT h.[Horse's Name],
h.[Registration Number],
h.[Date of Birth],
h.Sex,
h.[Inbreeding Coefficient],
h.Breeder,
h.[Achievement Level],
p.Sire.[Horse's Name],
p.Sire.[Registration Number],
p.Sire.[Other Registration #],
p.Sire.Title,
p.Dam.[Horse's Name],
p.Dam.[Registration Number],
p.Dam.[Other Registration #],
p.Dam.Title,
p.[S Sire].[Horse's Name],
p.[S Sire].[Registration Number],
p.[S Sire].[Other Registration #],
p.[S Sire].Title,
p.[S Dam].[Horse's Name],
p.[S Dam].[Registration Number],
p.[S Dam].[Other Registration #],
p.[S Dam].Title,
p.[D Sire].[Horse's Name],
p.[D Sire].[Registration Number],
p.[D Sire].[Other Registration #],
p.[D Sire].Title,
p.[D Dam].[Horse's Name],
p.[D Dam].[Registration Number],
p.[D Dam].[Other Registration #],
p.[D Dam].Title,
p.[S S Sire].[Horse's Name],
p.[S S Sire].[Registration Number],
p.[S S Sire].[Other Registration #],
p.[S S Sire].Title,
p.[S S Dam].[Horse's Name],
p.[S S Dam].[Registration Number],
p.[S S Dam].[Other Registration #],
p.[S S Dam].Title,
p.[S D Sire].[Horse's Name],
p.[S D Sire].[Registration Number],
p.[S D Sire].[Other Registration #],
p.[S D Sire].Title,
p.[S D Dam].[Horse's Name],
p.[S D Dam].[Registration Number],
p.[S D Dam].[Other Registration #],
p.[S D Dam].Title,
p.[D S Sire].[Horse's Name],
p.[D S Sire].[Registration Number],
p.[D S Sire].[Other Registration #],
p.[D S Sire].Title,
p.[D S Dam].[Horse's Name],
p.[D S Dam].[Registration Number],
p.[D S Dam].[Other Registration #],
p.[D S Dam].Title,
p.[D D Sire].[Horse's Name],
p.[D D Sire].[Registration Number],
p.[D D Sire].[Other Registration #],
p.[D D Sire].Title,
p.[D D Dam].[Horse's Name],
p.[D D Dam].[Registration Number],
p.[D D Dam].[Other Registration #],
p.[D D Dam].Title,
p.[S S S Sire].[Horse's Name],
p.[S S S Sire].[Registration Number],
p.[S S S Sire].[Other Registration #],
p.[S S S Sire].Title,
p.[S S S Dam].[Horse's Name],
p.[S S S Dam].[Registration Number],
p.[S S S Dam].[Other Registration #],
p.[S S S Dam].Title,
p.[S S D Sire].[Horse's Name],
p.[S S D Sire].[Registration Number],
p.[S S D Sire].[Other Registration #],
p.[S S D Sire].Title,
p.[S S D Dam].[Horse's Name],
p.[S S D Dam].[Registration Number],
p.[S S D Dam].[Other Registration #],
p.[S S D Dam].Title,
p.[S D S Sire].[Horse's Name],
p.[S D S Sire].[Registration Number],
p.[S D S Sire].[Other Registration #],
p.[S D S Sire].Title,
p.[S D S Dam].[Horse's Name],
p.[S D S Dam].[Registration Number],
p.[S D S Dam].[Other Registration #],
p.[S D S Dam].Title,
p.[S D D Sire].[Horse's Name],
p.[S D D Sire].[Registration Number],
p.[S D D Sire].[Other Registration #],
p.[S D D Sire].Title,
p.[S D D Dam].[Horse's Name],
p.[S D D Dam].[Registration Number],
p.[S D D Dam].[Other Registration #],
p.[S D D Dam].Title,
p.[D S S Sire].[Horse's Name],
p.[D S S Sire].[Registration Number],
p.[D S S Sire].[Other Registration #],
p.[D S S Sire].Title,
p.[D S S Dam].[Horse's Name],
p.[D S S Dam].[Registration Number],
p.[D S S Dam].[Other Registration #],
p.[D S S Dam].Title,
p.[D S D Sire].[Horse's Name],
p.[D S D Sire].[Registration Number],
p.[D S D Sire].[Other Registration #],
p.[D S D Sire].Title,
p.[D S D Dam].[Horse's Name],
p.[D S D Dam].[Registration Number],
p.[D S D Dam].[Other Registration #],
p.[D S D Dam].Title,
p.[D D S Sire].[Horse's Name],
p.[D D S Sire].[Registration Number],
p.[D D S Sire].[Other Registration #],
p.[D D S Sire].Title,
Click to expand...
 
M

Michael Gramelspacher

Do you think that a newer version of access would handle this better?

Thanks, Honey
Click to expand...


I would try changing the joins. (Horses INNER JOIN Members) works OK, you say.

So why not try ( Horses INNER JOIN Members) LEFT JOIN [Pedigree Query]

I gave the tables alaises, because it makes the query easier to understand.
There is just too much unnecessary cluther otherwise -- at least for me.
 

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

Similar Threads

specified field [equipment] could refer to more than one table listed in FROM clause of SQL statemen 1
same field in more than one table 2
Apparently Bogus "could refer to more than one. 9
The specified Field Could refer to more than one table... Access 2 5
Error: Specified field could refer to more than one table 8
The specified field could refer to more than one table listed in theFROM clause of your SQL statemen 5
adding criteria to LEFT JOIN - SQL 4
IIF to check values in more than one table 1

Top