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

  • Thread starter Thread starter Honey
  • Start date Start date
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.
 
Post the FROM clause of your SQL statement. Better still post the complete
SQL.
 
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...
 
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...
 
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...
 
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...
 
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...
 
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...
 
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...
 
Send me the file and I will fix it for you for free. Or call me on 250 885 2925 Seattle time.
 
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];
 
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...
 
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.
 
You must log in or register to reply here.

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

Combo Box data error 2
Relationship in a Horse Progeny Table 3
This is not a question about C# but a simple question about SQL query 6
Auto Fill & Combined Data 0
specified field [equipment] could refer to more than one table listed in FROM clause of SQL statemen 1
After Update Event Procedure will not work 7
Extracting data from one table into a temp table 3
finding duplicated id in a query 3

Back
Top