Results 1 to 12 of 12
  1. #1
    Aneta is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Aug 2011
    Posts
    7

    How to show monthly % allocation in database

    Dear All,
    I am an Access beginner and desperately need some help.
    I am trying to create a database which will show who is working on projects. The thing is, usually each person is involved in more than just one project, so I need to be able to show the, for example:
    in August person X was allocated 10% on Project 1 and 80% on Project 1, which means that there is 10% available to be allocated elsewhere.

    I'd need my reports to look more less like the attached example.



    The problem I have is I have no clue as to how to show person’s monthly allocation in %. Do I need and extra table for year 2010, where each entry would be a month to be filled in with % and then make and relationship to a person?

    Please, could you enlighten me?

    Thank you in advance!



    Aneta

  2. #2
    LillMcGill is offline Dagny fan
    Windows 7 64bit Access 2010 64bit
    Join Date
    Oct 2010
    Location
    Southern USA
    Posts
    70
    tblProjects to store attributes of projects. PK might be ProjectID
    tblEmployees to store attributes of employees, including salary. PK might be EmployeeID
    tblEmployeeProjects should contain ProjectID, EmployeeID, a field for the period begin. (Personally, I would use a Date/Time data type and for data entry use the first day of the month.) Also need an Allocation field... Number data type, Field Size=Single. Format=Percent.

    Added: that report looks to be a Crosstab query.
    Last edited by LillMcGill; 08-18-2011 at 11:31 AM. Reason: added

  3. #3
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    If you are reporting on a static year (the month end points do not change) then you can do what you're suggesting without a crosstab query but it's a little bit clunklier but lilmcgill has given you a good basic starting point. You do not want to keep different fiscal (or calendar) years on a separate table, just make sure that (using lilmcgill's example) your tblEmployeeProjects contains the calendar or fiscal year you'll be reporting on.

  4. #4
    Aneta is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Aug 2011
    Posts
    7
    thank you!
    I will work on it today, to see if I can make it work

    A.

  5. #5
    Aneta is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Aug 2011
    Posts
    7

    Question

    Hmmm,
    I created tables and set up the relationships between them (please see attached).
    However, I unable to create a query to get the result split in months (as in my original post).
    Is it because my relationships are wrong/incomplete?
    Could you please advise?

    Thank you in advance!

  6. #6
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    Do not use the name of the person as your linking field, use the STAFFID which I assume is an autonumber field. Never, never, never use a field that can possibly change (one of your employees may get married and change their last name which will orphan all of the data in your projects table AND your employee projects table).

    Second don't use special characters (?, #, %, etc) in your field names or object names they will cause you way more grief than you know. The only exception is the underscore (_).

    Are you reporting on a static year (your month end points do not change, i.e. you are always reporting august through july). Or are you reporting on a rolling year (you want to report on any 12 month period in your database)

    You are carrying a lot of the same information from table to table, the whole point of a relational database is to maintain as little data as possible and still be able to give the results you want. Just as an example you have PercentOnProject in two fields, one of them being the staff field, where it really has no business being. You also have a lot of from-to dates in your tables. When you enter your data for work completed is it on a daily basis or do you? For instance can a person work on several different projects in the same day and you want to enter a number of hours they worked on that project? Or do they only work on one project per day and you want to enter a start and ending time for their work?

    And why does your staff table have four salary data entry fields?

  7. #7
    Aneta is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Aug 2011
    Posts
    7
    Thank you for all your comments! Clearly, I am completely in the dark here


    Quote Originally Posted by rpeare View Post
    Do not use the name of the person as your linking field, use the STAFFID which I assume is an autonumber field. Never, never, never use a field that can possibly change (one of your employees may get married and change their last name which will orphan all of the data in your projects table AND your employee projects table).


    I tried using StaffID originally, but I got an error saying that I can only conncet field of the same format. My StaffID was Autonumber. I couldn't make StaffID in Projects table into autonumber, as I already used Autonumber for ProjectID.
    Thinking about it now, should I use "number"?
    Can I connect autonumber and number?

    Quote Originally Posted by rpeare View Post
    Second don't use special characters (?, #, %, etc) in your field names or object names they will cause you way more grief than you know. The only exception is the underscore (_).
    Will rename them.

    Quote Originally Posted by rpeare View Post
    Are you reporting on a static year (your month end points do not change, i.e. you are always reporting august through july). Or are you reporting on a rolling year (you want to report on any 12 month period in your database)
    My reports will always show year from August to July

    Quote Originally Posted by rpeare View Post
    You are carrying a lot of the same information from table to table, the whole point of a relational database is to maintain as little data as possible and still be able to give the results you want. Just as an example you have PercentOnProject in two fields, one of them being the staff field, where it really has no business being.
    I wasn't sure about that one. I thought that since each person has different % on different PercentOnProjec should belong Staff table. But if you think it shouldn't, I will remove it.

    Quote Originally Posted by rpeare View Post
    You also have a lot of from-to dates in your tables. When you enter your data for work completed is it on a daily basis or do you? For instance can a person work on several different projects in the same day and you want to enter a number of hours they worked on that project? Or do they only work on one project per day and you want to enter a start and ending time for their work?
    The purpose of this database is primarily is for planning, to show who is working where and on which %. SO i can see if we have some available resources, and also to check if we didn't over allocate a person. I don't need daily information, as long as I can set up my report to show me person's (or project's) allocation per month in one year (Aug-Jul).
    From-to-dates in Project table serves to show me how long each project lasts. So when I do the report it will only show me projects that are included within that year.
    From-to-dates in Staff table serves to show me when someone's contract finishes, so I don't include this person in my planning after they finished.

    Quote Originally Posted by rpeare View Post
    And why does your staff table have four salary data entry fields?
    Most of my planning is for consecutive years.
    Each year person's salary changes (e.g. increase by inflation or promotion). I also want to know how much money the project will cover for each person, each year if there is a certain % of this person on the project.


    Phew, hope that clears some of the issues.
    Once again thank you very much for looking into this!

  8. #8
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    Each table should have only one autonumber field, in your database your staff table should have a staffID autonumberfield, your projects a projectID field and your staffprojects a SPID. When you use the STAFFID in your STAFFONPROJECTS table you set it to NUMBER not AUTONUMBER. You will not be carrying your STAFFID in the PROJECTS Table, there's no valid reason to because each project can have multiple staff attached to it.

    As for the salaries, what you're doing is just going to give you a huge headache.

    What I would have is a table that contained the STAFFID, a starting date, an ending date, and their salary. Every time their salary changes you add a new record (leave the most recent record's end date blank because you don't know the end date) and update previous record to show the appropriate end date. Then you would have to do some calcualting to figure their salary, basically you'd divide the number of days they were at the previous salary in a given year and take a percentage of their salary commesurate with the number of days they worked at that salary then do the same for the 'new' salary. The easier way to handle this is if you figure out their salary for a given fiscal year by hand and put that in your database, you'd basically be doing this calculation by hand for each employee each year but if you're uncomfortable with the programming end it may be your best solution

    something like STAFFID FY Salary.

    Your needs are relatively simple to handle so I'm enclosing a simple database example. There are other fields you're interested in I've only included the ones that are necessary for the output you're stating (I just produce an individual report, but the summary for the year is pretty much the same in setup) Just open up the forms frmReports choose mickey mouse and fy 2011 then click the button. Don't be intimidated by the data I'm storing in the tblStaffProjects. Most of that data can be selected once and be carried through for all of your data entry (i.e. you're doing your data entry once a month so you can set up your data entry forms to select the month you're doing data entry for and it will carry through everything you enter so you don't have to enter it over and over)

  9. #9
    Aneta is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Aug 2011
    Posts
    7
    OH! Thank you so much!!!
    You are so fantastic!


    I will look into it now

  10. #10
    Aneta is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Aug 2011
    Posts
    7
    Hi again,

    I looked at the file you so kindly created for me, as I wanted to see how you get the result table I wanted.
    However, it seems I cannot see the relationships between tables.
    Is there any other way than just go to Database Tools/Relationships?

    Many thanks,
    Aneta

  11. #11
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    I generally do not add relationships to my tables (it's a poor practice on my part) so there aren't any in that database I don't think. You can see the relationships in the query though and you can generally tell which table is the ONE side of a relationship and which side is the MANY by which way the arrow points in my query design. If the arrow is pointing TO a table that's the Many side of the relationship.

  12. #12
    Aneta is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Aug 2011
    Posts
    7
    I didn't realise that you can create a query without establishing relationships first.
    Another new thing learnt

    Thank you so very much!!!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Access Database no show?
    By Stealth in forum Access
    Replies: 9
    Last Post: 08-14-2011, 02:03 PM
  2. Trouble with sorting monthly
    By Alliana Gray in forum Access
    Replies: 8
    Last Post: 08-10-2011, 02:44 PM
  3. Monthly Programming
    By ekaragew in forum Programming
    Replies: 1
    Last Post: 06-21-2011, 05:37 PM
  4. Is weekly / monthly automation possible?
    By 10 Gauge in forum Access
    Replies: 4
    Last Post: 03-17-2011, 07:23 AM
  5. Query + monthly report
    By tareksul in forum Reports
    Replies: 3
    Last Post: 12-19-2010, 01:09 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums