One Field Returns Wrong Information

[ridgerunner]

I have a query using multiple tables and multiple primay keys that is
returning the wrong information for one field that is not part of a multiple
primary key. I have set relationships between the tables and joins are in
place in the query. Can someone please advise what might be causing the
problem?

 

[John W. Vinson]

I have a query using multiple tables and multiple primay keys that is
returning the wrong information for one field that is not part of a multiple
primary key. I have set relationships between the tables and joins are in
place in the query. Can someone please advise what might be causing the
problem?

Yes; some error in the query.

For help finding the error, please post the SQL view of the query and an
example of the error (the table field involved, its datatype and value in the
table, and the value returned by the query).

 

[ridgerunner]

Thank you John. Below is a copy of the SQL view of of the query. "Store
Numbers" is a table containing the store numbers and their 'names' which
refer to their location. The "DM Checklist Table" contains only the store
number in each record. The store number is correct in the query but the
store name is not. The store name field data type is text. The data that is
returned for the store name, is the name in the next record instead of the
name that should be pulled.

SELECT [DM Checklist Table].[Store Number], [Store Numbers].[Store Name],
[DM Checklist Table].Date, [DM Checklist Table].[Point Value and Question],
[DM Checklist Table].Score, [DM Category Table].[Control Sort], [DM Checklist
Table].[DM Category], [Sales vs Goals YTD].[Store Sales vs Goal YTD:], [Sales
vs Goals YTD].[Textile Sales vs Goal YTD:], [Sales vs Goals YTD].[Wares Sales
vs Goal YTD:], [DM Category Comments Table].[Category Comments]
FROM ((([DM Checklist Table] LEFT JOIN [Sales vs Goals YTD] ON ([DM
Checklist Table].[DM Category] = [Sales vs Goals YTD].[DM Category]) AND ([DM
Checklist Table].Date = [Sales vs Goals YTD].Date) AND ([DM Checklist
Table].[Store Number] = [Sales vs Goals YTD].[Store Number])) LEFT JOIN
[Store Numbers] ON [DM Checklist Table].[Store Number] = [Store
Numbers].[Store Number]) LEFT JOIN [DM Category Comments Table] ON ([DM
Checklist Table].[Store Number] = [DM Category Comments Table].[Store
Number]) AND ([DM Checklist Table].Date = [DM Category Comments Table].Date)
AND ([DM Checklist Table].[DM Category] = [DM Category Comments Table].[DM
Category])) LEFT JOIN [DM Category Table] ON [DM Checklist Table].[DM
Category] = [DM Category Table].[DM Category]
ORDER BY [DM Checklist Table].[Store Number], [DM Checklist Table].Date, [DM
Category Table].[Control Sort];

 

[John W. Vinson]

Thank you John. Below is a copy of the SQL view of of the query. "Store
Numbers" is a table containing the store numbers and their 'names' which
refer to their location. The "DM Checklist Table" contains only the store
number in each record. The store number is correct in the query but the
store name is not. The store name field data type is text. The data that is
returned for the store name, is the name in the next record instead of the
name that should be pulled.

I wonder if you might have a corrupt index! Try Tools... Database Utilities...
Compact and Repair; if that doesn't help, break the relationship between DM
Checklist Table and Stores; remove any indexes on the Store number fields in
both tables; compact again; and recreate the relationship.

 

[ridgerunner]

Thank you John. I found the problem. The 'Store Number' field in the DM
Checklist Table was a "lookup field" into the Store Numbers Table and there
was an error in the Select statement for the field. After I found and fixed
that, the correct store name is returned.