Query returning values twice (1 Viewer)

[cjordan8]

Every time I run my query, I get the right results, but it returns them twice. So when I run my report based on that query, it also shows them twice. What to do?

 

[cjordan8]

now it's returning them 4 times

 

[David R]

Can you post your query?

It sounds like you're viewing both sides of a one-to-many relationship, and the one side is being duplicated.

 

[cjordan8]

how?

DO I have to send the whole database?

 

[David R]

Nope

Go into your query in design view, and click the button in the top left to get "SQL View". Copy and post the text you see there. It should start off "SELECT..."

 

[cjordan8]

thanks, i'm learning i promise

SELECT Competitors.ID, Competitors.City, Competitors.Competitor, Competitors.Owner, Competitors.Company, Competitors.[Based out of], Competitors.[Coverage Area], Competitors.[Distribution - say], Competitors.[Distribution - think], Competitors.[Circulation - say], Competitors.[Circulation - think], Competitors.Revenue, Competitors.[Other Products], Competitors.[Job Fairs], Competitors.Website, Competitors.Strength, Competitors.[Comments/History], Competitors.[Comments/History], [Major Competitors].[President & Publisher], [Major Competitors].[Vice President Sales], [Major Competitors].[Main Office], [Major Competitors].[Telephone Number], [Major Competitors].[Dist Frequency], [Major Competitors].[Dist Strategy], [Major Competitors].[Sales Strategy], [Major Competitors].[Product Information], [Major Competitors].Geography, [Major Competitors].[Web Presence], [Major Competitors].Comments, [Major Competitors].[Assumed Future Plans]
FROM Competitors, [Major Competitors]
WHERE (((Competitors.Competitor)=[Which Competitor]));

 

[cjordan8]

ALmost an infinte loop

Now it's returning massive amounts of duplicates. How do I make it stop?

 

[David R]

Make sure you join your Competitors table to your MajorCompetitors table via an appropriate field. You're getting what's called a cartesian product, which is every row in table1 matched with every row in table2. The total will be table1rows*table2rows!

 

[cjordan8]

The problem is I have 3 tables. 1 with general information about our national major competitors that is put in the header of the report. The second with all the competitor information by city, and the third with our own companies information by city. When I join the second and third together by city, the number goes down, but still returns four entries for each request. I'm sorry, but I'm a real novice at this stuff.

 

[cjordan8]

I think I figured out the problem. I connected the cities on the last two tables and the competitor names on the national level and the local level. all appears well, but that could change.
Thanks for your help!

 

[cjordan8]

Having the cartesian problem again with two different tables. My query searches by city for all the competitors. I want to be able to show my company's information for that city as well without showing all of the fields, so I have sum functions in the report footer. I've connected the two tables in the query by city, but I'm still getting 2 times the results.

 

[David R]

2x doesn't sound like cartesian products to me, unless one of the tables in question only has two entries.

If you're using Access 2k, compact your database and send it to me by email. I'm having a hard time picturing what's going wrong here.