Query muliplying calculated fields

G

Guest

Hello,

Back again for more punishment...

I have my database set and am building queries to generate some totals.

I have three tables which are related,
[Rep07] contains all report data, is related to [IndicentResponders] by
Number(primary key) of [Rep07] linked to IncidentID of [IncidentResponders}

[IncidentResponders] contains the list for each incident of the personnel
who responded and is also related to [Employees] by EmployeeID of
[IncidentResponders] linked to [EmployeeID](primary key) of [Employees]

[Employees] contains data on all employees.

What I am trying to get as a result is total time spent.

What I am using is Elapsed Time and Number of Responders.

My query has fields that calculate the elapsed time [Time]and number of
responders [Responders] both show as a numeric value. Elapsed time shows in
# of seconds and is calculated from two database fields [TimeOut]and [TimeIn]
from a Table named [Rep07]. Responders shows the # of responders and is
calculated on the Count of the field Responders in a subform table titled
[IndcidentResponders].

Syntax of Time field is:
Time: IIf(DateDiff("n",[DISPATCH],[TIME IN])>0,DateDiff("n",[DISPATCH],[TIME
IN]),DateDiff("n",[DISPATCH],[TIME IN])+1440), from the [Rep07] table.



Responders is [EmployeeName], (from the [IncidentResponders] table,with the
Total parameter set as Count.
I've tried adding a new field:

TotalTime: [Time]*[Responders] and set the fields Total value to Sum. The
code brings no result.

How do I accomplish this calculation?
 
J

Jeff Boyce

I may be misinterpreting your description...

It sounds like you are saying you are storing calculated values in your
tables. If so, reconsider this approach. Not only is this not necessary
(since you could calculate the amount 'on the fly' using queries), but it
puts your data's integrity at risk, since Access doesn't offer a way
(triggers) to automatically update a calculated field if one/more of the
underlying fields is modified (say, fixing a typo).

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
D

Dale Fye

I would change the name of the [Time] field in your query to ElapsedTime. I
believe that Time is a reserved word in Access, and you should avoid giving
fields in tables and queries names that are reserved words.

From the description you have given, it appears that you are trying to
determine the total time for all employees involved in a particular
incident. I'm not sure why the Employees table is relevant to this query,
so I will leave it out for now. Your description also indicates that your
time is computed in seconds, but the formula you show below is in minutes.
And why would the date difference between the Dispatch and Time In fields
not be greater than zero? And do you really want to use 24 hours as the
default Elapsed time if that is the case? Given that what you gave us is
correct, I think you should be able to do something like:

SELECT [Rep07].IncidentID, SUM(IIf(DateDiff("n",[DISPATCH],[TIME
IN])>0,DateDiff("n",[DISPATCH],[TIME IN]),DateDiff("n",[DISPATCH],[TIME
IN])+1440)) as IncidentTime
FROM [Rep07] INNER JOIN [IncidentResponders]
ON [Rep07].IncidentID = [IncidentResponders].IncidentID
GROUP BY [Rep07].IncidentID

But this query will compute the elapsed time for each responder. It might
be quicker to do:

SELECT Rep07.IncidentID, IIf(DateDiff("n",[DISPATCH],[TIME
IN])>0,DateDiff("n",[DISPATCH],[TIME IN]),DateDiff("n",[DISPATCH],[TIME
IN])+1440)) * DCOUNT("EmployeeID", "IncidentResponders", "IncidentID = " &
[Rep07].IncidentID) as IncidentTime
FROM Rep07

OR

SELECT Rep07.IncidentID, IIf(DateDiff("n",[DISPATCH],[TIME
IN])>0,DateDiff("n",[DISPATCH],[TIME IN]),DateDiff("n",[DISPATCH],[TIME
IN])+1440)) * T.Responders as IncidentTime
FROM Rep07 INNER JOIN (SELECT IncidentID, Count(EmployeeID) as Responders
FROM IncidentResponders GROUP BY IncidentID) as T
ON Rep07.IncidentID = T.IncidentID

