Help

[Chris61]

Hello,

Here is my issue with 2 forms.

Form A:

Contains work schedule: date, time in, time out, overtime, etc. At the end
of the 1st record, the total amount of time is totalled as hours worked.

Form B:

Contains productivity for the entire week.

What I would like to do is the following:

I want to reference the cumulative hours worked for the week by filtering
out all the other dates that I do not select and taking the total amount of
hours worked during the period that I select. I have two date field (Week
Begin and Week End). These dates would be my reference period. From there,
I want the total amount of hours worked from Form A (from the reference
period) to appear in the field in Form B.

Would anyone have any ideas?

I am currently using ACCESS 2007.

Thank you.

 

[John W. Vinson]

Hello,

Here is my issue with 2 forms.

Form A:

Contains work schedule: date, time in, time out, overtime, etc. At the end
of the 1st record, the total amount of time is totalled as hours worked.

Form B:

Contains productivity for the entire week.

What I would like to do is the following:

I want to reference the cumulative hours worked for the week by filtering
out all the other dates that I do not select and taking the total amount of
hours worked during the period that I select. I have two date field (Week
Begin and Week End). These dates would be my reference period. From there,
I want the total amount of hours worked from Form A (from the reference
period) to appear in the field in Form B.

Would anyone have any ideas?

I am currently using ACCESS 2007.

Thank you.

It all depends on your tables. Forms are secondary; they're just windows on
the data stored in your tables.

What are your tables and how are they related?

Do note that you should not be storing any cumulative hours or subtotals or
grand totals *anywhere* - they should by dynamically calculated from the
actual worked hours stored in the table. This can be done using a totals
query, a form (with totals in the form footer), or a report, but the
cumulative value would not need to be stored. It appears that you want a
Totals query using your reference period textboxes on your form as criteria,
in the form

= [Forms]![YourFormName]![Week Begin] AND < DateAdd("d", 1, [Forms]![YourFormName]![Week End])

The DateAdd takes care of the fact that your date/time field probably contains
a time portion; adding a day will get all of the timepoints between 12:00:00
am and 11:59:59+ pm on the day specified.

 

[Chris61]

Hello,

Here is my issue with 2 forms.

Form A:

Contains work schedule: date, time in, time out, overtime, etc. At the end
of the 1st record, the total amount of time is totalled as hours worked.

Form B:

Contains productivity for the entire week.

What I would like to do is the following:

I want to reference the cumulative hours worked for the week by filtering
out all the other dates that I do not select and taking the total amount of
hours worked during the period that I select. I have two date field (Week
Begin and Week End). These dates would be my reference period. From there,
I want the total amount of hours worked from Form A (from the reference
period) to appear in the field in Form B.

Would anyone have any ideas?

I am currently using ACCESS 2007.

Thank you.

It all depends on your tables. Forms are secondary; they're just windows on
the data stored in your tables.

What are your tables and how are they related?

Do note that you should not be storing any cumulative hours or subtotals or
grand totals *anywhere* - they should by dynamically calculated from the
actual worked hours stored in the table. This can be done using a totals
query, a form (with totals in the form footer), or a report, but the
cumulative value would not need to be stored. It appears that you want a
Totals query using your reference period textboxes on your form as criteria,
in the form

= [Forms]![YourFormName]![Week Begin] AND < DateAdd("d", 1, [Forms]![YourFormName]![Week End])

The DateAdd takes care of the fact that your date/time field probably contains
a time portion; adding a day will get all of the timepoints between 12:00:00
am and 11:59:59+ pm on the day specified.

Hi John,

In essence you are very correct. First off, no calculations will be saved
in tables. They are strictly in the forms.

Now my question relates to the formula you mentioned above for the totals
query. How should I go about doing this? Should I create the query and then
refer to in an unbound control on the form? Now considering that I have a
field where I wish this total value to be populated after the date filter
event, what should I do then? Thanks again!!

 

[John W. Vinson]

= [Forms]![YourFormName]![Week Begin] AND < DateAdd("d", 1, [Forms]![YourFormName]![Week End])

The DateAdd takes care of the fact that your date/time field probably contains
a time portion; adding a day will get all of the timepoints between 12:00:00
am and 11:59:59+ pm on the day specified.

Hi John,

In essence you are very correct. First off, no calculations will be saved
in tables. They are strictly in the forms.

Now my question relates to the formula you mentioned above for the totals
query. How should I go about doing this? Should I create the query and then
refer to in an unbound control on the form? Now considering that I have a
field where I wish this total value to be populated after the date filter
event, what should I do then? Thanks again!!

