Query duplicates

Question

I have an access query with two table data on it. Table 1 have the customer code and a few more field and table Two the customer name on it, the query has the property sheet set to show unique records. If I don’t include any field from table 2 the record shows the correct 8 unique fields but if I put the customer name from table 2 the record show 6X times the same record for each field. Any suggestions how to fix it? The joint proprieties are set to include all the record in table 1 and only the record 2 that the records match.

---

Fernando Salgueiro

Answer 1

Hi,

Just a guess but rather than setting Unique Records, try using Unique Values.

Hope that helps...

Answer 2

Sounds like a 'Cartesian' effect.

Post the SQL of your query.  On the ribbon click on the View icon, select SQL View, highlight all, copy, and paste in a post.

---

Build a little, test a little

Answer 3

Before the duplicate

SELECT DISTINCTROW [Wire VA Traveler Query].[Sequence Date], [Wire VA Traveler Query].Code, [Wire VA Traveler Query].[Purchase Order #], [Wire VA Traveler Query].[Sales Order], [Wire VA Traveler Query].Line, [Wire VA Traveler Query].[AEP Item], [Wire VA Traveler Query].[Mil Spec], [Wire VA Traveler Query].[Sequence Qty], [Wire VA Traveler Query].[AEP Component], [Wire VA Traveler Query].[MIL Component], [Wire VA Traveler Query].UOM, [Wire VA Traveler Query].Amount, [Wire VA Traveler Query].[Amount Need]
FROM [Wire VA Traveler Query] INNER JOIN [VA bill of materials with stations and process Table] ON ([Wire VA Traveler Query].[Mil Spec] = [VA bill of materials with stations and process Table].[Mil  Spec]) AND ([Wire VA Traveler Query].[AEP Item] = [VA bill of materials with stations and process Table].[AEP Item]) AND ([Wire VA Traveler Query].Code = [VA bill of materials with stations and process Table].Code)
ORDER BY [Wire VA Traveler Query].[Sequence Date], [Wire VA Traveler Query].Code, [Wire VA Traveler Query].[Purchase Order #], [Wire VA Traveler Query].[Sales Order], [Wire VA Traveler Query].Line;

After including the one field and the system giving the duplicate

SELECT DISTINCTROW [Wire VA Traveler Query].[Sequence Date], [Wire VA Traveler Query].Code, [Wire VA Traveler Query].[Purchase Order #], [Wire VA Traveler Query].[Sales Order], [Wire VA Traveler Query].Line, [Wire VA Traveler Query].[AEP Item], [Wire VA Traveler Query].[Mil Spec], [Wire VA Traveler Query].[Sequence Qty], [Wire VA Traveler Query].[AEP Component], [Wire VA Traveler Query].[MIL Component], [Wire VA Traveler Query].UOM, [Wire VA Traveler Query].Amount, [Wire VA Traveler Query].[Amount Need], [VA bill of materials with stations and process Table].[VA Process I]
FROM [Wire VA Traveler Query] INNER JOIN [VA bill of materials with stations and process Table] ON ([Wire VA Traveler Query].[Mil Spec] = [VA bill of materials with stations and process Table].[Mil  Spec]) AND ([Wire VA Traveler Query].[AEP Item] = [VA bill of materials with stations and process Table].[AEP Item]) AND ([Wire VA Traveler Query].Code = [VA bill of materials with stations and process Table].Code)
ORDER BY [Wire VA Traveler Query].[Sequence Date], [Wire VA Traveler Query].Code, [Wire VA Traveler Query].[Purchase Order #], [Wire VA Traveler Query].[Sales Order], [Wire VA Traveler Query].Line;

---

Fernando Salgueiro

Answer 4

Hi Fernando,

Going back to my suggestion earlier, try using DISTINCT rather than DISTINCTROW. But still, it's just a guess.

Hope it helps...

Answer 5

Great, it works great

Thank you

---

Fernando Salgueiro

Answer 6

Hi Fernando,

You're welcome. Glad to hear you got it to work. Good luck with your project.