To do this latter query, your best bet is to just type it into the SQL view.
Depending on what version of Access you have, if you shift between the SQL
view and the query grid, Access will probably change the subquery wrappers
( ) to [ ]. If this happens, and you go back and change something in the
query, you may get an error about something in your From clause. Just go
back and change the [ ]. back to ( ) and it should work.

HTH
Dale

CaptDD said:
Hello,

Back again for more punishment...

I have my database set and am building queries to generate some totals.

I have three tables which are related,
[Rep07] contains all report data, is related to [IndicentResponders] by
Number(primary key) of [Rep07] linked to IncidentID of
[IncidentResponders}

[IncidentResponders] contains the list for each incident of the personnel
who responded and is also related to [Employees] by EmployeeID of
[IncidentResponders] linked to [EmployeeID](primary key) of [Employees]

[Employees] contains data on all employees.

What I am trying to get as a result is total time spent.

What I am using is Elapsed Time and Number of Responders.

My query has fields that calculate the elapsed time [Time]and number of
responders [Responders] both show as a numeric value. Elapsed time shows
in
# of seconds and is calculated from two database fields [TimeOut]and
[TimeIn]
from a Table named [Rep07]. Responders shows the # of responders and is
calculated on the Count of the field Responders in a subform table titled
[IndcidentResponders].

Syntax of Time field is:
Time: IIf(DateDiff("n",[DISPATCH],[TIME IN])>0,
Click to expand...
DateDiff("n",[DISPATCH],[TIME IN]),
DateDiff("n",[DISPATCH],[TIME IN])+1440)
Responders is [EmployeeName], (from the [IncidentResponders] table,with
the
Total parameter set as Count.
I've tried adding a new field:

TotalTime: [Time]*[Responders] and set the fields Total value to Sum. The
code brings no result.

How do I accomplish this calculation?
Click to expand...
 
G

Guest

@Jeff,
You must have misread my post, I do not store calculated data in the
database, I need to calculate data to present on Daily, Monthly and Annual
Report forms using a query.

@Dale,
You seem to be right on track. My mistake, my coding does calculate in
elapsed Minutes not Seconds. I don't have time to test your coding today but
will do so on my next shift here at the fire station. The reason for the
less than zero code was for emergency runs that run from say 2350 (Monday
night) to 0150 (Tuesday morning). So the result of the calculation would
have been a negative number. The only issue I see with that coding is the
(god forbid) emergency that runs for more than 1 day! I will follow your
advice on the naming conventions... being self taught this is quite an
adventure!

I did seem to get my crazy way to work by adding a field TotalTime:
=[Time]. I guess the Total row in a query does calculations across all
fields!?!? Not sure how it works.... but it does. It's VERY ugly and am
appreciative of the new coding provided.
Thanks Dale, as I said I will be testing your coding on my next shift.

Dale Fye said:
I would change the name of the [Time] field in your query to ElapsedTime. I
believe that Time is a reserved word in Access, and you should avoid giving
fields in tables and queries names that are reserved words.

From the description you have given, it appears that you are trying to
determine the total time for all employees involved in a particular
incident. I'm not sure why the Employees table is relevant to this query,
so I will leave it out for now. Your description also indicates that your
time is computed in seconds, but the formula you show below is in minutes.
And why would the date difference between the Dispatch and Time In fields
not be greater than zero? And do you really want to use 24 hours as the
default Elapsed time if that is the case? Given that what you gave us is
correct, I think you should be able to do something like:

SELECT [Rep07].IncidentID, SUM(IIf(DateDiff("n",[DISPATCH],[TIME
IN])>0,DateDiff("n",[DISPATCH],[TIME IN]),DateDiff("n",[DISPATCH],[TIME
IN])+1440)) as IncidentTime
FROM [Rep07] INNER JOIN [IncidentResponders]
ON [Rep07].IncidentID = [IncidentResponders].IncidentID
GROUP BY [Rep07].IncidentID

But this query will compute the elapsed time for each responder. It might
be quicker to do:

SELECT Rep07.IncidentID, IIf(DateDiff("n",[DISPATCH],[TIME
IN])>0,DateDiff("n",[DISPATCH],[TIME IN]),DateDiff("n",[DISPATCH],[TIME
IN])+1440)) * DCOUNT("EmployeeID", "IncidentResponders", "IncidentID = " &
[Rep07].IncidentID) as IncidentTime
FROM Rep07

