UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Other Way Of A Crosstab, Access 2010    
 
   
habiler
post Dec 19 2017, 09:40 AM
Post#1



Posts: 60
Joined: 6-August 15



Hello,

I have a table with fields named EmployeeNbr and BirthDate.
How can I determine the number of child per worker with age (Birthdate) Lt 12y.
With a SQL or VBA instruction?

Thanks

Habiler

Go to the top of the page
 
GroverParkGeorge
post Dec 19 2017, 09:46 AM
Post#2


UA Admin
Posts: 31,654
Joined: 20-June 02
From: Newcastle, WA


How do you know which employees have children? How do you know which children belong to which employee?

SQL or VBA should work, but it depends on how the data is stored and in which tables.

One might guess that something like this "could" work.

SQL
SELECT EmployeeNbr, Count([Birthdate]) AS CountofBirthdates
FROM tblYourTableNameGoesHere WHERE Birthdate >= DateAdd("yyyy" -12, Date)
GROUP BY EmployeeNbr

--------------------
Go to the top of the page
 
kfield7
post Dec 19 2017, 04:36 PM
Post#3



Posts: 784
Joined: 12-November 03
From: Iowa Lot


I interpreted the question as in average number of children employed per workforce population:

(number of employees under 12 yr old) / (total number of employees).

If I'm on track (that would be unusual) then here's one way:
DSum([EmployeeNbr],"the employee table","(Date() - [Birthdate]) < 12 * 365.25") / DSum([EmployeeNbr],"the employee table")

or


DSum([EmployeeNbr],"the employee table","DateDiff("yyyy",Date(),[Birthdate])< 12") / DSum([EmployeeNbr],"the employee table")

This post has been edited by kfield7: Dec 19 2017, 04:45 PM
Go to the top of the page
 
GroverParkGeorge
post Dec 19 2017, 04:37 PM
Post#4


UA Admin
Posts: 31,654
Joined: 20-June 02
From: Newcastle, WA


That's a more logical interpretation perhaps.


--------------------
Go to the top of the page
 
projecttoday
post Dec 19 2017, 09:19 PM
Post#5


UtterAccess VIP
Posts: 8,823
Joined: 10-February 04
From: South Charleston, WV


You could use DateDiff between the birth date and the current date with the "y" option for the ages in years.

--------------------
Robert Crouser

Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    23rd January 2018 - 07:07 PM