Query producing 522 copies of same record

[FJB]

I wrote simple select query in a database I maintain this afternoon and
the query is returning multiple copies of the same record, 522 copies
to be exact. The query is written using three tables which are related
( I have used these relationships in other queries and they work fine).
To produce the required information, one of the fields has criteria,
specifically 12 "numbers" that it must look for in that field. If
one of those numbers is found, the record should be displayed. The
query works except for all those copies of the same record. It's
probably as simple as a setting from yes to no. Thanks for your help.

 

[Jeff Boyce]

Are your tables joined in the query?

Please post the SQL statement...

Regards

Jeff Boyce
<Access MVP>

 

[FJB]

I did not write and SQL query, I used the wizard and then entered the
criteria.

Thanks

 

[Duane Hookom]

FJB,
If you have a query, you have a SQL view that you should be able to share
with us.

 

[John Vinson]

I did not write and SQL query, I used the wizard and then entered the
criteria.

Thanks

Open the Query in design view.

From the Menu select View... SQL.

Copy and paste the SQL text to a message here.

The SQL *IS* the query; the query grid is simply a tool to let you
build SQL.

John W. Vinson[MVP]

 

[FJB]

SELECT [SAR Filings].[Relationship Name], [SAR Filings].[Reporting
Region] AS [SAR Filings_Reporting Region], [SAR Filings].[Cost Center],
[SAR Filings].[SSN/TIN], [SAR Filings].[Account Number], [SAR
Filings].[ICMS Case Number], Group_Region.[Reporting Region] AS
[Group_Region_Reporting Region], [STI Branch Listing].[BANK NAME]
FROM ([STI Branch Listing] INNER JOIN Group_Region ON [STI Branch
Listing].[STI GROUP] = Group_Region.Group) INNER JOIN [SAR Filings] ON
Group_Region.[Reporting Region] = [SAR Filings].[Reporting Region]
WHERE ((([SAR Filings].[Cost Center])="8000473")) OR ((([SAR
Filings].[Cost Center])="8000983")) OR ((([SAR Filings].[Cost
Center])="8000158")) OR ((([SAR Filings].[Cost Center])="8000476")) OR
((([SAR Filings].[Cost Center])="8000987")) OR ((([SAR Filings].[Cost
Center])="8000478")) OR ((([SAR Filings].[Cost Center])="8000472")) OR
((([SAR Filings].[Cost Center])="8000986")) OR ((([SAR Filings].[Cost
Center])="8000156")) OR ((([SAR Filings].[Cost Center])="8000475")) OR
((([SAR Filings].[Cost Center])="8000479")) OR ((([SAR Filings].[Cost
Center])="8000984"));

 

[Duane Hookom]

I expect [STI Branch Listing].[STI GROUP] or Group_Region.[Reporting Region]
might not be a primary key fields.

I would change the SQL to:
SELECT [SAR Filings].[Relationship Name],
[SAR Filings].[Reporting Region] AS
[SAR Filings_Reporting Region],
[SAR Filings].[Cost Center],
[SAR Filings].[SSN/TIN],
[SAR Filings].[Account Number],
[SAR Filings].[ICMS Case Number],
Group_Region.[Reporting Region] AS
[Group_Region_Reporting Region],
[STI Branch Listing].[BANK NAME]

FROM ([STI Branch Listing]
INNER JOIN Group_Region ON
[STI Branch Listing].[STI GROUP] = Group_Region.Group)
INNER JOIN [SAR Filings] ON
Group_Region.[Reporting Region] = [SAR Filings].[Reporting Region]

WHERE [SAR Filings].[Cost Center] IN ("8000473", "8000983",
"8000158", "8000476", "8000987", "8000478", "8000472",
"8000986", "8000156", "8000475", "8000479", "8000984");

 

[FJB]

I copied you suggestion into the SQL view of the query, ran it, and got
552 lines of the same record. (correct number is 552 not 522).

Thanks

 

[Duane Hookom]

I didn't expect my sql to fix your issue. It only provided an easier to read
sql view.

I don't think you have a firm grasp of your data and expect you are missing
a join line. Or, you expect a single value in a field where there are many.

 

[FJB]

Thanks. When I added a criteria for another field, I reduced the number
of lines to 12. I appreciate everyone's help.

 

[FJB]

Below is the layout of the database:

Main Table: SAR Filings
Other Tables: Group Region, Branch Listing, Commercial Centers

SAR Filings: Reporting Region is joined to Group Region: Region Name
Group Region: Group is joined to Branch Listing: Group
SAR Filings: Cost Center is joined to Commercial Centers: Center Code

 

[Duane Hookom]

Is this query fixed or are you still having issues?

 

[FJB]

Still having problems. The result from the query should be 1 record,
but as you know I am getting multiple copies of the same record. I
appreciate your help.

 

[Duane Hookom]

I can't help without understanding your tables, primary keys, fields,
data....