Multiple Lookups

[datamonkey]

I have a bit of a poser, and I'm hoping the forum will be able to help. I am setting up a project-based database where project details can be added to it and I am having trouble with a lookup field, which I need to be able to accept multiple values. As an example, the following tables:



ProjectTable
pk ProjectID
ProjectName
fk Staff(StaffTable)

StaffTable
pk StaffID
StaffName


It is easy to make the Staff field (in ProjectTable) a lookup field where I can type in the name of the staff member and it automatically searches for it and adds it to that field - which is fantastic, and I love the way access makes that possible through the sharepoint webapp. So far, so good.

However, most projects have more than one staff member working on it, so in that Staff field, I would want to enter multiple staff members - is there a way to do this? I know you can allow multiple values if you enter a manual list through the lookup wizard, but I can't find a way of doing it with a foreign field?

Any help you can give would be great,

Many thanks.

[CJ_London]

as a minimum you need a third table

tblStaffing
fkProject(Projecttable)
fkStaff(StaffTable)

you need two indexes.

The first is a primary key across both fields to prevent duplicates of project/staff
The second is on the staff field only (duplicates OK)

if you want a separate autonumber primary key for this table, that is not a problem but you still need the index across both fields to prevent project/staff duplicates

However you should also consider your business needs - you may need assignment/deassignment dates if staff can only be assigned to one project at a time for example.

You would need to provide more information about your business works to provide additional help

[datamonkey]

Thank you for your help with that. I do have a third table but that gives the same problem of entering multiple lookup values in the same field.

What I ideally want is when people enter information in that field rather than typing "Dave" they could type "Dave; Julia" and both would be linked.

Staff can work on more than one project at any time and projects can have more than one staff member working on it.

With the third table that would still give me the same problem of only being able to enter one record in that field would it not? Unless of course I just need to learn to build forms correctly to solve the problem?

[CJ_London]

With the third table that would still give me the same problem of only being able to enter one record in that field would it not?

Yes and no data would look like

fkproject fkstaff
1.............1
1.............2
1.............45
3.............1
3.............48

so 3 staff members on project 1 and 2 on project 2 - staff 1 is working on both of them

Unless of course I just need to learn to build forms correctly to solve the problem?

yes!

if you mean multivalue field, don't use them - if this is what you are referring to, you do not understand where they are appropriate to be used.

If you want a different form of user input which has code behind the split the input on the semi colon, it can be done, but a lot more work

using the third table, this would be on a subform on the project form - you would hide the fkproject control and the fkstaff control would be a combo with rowsource based on the staff table. Alternatively it can be a subform on the staff table where the fkstaff control would be hidden and the fkproject control would be a combo with a rowsource based on the project table. The subform linkchild/linkmaster properties handles the relations and will autopopulate the hidden control

[datamonkey]

if you mean multivalue field, don't use them - if this is what you are referring to, you do not understand where they are appropriate to be used.

I think this is a bit harsh. I do understand database design, however I would usually create everything using SQL and PHP rather than something like Access, which I am finding a little restrictive. The point I was trying to make was that usually I could code an input box to accept multiple entries, which would then update accordingly.

However, with the access webapp I like the fact that as a user begins to type in the field it offers suggestions and makes that easier, but I just wanted to know if it is possible to accept multiple values in a field in a form, which would then update the appropriate relational tables accordingly.

I am also somewhat frustrated by what appears to be a very limited set of toolbar options when editing a webapp as I expected more. For example I can't workout how to find any kind of relationship view. I also can't work out how to delete something from a form/view which is very annoying. Perhaps I am expecting too much.

[CJ_London]

but I just wanted to know if it is possible to accept multiple values in a field in a form, which would then update the appropriate relational tables accordingly.

I'm not familiar with the access web-app - too restrictive as you say. but with a client based solution you would use vba to generate sql. I think with a web app you are limited to macros - certainly cannot use vba.

user begins to type in the field it offers suggestions and makes that easier

that is a standard behaviour with a combo box - it is called typeahead.

With regard multivalue fields, issues include: cannot be indexed (so performance can suffer) cannot be easily referenced, cannot be upsized because other db's don't use them - although that might change with new db's coming along. Multivalue fields are intended to be used to reference a fairly limited range of options such colour or perhaps destinations which don't change very often. With staff the data is much more dynamic - they join, leave, change jobs etc.

I also use java and when I started I found it confusing because standard objects exist at a much lower level than used in Access so I had to build a number of objects to get to where I wanted - and things like continuous forms seemed like a foreign country! So with access you are trading complete flexibility for a rapid development environment.