Query will not populate with data

A

Amy Schmid

I am trying to get my query to run but it is giving me an error about SQL.

"The SQL statemtent could not be executed because it contains ambiguous
outer joins. To force one of hte joins to be performed first, create a
separate query that performs the first join and then inlude that in your SQL
statement."

I have no idea what the SQL statement is and how to do what the message is
asking.

Here is the SQL View on my query:

SELECT dbo_Clients.Cltnum, dbo_Clients.Cltname,
dbo_ajs_Ag_FS_Section.FSSection, dbo_ajs_Ag_FS_Detail.FSDetailName,
dbo_ajs_Ag_FS_Data.FSYear, dbo_ajs_Ag_FS_Data.FSAmount,
dbo_ajs_Ag_NFS_Data.NFSYear, dbo_ajs_Ag_NFS_Data.[DCQtr12-31PY],
dbo_ajs_Ag_NFS_Data.[DCQtr3-31], dbo_ajs_Ag_NFS_Data.[DCQtr3-31],
dbo_ajs_Ag_NFS_Data.[DCQtr6-30], dbo_ajs_Ag_NFS_Data.[DCQtr9-30],
dbo_ajs_Ag_NFS_Data.[DCQtr12-31], dbo_ajs_Ag_NFS_Data.[MCQtr12-31PY],
dbo_ajs_Ag_NFS_Data.[MCQtr3-31], dbo_ajs_Ag_NFS_Data.[MCQtr6-30],
dbo_ajs_Ag_NFS_Data.[MCQtr9-30], dbo_ajs_Ag_NFS_Data.[MCQtr12-31],
dbo_ajs_Ag_NFS_Data.CWTMilk, dbo_ajs_Ag_NFS_Data.OwnHrs,
dbo_ajs_Ag_NFS_Data.EmpHrs, dbo_ajs_Ag_NFS_Data.MlkPr
FROM (dbo_ajs_Ag_FS_Section LEFT JOIN (dbo_ajs_Ag_FS_Data RIGHT JOIN
dbo_ajs_Ag_FS_Detail ON dbo_ajs_Ag_FS_Data.FSDetailName =
dbo_ajs_Ag_FS_Detail.FSDetailName) ON dbo_ajs_Ag_FS_Section.FSSection =
dbo_ajs_Ag_FS_Detail.FSSection) LEFT JOIN ((dbo_ajs_Ag_NFS_MilkPrice LEFT
JOIN dbo_ajs_Ag_NFS_Data ON dbo_ajs_Ag_NFS_MilkPrice.MilkPriceID =
dbo_ajs_Ag_NFS_Data.MlkPr) LEFT JOIN dbo_Clients ON
dbo_ajs_Ag_NFS_Data.Cltnum = dbo_Clients.Cltnum) ON dbo_ajs_Ag_FS_Data.Cltnum
= dbo_ajs_Ag_NFS_Data.Cltnum;

I am using Access 2000.

Thanks in advance!
 
J

Jeff Boyce

Amy

Open the query in design view.

Look at the lines connecting your tables in the upper window. I'm guessing
one (or more) of them use an arrowhead, but another one (or more) that are
"downstream" do not use that arrowhead.

You can follow the recommendations of the error message and create one query
that does the "first join" that needs to get done, then create a second
query based on the first one.

Or you can change the join lines in the query design view to all point
"downstream".

It would help if you'd explain a bit more about your data, your tables and
the relationships...

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
A

Amy Schmid

Hi Jeff,

I checked the arrows. They are all downstream.

I have 6 tables, 5 are access tables and one is a SQL table.

SQL table is Clients (connects to our Practice Management program)
Access tables are as follows:
NFS_Data
NFS_MilkPrice (connected from NFS_MilkPrice table to NFS_Data table via
milkprice line)
FS_Section (connected from FS_Section table to FS_Data table via FSSection
line)
FS_Detail (conncted from FS_Detail table to FS_Data table via FSDetailName
line)
FS_Data

I need to connect to the Client table via the NFS_Data table and the FS_Data
table via the client number. My arrows go from NFS_Data to Client and then
from FS_Data to Client all via the client number.

I hope that helps expain things better. I really appreciate your help.
There are three of us here who have work with our Access Database and none of
us created our database just maintained it. We are struggling in creating
this new portion of our database.

Jeff Boyce said:
Amy

