Add a field in a query. Easy. . .so I though.

  • Thread starter Thread starter Flopbot
  • Start date Start date
F

Flopbot

I think that I’m missing something really easy here. I have a query drawn
from an “Volunteer Hours Tracking Table†that returns the total number of
hours worked by those volunteers who came in during the past month. To do
this it uses the equation: (Sum((DateDiff("n",[Start Time],[End
Time])/60)*[Number of People])) I have since added another field to the
original table labeled [Other Hours]. I want to ADD the [Other Hours] field
to my original equation in this query. I’ve already done it on my main form
but haven’t been able to do it in my query.

Can anyone see the error of my ways?



SQL View:

SELECT [Volunteer Hours Tracking Table].[Volunteer ID],
(Sum((DateDiff("n",[Start Time],[End Time])/60)*[Number of People])) AS Total
FROM [Volunteer Info Table] INNER JOIN [Volunteer Hours Tracking Table] ON
[Volunteer Info Table].[Volunteer ID] = [Volunteer Hours Tracking
Table].[Volunteer ID]
WHERE ((([Volunteer Hours Tracking Table].[Date
Worked])>DateAdd("m",-1,Date())))
GROUP BY [Volunteer Hours Tracking Table].[Volunteer ID], [Volunteer Hours
Tracking Table].[Other Hours];
 
I only see the field in the GROUP BY. How did you want it to be included?
What data does it contain and does it relate to the number of minutes you
extract from [Start Time] - [End Time] ?
 
Right. your question made me reconsider my query and what’s actually going
on. To answer your question, the original equation returns the number of
hours worked by a volunteer if they came in at X and left at Y. The [Other
Hours] field is a catch-all field for when someone comes in for Z hours (ex.
9) but I don’t necessarily know what their start and end time is.


Example:

ID ~ Date Volunteered ~ Calculated Hours ~ [Other Hours]
#1 ~ July 24, 2009 ~ “2 hours†~ “9 hoursâ€
#2 ~ July 22, 2009 ~ “4 hours†~ “0 hoursâ€
#1 ~ August 30, 2008 ~ “4 hours†~ “0 hoursâ€
My query should show that in the past month #1 worked 11 hours and #2 worked
4 hours.



I completely took [Other Hours] and any other non-essential fields off the
query until I hear back from you. They were confusing me. If it matters,
the [Other Hours] field is on the same table as the [Start Time] & [End Time]
fields.


My New SQL:

SELECT [Volunteer Hours Tracking Table].[Volunteer ID],
(Sum((DateDiff("n",[Start Time],[End Time])/60)*[Number of People])) AS Total
FROM [Volunteer Info Table] INNER JOIN [Volunteer Hours Tracking Table] ON
[Volunteer Info Table].[Volunteer ID] = [Volunteer Hours Tracking
Table].[Volunteer ID]
WHERE ((([Volunteer Hours Tracking Table].[Date
Worked])>DateAdd("m",-1,Date())))
GROUP BY [Volunteer Hours Tracking Table].[Volunteer ID];




KARL DEWEY said:
I only see the field in the GROUP BY. How did you want it to be included?
What data does it contain and does it relate to the number of minutes you
extract from [Start Time] - [End Time] ?

--
Build a little, test a little.


Flopbot said:
I think that I’m missing something really easy here. I have a query drawn
from an “Volunteer Hours Tracking Table†that returns the total number of
hours worked by those volunteers who came in during the past month. To do
this it uses the equation: (Sum((DateDiff("n",[Start Time],[End
Time])/60)*[Number of People])) I have since added another field to the
original table labeled [Other Hours]. I want to ADD the [Other Hours] field
to my original equation in this query. I’ve already done it on my main form
but haven’t been able to do it in my query.

Can anyone see the error of my ways?



SQL View:

SELECT [Volunteer Hours Tracking Table].[Volunteer ID],
(Sum((DateDiff("n",[Start Time],[End Time])/60)*[Number of People])) AS Total
FROM [Volunteer Info Table] INNER JOIN [Volunteer Hours Tracking Table] ON
[Volunteer Info Table].[Volunteer ID] = [Volunteer Hours Tracking
Table].[Volunteer ID]
WHERE ((([Volunteer Hours Tracking Table].[Date
Worked])>DateAdd("m",-1,Date())))
GROUP BY [Volunteer Hours Tracking Table].[Volunteer ID], [Volunteer Hours
Tracking Table].[Other Hours];
Click to expand...
Click to expand...
 
You should use a left join.
The [Number of People] implies that the individual volunteer brought others
with them to work. Should not the others be entered under their own ID?

Try the query below --
SELECT [Volunteer Hours Tracking Table].[Volunteer ID],
(Sum((DateDiff("n",[Start Time],[End Time])/60)*[Number of People])) +
Sum([Other Hours]) AS Total
FROM [Volunteer Info Table] LEFT JOIN [Volunteer Hours Tracking Table] ON
[Volunteer Info Table].[Volunteer ID] = [Volunteer Hours Tracking
Table].[Volunteer ID]
WHERE ((([Volunteer Hours Tracking Table].[Date
Worked])>DateAdd("m",-1,Date())))
GROUP BY [Volunteer Hours Tracking Table].[Volunteer ID];

--
Build a little, test a little.


Flopbot said:
Right. your question made me reconsider my query and what’s actually going
on. To answer your question, the original equation returns the number of
hours worked by a volunteer if they came in at X and left at Y. The [Other
Hours] field is a catch-all field for when someone comes in for Z hours (ex.
9) but I don’t necessarily know what their start and end time is.


Example:

ID ~ Date Volunteered ~ Calculated Hours ~ [Other Hours]
#1 ~ July 24, 2009 ~ “2 hours†~ “9 hoursâ€
#2 ~ July 22, 2009 ~ “4 hours†~ “0 hoursâ€
#1 ~ August 30, 2008 ~ “4 hours†~ “0 hoursâ€
My query should show that in the past month #1 worked 11 hours and #2 worked
4 hours.



I completely took [Other Hours] and any other non-essential fields off the
query until I hear back from you. They were confusing me. If it matters,
the [Other Hours] field is on the same table as the [Start Time] & [End Time]
fields.


My New SQL:

SELECT [Volunteer Hours Tracking Table].[Volunteer ID],
(Sum((DateDiff("n",[Start Time],[End Time])/60)*[Number of People])) AS Total
FROM [Volunteer Info Table] INNER JOIN [Volunteer Hours Tracking Table] ON
[Volunteer Info Table].[Volunteer ID] = [Volunteer Hours Tracking
Table].[Volunteer ID]
WHERE ((([Volunteer Hours Tracking Table].[Date
Worked])>DateAdd("m",-1,Date())))
GROUP BY [Volunteer Hours Tracking Table].[Volunteer ID];




KARL DEWEY said:
I only see the field in the GROUP BY. How did you want it to be included?
What data does it contain and does it relate to the number of minutes you
extract from [Start Time] - [End Time] ?

--
Build a little, test a little.


Flopbot said:
I think that I’m missing something really easy here. I have a query drawn
from an “Volunteer Hours Tracking Table†that returns the total number of
hours worked by those volunteers who came in during the past month. To do
this it uses the equation: (Sum((DateDiff("n",[Start Time],[End
Time])/60)*[Number of People])) I have since added another field to the
original table labeled [Other Hours]. I want to ADD the [Other Hours] field
to my original equation in this query. I’ve already done it on my main form
but haven’t been able to do it in my query.

Can anyone see the error of my ways?



SQL View:

SELECT [Volunteer Hours Tracking Table].[Volunteer ID],
(Sum((DateDiff("n",[Start Time],[End Time])/60)*[Number of People])) AS Total
FROM [Volunteer Info Table] INNER JOIN [Volunteer Hours Tracking Table] ON
[Volunteer Info Table].[Volunteer ID] = [Volunteer Hours Tracking
Table].[Volunteer ID]
WHERE ((([Volunteer Hours Tracking Table].[Date
Worked])>DateAdd("m",-1,Date())))
GROUP BY [Volunteer Hours Tracking Table].[Volunteer ID], [Volunteer Hours
Tracking Table].[Other Hours];
Click to expand...
Click to expand...
Click to expand...
 
No. Some of our volunteers are actually volunteer group so we simply
calculate the total hours put in by the group as a whole. Though I seem to
create an awful lot of it, I really don't like paperwork. :) Left join.
I'll have to learn about that. Will let you know.

KARL DEWEY said:
You should use a left join.
The [Number of People] implies that the individual volunteer brought others
with them to work. Should not the others be entered under their own ID?

