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

Sabtu, 24 Oktober 2015

[MS_AccessPros] Re: Right join duplicate rows

When I try to enter a new record in my main form, the subform for projects is already populated with all project numbers. How can I keep this blank until the user selects the projects they want to associate with the new need? I'm not sure what is cuasing the subform to populate in this manner.

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

Kacy-

Using a form / subform is the correct way to do this. Your outer form can edit either Projects or Needs. If you edit Needs on the outer form, then the Record Source of the subform should be a query that links the new tProjNeed table with the Projects table on ProjectID. Include all the fields from tProjNeed and the description field from Projects. Do not include the ProjectID field from Projects. Build a Continuous or Datasheet form on that Record Source and use a combo box that does a lookup to the Projects table to set tProjNeed.ProjID. The Link Master Fields should point to the Need ID in the outer form, and the Link Child Fields should point to the NeedID on the subform.

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 23, 2015, at 9:05 AM, theaterkacy <no_reply@yahoogroups.com> wrote:

John,

Yes, I believe that our messages must have crossed, so that I composed my last between your replies but it posted after you replied about the table redesign. Apologies.

I am working on implementing this element. I have created a junction table,
tProjNeed: ID, Need ID, Proj ID,
where the latter two have a many-1 relationship to their corresponding PKs.

I created a form with a combo box that displays the project number and description from the Projects table, and saves the selection to tProjNeed.ProjID. using column 1 as the bound value.

Now I need to connect this data to the Need ID. I inserted the form into my Needs form as a subform. I set the source as the form and now need to set Master/Child Fields, correct? Should these be master: Needs.ID and Child tProjNeed.NeedID?

When I test this and try to make a selection, I get an error beep, although no error message. I have not yet deleted the Needs.Project Multivalue field. Could that be causing the error or is it a Link Master/Child issue?

Crystal, Thank you, Changing that value from no to yes exposes the several locations where I have relied on multi-value fields. Those are not causing an issue (yet?) because of the way I am using that data. I am learning for future set-ups, indeed!

Kacy

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

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
http://www.viescas.com/
(Paris, France)

On Oct 22, 2015, at 6:59 PM, theaterkacy <no_reply@yahoogroups.com> 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