Duplicate showing in query when there shouldn't be

[Jennifer]

Hi,
So new to this I could pull my hair out. So go easy on me. Thank you for any
help you can offer.
I have 1 query that is pulling from 3 tables. In those tables there aren't
any duplicate entry's yet in the query i get duplicates for some entries. I
can find no correlation as to why it could be choosing these as duplicates.
Any direction?

 

[Steve Sanford]

Hi Jennifer,

What you have asked is akin to saying: "My car is making a funny noise, but
it shouldn't". Details, details, details..

There might be a couple of MVPs that are psychic, but the majority of us
(non MVPs)are not. It would be helpful (to start off with) to post the SQL of
your query since we cannot see your database.

HTH

 

[John Spencer]

As a guess there are multiple records in one table that match up to records in
another table.

Simple example
TableOne has only two records with Values in fieldOne of 1 and 2
TableTwo has four records with values in field2 of 1,1,1 and 2
If you run a query with TableOne joined to TableTwo on FieldOne To Field2, you
will get four records returned
3 records for TableOne.FieldOne where the value of field one = 1
1 record for TableOne.FieldOne where the value of FieldOne =2


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[KARL DEWEY]

You have what is known as a Cartesian effect. This happens when your tables
are not properly joined. The quanity of records in one is multipled by the
number of records in the other.

Post the SQL of your query for suggestions on how to fix.

 

[Jennifer]

So I am sorry to say I don't have a clue how to find the SQL of my query. I
can look at my query in design view but how to look in SQL?

I am so used to Excel all the Access language has me pulling my hair out.

 

[Steve Sanford]

Open the query in design view. Then, in the gray area where you add the
tables, right click and select the top option "SQL View".

HTH

 

[Jennifer]

Ahh gotcha. Ok so what do you need to know next?
SELECT [Breeding Cattle Table].[Entry No], IIf([Breeding Cattle
Table]![Cow/Calf],"Cow/Calf",[Breed Description Table]![Class Breed]) AS
[Division No], [Breeding Cattle Table].[Exhibitor ID], [Breeding Cattle
Table].Sex, [Breeding Cattle Table].[Date of Birth], [Breeding Cattle
Table].[Name of Animal], [Exhibitor Table]![First Name] & " " & [Exhibitor
Table]![Last Name] AS [Full Name], [Exhibitor Table].City, [Breeding Cattle
Table].Scratched, [Exhibitor Table].Withdrawn, IIf([Breeding Cattle
Table]![Bred and Owned],"**" & [Breeding Cattle Table]![Entry No],[Breeding
Cattle Table]![Entry No]) AS [P_Entry No], [Breeding Cattle Table].Weight,
[Breeding Cattle Table].[Bred and Owned], [Breeding Cattle Table].[B Show],
[Breeding Cattle Table].[B Scratched]
FROM ([Breeding Cattle Table] INNER JOIN [Breed Description Table] ON
[Breeding Cattle Table].Breed = [Breed Description Table].Breed) INNER JOIN
[Exhibitor Table] ON [Breeding Cattle Table].[Exhibitor ID] = [Exhibitor
Table].[Exhibitor ID];

 

[KARL DEWEY]

All I can think of is to check that [Breed Description Table].Breed and
[Exhibitor Table].[Exhibitor ID] are unique in these tables.
An easy way is to run a query like this --
SELECT [Breed Description Table].Breed, Count([Breed]) AS CountOfBreed
FROM [Breed Description Table]
GROUP BY Breed
WHERE Count([Breed]) > 1;

and

SELECT [Exhibitor Table].[Exhibitor ID], Count([Exhibitor ID]) AS
CountOfExhibitorID
FROM [Exhibitor Table]
GROUP BY [Exhibitor ID]
WHERE Count([Exhibitor ID]) > 1;


--
Build a little, test a little.

Ahh gotcha. Ok so what do you need to know next?
SELECT [Breeding Cattle Table].[Entry No], IIf([Breeding Cattle
Table]![Cow/Calf],"Cow/Calf",[Breed Description Table]![Class Breed]) AS
[Division No], [Breeding Cattle Table].[Exhibitor ID], [Breeding Cattle
Table].Sex, [Breeding Cattle Table].[Date of Birth], [Breeding Cattle
Table].[Name of Animal], [Exhibitor Table]![First Name] & " " & [Exhibitor
Table]![Last Name] AS [Full Name], [Exhibitor Table].City, [Breeding Cattle
Table].Scratched, [Exhibitor Table].Withdrawn, IIf([Breeding Cattle
Table]![Bred and Owned],"**" & [Breeding Cattle Table]![Entry No],[Breeding
Cattle Table]![Entry No]) AS [P_Entry No], [Breeding Cattle Table].Weight,
[Breeding Cattle Table].[Bred and Owned], [Breeding Cattle Table].[B Show],
[Breeding Cattle Table].[B Scratched]
FROM ([Breeding Cattle Table] INNER JOIN [Breed Description Table] ON
[Breeding Cattle Table].Breed = [Breed Description Table].Breed) INNER JOIN
[Exhibitor Table] ON [Breeding Cattle Table].[Exhibitor ID] = [Exhibitor
Table].[Exhibitor ID];

--
Thank you,

Jennifer

Open the query in design view. Then, in the gray area where you add the
tables, right click and select the top option "SQL View".

HTH

 

[Fred]

I sure that this is just a less expert way of saying what Karl already said,
but, each of your relationship lines has to have a "no duplicates" field at
one end of it. 99% of the time the "no duplicates" field is a Primary Key
field, and making it a primary key is a way to automatically prevent you from
entering duplicates.

Violating this is the most common cause of your problem.

 

[Robbie]

I sure that this is just a less expert way of saying what Karl already said,
but, each of your relationship lines has to have a "no duplicates" field at
one end of it. 99% of the time the "no duplicates" field is a Primary Key
field, and making it a primary key is a way to automatically prevent you from
entering duplicates.

Violating this is the most common cause of your problem.

 

[Robbie]

I have the same problem with Duplicates in my query. My table that the query
is based on is linked to a text file with no way that I know of to set the
primary key. any suggestions?

Here is my SQL code...

SELECT [Physical Count].Date, [Physical Count].SKU, dbo_Products.Inv_Lvl1,
dbo_Inv_Level_1.Name, dbo_Products.Inv_Lvl2, dbo_Inv_Level_2.Name,
dbo_Products.Inv_Lvl3, dbo_Inv_Level_3.Name, [Physical Count].Description,
[Physical Count].[Book Quantity], [Physical Count].[Book Amount], [Physical
Count].[Actual Quantity], [Physical Count].[Actual Amount], [Physical
Count].[Quantity Variance], [Physical Count].[Amount Variance]
FROM (((dbo_Products LEFT JOIN dbo_Inv_Level_1 ON dbo_Products.Inv_Lvl1 =
dbo_Inv_Level_1.Inv_Lvl1) LEFT JOIN dbo_Inv_Level_2 ON dbo_Products.Inv_Lvl2
= dbo_Inv_Level_2.Inv_Lvl2) LEFT JOIN dbo_Inv_Level_3 ON
dbo_Products.Inv_Lvl3 = dbo_Inv_Level_3.Inv_Lvl3) INNER JOIN [Physical Count]
ON dbo_Products.SKU_Number = [Physical Count].SKU;


Thanks in advance.
Robbie