Query Corrupting Results???

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

Guest

Good evening,

I have a query thatt combines data from a total of 8 tables.

I then created a secondary query that calls up the results of the first and
then applies criteria to filter exactly what the user needs. The problem is
that for one specific column, the results returned by the 2nd query is simply
two squares" and yet in the first query the proper text appears???

The only factor that I have managed to identify as being particular with
this data is the fact that this field is a memo field in the originating
table. Is this a problem?

Any help would be appreciated!

Thank you,

Daniel
 
Good evening,

I have a query thatt combines data from a total of 8 tables.

I then created a secondary query that calls up the results of the first and
then applies criteria to filter exactly what the user needs. The problem is
that for one specific column, the results returned by the 2nd query is simply
two squares" and yet in the first query the proper text appears???

The only factor that I have managed to identify as being particular with
this data is the fact that this field is a memo field in the originating
table. Is this a problem?
Click to expand...

Please post the SQL of at least the second query, preferably both
queries (and indicate which is the memo).

John W. Vinson[MVP]
 
There is a known problem that sounds like this.

I am not aware of a knowledgebase article covering this issue, but the steps
below will reproduce it for you. The important thing is that there is no
index on the MyNumber field in the table in step 1.

1. Create a new table, with 2 fields:
MyNumber Number (Long Integer)
MyMemo Memo
Save as Table1, answering NO to the question about primary key.

2. Switch to Datasheet view.
Enter 3 records:
1 one
2 two
3 three
Close.

3. Create another table, with one field:
MyNumber Number (Long Integer)
It does not matter if this is primary key or not.
Save as Table2. (It does not matter if it is primary key or not.)
Close.

4. Enter matching records:
1
2
3
Close.

5. Create a new query.
Switch to SQL View, and paste in this statement:
SELECT Table1.MyNum, Table1.MyMemo
FROM Table1 INNER JOIN Table2 ON Table1.MyNum = Table2.MyNum
GROUP BY Table1.MyNum, Table1.MyMemo;

6. Switch the query to Datasheet view. YOu see nonsense characters in the
second field of the query.
 
John,

here is the sql statements per your request. Also, I did a test and by
converting the field in question to text instead of memo ended up resolving
the problem. However, this is not an acceptable solution, simply
troubleshooting.


Query 1
***
SELECT [Stress Reports - General Info Tbl].[Stress Report Number], [Stress
Reports - Issue Info Tbl].[Stress Report Issue], [Stress Reports - General
Info Tbl].[Stress Report Description], [Work Package Tbl].[Work Package],
[Stress Reports - General Info Tbl].[Stress Report Project], [Str Rpts - Stat
Choices Tbl].[Stress Report Status], [Stress Reports - Issue Info
Tbl].[Stress Report Comment], Max([AiF Stress Need Dates Tbl].AIFNeedDt) AS
[AIF Need Date], Max([CDI Stress Commit Dates Tbl].CDIStrComDt) AS [CDI
Commit Date], [Str Rpts - Issue Dwg Info Tbl].DwgIssueID, [Drawing History
Tbl].[Drawing Number], [Drawing History Tbl].[Drawing Issue]
FROM (((([Work Package Tbl] RIGHT JOIN [Stress Reports - General Info Tbl]
ON [Work Package Tbl].Id = [Stress Reports - General Info Tbl].[Stress Report
Work Package]) LEFT JOIN ([Str Rpts - Stat Choices Tbl] RIGHT JOIN [Stress
Reports - Issue Info Tbl] ON [Str Rpts - Stat Choices Tbl].[Stress Report
Status Id] = [Stress Reports - Issue Info Tbl].[Stress Report Status]) ON
[Stress Reports - General Info Tbl].[Stress Report ID] = [Stress Reports -
Issue Info Tbl].[Stress Report AutoNumber]) LEFT JOIN [AiF Stress Need Dates
Tbl] ON [Stress Reports - Issue Info Tbl].[Stress Report Issue AutoNumber] =
[AiF Stress Need Dates Tbl].StrRptIssueID) LEFT JOIN [CDI Stress Commit
Dates Tbl] ON [Stress Reports - Issue Info Tbl].[Stress Report Issue
AutoNumber] = [CDI Stress Commit Dates Tbl].StrRptIssueID) LEFT JOIN
([Drawing History Tbl] RIGHT JOIN [Str Rpts - Issue Dwg Info Tbl] ON [Drawing
History Tbl].Id = [Str Rpts - Issue Dwg Info Tbl].DwgIssueID) ON [Stress
Reports - Issue Info Tbl].[Stress Report Issue AutoNumber] = [Str Rpts -
Issue Dwg Info Tbl].StrRptIssueID
GROUP BY [Stress Reports - General Info Tbl].[Stress Report Number], [Stress
Reports - Issue Info Tbl].[Stress Report Issue], [Stress Reports - General
Info Tbl].[Stress Report Description], [Work Package Tbl].[Work Package],
[Stress Reports - General Info Tbl].[Stress Report Project], [Str Rpts - Stat
Choices Tbl].[Stress Report Status], [Stress Reports - Issue Info
Tbl].[Stress Report Comment], [Str Rpts - Issue Dwg Info Tbl].DwgIssueID,
[Drawing History Tbl].[Drawing Number], [Drawing History Tbl].[Drawing Issue]
HAVING ((([Str Rpts - Stat Choices Tbl].[Stress Report Status])<>"Approved"))
ORDER BY [Stress Reports - General Info Tbl].[Stress Report Number], [Stress
Reports - Issue Info Tbl].[Stress Report Issue] DESC;

***

Query 2
***
SELECT [DRM Stress Report Qry].[Stress Report Number], [DRM Stress Report
Qry].[Stress Report Issue], [DRM Stress Report Qry].[Stress Report
Description], [DRM Stress Report Qry].[Work Package], [DRM Stress Report
Qry].[Stress Report Project], [DRM Stress Report Qry].[Stress Report Status],
[DRM Stress Report Qry].[Stress Report Comment], [DRM Stress Report Qry].[AIF
Need Date], [DRM Stress Report Qry].[CDI Commit Date], [DRM Stress Report
Qry].DwgIssueID, [DRM Stress Report Qry].[Drawing Number], [DRM Stress Report
Qry].[Drawing Issue]
FROM [DRM Stress Report Qry]
WHERE ((([DRM Stress Report Qry].[Work Package]) In ("Value 1","Value2")));
***


Daniel
 
Allen,

Thank you for the heads up and this does sound exactly like my case! If
ever you hear of a solution please send it my way!!!

Thank you for the help,

Daniel
 
I am not aware of a knowledgebase article covering this issue, but the steps
below will reproduce it for you.
Click to expand...

Thanks for posting this Allen - I was not aware of this issue!!!

John W. Vinson[MVP]
 
Any chance of adding a primary key field to the table, or at least adding an
index to the field that is used to join to the other table?
 
Back
Top