Re: Query will not populate with data

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

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

Regards

Jeff Boyce
Microsoft Office/Access MVP

"Amy Schmid" <AmySchmid@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:E607D957-1275-48E0-B9ED-D9CE337ABDEB@xxxxxxxxxxxxxxxx
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" wrote:

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" wrote:

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" <AmySchmid@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:B3DFF077-0269-4898-B924-148959D6B4F5@xxxxxxxxxxxxxxxx
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!





.

Relevant Pages

  • Re: AARGGH!! How to store data - HEEELLLPP!
    ... There are ways to populate the Issue table automatically when you add a new mag. ... If you wish, I can explain how to create such an append query: let us know if ALL your products will only ever be monthlies, of if you anticipate the could be weeklies, quarterlies, etc. ... Client table, ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Query will not populate with data
    ... I then created a query that combined the two mentioned above. ... query does not populate properly. ... 5 are access tables and one is a SQL table. ... I need to connect to the Client table via the NFS_Data table and the FS_Data ...
    (microsoft.public.access.queries)
  • Re: combo box, drop down - dynamic populate
    ... Am I right that you use the client ID in the query string strquery4 to get the ... the document and pass it into the userform to build that query string? ... I know how to sql the databases but unsure of how to populate the drop-down ...
    (microsoft.public.word.vba.general)
  • Re: Merge problem with Access DB
    ... Once I ask the query to retrieve a particular ... data, the query will retrieve it, but Word will not populate the data. ... not want the client to have to use the "Mail Merge Recipients" to select a ...
    (microsoft.public.word.mailmerge.fields)
  • Re: SELECT * or list fields?
    ... SELECT * is used in a query. ... As reagards maintenance, adding columns to the table is not so bad but ... As regards SQL statements ('query' is often used in the ...
    (microsoft.public.access.queries)