CALCULATED VALUES IN A FORM BASED ON USER DATA INPUT

[Thorfinn]

This is a post from another site; but I still need help. Maybe someone from
THIS site can help. Hope I've explained it well enough.
----------------------------------------------------------------------------------------

We are trying to calculate values in an entry form, based on input values of
the current record after it is "accepted," i.e., either saved or clicked to another entry.

The entry form is based on a query that connects data from various tables.
The data from the tables is input to the form (and the appropriate
associate-table) via combo box. The entries are:

-- Weekending (a date that is always a Wednesday),
-- Resource Name (a person's name),
-- Project Name (the task they are working on),
-- Week Hours Worked Qty (the number of hours worked by that person on the
selected project for the selected week)
-- Comment: Describes what the person accomplished that week.

We have been (more or less) successful in calculating the work hours
quantity for the entire group, using the expression shown below.

CurrentWeeklyGroupTotalHours:
=DSum("[Week Hours Worked Qty]","MasterFactTable","[Weekending] between date() and date()-7")

(There are probably better ways to do the above; but the answer to that will
be obvious once the other questions are answered.)

What we haven't been able to figure out is how to get the cummulative total
for the week for a person, or the cumulative total for the week for a
specific project.

Again, the above calculations are intended to display on the right side of
the entry form, in real time, while the user enters the basic data described
above in the user fields on the left side of the form.

Please be gently basic, as we are not VB programmers, nor do we really
understand macros, expressions, etc.

Thanks in advance.
Thorfinn

-----------------------------------------------------------------------------

I would try using Datediff.
Try hours worked:datediff("d",[start],[end]) and then divide by 8. Or if you
need it in hours try datediff("h",[start],[end])

----------------------------------------------------------------------------------
Golfinray:

Thanks for answering back quickly.

Unfortunately, this did not provide assistance on how to specify the values
in a current record (current person or current project, as well as current
date) to add hours related to that person or project for that week.

It did provide some thoughts on how to do the dates differently, but on
attempting their use, I either got an improper syntax message, or the system
insisted on reverting back to the original coding.

I'm not sure how I would use datediff as expressed -- and probably, the
ignorance is mine.

It appears that the [Start] value would be [Weekending]-7, and the [End]
value would be [Weekending]. But again, that didn't work for me/us.

Thanks again
Thorfinn

 

[Thorfinn]

Dale Fye:

Thanks for the response.

I tried it; but the system won't accept it. When I enter your changes to
the expression box and click OK (and then save), it reverts back to the
original algorythm. This algorythm produces a result ( the total for the
latest work period for the whole group) but not the one I want (total for an
individual for the latest period).

Any suggestions for getting Access to accept the changes?

Thanks again!!
--
Thorfinn

Thorfinn,

You will need to include more detail in the criteria portion of the DSUM( )
domain function.

For the hours worked during the previous week, by the individual you will
need to add to the critieria on only include those hours worked by that
person. Assuming that the [Resource Name] field actually contains a name,
and you select this from a combo box (cboResourceName), then the statement
might look like:

=DSum("[Week Hours Worked Qty]","MasterFactTable","[Weekending]
between date() and date()-7 AND [Resource Name] = '" & me.
cboResourceName & "'")

Although I prefer to use the DateAdd function rather over simply adding an
integer to a date value.

=DSum("[Week Hours Worked Qty]","MasterFactTable","[Weekending]
between date() and dateadd("d", -7, Date()) AND [Resource Name] = '" &
me.cboResourceName & "'")

Note that I have wrapped the value of cboResourceName inside single quotes.
Otherwise, this would generate an error. To do the same for a project, the
statement might look like:

=DSum("[Week Hours Worked Qty]","MasterFactTable","[Weekending]
between date() and dateadd("d", -7, Date()) AND [Project Name] = '" &
me.cboProjectName & "'")

HTH
Dale

This is a post from another site; but I still need help. Maybe someone from
THIS site can help. Hope I've explained it well enough.
----------------------------------------------------------------------------------------

:

We are trying to calculate values in an entry form, based on input values of
the current record after it is "accepted," i.e., either saved or clicked to

another entry.

The entry form is based on a query that connects data from various tables.
The data from the tables is input to the form (and the appropriate

[quoted text clipped - 12 lines]

CurrentWeeklyGroupTotalHours:
=DSum("[Week Hours Worked Qty]","MasterFactTable","[Weekending]

between date() and date()-7")

(There are probably better ways to do the above; but the answer to that will
be obvious once the other questions are answered.)

[quoted text clipped - 12 lines]

Thanks in advance.
Thorfinn

-----------------------------------------------------------------------------

I would try using Datediff.
Try hours worked:datediff("d",[start],[end]) and then divide by 8. Or if you
need it in hours try datediff("h",[start],[end])

----------------------------------------------------------------------------------
Golfinray:

Thanks for answering back quickly.

Unfortunately, this did not provide assistance on how to specify the values
in a current record (current person or current project, as well as current
date) to add hours related to that person or project for that week.

It did provide some thoughts on how to do the dates differently, but on
attempting their use, I either got an improper syntax message, or the system
insisted on reverting back to the original coding.

I'm not sure how I would use datediff as expressed -- and probably, the
ignorance is mine.

It appears that the [Start] value would be [Weekending]-7, and the [End]
value would be [Weekending]. But again, that didn't work for me/us.

Thanks again
Thorfinn

--
HTH

Dale Fye

Message posted via AccessMonster.com

 

[Thorfinn]

Dale Fry:

Additional reponse:

I played around with the quotation marks in both your original and altered
forms. In each case, I get one of the following four results:

#Name?
#Error
I get an empty expression field and a dialog box not that the syntax is wrong.
It reverts back to the original algorythm

Further, the result isn't always the same with any one change. That is the
frustrating thing about Access -- there's some "quantum physics" that seems
to apply where the same string of ones and zeros (or whatever) don't equal
the same thing everytime. If that is true, "computer science" may be closer
to Taro Cards, et al.

Anyhow -- just me whining/ranting. Please respond if there is any help.

Thanks again,
--
Thorfinn

Thorfinn,

You will need to include more detail in the criteria portion of the DSUM( )
domain function.

For the hours worked during the previous week, by the individual you will
need to add to the critieria on only include those hours worked by that
person. Assuming that the [Resource Name] field actually contains a name,
and you select this from a combo box (cboResourceName), then the statement
might look like:

=DSum("[Week Hours Worked Qty]","MasterFactTable","[Weekending]
between date() and date()-7 AND [Resource Name] = '" & me.
cboResourceName & "'")

Although I prefer to use the DateAdd function rather over simply adding an
integer to a date value.

=DSum("[Week Hours Worked Qty]","MasterFactTable","[Weekending]
between date() and dateadd("d", -7, Date()) AND [Resource Name] = '" &
me.cboResourceName & "'")

Note that I have wrapped the value of cboResourceName inside single quotes.
Otherwise, this would generate an error. To do the same for a project, the
statement might look like:

=DSum("[Week Hours Worked Qty]","MasterFactTable","[Weekending]
between date() and dateadd("d", -7, Date()) AND [Project Name] = '" &
me.cboProjectName & "'")

HTH
Dale

This is a post from another site; but I still need help. Maybe someone from
THIS site can help. Hope I've explained it well enough.
----------------------------------------------------------------------------------------

:

We are trying to calculate values in an entry form, based on input values of
the current record after it is "accepted," i.e., either saved or clicked to

another entry.

The entry form is based on a query that connects data from various tables.
The data from the tables is input to the form (and the appropriate

[quoted text clipped - 12 lines]

CurrentWeeklyGroupTotalHours:
=DSum("[Week Hours Worked Qty]","MasterFactTable","[Weekending]

between date() and date()-7")

(There are probably better ways to do the above; but the answer to that will
be obvious once the other questions are answered.)

[quoted text clipped - 12 lines]

Thanks in advance.
Thorfinn

-----------------------------------------------------------------------------

I would try using Datediff.
Try hours worked:datediff("d",[start],[end]) and then divide by 8. Or if you
need it in hours try datediff("h",[start],[end])

----------------------------------------------------------------------------------
Golfinray:

Thanks for answering back quickly.

Unfortunately, this did not provide assistance on how to specify the values
in a current record (current person or current project, as well as current
date) to add hours related to that person or project for that week.

It did provide some thoughts on how to do the dates differently, but on
attempting their use, I either got an improper syntax message, or the system
insisted on reverting back to the original coding.

I'm not sure how I would use datediff as expressed -- and probably, the
ignorance is mine.

It appears that the [Start] value would be [Weekending]-7, and the [End]
value would be [Weekending]. But again, that didn't work for me/us.

Thanks again
Thorfinn

--
HTH

Dale Fye

Message posted via AccessMonster.com

 

[Thorfinn]

Dale:

Thanks again for your assistance.

Unfortunately, it didn't work this time either -- similar kinds of results.

In fact, any change at all from the original expression I gave you no longer
works. I can't even substitute just the "dateadd" functions for the date()
without a malfunction, nor does anything with the "me.cboResourceName & "'"
work.

I'll continue to try to mess with it. Until then, if you have any other
inspirations, please feel free to pile on.

I'm adding some additional detail in case it is helpful. I'm adding the SQL
that comprises the "MasterFactTable" query. If I could, I'd add a picture of
the data model as well -- though that may be intuitive from the SQL.

I should add that, even though the connecting primary/foreign keys are
system generated numeric fields, the actual data for display is text (for
Resource Name, Project Name, etc. The "weekending" is a date, even though
the date table is also connected via a numeric primary/foreign key.

Herewith, the SQL:

SELECT WeekEndingDate.WeekEnding, Format(Year([weekending])) AS YearNo,
Format(Month([Weekending])) AS MonthNo, Format([Weekending],"yyyy mmmm") AS
YearMonth, DatePart("q",[Weekending]+90) AS [Fiscal Quarter], [Person
Resource].[Resource Name], Project.[Project Name], [Project
Transaction].[Week Hours Worked Qty], [Project Transaction].[Project Person
Comment]
FROM WeekEndingDate INNER JOIN (Project INNER JOIN ([Person Resource] INNER
JOIN ([Person Resource Role] INNER JOIN [Project Transaction] ON [Person
Resource Role].[Role Code]=[Project Transaction].[Person Resource Role Code])
ON [Person Resource].[Person Resource ID]=[Project Transaction].[Person
Resource ID]) ON Project.[Project ID]=[Project Transaction].[Project ID]) ON
WeekEndingDate.ID=[Project Transaction].[Week Ending Date ID]
ORDER BY WeekEndingDate.WeekEnding, [Person Resource].[Resource Name],
Project.[Project Name];

Note that not everything in the query is being used in the form. This query
was intended pull "everything" together for use by forms and reports.

Thanks again for your help.
--
Thorfinn

Try breaking up the string and putting the literal values of the date in the
criteria of the DSUM.

=DSum("[Week Hours Worked Qty]","MasterFactTable","[Weekending]
between #" & date() & "# and #" & dateadd("d", -7, Date()) & "# AND
[Resource Name] = '" &
me.cboResourceName & "'")

is the [ResourceName] field actually text? If so, then that part of the
query should be correct. If, however, ResourceName actually is numeric, then
it should read like:

=DSum("[Week Hours Worked Qty]","MasterFactTable","[Weekending]
between #" & dateadd("d", -7, Date()) & "# and #" & date() & "# AND
[Resource Name] = '" &
me.cboResourceName & "'")

Dale Fry:

Additional reponse:

I played around with the quotation marks in both your original and altered
forms. In each case, I get one of the following four results:

#Name?
#Error
I get an empty expression field and a dialog box not that the syntax is wrong.
It reverts back to the original algorythm

Further, the result isn't always the same with any one change. That is the
frustrating thing about Access -- there's some "quantum physics" that seems
to apply where the same string of ones and zeros (or whatever) don't equal
the same thing everytime. If that is true, "computer science" may be closer
to Taro Cards, et al.

Anyhow -- just me whining/ranting. Please respond if there is any help.

Thanks again,

Thorfinn,

[quoted text clipped - 77 lines]

Thanks again
Thorfinn

--
HTH

Dale Fye

Message posted via AccessMonster.com