Results 1 to 12 of 12
  1. #1
    Star Lounger
    Join Date
    May 2002
    Posts
    69
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Access Headcount Report (Access2000)

    Okay you Access Masters, I think this may be the challenge your looking for...

    I have a table with the following fields:
    Tier1, Tier2,Tier3,Tier4, ABC,DEF,GHI,JKL,DeptNumber, Part-time, Full-time, Temp

    I need to create a headcount report based on this data. However, the report should display all of the data into certain categories so I created a function called Financial:[DEF}+[GHI] to create the "Financial Category" and so on. Now the problem is the following:

    I need to count how many part-timers there are for each of the categories I created . Such as for the Financial group how many part-timers there are and so on. But, How do I do that?

    AND, after all categories and groups are done, I need to group the data in different groups based on the dept. Such as:
    Dept 1000 should display by Tier 1 and Tier3
    Dept 2000 should display by Tier 4 and Tier 2 etc.

    Please let me know if you need anymore info. Any help will be greatly appreciated..
    <img src=/S/brickwall.gif border=0 alt=brickwall width=25 height=15>

  2. #2
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    USA
    Posts
    379
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access Headcount Report (Access2000)

    I'm not clear on why you have three fields to account for PartTime, FullTime and Temp. I would think one field would do it with an entry of either P,F or T. Nonetheless, in order to count how many part-timers there are for each of the categories you need to use something similar to the Financial Function you created, but only count those where your field PartTime = your PartTime value. It would be helpful if you posted the code for this function.

    For the second question, I don't understand the values of theTier fields. What do they contain?

  3. #3
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Access Headcount Report (Access2000)

    Quite frankly, I don't understand your situation at all. Perhaps you could give an example?
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  4. #4
    Star Lounger
    Join Date
    May 2002
    Posts
    69
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access Headcount Report (Access2000)

    Sorry for the confusion...
    I have a table that looks like the following:

    Tier 1 Tier2 Tier3 Tier 4 dept BMP PFA FA BMN PTA
    9000 9564 3456 6322 1000 4 3 6 5 7
    9123 6667 5555 1212 0005 3 5 6 3 2

    I had to group and sum the BMP's, PFA's and BPA's as "Financial Advisors" using the function:
    Financial Advisors: Sum([HeadcountTable]![BMP ]+[HeadcountTable]![PFA ]+[HeadcountTable]![FA])
    now out of that function I need to know how many Financial Advisors are part-time. Partimers are the sum of the PFA's and PTA's which I used an IIF statement: IIF([QryHeadcountTable]![Financial Advisors]>0, sum([HeadcountTable]![PFA]+[HeadcountTable]![PTA])). But it doesn't work... [img]/forums/images/smilies/sad.gif[/img]

    The report should look like this for the above example

    Tier 1- 9000
    Tier3 -3456
    Total Part-time
    Financial Advisors 13 3

    Hope this makes a little more sense and thanks for your help!

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Access Headcount Report (Access2000)

    Your biggest problem is your table design ... or maybe just in the field names in your example. If this table the result of a maketable query?

    Just because you want to see the data that way is no reason to store it that way if that's what you're doing. Fields like Tier1, Tier2 ...etc. raise all kinds of red flags with us relational folk because they suggest an unnormalized table. A more normalized structure would look something like this:

    TierNo TierValue Dept Role
    1 9000 1000 BMP
    2 9564 1000 BMP

    etc.

    Since I haven't a clue as to what the numbers mean, this might not make sense, but it's far easier to sum data in this structure than in a flat table. If that's already what you're doing and this is the result of a maketable, then do your summation on the original table instead of this one.
    Charlotte

  6. #6
    Star Lounger
    Join Date
    May 2002
    Posts
    69
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access Headcount Report (Access2000)

    Using a "Normal Table" example, here it is...

    I have a table that looks like the following:

    Division | Group | Unit | Dept | total # Employees| # of Professional | # of Clerical| # of Temps| Associates
    2013 | 2345 | 1800 | 1000 | 20 | 10 | 5| 3| 2
    2456 | 2600 | 2489 | 2000| 30 | 10 | 10 | 5| 5

    For dept 1000, I need to sum the number of Financial Advisors which are defined as the sum of "Professionals", "Associates" and "Clerical", so I used the following function : Financial Advisors:[professional]+[Clerical]+[Associates] for a total of 17. Now my problem is:

    Out of the 17 in the Financial Advisors I just created, I need to calculate how many are part-timers there are. Part-timers are defined as those in "Professional" and Associates" column. Therefore I need to create a function which will calculate the number of professionals and associates in the Financial Advisors group for a total of 12. Therefore the result should look like the following:

    Dept | Financial Advisors(sum of Professional + Associates+ Clerical)|Part-Time (Sum of professionals+Associates)
    1000 | 17 | 12

    Does anyone know how I can perform this calculation?

  7. #7
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Access Headcount Report (Access2000)

    Well, you aren't to normal yet in your table design, but I'm not sure where the actual data is coming from. Is total # Employees always the sum of [Profession] + [Clerical] + [Temps] + [Associates]? You don't ordinarily store values that can be calculated, so if it is the sum of the other values, what's it there for?

    You can easily query a table like this and create a calculated value that totals all the category fields for that record. Then you create another calculated value that sums the Professionals + the Associates to give you the part-timers.
    Charlotte

  8. #8
    Star Lounger
    Join Date
    May 2002
    Posts
    69
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access Headcount Report (Access2000)

    That's what I need your help with, the formula that calculates the number of part-timers in the "Financial Advisors" category I created using the following function: Financial Advisors: ([Field1+Field2+Field3]), but how do I create another formula that can count the number of part-timers within that function?

  9. #9
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Access Headcount Report (Access2000)

    Another column in the query:

    PartTimers: [Field1]+[Field3]
    Charlotte

  10. #10
    Star Lounger
    Join Date
    May 2002
    Posts
    69
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access Headcount Report (Access2000)

    That function is just looking for part timers, but, what I need is the number of "part time financial advisors"

  11. #11
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Access Headcount Report (Access2000)

    It isn't a function. If you can define "part time financial advisors" as this field plus that field, etc., then you can calculate it in an expression in a query. Otherwise, you can't calculate it from the data you described. There isn't any way to tell a query to take a portion of summary data. That was my original point. When you store summary data, it's not really possible to extract the other information you might want.
    Charlotte

  12. #12
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Access Headcount Report (Access2000)

    I guess I'm really confused here. It seems to me that the answer Charlotte gave you should give you the result you are looking for. You said:

    <<<font color=448800>Out of the 17 in the Financial Advisors I just created, I need to calculate how many are part-timers there are. Part-timers are defined as those in "Professional" and Associates" column. Therefore I need to create a function which will calculate the number of professionals and associates in the Financial Advisors group for a total of 12. </font color=448800>>>

    So what is wrong with her reply of:

    <<<font color=blue>Another column in the query:

    PartTimers: [Field1]+[Field3]</font color=blue>
    >>?

    Isn't the exact formula you want PartTimeFinAdvs: [Professional] + [Associates] as new column in your query grid? I assume that by definition, a Professional is a Financial Advisor who is Part Time, and the same is true of Associates.
    Wendell

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •