Access query

[Vistaz]

hi there,

I am confused here, please help me out.

1. I have table with Date of birth, Occupational level etc. i need to calculate the age as it today.
2. i have calculated the age now i need to group by occupational level, and for each age mybe 5 people have 57 years. i need to calculate the percentage for that number of employees within that age.

here is my query:

SELECT Age_Anaylsis.OccpLvl, (Age_Anaylsis.Age) As Age, Count(Age_Anaylsis.Age) AS Number_Of_Employees
FROM Age_Anaylsis
GROUP BY Age_Anaylsis.OccpLvl, Age_Anaylsis.Age;

[rpeare]

if you're determined to do this in a query as opposed to a report or a form (where it's easier to deal with) you'll have to use domain functions (look at dsum, dcount, dmax, dmin, etc)

in your case you'd have another field that's dcount("*", "[EmployeeTable]") as TotalEmployees.

If you want a criteria on your dcount, for instance let's say you have an 'active' flag for people who are currently employed only you'd have something like

dcount("*", "[EmployeeTable]", "[ActiveFlag] = 'A'") as TotalEmployees

Just be very, very cautious about using domain functions in queries, they are incredibly memory intensive because basically for every record of your query it is performing the exact same function over and over and over. Domain functions can be very handy but they will bloat your database pretty quickly especially with larger datasets.