Open the query in design view.

Look at the lines connecting your tables in the upper window. I'm guessing
one (or more) of them use an arrowhead, but another one (or more) that are
"downstream" do not use that arrowhead.

You can follow the recommendations of the error message and create one query
that does the "first join" that needs to get done, then create a second
query based on the first one.

Or you can change the join lines in the query design view to all point
"downstream".

It would help if you'd explain a bit more about your data, your tables and
the relationships...

Regards

Jeff Boyce
Microsoft Office/Access MVP

Amy Schmid said:
I am trying to get my query to run but it is giving me an error about SQL.

"The SQL statemtent could not be executed because it contains ambiguous
outer joins. To force one of hte joins to be performed first, create a
separate query that performs the first join and then inlude that in your
SQL
statement."

I have no idea what the SQL statement is and how to do what the message is
asking.

Here is the SQL View on my query:

SELECT dbo_Clients.Cltnum, dbo_Clients.Cltname,
dbo_ajs_Ag_FS_Section.FSSection, dbo_ajs_Ag_FS_Detail.FSDetailName,
dbo_ajs_Ag_FS_Data.FSYear, dbo_ajs_Ag_FS_Data.FSAmount,
dbo_ajs_Ag_NFS_Data.NFSYear, dbo_ajs_Ag_NFS_Data.[DCQtr12-31PY],
dbo_ajs_Ag_NFS_Data.[DCQtr3-31], dbo_ajs_Ag_NFS_Data.[DCQtr3-31],
dbo_ajs_Ag_NFS_Data.[DCQtr6-30], dbo_ajs_Ag_NFS_Data.[DCQtr9-30],
dbo_ajs_Ag_NFS_Data.[DCQtr12-31], dbo_ajs_Ag_NFS_Data.[MCQtr12-31PY],
dbo_ajs_Ag_NFS_Data.[MCQtr3-31], dbo_ajs_Ag_NFS_Data.[MCQtr6-30],
dbo_ajs_Ag_NFS_Data.[MCQtr9-30], dbo_ajs_Ag_NFS_Data.[MCQtr12-31],
dbo_ajs_Ag_NFS_Data.CWTMilk, dbo_ajs_Ag_NFS_Data.OwnHrs,
dbo_ajs_Ag_NFS_Data.EmpHrs, dbo_ajs_Ag_NFS_Data.MlkPr
FROM (dbo_ajs_Ag_FS_Section LEFT JOIN (dbo_ajs_Ag_FS_Data RIGHT JOIN
dbo_ajs_Ag_FS_Detail ON dbo_ajs_Ag_FS_Data.FSDetailName =
dbo_ajs_Ag_FS_Detail.FSDetailName) ON dbo_ajs_Ag_FS_Section.FSSection =
dbo_ajs_Ag_FS_Detail.FSSection) LEFT JOIN ((dbo_ajs_Ag_NFS_MilkPrice LEFT
JOIN dbo_ajs_Ag_NFS_Data ON dbo_ajs_Ag_NFS_MilkPrice.MilkPriceID =
dbo_ajs_Ag_NFS_Data.MlkPr) LEFT JOIN dbo_Clients ON
dbo_ajs_Ag_NFS_Data.Cltnum = dbo_Clients.Cltnum) ON
dbo_ajs_Ag_FS_Data.Cltnum
= dbo_ajs_Ag_NFS_Data.Cltnum;

I am using Access 2000.

Thanks in advance!
Click to expand...
Click to expand...
 
A

Amy Schmid

I created two queries. One for the NFS tables and then another for the FS
tables. The queries both ran and populated as expected.

I then created a query that combined the two mentioned above. My combo
query does not populate properly. It only populated the FSSection and the
other fields are blank.

Thanks,
Amy

Amy Schmid said:
Hi Jeff,

I checked the arrows. They are all downstream.

I have 6 tables, 5 are access tables and one is a SQL table.

SQL table is Clients (connects to our Practice Management program)
Access tables are as follows:
NFS_Data
NFS_MilkPrice (connected from NFS_MilkPrice table to NFS_Data table via
milkprice line)
FS_Section (connected from FS_Section table to FS_Data table via FSSection
line)
FS_Detail (conncted from FS_Detail table to FS_Data table via FSDetailName
line)
FS_Data

I need to connect to the Client table via the NFS_Data table and the FS_Data
table via the client number. My arrows go from NFS_Data to Client and then
from FS_Data to Client all via the client number.

