Another multiple value field problem

[Montez659]

So I just received a request to change up something within our db and I can't figure out how to do it because I keep getting an error. I'll lay out the scenario for you:

It is essentially a task list that with a table called "Assignments" within an exisitng db. As far as who the task is assigned to, I have a lookup field referring to table "Contacts", where there is ID, fName, lName.

Everything worked well.

Now the field in the "Assignments" table needs to allow multiple values for collaboration and so that the report will spit out the same assignment for the different names associated with it (the report is grouped by fname) but that isn't the problem. The problem lies in the fact that the relationship of Contacts to Assignments is based off of the ID number, but the name is displayed in the lookup field. And so if I try to convert the "assignedTo" field to a multiple values field, I get the following error:

Columns that accept multiple values for a record cannot be included in a mulitple-column relationship.

I understand the message to a degree. But I need my lookup field to display a name and not a number. From what I see, this error is basically telling me this is not possible.

So, is there any way around this? I know multiple field values are not the friendliest things in the world, but it would seem that this is a situation where it should work well. If I can't do it via multiple values, how would I allow more than one person per assignment?

I am trying to stay within the framework of what is alredy built, but of course changes are necessary for things to work.

 

[xenou]

You might need to find out how to get at the ID number in the multi-valued field. Possibly something like [MyField](0) or [MyField].Columns(1) or [MyField].Columns("ID"). I don't know if the indexes would be one-based or zero-based. Fields are usually zero-based and columns are usually 1-based in Access.

I'm guessing of course - I've not used m-v fields but did something like this once as an experiment (getting a single value out of the m-v field).

It *might* be better not to use them. They were created (so far as I can tell) to integrate with sharepoint that has a similar data type. Without an m-v field, if you have a many-to-many between contacts and assignments (assignment related to many contacts and contact related to many assignments) then you typically use a linking table (aka junction table or associative table) to resolve the relationship.

 

[Montez659]

I think that a junction table is probably what I am looking for. Thought that a m-v field would cut out some work but I guess not.

 

[Montez659]

Ok, so I got my junction table set up, but I am at a loss as how to translate this back into - shoot, I don't even know what to say!

Let me describe it. I've set up a junction table called tblAssignmentsContacts with both ID (from table Contacts) and assignID (from table tblAssignments) as primary keys. There is a one to many relationship with both of these.

Back on my form, I am still only able to select one name from a combobox (or in essence, associate on name with any record). What do I need to do to be able to offer the ability to assign more than one name with each record? Also, how would this translate to my reports, grouped by name?

This is the first time that I have had to use a junction table like this so I really am in the dark about all of this. Any good resources are appreciated, and I understand and can follow along with videos best.

Thanks!

 

[Norie]

Well you won't be able to make multiple selections from a combobox without a little work, so why not try a listbox or even a subform.

The subform would basically be a list of all the contacts for the assigment which is being displayed in the main form.

It would be based on your junction table and the default value for the assignID field would be the primary key for the assignment in the main form.

Those 2 fields would also be the master/child link between main/sub form.

Hope you understand that so far, it's definitely the sort of approach I would take.

I would probably also have another continuous form that lists all the assignments.

Each record would have a button on it that when clicked would lead to the form described above.

PS I think multi-value fields might have been suggested by the same person who thought up merged cells in Excel.

 

[Montez659]

Alright, I seem to be getting the hang of it. If I wanted to display that First Name and Last Name in the subreport as opposed to the Contact ID, would I just need to add those two fields to my junction table and hide the contact ID column?

 

[Montez659]

Nm, I got it. Appreciate the expertise that you guys always give! Always glad to be a part of this community!

Anyway to remove the record navigation controls on the bottom of the subform?

 

[Montez659]

Ok, got all of the above figured out, but two problems I seem to be having.

1.) I converted the control in the subform to a combobox for the selection of who to assign to. I have 1 bound column, 3 columns - should be id, first, last. The width of column 1 is 0, the other not defined. When the combobox is in drop down mode, you can see the first and last name, but after the name is chosen, and the drop down disappears there is only a first name in the box. I would like to have first and last.

2.) I used to have a report run off of assignedTo, but that field no longer exists due to the junction table and all. How do I run my report so that it is grouped by contact, with duplicate assignment details showing up under each respective name?

Thanks!

 

[Norie]

1 What's the Row Source of the combobox? It should include the first and last name - perhaps concatenated and the ID.

The ID is the important field and you probaly shouldn't show it but it should be the BoundColumn.

Depending on whether or not you've concatenated the first and last name you should hav 2 (concatenated) or 3 fields/columns.

Assuming the first column is the ID then set it's width to 0 and the other columns to a width that suits.

You can set the ColumnWidths property for that, something like this perhaps

0;50;50 -3 columns

0;100 - 2 columns

2 You should just scrap the existing report and create a new one.

It would be possible to change the existing one but you really don't want to do that - honestly.

 

[Montez659]

1. The Rowsource was

SELECT Contacts.ID, Contacts.[First Name], Contacts.[Last Name] FROM Contacts;

are you saying to just concatenate the first name and last name in the SQL? If so, I am not so hot on SQL, so if you got an example, that would help.

2. I don't mind scraping the original report at all, as a matter of fact, that was my intention. I am just not sure how to go about grouping the report. For the query, the only limiting factor is begDate and endDate, but I have that expression already worked out. I added the junction table fields to that query. Started up the Report wizard but not sure when it is asking me to group by which table what I should choose - Contacts, tblAssignmentsContacts, or tblAssignments.

I ran it once using the junction table and had nothing come up on the report.