Help with Project Organization for a beginner

[KryptoRoxx]

I know nothing...but I am trying to learn lol.

My project entails about 800 employees that I need to be able to select from a table and put out a roster of those selected employees. This much I got to work.

Additionally I need to be able to put the project name, project leader, and his right hand man on the report but I can't have the employees "tied" to the project as I start new ones and shift people back and forth all the time. I also need to be able to add external temps to projects where their information is not in the main table of employees. They need to be inputted manually and be on the report but I don't need to store their info.

Finally after I get the guts working right I need some advice on setting up a good organized switchboard that others could use besides myself. I am completely willing to start from ground zero to make a well built database that performs efficiently. Any help is greatly appreciated.

 

[speakers_86]

This doesn't sound too hard from what I read.

tblProjects
ProjectID
LeaderID (the employee's id for the project leader)
SecondaryLeaderID

tblTempEmployee
all relevant info

tblTempAssignments
TempAssignmentID (autonumber for PK)
TempEmployeeID
ProjectID


As for the switchboard, please DO NOT use the native switchboard function. You are better off building one yourself using a blank form. They look better, are easier to use and maintain in my opinion.

 

[KryptoRoxx]

I will keep the advice in mind about the switchboard. I think I remember how to do the subforums and will use that approach for that. However I am still having problems fixing this up. I wish that I could put Access to use more often as I gain a lot of profiency from practical application.

1. I might be trying to use the data the wrong way but what I want to do is only combine the data at the end in the report. I tried to make the report without the relationships but it didn't work. If I can't do it with independent tables maybe I can do it with some type of VB? I really know nothing about VB but I will learn if I can get it done like that.

2. It occurred to me while I was creating this that I would like to be able to clear all the data that I inputted for a roster in one or two clicks vs. erasing manually. I know this would be a delete query and I tried this...and then ended up restoring my database lol. Tips on this would be appreciated.

Here's my flow process that I do with these rosters. I usually get them handwritten from the project and of course at the top it has the project name, leader, and assistant. I only need the names to appear like it does on the handwritten roster. Below that is the ID of the employees that are on the project. That's why I did the select so that I could display the employee info without typing it in every time. After that I have the temp employees which have IDs but they are not stored. It's for pay purposes really. So I can input all of the info for the temps as a new record in a form. That can also be seperate until it gets transferred into the report. However I know that I can relate the temp and permenant employee tables easily with the ID. The goal that I'm after is from the completed report in access I export it out to Adobe and store it or print it for record purposes in a clean professional display. I hope that this helps anyone trying to make sense of how I do things....and if I have to rethink this and go about it another way I need to know your thoughts on that so that I can fall in line with your thinking and conceptualize what I need to do in Access. Sorry for the long winded reply but I'm trying to wrap my mind around this.

 

[speakers_86]

You need to start at the beginning. Look into database normalization.

 

[KryptoRoxx]

Ok I did read through and look at the normalization and I think I have a solution. However I want to check and see if this will work. So what I have is a bunch of info that isn't related until after I select employees for a project. So I have my employee table and I have my temp table and I have project info table. All unrelated. So to make them related I have to select the employees through a form and query. The query populates a secondary table (reporttable) so that I can relate it to a project. In the same way that I seperated the employees I seperate projects from the table that gets populated with employee info. I can splice project info into the reporttable table through a form and query. After that I have to input temp employee info one by one through a form into reporttable. From there I have all of the info that I need to create my report and I have my tables all related and the report will come out as I want it....as long as my logic is correct?

So here's what I will be trying to teach myself.
1. How to populate a table with info from a query.
2. How to run a query that fills in info (project id)for each record that is selected (yes/no filter)
3. How to erase a table after report has been generated (reporttable)

If this works then I will make one "super form" that has all the input sections in order with buttons to run the query and populate the reporttable in the background. If I get it the way that I want it all you will see at the end is the selections of employees, project info input, and temp employee input with a button that says done...and the report will generate.

 

[speakers_86]

First of, I can see you don't quite get it, though your studies have helped. Attached is a simple version you can build on. Although, you need to realize that even moderate goals in db development can take some time, especially if you are new to this. A simple one can be easy to make, but are more limited in their capabilities. What I have attached only took me 10 minutes, but of course it's not nearly ready for use yet.

 

[KryptoRoxx]

Yeah I am definitely new to this and from looking at your db I was just on the tip of the iceburg. Especially this type of project. The last one that I did was basically equipment attached to an employee id....it was pretty simple. This one is definitely more complex as far as moving data. I think I see your approach to the db though. I am working through your example though. It's going to take me some time to digest it but I think I'm beginning to understand it. Thank you very much for your help btw. I appreciate it greatly. I'm waiting on some books to get here that I ordered for some assistance and reference. Those should help a lot with some of the terminology and some of my concepts I think. I am going to go over this tonight and do some research (not to check you, just to see why you did some things) and then hopefully tomorrow I can ask some intelligent questions. Right now it looks like you use queries to pull the majority of your data and organize it rather than organizing tables with data. It makes sense...but I don't understand it quite yet. I'm grateful for your patience on this as it seems this project is growing in it's comlexities. I think what I have to learn most is that Access can't make the leaps in thought that a human mind can....we can think in almost any process instantaneously and don't need any queries or forms to do it lol.

 

[speakers_86]

Right now it looks like you use queries to pull the majority of your data and organize it rather than organizing tables with data. It makes sense...but I don't understand it quite yet.

Tables only store information about one topic. For instance, tblProjects can have the project id, project name, project start date, and employeeID in charge of the equipment (note I said employee ID!, not employee name, the employee name goes in the employee table).

The query takes two different sources, like tblProjects and tblEmployees and brings related data together. This is how you can get the employee's name next to the information on the project (in most cases its better to leave it as employeeID and use a combo box on a form to show the employee name, but hey, one step at a time!)

edit- While I'm at it, I may as well explain the other two major parts. The form is a pretty way to present the end user with the information and means to input information.

Reports on the other hand are a way to take said information from the form and print it in a professional manor.

While you look at what I uploaded here is what you need to check out. Look at the relationships window. That is where you should always start when looking at a db for the first time. Also, if you look at tblProjects, I gave some brief descriptions on the fields I made. And of course, check out the queries. Notice how queries can be used to create other queries. As you can see there were 2 or 3 queries I did based on another query. To see the progression, start with the query with no number (I don't remember the name). Perhaps it was qryProject. Then qryProject2 uses qryProject for information. More complex queries need to be dragged out into multiple steps, or you'll tend to get errors. There usually not so much errors as mistakes of the designers.

Sorry if I overloaded you.

 

[KryptoRoxx]

You didn't overload me but I had just not thought about organizing data that way. That also being said I hadn't thought about using queries to really organize the data vs. using tables. This helps me greatly and I think I should be able to make some headway on the project. I wish I could devote all my time to it but in the meantime I have to keep doing it the manual way lol. I have got some time tonight though and should be able to make some headway on it. I guess you could say that my eyes have been opened. Now from what I've seen in the queries you first organize the data from the two types of tables and then you start to seperate employees into on the project or not and whether or not they are the project leader and so on. The finished result being a query that brings the results as I would need them (or with some work) into a report. Everything makes sense...but I'm sure that it's going to be some trial and error to get everything working right.

 

[KryptoRoxx]

UPDATE:

I have everything working! well except the project name. I was able to add 3 fields onto the employee table to select them for the project leader and assistant as well as if they were working on the project. I simply used a yes/no column for each of the fields and then used a single form to be able to scroll through the whole employee list and select all the personnel in one swoop which makes things fast for me. I know it's kind of a rough solution but this definitely works for me. Then what I did was a make new table query where it selects all employees that =-1 and voila! All the info I need is populated from my employees. Then I have a form for inputting and editing temps and that addends into the table that is created with the query. That puts them below the actual employees and that's just how I wanted it. Then I just have select querys for the project leader and assistant and all that gets put into the report for display. Now I'm just working on the project name and I think I have a solution for that as well. Thank you for your help. The multiple queries and queries that are built on queries really worked well. I don't have it quite as complex as your solution was but the lightbulb came on over my head and 15 minutes later I have a 90% solution. I can easily streamline it later and make it more fancy but I need to study more access to really do it. Learning has occurred and you have my thanks.

 

[jdraw]

Almost all projects are 90% complete if you ask the developer. You could not over estimate the number of projects/systems that are going to be streamlined/finalized/modernized (with 100 % sincerity) once some sort of workable "temporary setup" actually runs. As speakers_86 has said, it takes time. Reading and learning is one thing; being able to apply the theory is quite another. You need to have a big picture and you need some discipline; and of course will power and commitment.

I have everything working!

is a little scary, after only a few posts and interaction.

For some reference and to keep a big picture view available I would recommend this site; especially the first topics at least.

http://www.rogersaccesslibrary.com/forum/topic238.html

Good luck

 

[KryptoRoxx]

I know what you're saying. People go with if it's not broke, don't fix it. However with this project I have expanded my view of design considerbly and while I know I'm not done learning yet you have to start somewhere. I really appreciate the link though. I am reading through it and it has some very interesting points. I am working on another model of this database in order to practice and teach myself more about Access. I have everything working is a scary statement however the database was fairly simple and with the next version I plan on adding more useability to it. Some of the company that I work for uses Sharepoint and I am interested in tying this into it. Other things that I could add is a automatic filing system for these reports that I make or a tracker for how many projects that they work on in a given time period. I mean the possiblities are endless and all I have to do is learn how to do it. I am a perfectionist by nature and try to push my knowledge to the limit.

 

[jdraw]

I think your response is bang on. You do see a bigger picture, and you do see where the current version fits, and even what the next version should/could do.
You could lay these out as "potential projects'. And, especially when the improved version is working and you get positive feedback, you could suggest some "future efforts" aligned with your potential projects. Having happy users with a system that does what they need; and an improved "module" that expands their abilities/capabilities is key to getting to that bigger picture.