SQL/MS Acess Problem

[agahnim]

Is there anyway that you can write a query that averages people's birthdates? I tried just averaging the dates and it just gives me a large number that is not in a date format.

 

[Saynac]

If you post the schemata of your target database and exactly what you want, it would be much easier to help you. I believe you can run AVG() functions in Access.

 

[agahnim]

It's an employee database with a department, employees, and family members tables. The family memebers table has the dates in it and I am trying to average the dates and group them by department.

Here is the SQL code that I have so far that doesn't work:

SELECT Employees.DepartmentName, Avg(FamilyMembers.FamilyMemberDateOfBirth) AS AvgOfFamilyMemberDateOfBirth
FROM Employees INNER JOIN FamilyMembers ON Employees.EmpID = FamilyMembers.EmpID
GROUP BY Employees.DepartmentName;

Thanks for the help.

 

[Saynac]

Looks fine to me so far. What data type is the FamilyMemberDateOfBirth field?

 

[agahnim]

it's the date/time data type im getting averages of like 21380 though doesn't seem right

 

[Saynac]

Doh. You can't use AVG on date/time fields, I believe. You can, however, split the month, day and year out individually and avg them. I'll whip up a quick SQL statement and test it. If it works on my machine, I'll post it here in a couple minutes.

 

[EagleKeeper]

That number may be correct. Can you convert it into the Date/time format

And no, I do not know how to within Access itself.

 

[agahnim]

Cool thanks, the instructor says to show the average age date, so I am assuming he wants the whole date and not an age. It's not very clear on the directions. Thanks again.

 

[agahnim]

Yea I was wondering if you could format the data to but one of the averages is 29953.5 not sure if that could be an acceptbale date or not.

 

[Saynac]

Okay, Agahnim, here is the SQL. It might be a little easier to use some built-in tools for Access but I never touch that stuff, I just know the SQL.

SELECT Employees.DepartmentName, (Str(Int(Avg(DatePart("m", FamilyMembers.FamilyMemberDateOfBirth)))) & '/' & Str(Int(Avg(DatePart("d", FamilyMembers.FamilyMemberDateOfBirth)))) & '/' & Str(Int(Avg(DatePart("yyyy", FamilyMembers.FamilyMemberDateOfBirth))))) AS AvgOfFamilyMemberDateOfBirth
FROM Employees INNER JOIN FamilyMembers ON Employees.EmpID = FamilyMembers.EmpID
GROUP BY Employees.DepartmentName;


It is ugly but it works on my machine. You might want to replace the ampersands with + signs.

Just to explain it, the DatePart function rips a certain piece of the date out. "m" as a argument will get the month, "d" the day and "yyyy" the four character year. Then, just average those results and turn them into an integer (to round the number, since AVG returns a decimal as well i.e. 12.6666667). Finally it just converts them to a string. Enjoy!

** EDIT **

Excellent! Glad to see it work, Agahnim. Have a great weekend and stay away from "The Flu".

 

[agahnim]

Sweet it worked thanks for your help and time I really appreciate it!

 

[agahnim]

Yea I read on one our local news sites here in Kansas and it says half the population has the flu, and a couple of people have died just recently.