Try the query below --
SELECT [Volunteer Hours Tracking Table].[Volunteer ID],
(Sum((DateDiff("n",[Start Time],[End Time])/60)*[Number of People])) +
Sum([Other Hours]) AS Total
FROM [Volunteer Info Table] LEFT JOIN [Volunteer Hours Tracking Table] ON
[Volunteer Info Table].[Volunteer ID] = [Volunteer Hours Tracking
Table].[Volunteer ID]
WHERE ((([Volunteer Hours Tracking Table].[Date
Worked])>DateAdd("m",-1,Date())))
GROUP BY [Volunteer Hours Tracking Table].[Volunteer ID];

--
Build a little, test a little.


Flopbot said:
Right. your question made me reconsider my query and what’s actually going
on. To answer your question, the original equation returns the number of
hours worked by a volunteer if they came in at X and left at Y. The [Other
Hours] field is a catch-all field for when someone comes in for Z hours (ex.
9) but I don’t necessarily know what their start and end time is.


Example:

ID ~ Date Volunteered ~ Calculated Hours ~ [Other Hours]
#1 ~ July 24, 2009 ~ “2 hours†~ “9 hoursâ€
#2 ~ July 22, 2009 ~ “4 hours†~ “0 hoursâ€
#1 ~ August 30, 2008 ~ “4 hours†~ “0 hoursâ€
My query should show that in the past month #1 worked 11 hours and #2 worked
4 hours.



I completely took [Other Hours] and any other non-essential fields off the
query until I hear back from you. They were confusing me. If it matters,
the [Other Hours] field is on the same table as the [Start Time] & [End Time]
fields.


My New SQL:

SELECT [Volunteer Hours Tracking Table].[Volunteer ID],
(Sum((DateDiff("n",[Start Time],[End Time])/60)*[Number of People])) AS Total
FROM [Volunteer Info Table] INNER JOIN [Volunteer Hours Tracking Table] ON
[Volunteer Info Table].[Volunteer ID] = [Volunteer Hours Tracking
Table].[Volunteer ID]
WHERE ((([Volunteer Hours Tracking Table].[Date
Worked])>DateAdd("m",-1,Date())))
GROUP BY [Volunteer Hours Tracking Table].[Volunteer ID];




KARL DEWEY said:
I only see the field in the GROUP BY. How did you want it to be included?
What data does it contain and does it relate to the number of minutes you
extract from [Start Time] - [End Time] ?

--
Build a little, test a little.


:

I think that I’m missing something really easy here. I have a query drawn
from an “Volunteer Hours Tracking Table†that returns the total number of
hours worked by those volunteers who came in during the past month. To do
this it uses the equation: (Sum((DateDiff("n",[Start Time],[End
Time])/60)*[Number of People])) I have since added another field to the
original table labeled [Other Hours]. I want to ADD the [Other Hours] field
to my original equation in this query. I’ve already done it on my main form
but haven’t been able to do it in my query.

Can anyone see the error of my ways?



SQL View:

SELECT [Volunteer Hours Tracking Table].[Volunteer ID],
(Sum((DateDiff("n",[Start Time],[End Time])/60)*[Number of People])) AS Total
FROM [Volunteer Info Table] INNER JOIN [Volunteer Hours Tracking Table] ON
[Volunteer Info Table].[Volunteer ID] = [Volunteer Hours Tracking
Table].[Volunteer ID]
WHERE ((([Volunteer Hours Tracking Table].[Date
Worked])>DateAdd("m",-1,Date())))
GROUP BY [Volunteer Hours Tracking Table].[Volunteer ID], [Volunteer Hours
Tracking Table].[Other Hours];
Click to expand...
Click to expand...
Click to expand...
 
Sorry. I’m not the greatest with Access though I am learning. Everything I
can find about “Left Joins†seems to say that they only occur between two
different tables. The two query fields that I want to add together (listed
below) both originate from the same table.

Total: (Sum((DateDiff("n",[Start Time],[End Time])/60)*[Number of People]))

Total2: Other Hours


I played with it and made some slight headway. My newest version of the
query shows the hours worked in both categories in two separate columns. I
couldn't find any tutorials on MS HELP that clairified this for me; nor any
of the other threads here. I still can’t figure out how to get the two
columns to add together.

Query results:

ID ~ Total ~ Total2
#4 ~ “4 hours†~ “0 hoursâ€
#5 ~ “0 hours†~ “9 hoursâ€
#6 ~ “4 hours†~ “0 hoursâ€


Newest SQL view:

