Expression help needed

  • Thread starter Thread starter ken
  • Start date Start date
K

ken

I have a table with fields defiened as callID, hrs, min and task.
Some of the data in the fields are:
41 4 15 hq
41 2 0 hq
42 4 0 oc
The field is for an employee to list the amount of time spent on a specific
task. The date that the task is logged in is in another table. I need to get
the effort expended per month for a specific task. How can I do a query to
get the results like

HQ
month effort
Feb 6hr 15 min

OC
month effort
Feb 4hr 00min

While sill learnning access this newsgroup has helped me alot.
Thanks
Ken
 
You might try a query whose SQL looks something like this:

SELECT
[Your Table].[task],
Year([Your Other Table].[Date Logged]) AS [Year],
Month([Your Other Table].[Date Logged]) AS [Month],
Int(Sum([Your Table].[hrs] * 60 + [Your Table].[min])/60) AS [hrs],
Int(Sum([Your Table].[hrs] * 60 + [Your Table].[min]) Mod 60) AS [min]
FROM
[Your Table]
INNER JOIN
[Your Other Table]
ON
[Your Table].[callID] = [Your Other Table].[callID]
GROUP BY
[Your Table].[task],
Year([Your Other Table].[Date Logged]) AS [Year],
Month([Your Other Table].[Date Logged]) AS [Month]

This assumes:

1. Your table is named "Your Table".

2. Your other table is named "Your Other Table" and has a 1-to-many
relationship with "Your Table" on callID.

3. Your other table has a Date/Time field named "Date Logged".
 
I am getting a Syntax error on the following line Year([calls].[openDate])
AS [Year]. It is not liking the AS.
For my tables. the Calls table has the OpenDate and the Notes table has the
task, hr, min and callID with a One to Many Relation to callId in Calls.

Thanks
Ken

Brian Camire said:
You might try a query whose SQL looks something like this:

SELECT
[Your Table].[task],
Year([Your Other Table].[Date Logged]) AS [Year],
Month([Your Other Table].[Date Logged]) AS [Month],
Int(Sum([Your Table].[hrs] * 60 + [Your Table].[min])/60) AS [hrs],
Int(Sum([Your Table].[hrs] * 60 + [Your Table].[min]) Mod 60) AS [min]
FROM
[Your Table]
INNER JOIN
[Your Other Table]
ON
[Your Table].[callID] = [Your Other Table].[callID]
GROUP BY
[Your Table].[task],
Year([Your Other Table].[Date Logged]) AS [Year],
Month([Your Other Table].[Date Logged]) AS [Month]

This assumes:

1. Your table is named "Your Table".

2. Your other table is named "Your Other Table" and has a 1-to-many
relationship with "Your Table" on callID.

3. Your other table has a Date/Time field named "Date Logged".

ken said:
I have a table with fields defiened as callID, hrs, min and task.
Some of the data in the fields are:
41 4 15 hq
41 2 0 hq
42 4 0 oc
The field is for an employee to list the amount of time spent on a specific
task. The date that the task is logged in is in another table. I need to get
the effort expended per month for a specific task. How can I do a query to
get the results like

HQ
month effort
Feb 6hr 15 min

OC
month effort
Feb 4hr 00min

While sill learnning access this newsgroup has helped me alot.
Thanks
Ken
Click to expand...
Click to expand...
 
Given your table and field names, the SQL might look like this:

SELECT
Notes.task,
Year(Calls.Date Logged) AS Year,
Month(Calls.Date Logged) AS Month,
Int(Sum(Notes.hrs * 60 + Notes.min)/60) AS hrs,
Int(Sum(Notes.hrs * 60 + Notes.min) Mod 60) AS min
FROM
Notes
INNER JOIN
Calls
ON
Notes.callID = Calls.callID
GROUP BY
Notes.task,
Year(Calls.Date Logged),
Month(Calls.Date Logged)

The SQL in my previous post incorrectly had AS in the GROUP BY clause, which
may have cause the problem.