OR

SELECT Rep07.IncidentID, IIf(DateDiff("n",[DISPATCH],[TIME
IN])>0,DateDiff("n",[DISPATCH],[TIME IN]),DateDiff("n",[DISPATCH],[TIME
IN])+1440)) * T.Responders as IncidentTime
FROM Rep07 INNER JOIN (SELECT IncidentID, Count(EmployeeID) as Responders
FROM IncidentResponders GROUP BY IncidentID) as T
ON Rep07.IncidentID = T.IncidentID

To do this latter query, your best bet is to just type it into the SQL view.
Depending on what version of Access you have, if you shift between the SQL
view and the query grid, Access will probably change the subquery wrappers
( ) to [ ]. If this happens, and you go back and change something in the
query, you may get an error about something in your From clause. Just go
back and change the [ ]. back to ( ) and it should work.

HTH
Dale

CaptDD said:
Hello,

Back again for more punishment...

I have my database set and am building queries to generate some totals.

I have three tables which are related,
[Rep07] contains all report data, is related to [IndicentResponders] by
Number(primary key) of [Rep07] linked to IncidentID of
[IncidentResponders}

[IncidentResponders] contains the list for each incident of the personnel
who responded and is also related to [Employees] by EmployeeID of
[IncidentResponders] linked to [EmployeeID](primary key) of [Employees]

[Employees] contains data on all employees.

What I am trying to get as a result is total time spent.

What I am using is Elapsed Time and Number of Responders.

My query has fields that calculate the elapsed time [Time]and number of
responders [Responders] both show as a numeric value. Elapsed time shows
in
# of seconds and is calculated from two database fields [TimeOut]and
[TimeIn]
from a Table named [Rep07]. Responders shows the # of responders and is
calculated on the Count of the field Responders in a subform table titled
[IndcidentResponders].

Syntax of Time field is:
Time: IIf(DateDiff("n",[DISPATCH],[TIME IN])>0,
Click to expand...
DateDiff("n",[DISPATCH],[TIME IN]),
DateDiff("n",[DISPATCH],[TIME IN])+1440)
Responders is [EmployeeName], (from the [IncidentResponders] table,with
the
Total parameter set as Count.
I've tried adding a new field:

TotalTime: [Time]*[Responders] and set the fields Total value to Sum. The
code brings no result.

How do I accomplish this calculation?
Click to expand...
Click to expand...
 
G

Guest

CaptDD,

If I were doing this, I would store your Dispatch and TimeIn fields as
datetime values, storing both the date and the time. Then, your DateDiff
function would work properly and not have the negative value, making the
query much easier.

--
Email address is not valid.
Please reply to newsgroup only.


CaptDD said:
@Jeff,
You must have misread my post, I do not store calculated data in the
database, I need to calculate data to present on Daily, Monthly and Annual
Report forms using a query.

@Dale,
You seem to be right on track. My mistake, my coding does calculate in
elapsed Minutes not Seconds. I don't have time to test your coding today but
will do so on my next shift here at the fire station. The reason for the
less than zero code was for emergency runs that run from say 2350 (Monday
night) to 0150 (Tuesday morning). So the result of the calculation would
have been a negative number. The only issue I see with that coding is the
(god forbid) emergency that runs for more than 1 day! I will follow your
advice on the naming conventions... being self taught this is quite an
adventure!

I did seem to get my crazy way to work by adding a field TotalTime:
=[Time]. I guess the Total row in a query does calculations across all
fields!?!? Not sure how it works.... but it does. It's VERY ugly and am
appreciative of the new coding provided.
Thanks Dale, as I said I will be testing your coding on my next shift.

Dale Fye said:
I would change the name of the [Time] field in your query to ElapsedTime. I
believe that Time is a reserved word in Access, and you should avoid giving
fields in tables and queries names that are reserved words.

From the description you have given, it appears that you are trying to
determine the total time for all employees involved in a particular
incident. I'm not sure why the Employees table is relevant to this query,
so I will leave it out for now. Your description also indicates that your
time is computed in seconds, but the formula you show below is in minutes.
And why would the date difference between the Dispatch and Time In fields
not be greater than zero? And do you really want to use 24 hours as the
default Elapsed time if that is the case? Given that what you gave us is
correct, I think you should be able to do something like:

SELECT [Rep07].IncidentID, SUM(IIf(DateDiff("n",[DISPATCH],[TIME
IN])>0,DateDiff("n",[DISPATCH],[TIME IN]),DateDiff("n",[DISPATCH],[TIME
IN])+1440)) as IncidentTime
FROM [Rep07] INNER JOIN [IncidentResponders]
ON [Rep07].IncidentID = [IncidentResponders].IncidentID
GROUP BY [Rep07].IncidentID

But this query will compute the elapsed time for each responder. It might
be quicker to do:

SELECT Rep07.IncidentID, IIf(DateDiff("n",[DISPATCH],[TIME
IN])>0,DateDiff("n",[DISPATCH],[TIME IN]),DateDiff("n",[DISPATCH],[TIME
IN])+1440)) * DCOUNT("EmployeeID", "IncidentResponders", "IncidentID = " &
[Rep07].IncidentID) as IncidentTime
FROM Rep07

