Group by, sum, and Datediff questions regarding a query

[Guest]

This is what I have tried. I have created a query that contains
WatchResults.ChairsID, Employee.LastName, Employee.FirstName,
WatchResults.DateWorked, WatchResults.OvertimeHours.

I want only unique employee LastNames to appear in the query. I tried to add
group by to ChairsID, but i get an error reading Data Type MisMatch in
Criteria Expression. I then thought I could add "sum" to the criteria of
Overtime Hours to sum up the hours according to the unique ChairsID, but that
did nothing.

Finally, I tried to add criteria to DateWorked field, placing this in the
criteria:
DateDiff('yyyy',1/1/2004,Date()) ...
In order to try get year to date overtime hours by employee..

Now if someone could tell my why this isn't working, I would appreicate it.
Thanks in advance.

 

[John Vinson]

This is what I have tried. I have created a query that contains
WatchResults.ChairsID, Employee.LastName, Employee.FirstName,
WatchResults.DateWorked, WatchResults.OvertimeHours.

I want only unique employee LastNames to appear in the query.

So if you have two employees who both happen to be named Smith, what
will you do? Fire one? Group by the unique EmployeeID, surely!

I tried to add
group by to ChairsID, but i get an error reading Data Type MisMatch in
Criteria Expression. I then thought I could add "sum" to the criteria of
Overtime Hours to sum up the hours according to the unique ChairsID, but that
did nothing.

You can... but you can't show the employee names as well. The query
can be grouped by employee, or it can be grouped by ChairID, but it
can't be grouped by both simultaneously.

Finally, I tried to add criteria to DateWorked field, placing this in the
criteria:
DateDiff('yyyy',1/1/2004,Date()) ...
In order to try get year to date overtime hours by employee..

This will return the number 0 for any date in 2004, since that is the
difference in years between a date in 2004 and another date in 2004.

Now if someone could tell my why this isn't working, I would appreicate it.
Thanks in advance.

GUESSING at what you want - i.e. assuming you want to group by
ChairsID, LastName and FirstName and sum OvertimeHours:

SELECT ChairsID, LastName, FirstName,
Sum([OvertimeHours]) AS TotalOvertime
FROM Yourtable
WHERE Year([DateWorked]) = 2004
GROUP BY ChairsID, LastName, FirstName;

Copy and paste this SQL into the SQL window of a new query; change the
table name to your table's name; and see if that does what you want.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[Guest]

Ok.. I'm headed down the right path now. One this is that ChairsID is the
foreignkey in the WatchResults table and Primary Key in the Employee table,
therefore query isn't working because it does not know where to grab the
ChairsID from. I want to to group the chairsID's together that are in the
WatchResults table.

Also, how would i i set the date so it equalled this quarter, month, week,
etc?

This is what I have tried. I have created a query that contains
WatchResults.ChairsID, Employee.LastName, Employee.FirstName,
WatchResults.DateWorked, WatchResults.OvertimeHours.

I want only unique employee LastNames to appear in the query.

So if you have two employees who both happen to be named Smith, what
will you do? Fire one? Group by the unique EmployeeID, surely!

I tried to add
group by to ChairsID, but i get an error reading Data Type MisMatch in
Criteria Expression. I then thought I could add "sum" to the criteria of
Overtime Hours to sum up the hours according to the unique ChairsID, but that
did nothing.

You can... but you can't show the employee names as well. The query
can be grouped by employee, or it can be grouped by ChairID, but it
can't be grouped by both simultaneously.

Finally, I tried to add criteria to DateWorked field, placing this in the
criteria:
DateDiff('yyyy',1/1/2004,Date()) ...
In order to try get year to date overtime hours by employee..

This will return the number 0 for any date in 2004, since that is the
difference in years between a date in 2004 and another date in 2004.

Now if someone could tell my why this isn't working, I would appreicate it.
Thanks in advance.

GUESSING at what you want - i.e. assuming you want to group by
ChairsID, LastName and FirstName and sum OvertimeHours:

SELECT ChairsID, LastName, FirstName,
Sum([OvertimeHours]) AS TotalOvertime
FROM Yourtable
WHERE Year([DateWorked]) = 2004
GROUP BY ChairsID, LastName, FirstName;

Copy and paste this SQL into the SQL window of a new query; change the
table name to your table's name; and see if that does what you want.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[Guest]

I went in to edit the query in design view and connected the tables to form a
relationship and made sure ChairsID was coming from the WatchResults table.
However when i try to run the querry, it asks me to enter overtime hours and
dateworked in a parameter box.