ken said:
I am getting a Syntax error on the following line Year([calls].[openDate])
AS [Year]. It is not liking the AS.
For my tables. the Calls table has the OpenDate and the Notes table has the
task, hr, min and callID with a One to Many Relation to callId in Calls.

Thanks
Ken

Brian Camire said:
You might try a query whose SQL looks something like this:

SELECT
[Your Table].[task],
Year([Your Other Table].[Date Logged]) AS [Year],
Month([Your Other Table].[Date Logged]) AS [Month],
Int(Sum([Your Table].[hrs] * 60 + [Your Table].[min])/60) AS [hrs],
Int(Sum([Your Table].[hrs] * 60 + [Your Table].[min]) Mod 60) AS [min]
FROM
[Your Table]
INNER JOIN
[Your Other Table]
ON
[Your Table].[callID] = [Your Other Table].[callID]
GROUP BY
[Your Table].[task],
Year([Your Other Table].[Date Logged]) AS [Year],
Month([Your Other Table].[Date Logged]) AS [Month]

This assumes:

1. Your table is named "Your Table".

2. Your other table is named "Your Other Table" and has a 1-to-many
relationship with "Your Table" on callID.

3. Your other table has a Date/Time field named "Date Logged".

ken said:
I have a table with fields defiened as callID, hrs, min and task.
Some of the data in the fields are:
41 4 15 hq
41 2 0 hq
42 4 0 oc
The field is for an employee to list the amount of time spent on a specific
task. The date that the task is logged in is in another table. I need
Click to expand...
to
get
the effort expended per month for a specific task. How can I do a
Click to expand...
query
to
get the results like

HQ
month effort
Feb 6hr 15 min

OC
month effort
Feb 4hr 00min

While sill learnning access this newsgroup has helped me alot.
Thanks
Ken
Click to expand...
Click to expand...
Click to expand...
 
That works great. Thanks for all the help.
Brian Camire said:
Given your table and field names, the SQL might look like this:

SELECT
Notes.task,
Year(Calls.Date Logged) AS Year,
Month(Calls.Date Logged) AS Month,
Int(Sum(Notes.hrs * 60 + Notes.min)/60) AS hrs,
Int(Sum(Notes.hrs * 60 + Notes.min) Mod 60) AS min
FROM
Notes
INNER JOIN
Calls
ON
Notes.callID = Calls.callID
GROUP BY
Notes.task,
Year(Calls.Date Logged),
Month(Calls.Date Logged)

The SQL in my previous post incorrectly had AS in the GROUP BY clause, which
may have cause the problem.

ken said:
I am getting a Syntax error on the following line Year([calls].[openDate])
AS [Year]. It is not liking the AS.
For my tables. the Calls table has the OpenDate and the Notes table has the
task, hr, min and callID with a One to Many Relation to callId in Calls.

Thanks
Ken

Brian Camire said:
You might try a query whose SQL looks something like this:

SELECT
[Your Table].[task],
Year([Your Other Table].[Date Logged]) AS [Year],
Month([Your Other Table].[Date Logged]) AS [Month],
Int(Sum([Your Table].[hrs] * 60 + [Your Table].[min])/60) AS [hrs],
Int(Sum([Your Table].[hrs] * 60 + [Your Table].[min]) Mod 60) AS [min]
FROM
[Your Table]
INNER JOIN
[Your Other Table]
ON
[Your Table].[callID] = [Your Other Table].[callID]
GROUP BY
[Your Table].[task],
Year([Your Other Table].[Date Logged]) AS [Year],
Month([Your Other Table].[Date Logged]) AS [Month]

This assumes:

1. Your table is named "Your Table".

2. Your other table is named "Your Other Table" and has a 1-to-many
relationship with "Your Table" on callID.

3. Your other table has a Date/Time field named "Date Logged".

I have a table with fields defiened as callID, hrs, min and task.
Some of the data in the fields are:
41 4 15 hq
41 2 0 hq
42 4 0 oc
The field is for an employee to list the amount of time spent on a
specific
task. The date that the task is logged in is in another table. I
Click to expand...
Click to expand...
need
to
Click to expand...
Click to expand...
 
Back
Top