I hope that helps expain things better. I really appreciate your help.
There are three of us here who have work with our Access Database and none of
us created our database just maintained it. We are struggling in creating
this new portion of our database.

Jeff Boyce said:
Amy

Open the query in design view.

Look at the lines connecting your tables in the upper window. I'm guessing
one (or more) of them use an arrowhead, but another one (or more) that are
"downstream" do not use that arrowhead.

You can follow the recommendations of the error message and create one query
that does the "first join" that needs to get done, then create a second
query based on the first one.

Or you can change the join lines in the query design view to all point
"downstream".

It would help if you'd explain a bit more about your data, your tables and
the relationships...

Regards

Jeff Boyce
Microsoft Office/Access MVP

Amy Schmid said:
I am trying to get my query to run but it is giving me an error about SQL.

"The SQL statemtent could not be executed because it contains ambiguous
outer joins. To force one of hte joins to be performed first, create a
separate query that performs the first join and then inlude that in your
SQL
statement."

I have no idea what the SQL statement is and how to do what the message is
asking.

Here is the SQL View on my query:

SELECT dbo_Clients.Cltnum, dbo_Clients.Cltname,
dbo_ajs_Ag_FS_Section.FSSection, dbo_ajs_Ag_FS_Detail.FSDetailName,
dbo_ajs_Ag_FS_Data.FSYear, dbo_ajs_Ag_FS_Data.FSAmount,
dbo_ajs_Ag_NFS_Data.NFSYear, dbo_ajs_Ag_NFS_Data.[DCQtr12-31PY],
dbo_ajs_Ag_NFS_Data.[DCQtr3-31], dbo_ajs_Ag_NFS_Data.[DCQtr3-31],
dbo_ajs_Ag_NFS_Data.[DCQtr6-30], dbo_ajs_Ag_NFS_Data.[DCQtr9-30],
dbo_ajs_Ag_NFS_Data.[DCQtr12-31], dbo_ajs_Ag_NFS_Data.[MCQtr12-31PY],
dbo_ajs_Ag_NFS_Data.[MCQtr3-31], dbo_ajs_Ag_NFS_Data.[MCQtr6-30],
dbo_ajs_Ag_NFS_Data.[MCQtr9-30], dbo_ajs_Ag_NFS_Data.[MCQtr12-31],
dbo_ajs_Ag_NFS_Data.CWTMilk, dbo_ajs_Ag_NFS_Data.OwnHrs,
dbo_ajs_Ag_NFS_Data.EmpHrs, dbo_ajs_Ag_NFS_Data.MlkPr
FROM (dbo_ajs_Ag_FS_Section LEFT JOIN (dbo_ajs_Ag_FS_Data RIGHT JOIN
dbo_ajs_Ag_FS_Detail ON dbo_ajs_Ag_FS_Data.FSDetailName =
dbo_ajs_Ag_FS_Detail.FSDetailName) ON dbo_ajs_Ag_FS_Section.FSSection =
dbo_ajs_Ag_FS_Detail.FSSection) LEFT JOIN ((dbo_ajs_Ag_NFS_MilkPrice LEFT
JOIN dbo_ajs_Ag_NFS_Data ON dbo_ajs_Ag_NFS_MilkPrice.MilkPriceID =
dbo_ajs_Ag_NFS_Data.MlkPr) LEFT JOIN dbo_Clients ON
dbo_ajs_Ag_NFS_Data.Cltnum = dbo_Clients.Cltnum) ON
dbo_ajs_Ag_FS_Data.Cltnum
= dbo_ajs_Ag_NFS_Data.Cltnum;

I am using Access 2000.

Thanks in advance!
Click to expand...
Click to expand...
Click to expand...
 
J

Jeff Boyce

Consider posting the SQL statements of your three queries...

Regards

Jeff Boyce
Microsoft Office/Access MVP

Amy Schmid said:
I created two queries. One for the NFS tables and then another for the FS
tables. The queries both ran and populated as expected.

I then created a query that combined the two mentioned above. My combo
query does not populate properly. It only populated the FSSection and the
other fields are blank.

Thanks,
Amy

Amy Schmid said:
Hi Jeff,

I checked the arrows. They are all downstream.

I have 6 tables, 5 are access tables and one is a SQL table.