I want the user to have to enter a chairs ID and the overtimehours over a
specific time period will show up.

Thanks in advance!

This is what I have tried. I have created a query that contains
WatchResults.ChairsID, Employee.LastName, Employee.FirstName,
WatchResults.DateWorked, WatchResults.OvertimeHours.

I want only unique employee LastNames to appear in the query.

So if you have two employees who both happen to be named Smith, what
will you do? Fire one? Group by the unique EmployeeID, surely!

I tried to add
group by to ChairsID, but i get an error reading Data Type MisMatch in
Criteria Expression. I then thought I could add "sum" to the criteria of
Overtime Hours to sum up the hours according to the unique ChairsID, but that
did nothing.

You can... but you can't show the employee names as well. The query
can be grouped by employee, or it can be grouped by ChairID, but it
can't be grouped by both simultaneously.

Finally, I tried to add criteria to DateWorked field, placing this in the
criteria:
DateDiff('yyyy',1/1/2004,Date()) ...
In order to try get year to date overtime hours by employee..

This will return the number 0 for any date in 2004, since that is the
difference in years between a date in 2004 and another date in 2004.

Now if someone could tell my why this isn't working, I would appreicate it.
Thanks in advance.

GUESSING at what you want - i.e. assuming you want to group by
ChairsID, LastName and FirstName and sum OvertimeHours:

SELECT ChairsID, LastName, FirstName,
Sum([OvertimeHours]) AS TotalOvertime
FROM Yourtable
WHERE Year([DateWorked]) = 2004
GROUP BY ChairsID, LastName, FirstName;

Copy and paste this SQL into the SQL window of a new query; change the
table name to your table's name; and see if that does what you want.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[Guest]

Sorry for the redundent posts, but i forgot the code. This is what i now have:
SELECT WatchResults.ChairsID, Employee.LastName, Employee.FirstName,
Sum([OvertimeHours]) AS TotalOvertime
FROM Employee INNER JOIN WatchResults ON Employee.ChairsID =
WatchResults.ChairsID
WHERE (((Year([DateWorked]))=2004))
GROUP BY WatchResults.ChairsID, Employee.LastName, Employee.FirstName;

This is what I have tried. I have created a query that contains
WatchResults.ChairsID, Employee.LastName, Employee.FirstName,
WatchResults.DateWorked, WatchResults.OvertimeHours.

I want only unique employee LastNames to appear in the query.

So if you have two employees who both happen to be named Smith, what
will you do? Fire one? Group by the unique EmployeeID, surely!

I tried to add
group by to ChairsID, but i get an error reading Data Type MisMatch in
Criteria Expression. I then thought I could add "sum" to the criteria of
Overtime Hours to sum up the hours according to the unique ChairsID, but that
did nothing.

You can... but you can't show the employee names as well. The query
can be grouped by employee, or it can be grouped by ChairID, but it
can't be grouped by both simultaneously.

Finally, I tried to add criteria to DateWorked field, placing this in the
criteria:
DateDiff('yyyy',1/1/2004,Date()) ...
In order to try get year to date overtime hours by employee..

This will return the number 0 for any date in 2004, since that is the
difference in years between a date in 2004 and another date in 2004.

Now if someone could tell my why this isn't working, I would appreicate it.
Thanks in advance.

GUESSING at what you want - i.e. assuming you want to group by
ChairsID, LastName and FirstName and sum OvertimeHours:

SELECT ChairsID, LastName, FirstName,
Sum([OvertimeHours]) AS TotalOvertime
FROM Yourtable
WHERE Year([DateWorked]) = 2004
GROUP BY ChairsID, LastName, FirstName;

Copy and paste this SQL into the SQL window of a new query; change the
table name to your table's name; and see if that does what you want.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[John Vinson]

Sorry for the redundent posts, but i forgot the code. This is what i now have:

SELECT WatchResults.ChairsID, Employee.LastName, Employee.FirstName,
Sum([OvertimeHours]) AS TotalOvertime
FROM Employee INNER JOIN WatchResults ON Employee.ChairsID =
WatchResults.ChairsID
WHERE (((Year([DateWorked]))=2004))
GROUP BY WatchResults.ChairsID, Employee.LastName, Employee.FirstName;

This ought to be working, if in fact ChairsID is the PK of Employees
and a nonunique foreign key in WatchResults. What's coming out wrong?


John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps