ACCESS FEED: Re: [MS_AccessPros] Re: Right join duplicate rows

Kamis, 22 Oktober 2015

Re: [MS_AccessPros] Re: Right join duplicate rows

Hi Kacy,

go to the design view of the report
turn on the property sheet (right-click anywhere and choose Properties ... ) from the shortcut menu
select the report by clicking in the upper left where the rulers intersect
click on the DATA tab of the Property Sheet
Click in the RecordSource property
click the Builder Button ... on the right or press Ctrl-F2

in the query builder, click on a blank area in the top pane, for instance, and you should see Query Properties in the Property Sheet
Set Unique Values (not Unique Records) to Yes.

Naturally, you cannot have any key fields in columns that will make each row different.

warm regards,
crystal

Document Query SQL, Form and Report RecordSources
http://www.rogersaccesslibrary.com/forum/document-query-sql-form-and-report-recordsources_topic606.html
Create Word documents with SQL for queries, form and report RecordSources, and make a query showing database objects

~ have an awesome day ~

On 10/22/2015 10:59 AM, theaterkacy wrote:

John,

Thank you for your patience.

I am trying to create a report that combines data from two tables. One table is based around Projects. The second is a list of needs which are assigned to various projects. Any given need might be attributed to more than one project.
On the report, I would like to list the projects and then the needs that are associated with each. I am using a left/right join on the Projects table to include all of the projects. When I do the join, some of the needs records are duplicated up to four times. How can I prevent the duplications from appearing in the report?

I had a scary moment where the report dropped the record source SQL and then started truncating it, but I was able to fix this by saving the query as its own item and then referring to it.

Here is the Record Source query:
SELECT Projects.ID AS Projects_ID, Projects.[Project Number], Projects.[Project Low], Projects.[Project High], Projects.[Estimated Downtime], Projects.[Action Year], Projects.Rec, Projects.[Project Desc], Projects.[Related Projects], Projects.[Related Projects].Value, Projects.[Proj Full Descript], [Needs and Recommendations].[Stated Need], [Needs and Recommendations].Recommendation, [Needs and Recommendations].ID AS [Needs and Recommendations_ID], [Needs and Recommendations].Priority, [Needs and Recommendations].[Work By], [Needs and Recommendations].Photo1, [Needs and Recommendations].Photo2, [Needs and Recommendations].Comment, [Needs and Recommendations].[System Primary], [Needs and Recommendations].[System Other], [Needs and Recommendations].[System Other].Value, [Needs and Recommendations].Project.Value
FROM Projects LEFT JOIN [Needs and Recommendations] ON Projects.[Project Number] = [Needs and Recommendations].[Project].[Value];

My first group header is grouped by Projects.[Project Number]
This header has all the fields specific to the project information.

I would like to sort this group by Projects.Rec, a yes/no field.

I have a second group, Needs and Recommendations_ID to try to eliminate the duplicate records here, by grouping the IDs. This header contains labels for the details.

The detail portion has the "Needs and Recommendations" fields specific to the Project.

The second group header does appear to be grouping, as I'm getting multiple duplicate records in the detail with the same ID (PK).

---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :

Kacy-

You didn't include the rest of the thread, so I can no longer see your original query. Did you put the Project controls in the Group Header as I told you to do? You shouldn't have to do a second Grouping.

If you reply again using the web interface, be sure to click Show Message History in your reply.

John Viescas, Author

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications

SQL Queries for Mere Mortals

http://www.viescas.com/

(Paris, France)

On Oct 22, 2015, at 5:26 PM, theaterkacy <no_reply@yahoogroups.com> wrote:

Thank you, John.
I got rid of the group by. The query is the record source for the report.

I have my report set to group Projects.[Project Number]
Then sort by Projects.Rec (a yes/no field)
Then group by [Needs and Recommendations].ID

(Sorry for the spaces in object names; I know better now...)

However, the report is not sorting per the sort instruction and it still has the duplicate records printing repeatedly, rather than grouping them. I am not receiving any error messages.

Kacy