OR

SELECT Rep07.IncidentID, IIf(DateDiff("n",[DISPATCH],[TIME
IN])>0,DateDiff("n",[DISPATCH],[TIME IN]),DateDiff("n",[DISPATCH],[TIME
IN])+1440)) * T.Responders as IncidentTime
FROM Rep07 INNER JOIN (SELECT IncidentID, Count(EmployeeID) as Responders
FROM IncidentResponders GROUP BY IncidentID) as T
ON Rep07.IncidentID = T.IncidentID

To do this latter query, your best bet is to just type it into the SQL view.
Depending on what version of Access you have, if you shift between the SQL
view and the query grid, Access will probably change the subquery wrappers
( ) to [ ]. If this happens, and you go back and change something in the
query, you may get an error about something in your From clause. Just go
back and change the [ ]. back to ( ) and it should work.

HTH
Dale

CaptDD said:
Hello,

Back again for more punishment...

I have my database set and am building queries to generate some totals.

I have three tables which are related,
[Rep07] contains all report data, is related to [IndicentResponders] by
Number(primary key) of [Rep07] linked to IncidentID of
[IncidentResponders}

[IncidentResponders] contains the list for each incident of the personnel
who responded and is also related to [Employees] by EmployeeID of
[IncidentResponders] linked to [EmployeeID](primary key) of [Employees]

[Employees] contains data on all employees.

What I am trying to get as a result is total time spent.

What I am using is Elapsed Time and Number of Responders.

My query has fields that calculate the elapsed time [Time]and number of
responders [Responders] both show as a numeric value. Elapsed time shows
in
# of seconds and is calculated from two database fields [TimeOut]and
[TimeIn]
from a Table named [Rep07]. Responders shows the # of responders and is
calculated on the Count of the field Responders in a subform table titled
[IndcidentResponders].

Syntax of Time field is:
Time: IIf(DateDiff("n",[DISPATCH],[TIME IN])>0,
Click to expand...
DateDiff("n",[DISPATCH],[TIME IN]),
DateDiff("n",[DISPATCH],[TIME IN])+1440)
Responders is [EmployeeName], (from the [IncidentResponders] table,with
the
Total parameter set as Count.
I've tried adding a new field:

TotalTime: [Time]*[Responders] and set the fields Total value to Sum. The
code brings no result.

How do I accomplish this calculation?
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Dale,

Thanks for the suggestion. I will look into that. I think that would
require that the field entry be date/time and I'm not sure how that entry
would be formatted to be sure it was always done correctly. My database has
to be "firefighter proof"... LOL
 
D

Dale Fye

You could do date and time entries separately then merge them into a single
field by adding them together.

Dale
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Query using check box data 7
Total Query: Adding a field to a calculation. 3
Where did the query field come from? 3
Update query. Within the same table: populating field 2 from field 6
Subtracting Holiday Hours 1
DLookup in Query 8
Update query syntax: SUM and GROUP BY 3
Add a field in a query. Easy. . .so I though. 5

Top