Create or use a Form (I'm calling it YourFormName, substitute your own
formname of course) wit two unbound controls named Week Begin and Week End
(though I'd actually avoid using blanks in names - i'd use WeekBegin and
WeekEnd for example).

Use the expression above on the Criteria line of the query, applying it to the
date field containing the date that you want to filter, and use the "Where"
totals operator.

There isn't a separate "filter event" - the criterion is simply part of the
query and limits which records are retrieved in the first place.

 

[Chris61]

= [Forms]![YourFormName]![Week Begin] AND < DateAdd("d", 1, [Forms]![YourFormName]![Week End])

The DateAdd takes care of the fact that your date/time field probably contains
a time portion; adding a day will get all of the timepoints between 12:00:00
am and 11:59:59+ pm on the day specified.

Hi John,

In essence you are very correct. First off, no calculations will be saved
in tables. They are strictly in the forms.

Now my question relates to the formula you mentioned above for the totals
query. How should I go about doing this? Should I create the query and then
refer to in an unbound control on the form? Now considering that I have a
field where I wish this total value to be populated after the date filter
event, what should I do then? Thanks again!!

Create or use a Form (I'm calling it YourFormName, substitute your own
formname of course) wit two unbound controls named Week Begin and Week End
(though I'd actually avoid using blanks in names - i'd use WeekBegin and
WeekEnd for example).

Use the expression above on the Criteria line of the query, applying it to the
date field containing the date that you want to filter, and use the "Where"
totals operator.

There isn't a separate "filter event" - the criterion is simply part of the
query and limits which records are retrieved in the first place.

I'm fairly certain that I didn't quite get it. Please bear with me or take
a long sip of something strong.

Okay.

Form A - Main form: gives personal information (name, address, etc...)

Form B - Subform 1: scheduling info (Date, Time in, Time out, overtime and
total hrs worked) The fields in this subform are numerical based on 0.25
increments. All this information appears in one record to represent different
days.

Form C - Subform 2: productivity

It is Form C that is causing my current headache. As I explained, I have 2
text boxes representing the beginning and end of a week. Followed by various
text boxes for productivity measures, and then a text box for the hrs worked
appearing again. The key is that I want the total of my productivity
measures to be divided by my hours worked, this would give me the
productivity rate. However, the hours worked from Form B are daily. I want
the text box in Form C to give me the total hrs worked for a weekly period of
my choosing.

If I understood you correctly, I should create a totals query. What I am
not mastering is the query build itself.

Column 1 of my query would contain the date field. In the criteria I would
put the formula that you provided with the totals operator “Whereâ€. However,
what I am missing is how this will result in the hrs worked weekly total
populated in the text box that I have in Form C.

Thanks!

Chris

 

[John W. Vinson]

I'm fairly certain that I didn't quite get it. Please bear with me or take
a long sip of something strong.

Okay.

Form A - Main form: gives personal information (name, address, etc...)

Form B - Subform 1: scheduling info (Date, Time in, Time out, overtime and
total hrs worked) The fields in this subform are numerical based on 0.25
increments. All this information appears in one record to represent different
days.

Form C - Subform 2: productivity

It is Form C that is causing my current headache. As I explained, I have 2
text boxes representing the beginning and end of a week. Followed by various
text boxes for productivity measures, and then a text box for the hrs worked
appearing again. The key is that I want the total of my productivity
measures to be divided by my hours worked, this would give me the
productivity rate. However, the hours worked from Form B are daily. I want
the text box in Form C to give me the total hrs worked for a weekly period of
my choosing.

If I understood you correctly, I should create a totals query. What I am
not mastering is the query build itself.

Column 1 of my query would contain the date field. In the criteria I would
put the formula that you provided with the totals operator “Where”. However,
what I am missing is how this will result in the hrs worked weekly total
populated in the text box that I have in Form C.

I may be mistaken... but it appears that you're making the very common
beginner's mistake of focusing on Forms.

Data is not stored in Forms. Queries do not pull data from Forms (usually,
they can in very special peculiar situations).

Data is stored in Tables and ONLY in Tables. Queries - especially totals
queries - are built upon Tables.

What are your TABLES? How are they related? What is the structure of the table
in schedule B - do you <shudder> have fields for MondayTimeIn, TuesdayTimeIn,
WednesdayTimeIn?

What is it (from the table!!!) that you want displayed on form C?