please help structuring db for future expansion

[aflores3]

first thing's first... i've never set up a Access db. I have, however, worked with php/mysql so I understand dbs and the basic principles behind them.
i was hoping you could help me set up my db to accomplish my goals now and help me in the future when i want to make this system more advanced.

the purpose of the database is to track the 'stages' of several different projects as well as keep account of which contacts are involved.

example

I have projects A, B, C
I have contacts I, II, III
the stages of the projects are 1, 2, 3

I already have these tables (projects, contaces, stages)

the first issue is, i dont know how to assign mutltiple contacts for specific projects
i.e. project A involves I, II, and III while project B only involves I and III. I would like a form to have a listbox with all the contacts in it and i could hold ctrl and select mutiple contacts for a specific project

the second issue... i would like to db to store dates of when a specific project moves from stage 1 to stage 2 for instance. i know i can set up fields in the projects table for these, but what happens when i add a new stage? will i need to go into the projects table and add a new field? can i make this dynamic?


i hope this makes sense to you. if you could give me direction, i would greatly appreciate it. before doing something that i will regret later, i hope you can give me some tips based on your experiences.

thanks in advance.

 

[KjWhal]

first thing's first... i've never set up a Access db. I have, however, worked with php/mysql so I understand dbs and the basic principles behind them.
i was hoping you could help me set up my db to accomplish my goals now and help me in the future when i want to make this system more advanced.

the purpose of the database is to track the 'stages' of several different projects as well as keep account of which contacts are involved.

example

I have projects A, B, C
I have contacts I, II, III
the stages of the projects are 1, 2, 3

I already have these tables (projects, contaces, stages)

the first issue is, i dont know how to assign mutltiple contacts for specific projects
i.e. project A involves I, II, and III while project B only involves I and III. I would like a form to have a listbox with all the contacts in it and i could hold ctrl and select mutiple contacts for a specific project

the second issue... i would like to db to store dates of when a specific project moves from stage 1 to stage 2 for instance. i know i can set up fields in the projects table for these, but what happens when i add a new stage? will i need to go into the projects table and add a new field? can i make this dynamic?


i hope this makes sense to you. if you could give me direction, i would greatly appreciate it. before doing something that i will regret later, i hope you can give me some tips based on your experiences.

thanks in advance.

If you want to have multiple contacts per project you're going to need a table for that relationship.

For example you have something like this I assume:

tableContact       tableProject
ID  (primaryKey)   ID  (primaryKey)
<fields>              <fields>

You need another table which looks like

tableProject_Link
contactID    (foreign key from tableContact)
projectID     (foreign key from table Project)

As for your second issue, create another table like

tableTransitionDates
ID
projectID     (foreign key from table Project)
stage
Date

Then when your user moves a project from stage to stage this is where you put the records.

One last thing I probably should have mentioned.

As far as the various stages, and if you think you want to add more in the future:

You can have a comboBox of values that they select from, and simply change this as needed, or you can create another table such as

tableStages
ID (primaryKey)
stageName

and then put the ID of the stage in the stage field of the previous table I mentioned. It's also handy if for some reason in the future one of your stages gets renamed - you can change 1 field instead of every record that stage was in.

 

[aflores3]

Wow, looks great

thank you for the response. the set-up looks great. I have set up the tables are you suggested, and it looks like they will work great. I do have a question regarding the Form to assign several contacts to one project.

The table is set up as

tableProject_Link
contactID    (foreign key from tableContact)
projectID     (foreign key from table Project)

I would like a form to have a listbox with all the contacts in it and i could hold ctrl and select mutiple contacts for a specific project.

is this possible with the table set up as it is?
(Should i repost this in the Forms section of the forums?)

Thanks in advance.

 

[KjWhal]

thank you for the response. the set-up looks great. I have set up the tables are you suggested, and it looks like they will work great. I do have a question regarding the Form to assign several contacts to one project.

The table is set up as

tableProject_Link
contactID    (foreign key from tableContact)
projectID     (foreign key from table Project)

I would like a form to have a listbox with all the contacts in it and i could hold ctrl and select mutiple contacts for a specific project.

is this possible with the table set up as it is?
(Should i repost this in the Forms section of the forums?)

Thanks in advance.

Yes it is possible. I would do it with VBA, if you're comfortable with that I can explain how. If not then head over to the forms section because I don't know how to do it any other way (if possible)

 

[aflores3]

if you could explain here, i'd appreciate it.
it seems easier than having to re-explain my db to someone else.

if this is too much to ask, i'll make my way over there.

 

[KjWhal]

if you could explain here, i'd appreciate it.
it seems easier than having to re-explain my db to someone else.

if this is too much to ask, i'll make my way over there.

' I am making up names for fields and such, change these accordingly.

Put a listbox called contactsListBox on your form for the contacts

Set the RecordSource =
' I assume you have lastName and firstName for each contact separate, if its just one field then just do contactID,contactName

SELECT contactID, lastName & ", " & firstName AS contactName
FROM tableContact

Put another one on your form for the projects called projectsListBox

Set the RecordSource =

SELECT projectID, ProjectName
FROM tableProject

Now create a button

Add an OnClick event for this button with the following code

Dim insertStatement as String
Dim indexContact, indexProject As Integer

Do While indexProject < projectsListBox.ItemsSelected.Count
Do While indexContact < contactsListBox.ItemsSelected.Count
    insertStatement = "INSERT INTO tableProject_Link (contactID,projectID)              
    VALUES ("
    insertStatement = insertStatement &
    contactsListBox.Column(contactsListBox.ItemsSelected(indexContact),0) & "," &       
    projectListBox.Column(projectsListBox.ItemsSelected(indexProject),0) & ")"

    DoCmd.RunSQL (insertStatement)
    insertStatement = ""
    indexContact = indexContact +1
Loop
indexProject = indexProject + 1
Loop

In theory that will allow your user to add multiple contacts to multiple projects at the same time (IE add contacts 1,2,3 to projects A,B,C at the same time)

I may have written the code referencing the projectID and contactIDs a little incorrectly, I am doing this from memory. The code I wrote before is at work and I won't see that till tommorow. Or you may be able to correct any errors yourself.

Note: If you want you can add
DoCmd.SetWarnings False
DoCmd.SetWarnings True around the DoCmd.RunSQL if you don't want Access notifying you that yes you are inserting a record.

If you want me to explain the code I can do that too.

 

[aflores3]

WOW! that worked. I can't tell you how much i appreciate your help. it's just so frustrating sometime b/c I know ANYTHING is possible, it's just a matter of knowing how to do it.

thanks for showing me the way.