Emplyees and Projects database

[Immortal]

I'm sort of making a database with 2 tables: employees and projects. I have set up a many to many relation between both using another table.

I have some questions:

1- Now in the projects table, I want to add a field that calculates how many employees working on the project. Should this be done directly on the projects form? should it be stored in the table or in a query? What is the best way and how is it done?

2-I want to be able to see the names of employees on a project in this project's form

3-And the same for employees: get the number of projects an employee took part in,

4- and a way to display the projects on the employee's form (in a nice concatenation )
ex: "Project1", "Project2"

I'm still a beginner so please if you are mentioning something a bit advanced, point me to some resource so I could learn it.

 

[GinaWhipp]

You are missing a critical table...

tblEmployeeProjects
epID
epEmployeeID (FK relate to table Employees)
epProjectID (FK relate to table Projects)

You need the above because I am going to assume an Employee can be assigned to more than one Project and a Project can be assigned to more than one Employee.

Hmm, as you are starting out, let me point you to some links that might be helpful ...

Jeff Conrad's resources page...
http://www.accessmvp.com/JConrad/accessjunkie/resources.html

The Access Web resources page...
http://www.mvps.org/access/resources/index.html

A free tutorial written by Crystal (MS Access MVP)...
http://allenbrowne.com/casu-22.html

MVP Allen Browne's tutorials...
http://allenbrowne.com/links.html#Tutorials

UtterAccess Newcomer's Reading List
http://www.utteraccess.com/forum/Newcomer-s-Reading-List-t1998783.html

Sample data models...
http://www.databasedev.co.uk/table-of-contents.html
http://www.databaseanswers.org/data_models/

Naming Conventions…
http://www.access-diva.com/d1.html

Other helpful tips…

Setting up a Model Database
http://www.access-diva.com/d11.html

My Database Standards...
http://regina-whipp.com/blog/?p=102

 

[Immortal]

You are missing a critical table...

tblEmployeeProjects
epID
epEmployeeID (FK relate to table Employees)
epProjectID (FK relate to table Projects)

You need the above because I am going to assume an Employee can be assigned to more than one Project and a Project can be assigned to more than one Employee.

Thanks for the resources

I already made a third table as I clarified above in order to make a "many to many" relationship between the 2 tables. Is that what you meant ? or am I missing something ?

I don't know what to look for to solve my problem :banghead:
any guidance please.

 

[LordLoki]

Hi Immortal,

Im still a beginner in Excel too but i think i can help you with this.

1. Never use Calculated Fields in a table that is just causing problems from all i read so far. So you have to do the Calculation in a query

2. if you already have a 3rd table with employee id and Project id you can just run a simple count query on that table.

If i have some spare time i will give you a quick example on this later

greets
Loki

 

[GinaWhipp]

The only many-to-many is the newly created table. The other two should be a one-to-many between the newly created table. What do you have?

 

[LordLoki]

Hi There,

Just clicked together some Sample Database.
Only VBA that is involved is to refresh the Listboxes when you change the Combobox for Employee/Project

Its not nice looking just to help you understand how it works.
As i say i am new to access too but maybe it helps you to get your thing going :
And for me its good Training

If you have questions just Ask

Greets
Loki

 

[GinaWhipp]

@LordLoki,

Nice sample only suggestion I would make is so not name every PK ID. This becomes confusing when you begin to write code, as well as, when creating relationships. Instead do ProjectID, EmployeeID, etc...

Oops, another suggestion... tabl POV what does that mean? In a year you'll be asking yourself that question. Name your tables so at a glance anyone can identify them.

 

[Immortal]

First of all, I am really happy to find people actually helping me. cheers to you guys, you are the best

Hi There,

Just clicked together some Sample Database.
Only VBA that is involved is to refresh the Listboxes when you change the Combobox for Employee/Project

Its not nice looking just to help you understand how it works.
As i say i am new to access too but maybe it helps you to get your thing going :
And for me its good Training

If you have questions just Ask

Greets
Loki

Special thanks to you LordLoki for bothering yourself to make this database sample. Now please bear with me here

Anyway looks like the problem with me was queries. I did not know them enough. could you explain me what you did in the queries and what are these codes:
[Forms]![Projects]![CB_Pname]
[Forms]![Employee]![CB_EName]

Also in the forms,
I need to understand what you did with the combobox and the listbox and the codes you wrote in their Row Source because I want to be able to replicate them in another way (lets say an employees form with the default design where you have textboxes displaying an employee's info)

And I want to be able to add/remove a project from an employee or go to the project itself and remove the employee (so they have the same effect) so how ?

 

[LordLoki]

Hi Immortal,

I am with you my friend but i will be posting some information later cause i am on the way to work now.

For the time check out this video about Queries i found it helpful:
Creating Queries with Microsoft Access 2010: http://youtu.be/n2LWqnN-yCY

The [Forms]![Projects]![CB_Pname]
Refers to the dropdown in the Projects form.

Tonight i will write down some more detailed information about how i did that

 

[Immortal]

Soo thanks for the video.

I have been fiddling with your database sample and was able to replicate it as I wanted where I cycle between employees records in a form and by setting the query parameter value as the EmployeeID I was able to output the projects on a listbox. I also made 2 buttons to cycle between the records and to Requery the listbox.

But I would still like your explanation so I could comprehend the way it goes.
Anyway, I'm not in a hurry so don't push yourself.

Good luck at your work

 

[GinaWhipp]

Isn't this great... so, you two can help each other and I'll just sit here in background, sipping my coffee, in case you have any problems!

 

[LordLoki]

Thats the best thing if you can learn by helping each other and have guidance from an experienced User

Cause i was lazy for writing and got confused by my own explenation i just show how i did it in small vid tutorial.

i was not 100% Focused so the was a little mistake in the beginning that i did not see but i was already 80% through the thing when i realized it. Its explained in the Video so i think its still ok.

Maybe i will go on with a small tutorial on that thing to show some other stuff.

Now i get a cup of Coffee on my own and then go to bed
Have fun with the Video

http://youtu.be/ewVURtiH7u4

Greets
Loki

 

[Immortal]

That's really nice :')
But you made the video private, you should make it unlisted so that anyone with the link can "access" it

 

[GinaWhipp]

Yep, I can't even watch... Can you share?

 

[Immortal]

No, nobody can watch it, yet.

 

[LordLoki]

Uups! Fixed that!

I think i did not have enough Coffee Yesterday

 

[GinaWhipp]

Yipee! watching now but so far so good. Immortal should get alot out of this!

 

[LordLoki]

Good i was hopong its not too bad

 

[LordLoki]

Ah Gina just see that i missed yout Post with suggestions. And yes you are right i should force myself to better naming of variables and tables.

Point is i can look at even 3 year old code i wrote and identify everything cause thats how my mind works. Problem is coming at work where sometimes other people need to work on my vba scripts and dont get it.

I just hate long names when im coding. Thats why i make everything short and simple. But i will try to change that in the Future

Btw: POV is project Overview

 

[GinaWhipp]

Hmm, please do because you would hate to run into this...

http://regina-whipp.com/blog/?p=102

See what that poor guy is staring at? You would hate to get a database filled with that and that is what yours looks like to the *other guy*. Leads to wasted time for you explaining and the other guy trying to understand.

POV = never crossed my mind that is what that meant.