re: Updating Junction Tables

[BkPlanner]

First, I will appologize if this seems like a basic question, but I'm relatively new to access (but not VBA programming), and just cant seem to get this to work the way I want to.

So here goes..

I am trying to build a database to keep track of "Projects" however, each project may have multiple "Project Managers" and (of course) each "Project Manager" will be working on multiple projects. In addition, each "Project" may have multiple "Applicant"s (the person or entity initiating a project) and each "Applicant" may have initiated multiple Projects.

This is really just the beginning because What I need to do is come up with a way of allowing "Project Managers" with a way of entering "Comments" for each project they are working on to keep track of actions they have taken to move each "Project" towards particular milestones... but i digress.

The project I am having is that I have to set up Many-to-Many relationships for the Project to Project Manager and Project to Applicant relationships. I did this by using junction tables which contain the primary keys of the two parent tables. Since it will work the same for both relationships, lets just stick to the Project and Project Manager relationship

I have three tables:

1. Project: ProjectID, Project Name
2. Project-Manager: ProjectID, ManagerID
3. Manager: ManagerID, ManagerFirst, ManagerLast, ManagerPhone, etc...

I want to use a form to allow people to add or update projects, but cant seem to get the junction table in the middle to update automatically. Since I find the user interface for access to be pretty confusing, I am willing to juyst bypass it and use VB to do the updating. Hopefully,I'm making sense. But any help would be much apprecaited.

thanks

eric

[JimmyTheHand]

Hi Eric :hi:

As it happens, I created a project management database sometime ago, which I'm willing to share. Surely it's not exactly what you want, but might give you a few ideas, or show you tricks. If you are interested, send me your email address in private message. (Do not ever expose your email address to public scrutiny -> and scanning bots of spammers.) The only thing I ask in exchange is that you would not sell my DB or make money out of it. What do you say?

Jimmy

[OBP]

eric, if Jimmy's very generous offer does not work out for you then I may be able to help you with this.

[BkPlanner]

I'm willing to accept help from multiple sources! I get the concept, i think but the execution is where I'm lacking, and there doenst seem to be much information on the mechanics of doing this (though setting up relationships m-t-m relationships between tables is well documented).

So I'm gald to see your way of dealing with this problem.

thx

eric

[OBP]

eric, it would be nice if you could post a zipped copy of the database on here to play with.

[BkPlanner]

Do you care that is basically just a set of tables and relationships? Since I'm new at this, I havent even really added much data or built many forms for it yet.

[OBP]

That would be great, it doesn't even need to be real data in the tables.

[OBP]

eric, that would be great, it doesn't even need to be real data in the tables, just so that we can see their structure.

[BkPlanner]

Ok..so here goes.

There are a bunch of tables in this database and almost all of them have many-to-many relationships of some form.

For each project I need to keep track of multiple Project Managers. I also need to keep track of comments that may be entered by multiple project managers for multiple projects. You'll see when you open the table definitions.

So what I would like to do FIRST is to create a form that allows me to view and enter new data based on what I view as the key organizational idea, which are PROJECTS. From this form you would be able to see all information relating to a specific project (such as EMPLOYEES, CONTACTS, COMMENTS, APPLICANT, etc...). There are more tables that need to be eventually added to this database, but for the time being I need to understand how i can use forms and/or VBA to update the junction tables between to one-to-many relationships.

I know this may be a fairly basic question, but I cant seem to find straight forward answers to how this is done. I seem to be able to create a form with a subform in it, and can link the master/child fields, but then it just doesnt do what I want it to do.

If you can help me create a single form which emulates the behavior for any one of these many-to-many relationships I would be able to apply it to any of the other ones (i think). Thanks in advance for the help. Its very much apprecaited.

best.

eric

[OBP]

eric, one thing I need to know, is this to be used by the Project managers themselves?

[OBP]

eric, I do not see a Project manager Table! :dunno

[BkPlanner]

Yes, project managers will be entering data into the system as well. Mostly Comments and another table (which is not in there yet) that tracks a set of dates/deadlines realted to project milestones (but i cant get my @*&*^$)# people to commit to the final list of fields)

[BkPlanner]

the project manager table is called EMPLOYEE (it seemed more generic since there are employees who are not project managers but would still be entering data). Sorry for the confusion.

[OBP]

eric, as I started before seeing these last post, I used the Project/Applicant table to demonstrate a simple way to get the data in to the table.
It uses a Main/Subform set up linked via Project ID and a simple one line of VBA code in the Applicant Combo Box's after update event procedure.
Have a look and let me know if this is what you had in mind.
I have added one record.

[OBP]

With regard to the users entering the data, if you want to automatically record who enters the data (rather than selecting an Employee as per the Project/Applicant version) then I would suggest that you "Secure" the database and use the user's Log On ID to identify them.

[OBP]

Do you want to include a Ghantt chart in the database?

[BkPlanner]

I hadn't thought of a gantt chart, but geee.. that does seem like a good idea, especially when project managers may have multiple projects going on at the same time (which most do) it would allow them to see how they all line up.

i'm about to look at the form you created. I cant thank you enough.

eric

[BkPlanner]

OBP -

I think the form basically does what I want it to. Thank you.

However, once i've selected a project from the list, I want to know if there is/are applicant(s) already associated with this project. So I would want the name of the applicant(s) to show up for the associated project. Im not sure if your form does that. Is there a way to 're-query' the database (is that even the right term) to have the combobox and/or other controls on the form/subform to show all data already associated with a single project ?

thanks for your help!

eric

[OBP]

eric, of course the answer is YES.
It alreday does it, however to see the data in the Subform better change it's default view property from Single to Continuous Forms.
Add another record on the sub form when you have the record that I entered showing.

[BkPlanner]

OBP -

I'll check it out and see how it goes, though I may be back for more! Thanks again for all of your help so far.

eric