Kacy-
As I explained to you in another reply, the problem is your Project field in Needs and Recommendations is a multi-value field that is messing up your results. You need to redesign your tables as I suggested.
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
(Paris, France)
On Oct 22, 2015, at 6:59 PM, theaterkacy <no_reply@yahoogroups.com> wrote:
John,
Thank you for your patience.
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 :
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?
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
(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
__._,_.___
Posted by: John Viescas <johnv@msn.com>
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (8) |
.
__,_._,___
Tidak ada komentar:
Posting Komentar