Parameter trouble....

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,
I created a query to search for documents based on a parameter which asks
for the office the document is related to. Now I have multiple documents
related to the same office, yet when I run the query and enter in that
specific office only one document is returned by the query? What gives? I
can't figure it out. Thanks for the help.

-justin
 
Hi,
I created a query to search for documents based on a parameter which asks
for the office the document is related to. Now I have multiple documents
related to the same office, yet when I run the query and enter in that
specific office only one document is returned by the query? What gives? I
can't figure it out. Thanks for the help.

-justin
Click to expand...

Please post the SQL view of the query. This doesn't sound right!

John W. Vinson[MVP]
 
It's acting weird. There must be something I don't see, because now it will
return almost all the documents. I have four documents all related to the
same office, but if I search for that office only three documents will be
returned.

Anyone have any ideas? thanks.

-justin
 
It's acting weird. There must be something I don't see, because now it will
return almost all the documents. I have four documents all related to the
same office, but if I search for that office only three documents will be
returned.

Anyone have any ideas? thanks.

-justin
Click to expand...

"Doctor, I don't feel good. What should I take?"

There is something wrong with your query. It's impossible for anyone
to tell WHAT is wrong with your query, since we can't see it.

Please open your query - as requested - in SQL view and post the SQL
text here.

John W. Vinson[MVP]
 
John Vinson said:
Please open your query - as requested - in SQL view and post the SQL
text here.

John W. Vinson[MVP]
Click to expand...

my query is SQL view:

SELECT tblContractInfo.DocumentID, tblContractInfo.DocumentName,
tblContractInfo.DocumentType, tblContractInfo.CSFSProjectContact,
tblContractInfo.DocumentActive, tblContractDates.RecievedDate,
tblContractDates.RouteDate, tblContractDates.RouteCompletionDate,
tblComments.comments
FROM (tblContractInfo INNER JOIN tblContractDates ON
tblContractInfo.DocumentID = tblContractDates.DocumentID) INNER JOIN
tblComments ON tblContractInfo.DocumentID = tblComments.ID
WHERE (((tblContractInfo.CSFSOffice)=[Enter the office handling the
document]));
 
John Vinson said:
Please open your query - as requested - in SQL view and post the SQL
text here.

John W. Vinson[MVP]
Click to expand...

my query is SQL view:

SELECT tblContractInfo.DocumentID, tblContractInfo.DocumentName,
tblContractInfo.DocumentType, tblContractInfo.CSFSProjectContact,
tblContractInfo.DocumentActive, tblContractDates.RecievedDate,
tblContractDates.RouteDate, tblContractDates.RouteCompletionDate,
tblComments.comments
FROM (tblContractInfo INNER JOIN tblContractDates ON
tblContractInfo.DocumentID = tblContractDates.DocumentID) INNER JOIN
tblComments ON tblContractInfo.DocumentID = tblComments.ID
WHERE (((tblContractInfo.CSFSOffice)=[Enter the office handling the
document]));
Click to expand...

Looks OK to me. What is the datatype of CSFSOffice - text? are you
ABSOLUTELY certain that all the records you expect to find have
exactly the same spelling? Does every record in tblContractInfo have
matching records in tblContractDates and in tblComments? (If there is
no matching record in both of these tables, then you will not see the
ContractInfo).

To solve the latter problem change both INNER JOIN to LEFT JOIN.

John W. Vinson[MVP]
 
It was the latter. Changed to LEFT JOIN, and things are looking good. Thanks
for the help.

-justin

John Vinson said:
John Vinson said:
Please open your query - as requested - in SQL view and post the SQL
text here.

John W. Vinson[MVP]
Click to expand...

my query is SQL view:

SELECT tblContractInfo.DocumentID, tblContractInfo.DocumentName,
tblContractInfo.DocumentType, tblContractInfo.CSFSProjectContact,
tblContractInfo.DocumentActive, tblContractDates.RecievedDate,
tblContractDates.RouteDate, tblContractDates.RouteCompletionDate,
tblComments.comments
FROM (tblContractInfo INNER JOIN tblContractDates ON
tblContractInfo.DocumentID = tblContractDates.DocumentID) INNER JOIN
tblComments ON tblContractInfo.DocumentID = tblComments.ID
WHERE (((tblContractInfo.CSFSOffice)=[Enter the office handling the
document]));
Click to expand...

Looks OK to me. What is the datatype of CSFSOffice - text? are you
ABSOLUTELY certain that all the records you expect to find have
exactly the same spelling? Does every record in tblContractInfo have
matching records in tblContractDates and in tblComments? (If there is
no matching record in both of these tables, then you will not see the
ContractInfo).

To solve the latter problem change both INNER JOIN to LEFT JOIN.

John W. Vinson[MVP]
Click to expand...
 
Ok, so now that I fixed that problem, I've run into another one. Arg!

Anyways,
If I have more than one comment for a document, and then I try to run this
query, or any of my other queries for that matter, I will get the same
document showing up multiple times. For example, if I have 2 comments about a
document, that document will show up twice when I run the query.

This problem is really annoying because it is showing up in all my queries
and I can't correct the problem.

Maybe you, or anyone else who reads this, have some insight into what might
be wrong and what I can try to get things running the way I'd like them to.
Thanks again.

-justin
 
Ok, so now that I fixed that problem, I've run into another one. Arg!

Anyways,
If I have more than one comment for a document, and then I try to run this
query, or any of my other queries for that matter, I will get the same
document showing up multiple times. For example, if I have 2 comments about a
document, that document will show up twice when I run the query.
Click to expand...

Exactly; that's precisely and exactly how a relational query is
designed to work. It repeats the data from the "one" side table as
many times as there are "many" records.
This problem is really annoying because it is showing up in all my queries
and I can't correct the problem.

Maybe you, or anyone else who reads this, have some insight into what might
be wrong and what I can try to get things running the way I'd like them to.
Thanks again.
Click to expand...

I SUSPECT - though my telepathy has been known to be faulty - that you
would like to *see on the screen* (or on paper) the information for a
Document, with a series of lines or paragraphs showing the comments on
that document.

If that's close - don't use a Query. Query datasheets are pretty lousy
as display tools anyway.

Instead, use a Form based on the Documents table, with a Subform based
on the Comments table; or, for printing, use a Report based on your
outer join query, with the Sorting and Grouping set so that the
document information is displayed in the document header, and the
comments in the detail section for the "documents" grouping.

John W. Vinson[MVP]
 
Back
Top