SELECT [Volunteer Hours Tracking Table].[Volunteer ID],
(Sum((DateDiff("n",[Start Time],[End Time])/60)*[Number of People])) AS
Total, [Volunteer Hours Tracking Table].[Other Hours] AS Total2
FROM [Volunteer Info Table] INNER JOIN [Volunteer Hours Tracking Table] ON
[Volunteer Info Table].[Volunteer ID] = [Volunteer Hours Tracking
Table].[Volunteer ID]
WHERE ((([Volunteer Hours Tracking Table].[Date
Worked])>DateAdd("m",-1,Date())))
GROUP BY [Volunteer Hours Tracking Table].[Volunteer ID], [Volunteer Hours
Tracking Table].[Other Hours];





Flopbot said:
No. Some of our volunteers are actually volunteer group so we simply
calculate the total hours put in by the group as a whole. Though I seem to
create an awful lot of it, I really don't like paperwork. :) Left join.
I'll have to learn about that. Will let you know.

KARL DEWEY said:
You should use a left join.
The [Number of People] implies that the individual volunteer brought others
with them to work. Should not the others be entered under their own ID?

Try the query below --
SELECT [Volunteer Hours Tracking Table].[Volunteer ID],
(Sum((DateDiff("n",[Start Time],[End Time])/60)*[Number of People])) +
Sum([Other Hours]) AS Total
FROM [Volunteer Info Table] LEFT JOIN [Volunteer Hours Tracking Table] ON
[Volunteer Info Table].[Volunteer ID] = [Volunteer Hours Tracking
Table].[Volunteer ID]
WHERE ((([Volunteer Hours Tracking Table].[Date
Worked])>DateAdd("m",-1,Date())))
GROUP BY [Volunteer Hours Tracking Table].[Volunteer ID];

--
Build a little, test a little.


Flopbot said:
Right. your question made me reconsider my query and what’s actually going
on. To answer your question, the original equation returns the number of
hours worked by a volunteer if they came in at X and left at Y. The [Other
Hours] field is a catch-all field for when someone comes in for Z hours (ex.
9) but I don’t necessarily know what their start and end time is.


Example:

ID ~ Date Volunteered ~ Calculated Hours ~ [Other Hours]
#1 ~ July 24, 2009 ~ “2 hours†~ “9 hoursâ€
#2 ~ July 22, 2009 ~ “4 hours†~ “0 hoursâ€
#1 ~ August 30, 2008 ~ “4 hours†~ “0 hoursâ€
My query should show that in the past month #1 worked 11 hours and #2 worked
4 hours.



I completely took [Other Hours] and any other non-essential fields off the
query until I hear back from you. They were confusing me. If it matters,
the [Other Hours] field is on the same table as the [Start Time] & [End Time]
fields.


My New SQL:

SELECT [Volunteer Hours Tracking Table].[Volunteer ID],
(Sum((DateDiff("n",[Start Time],[End Time])/60)*[Number of People])) AS Total
FROM [Volunteer Info Table] INNER JOIN [Volunteer Hours Tracking Table] ON
[Volunteer Info Table].[Volunteer ID] = [Volunteer Hours Tracking
Table].[Volunteer ID]
WHERE ((([Volunteer Hours Tracking Table].[Date
Worked])>DateAdd("m",-1,Date())))
GROUP BY [Volunteer Hours Tracking Table].[Volunteer ID];




:

I only see the field in the GROUP BY. How did you want it to be included?
What data does it contain and does it relate to the number of minutes you
extract from [Start Time] - [End Time] ?

--
Build a little, test a little.


:

I think that I’m missing something really easy here. I have a query drawn
from an “Volunteer Hours Tracking Table†that returns the total number of
hours worked by those volunteers who came in during the past month. To do
this it uses the equation: (Sum((DateDiff("n",[Start Time],[End
Time])/60)*[Number of People])) I have since added another field to the
original table labeled [Other Hours]. I want to ADD the [Other Hours] field
to my original equation in this query. I’ve already done it on my main form
but haven’t been able to do it in my query.

Can anyone see the error of my ways?



SQL View:

SELECT [Volunteer Hours Tracking Table].[Volunteer ID],
(Sum((DateDiff("n",[Start Time],[End Time])/60)*[Number of People])) AS Total
FROM [Volunteer Info Table] INNER JOIN [Volunteer Hours Tracking Table] ON
[Volunteer Info Table].[Volunteer ID] = [Volunteer Hours Tracking
Table].[Volunteer ID]
WHERE ((([Volunteer Hours Tracking Table].[Date
Worked])>DateAdd("m",-1,Date())))
GROUP BY [Volunteer Hours Tracking Table].[Volunteer ID], [Volunteer Hours
Tracking Table].[Other Hours];
Click to expand...
Click to expand...
Click to expand...
 
Back
Top