If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Duplicate showing in query when there shouldn't be
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? -- Thank you, Jennifer |
#2
|
|||
|
|||
Duplicate showing in query when there shouldn't be
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 Jennifer wrote: 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? |
#3
|
|||
|
|||
Duplicate showing in query when there shouldn't be
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. -- Build a little, test a little. "Jennifer" wrote: 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? -- Thank you, Jennifer |
#4
|
|||
|
|||
Duplicate showing in query when there shouldn't be
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. -- Thank you, Jennifer "KARL DEWEY" wrote: 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. -- Build a little, test a little. "Jennifer" wrote: 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? -- Thank you, Jennifer |
#5
|
|||
|
|||
Duplicate showing in query when there shouldn't be
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. |
#6
|
|||
|
|||
Duplicate showing in query when there shouldn't be
Karl:
Those should be HAVING clauses, not WHERE clauses. The former operates after grouping, which is what's wanted here, the latter before grouping. Ken Sheridan Stafford, England KARL DEWEY wrote: 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; Ahh gotcha. Ok so what do you need to know next? SELECT [Breeding Cattle Table].[Entry No], IIf([Breeding Cattle [quoted text clipped - 37 lines] can find no correlation as to why it could be choosing these as duplicates. Any direction? -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...arted/201002/1 |
#7
|
|||
|
|||
Duplicate showing in query when there shouldn't be
As Karl says the only cogent explanation is that either Breed Description
Table or Exhibitor Table have duplicate values in the Breed or Exhibitor ID columns. If you try setting these columns to be the primary key of each table as Fred suggests and it refuses to allow you do so in one or both cases then this means there are duplicate values which will need to be pinned down and eliminated first; another possibility would be that there are one or more Nulls in the column, but I think that's unlikely. My guess would be that the Breed column is the likely culprit as its presumably text, whereas Exhibitor ID sounds like it could be an autonumber. It would also suggest that the tables are not properly related, so having eliminated any duplicates and made the columns the primary keys you should create relationships between Breed Description Table and Breeding Cattle Table on Breed, and between Exhibitor Table and Breeding Cattle Table on Exhibitor ID. In each case enforce referential integrity, and in the case if the Breed Description Table/Breeding Cattle Table relationship also enforce cascade updates. This will ensure (a) that only a valid breed or exhibitor can be entered for a beast, (b) that rows cannot be deleted from Breed Description Table or Exhibitor Table while a matching row exists in Breeding Cattle Table, and (c) that if a value in a row in the Breed column in Breed Description Table should be changed for any reason, the values in the Breed column in related rows in Breeding Cattle Table will automatically be changed. Enforced relationships are one of the principle means of protecting the integrity of the data. Ken Sheridan Stafford, England Jennifer wrote: 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]; 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". [quoted text clipped - 20 lines] can find no correlation as to why it could be choosing these as duplicates. Any direction? -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...arted/201002/1 |
#8
|
|||
|
|||
Duplicate showing in query when there shouldn't be
"Fred" wrote: 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. |
#9
|
|||
|
|||
Duplicate showing in query when there shouldn't be
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 "Fred" wrote: 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. |
Thread Tools | |
Display Modes | |
|
|