Field calculation based on data in separate fields within the same subform...

Hello- I'm working on an employee record database with several subforms. One of the subforms keeps track of occurrence info. The fields in the occurrence subform are : Last Name( links subform to main form ) , # of occurrences, date occurences recieved, and total occurrences in the last 12 months. I can't figure out what the code should look like for the control source of the [# of occurrences in the last 12 months] field to make it perform that calculation. That is - only sum the data in the [# of occurrences] field based on the data in the [date occurrences recieved] field - if it is within the last 12 months. Any help would be greatly appreciated. Thanks...

--
Message posted via http://www.accessmonster.com

Rob,

You can't do a calculation in a field.

If I understand you correctly, this subform can have a number of
records, one for each occurences date, right? So I guess this is a
continuous view form, right? But you only need one place where you see
the [# of occurrences in the last 12 months], right? Well if you have a
field for this, I suggest you remove it. Instead, you can put an
unbound textbox in the Form Footer section of the subform, and set it's
Control Source to something like this...
=Sum(IIf([date occurrences received]DateAdd("yyyy",-1,Date()),0,[# of
occurrences]))

A couple of other "by the way" comments, if I may...
- it is not a good idea to put a # as part of the name of a field or
control.
- linking a subform to a main form on a [Last Name] field assumes there
will never be more than one employee with the same Last Name, and for
this reason would not normally be a recommended practice.

--
Steve Schapel, Microsoft Access MVP


rob mccoy via AccessMonster.com wrote:
Hello- I'm working on an employee record database with several
subforms. One of the subforms keeps track of occurrence info. The
fields in the occurrence subform are : Last Name( links subform to
main form ) , # of occurrences, date occurences recieved, and total
occurrences in the last 12 months. I can't figure out what the code
should look like for the control source of the [# of occurrences in
the last 12 months] field to make it perform that calculation. That
is - only sum the data in the [# of occurrences] field based on the
data in the [date occurrences recieved] field - if it is within the
last 12 months. Any help would be greatly appreciated. Thanks...

Steve,

Thank you. Thank you. Thank you. ...sigh...

The code worked great, and the tips are appreciated. If the subforms were linked to the main form by first AND last names, in separate fields, do you forsee any problems? thanks again...

....rob

--
Message posted via http://www.accessmonster.com

Rob,

Well, using Last Name and First Name would certainly reduce the chances,
but the principle remains. There are a lot of John Smiths out there!
The usual approach would be to use something that will always be unique.
If the organisation does not allocate some sort of Employee Number or
some such, then you cna use an AutoNumber field in the Employee table to
create an arbitrary ID... that's what I would do.

--
Steve Schapel, Microsoft Access MVP

rob mccoy via AccessMonster.com wrote:
Steve,

Thank you. Thank you. Thank you. ...sigh...

The code worked great, and the tips are appreciated. If the subforms
were linked to the main form by first AND last names, in separate
fields, do you forsee any problems? thanks again...

...rob

On Fri, 14 Jan 2005 10:05:02 GMT, "rob mccoy via AccessMonster.com"
wrote:

If the subforms were linked to the main form by first AND last names, in separate fields, do you forsee any problems?

Certainly, you will have problems. Names ARE NOT UNIQUE. I know three
people named Fred Brown, right here in little Parma, Idaho; and I once
worked with Dr. Lawrence David Wise and his colleague, Dr. Lawrence
David Wise. Names should never, ever be used as primary keys or
linking fields.

John W. Vinson[MVP]

steve,
thanks again for your help. i took your advice and linked the forms based on an employee number, which is unique to each employee. Hopefully this eliminates the chance of screwing up the records. Another question: I've got a subform for payroll - contains the following fields: [in time] , [out time] , [lunch] , [total time](for the day), and on the footer-[total time for the week]. When we turn in payroll to our corporate office- we base times on .1hr (=6 min), so say jon smith worked 8 1/2hr it would be 8.5 - so, how to calculate [total time](for the day) based on [in time], [out time], - [lunch] ,and display the results in the decimal format? Thank you once again for your help...


....rob

--
Message posted via http://www.accessmonster.com

Rob,

Assuming that [in time] and [out time] are both Date/Time data type
fields, and that [lunch] is a Number data type, try the following as the
Control Source of an unbound text box in the footer of the subform...
=Round(Sum(([out time]-[in time])*24-[lunch]),1)

--
Steve Schapel, Microsoft Access MVP

rob mccoy via AccessMonster.com wrote:
steve, thanks again for your help. i took your advice and linked the
forms based on an employee number, which is unique to each employee.
Hopefully this eliminates the chance of screwing up the records.
Another question: I've got a subform for payroll - contains the
following fields: [in time] , [out time] , [lunch] , [total time](for
the day), and on the footer-[total time for the week]. When we turn
in payroll to our corporate office- we base times on .1hr (=6 min),
so say jon smith worked 8 1/2hr it would be 8.5 - so, how to
calculate [total time](for the day) based on [in time], [out time], -
[lunch] ,and display the results in the decimal format? Thank you
once again for your help...


...rob

Steve,
I tried the expression that you gave me - the value that it returns is : #name?
the format for both[in time] and [out time] is set at date/time;medium time. The format for lunch is standard number. The Fomat for [total daily time] is standard number and it's in the footer. thanks for all your help...


....rob

--
Message posted via http://www.accessmonster.com

Steve,
another thing that is happening is that when I enter data into the [in time] [out time] fields -those values only appear in the form representing that particular day, but when I enter data in to the [lunch] field-that value carries over into all of the other forms/records in the subform...

....rob

--
Message posted via http://www.accessmonster.com

Rob,

You mean in the footer of the subform, not the footer of the main form,
right? If so, the most likely cause of the problem is a spelling error.
Or, the unlikely event that the name of the textbox in question is the
same as the name of a field in the subform's record source.

As for [total daily time], you mentioned this is a field. This should
not be a field, and I would recommend you remove it from the table. I'm
sorry, I overlooked responding to this in my earlier reply. You can put
an unbound control for this in the Detail section of the subform, and
the Control Source expression to show the daily hours is similar to the
one for the weekly, just without the Sum()...
=Round(([out time]-[in time])*24-[lunch],1)

--
Steve Schapel, Microsoft Access MVP

rob mccoy via AccessMonster.com wrote:
Steve, I tried the expression that you gave me - the value that it
returns is : #name? the format for both[in time] and [out time] is
set at date/time;medium time. The format for lunch is standard
number. The Fomat for [total daily time] is standard number and it's
in the footer. thanks for all your help...


...rob