Can't get rid of duplicate rows

[pstraszynski]

Hello/Hola/Bonjour !

I have a query based on two tables, joined by the primary key of the left table (right table has a different primary key). There is a one-to-many relationship between the left table and the right. (e.g. 1 to 4)

What I want to show is all of the data in the four (right table) rows, plus only a few columns from the left table. That is, four rows total.

The problem I'm having is that the data from the left table is showing in each one of the four rows. (Instead of getting four rows with $800 in only one of those rows and three empty cells below it, I'm getting all four rows with $800 in that column).

I've tried a few things to get rid of the duplicates but no success so far. I'm not really well versed in Access queries but there must be a way to do this.

Help/suggestions appreciated
Paul Stras

 

[georgedwilkinson]

Post the SQL of your query here. You can get that by going into SQL view in the query builder.

 

[MSAccessRookie]

Hello/Hola/Bonjour !

I have a query based on two tables, joined by the primary key of the left table (right table has a different primary key). There is a one-to-many relationship between the left table and the right. (e.g. 1 to 4)

What I want to show is all of the data in the four (right table) rows, plus only a few columns from the left table. That is, four rows total.

The problem I'm having is that the data from the left table is showing in each one of the four rows. (Instead of getting four rows with $800 in only one of those rows and three empty cells below it, I'm getting all four rows with $800 in that column).

I've tried a few things to get rid of the duplicates but no success so far. I'm not really well versed in Access queries but there must be a way to do this.

Help/suggestions appreciated
Paul Stras

Sounds to me like you have a "Cartesian Join" or "Full Join" ocurring. You may need to verify that the fields selected in the join have values that are consistant with your expectations (Once per Join item, on the left and many per join item on the right). It sounds like this is not the case., and that at least some of them are either many to many, or you have a problem with your Join statement. Feel free to post the join statement here if you are not able to find a resolution.

 

[neileg]

Sorry George and Rookie, I think you've misunderstood.

Paul, what you describe is absolutely correct an normal. When you create a query based on a one to many relationship, you will always get the one side data repeated for each many side result. You can't change this in a query. What you do is use a form or a report to display the data. If, for instance, you use a form to show the one side data, you can use a subform to get the many side data.

 

[georgedwilkinson]

That is what I assumed but can not validate until I see the SQL.

 

[pstraszynski]

Here is the simplified SQL for the query. (I have removed some fields for clarity).
SELECT
POPData.[PSPO], POPData.[kW saved winter (Est)], POPData.[kWh Summer (Est)], POPData.[kWh Winter (Est)],
POPIncentives.VerifiedkWWinter, POPIncentives.VerifiedkWhSummer
FROM POPData LEFT JOIN POPIncentives ON POPData.PSPO = POPIncentives.PSPO

The left table, called POPData has a unique identifier for each record , called POPData.PSPO. The (Est) fields occur in this table also.
The right table, called POPIncentives, has multiple records for each POPIncentives.PSPO. Fields such as "Electric Incentive" occur more than once for each PSPO and are all different values.

This query gives duplicates of the (Est) values which is of course wrong. The user wants to see lots of data at once so a form/subform is out. I tried creating a report but of course using this SQL I still get duplicates. Is there a way to avoid the duplicates when creating a report? I also tried moving the data to the right table (had to create add'l columns) but still no luck. I'm going to keep experimenting with reports.

Paul

 

[georgedwilkinson]

Hmmm. The SQL still doesn't give many clues without seeing the output. My suspicion all along is what neileg says above. However, I'd like to help you solve the problem, not tell you you did it wrong. Are the (est) fields pre-calculated? If so, that is probably the problem. And if it is, you'll need to re-write the query with the calculation in it.

Another thing to try is:

SELECT 
POPData.[PSPO], POPData.[kW saved winter (Est)], POPData.[kWh Summer (Est)], POPData.[kWh Winter (Est)], 
POPIncentives.VerifiedkWWinter, POPIncentives.VerifiedkWhSummer
FROM POPData LEFT JOIN POPIncentives ON POPData.PSPO = POPIncentives.PSPO
GROUP BY 
POPData.[PSPO], POPData.[kW saved winter (Est)], POPData.[kWh Summer (Est)], POPData.[kWh Winter (Est)], 
POPIncentives.VerifiedkWWinter, POPIncentives.VerifiedkWhSummer

You could accomplish pretty much the same thing by using the "DISTINCT" keyword in your select clause.

 

[neileg]

I think that you need to use a report. You can use the sorting and grouping functionality so that you get the left side data once followed by the right side data.