SQL table is Clients (connects to our Practice Management program)
Access tables are as follows:
NFS_Data
NFS_MilkPrice (connected from NFS_MilkPrice table to NFS_Data table via
milkprice line)
FS_Section (connected from FS_Section table to FS_Data table via
FSSection
line)
FS_Detail (conncted from FS_Detail table to FS_Data table via
FSDetailName
line)
FS_Data

I need to connect to the Client table via the NFS_Data table and the
FS_Data
table via the client number. My arrows go from NFS_Data to Client and
then
from FS_Data to Client all via the client number.

I hope that helps expain things better. I really appreciate your help.
There are three of us here who have work with our Access Database and
none of
us created our database just maintained it. We are struggling in
creating
this new portion of our database.

Jeff Boyce said:
Amy

Open the query in design view.

Look at the lines connecting your tables in the upper window. I'm
guessing
one (or more) of them use an arrowhead, but another one (or more) that
are
"downstream" do not use that arrowhead.

You can follow the recommendations of the error message and create one
query
that does the "first join" that needs to get done, then create a second
query based on the first one.

Or you can change the join lines in the query design view to all point
"downstream".

It would help if you'd explain a bit more about your data, your tables
and
the relationships...

Regards

Jeff Boyce
Microsoft Office/Access MVP

I am trying to get my query to run but it is giving me an error about
SQL.

"The SQL statemtent could not be executed because it contains
ambiguous
outer joins. To force one of hte joins to be performed first, create
a
separate query that performs the first join and then inlude that in
your
SQL
statement."

I have no idea what the SQL statement is and how to do what the
message is
asking.

Here is the SQL View on my query:

SELECT dbo_Clients.Cltnum, dbo_Clients.Cltname,
dbo_ajs_Ag_FS_Section.FSSection, dbo_ajs_Ag_FS_Detail.FSDetailName,
dbo_ajs_Ag_FS_Data.FSYear, dbo_ajs_Ag_FS_Data.FSAmount,
dbo_ajs_Ag_NFS_Data.NFSYear, dbo_ajs_Ag_NFS_Data.[DCQtr12-31PY],
dbo_ajs_Ag_NFS_Data.[DCQtr3-31], dbo_ajs_Ag_NFS_Data.[DCQtr3-31],
dbo_ajs_Ag_NFS_Data.[DCQtr6-30], dbo_ajs_Ag_NFS_Data.[DCQtr9-30],
dbo_ajs_Ag_NFS_Data.[DCQtr12-31], dbo_ajs_Ag_NFS_Data.[MCQtr12-31PY],
dbo_ajs_Ag_NFS_Data.[MCQtr3-31], dbo_ajs_Ag_NFS_Data.[MCQtr6-30],
dbo_ajs_Ag_NFS_Data.[MCQtr9-30], dbo_ajs_Ag_NFS_Data.[MCQtr12-31],
dbo_ajs_Ag_NFS_Data.CWTMilk, dbo_ajs_Ag_NFS_Data.OwnHrs,
dbo_ajs_Ag_NFS_Data.EmpHrs, dbo_ajs_Ag_NFS_Data.MlkPr
FROM (dbo_ajs_Ag_FS_Section LEFT JOIN (dbo_ajs_Ag_FS_Data RIGHT JOIN
dbo_ajs_Ag_FS_Detail ON dbo_ajs_Ag_FS_Data.FSDetailName =
dbo_ajs_Ag_FS_Detail.FSDetailName) ON dbo_ajs_Ag_FS_Section.FSSection
=
dbo_ajs_Ag_FS_Detail.FSSection) LEFT JOIN ((dbo_ajs_Ag_NFS_MilkPrice
LEFT
JOIN dbo_ajs_Ag_NFS_Data ON dbo_ajs_Ag_NFS_MilkPrice.MilkPriceID =
dbo_ajs_Ag_NFS_Data.MlkPr) LEFT JOIN dbo_Clients ON
dbo_ajs_Ag_NFS_Data.Cltnum = dbo_Clients.Cltnum) ON
dbo_ajs_Ag_FS_Data.Cltnum
= dbo_ajs_Ag_NFS_Data.Cltnum;

I am using Access 2000.

Thanks in advance!
Click to expand...
Click to expand...
Click to expand...
 

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

Data Entry in Form 1
Query that will not run 1
Join Query problem for Stock Control 1

Top