Grouping of data within a form/table

[DominicGreco via AccessMonster.com]

I've got a table that contains timesheet info. During the week, a user will
open a form and enter the tasks they've completed for that day. At the end of
the week, after they've entered all the tasks they've completed during the
week, I would like to have a separate form that gathers all the data entered
(for that week) and groups it together in datasheet mode. This will probably
end up being a subform on the main timesheet form. This sounds alot like a
query that would display the information from the table. But there is a twist.


I would like all the tasks that are identical to only list once. The hours
for the identical tasks would be summed up and listed in a column. (This data
will be stored in another table). You see, the users need to enter a "%
complete" for the tasks. In order to do this, I need to gather up all the
identical tasks and make them one entry.

The user would then enter in the percent complete for each of the tasks.
(This percent complete is viewed by the PMs and is pretty much the reason for
the entire database!). Of course, there needs to be some code written that
detects if the users has completed their timesheet for the week before it
will allow them to start adding "% Complete" for their tasks.

I know there is a way to group similar data like this. I just don't know what
it is called.

What's the best way to do this?

Thanks,
Dominic

 

[Arvin Meyer [MVP]]

I know there is a way to group similar data like this. I just don't know
what
it is called.

It's an aggregate or Totals query. Use the Sigma button in the query editor
to turn it on.

 

[DominicGreco via AccessMonster.com]

Thanks! Before I read your reply I had been playing with Sub Queries and was
using the wizard to build a query of my main query. What I ended up with was
a Totals/aggregate query!

Using your tip I just used the sigma button, and used grouping and sum to
change the way the data is displayed.

Now I have to figure out a way to store that sum (most likely a separate
table) and add some columns for "% complete" and "hours remaining".

 

[Klatuu]

First, a totals query cannot be updated because each row you see is actually
an aggragate of 1 or more rows.

If you need to put these percentages in a table, you will have to have a
table to carry those data. It may be possible you could use the result of
your totals query to update a table that you could then use in your subform
that would be updatable.

 

[DominicGreco via AccessMonster.com]

You wrote:
"First, a totals query cannot be updated because each row you see is actually

an aggragate of 1 or more rows."

Not sure I understand what you mean. Can you clarify that a bit?

I do understand about needing a table to store the new data. The most likely
place would be a separate table that would look alot like the one my query
was written about (but it would not have as many fields) .

But right now I'm having trouble getting the data from the datasheet field
into my table. The control source is field in my query. You chance that and
the field fails. I'm thinking that I can make an invisble textbox that reads
the field in the datasheet then will in turn have it's control source be my
table. Sort of like a "Pass Through" box.

First, a totals query cannot be updated because each row you see is actually
an aggragate of 1 or more rows.

If you need to put these percentages in a table, you will have to have a
table to carry those data. It may be possible you could use the result of
your totals query to update a table that you could then use in your subform
that would be updatable.

Thanks! Before I read your reply I had been playing with Sub Queries and was
using the wizard to build a query of my main query. What I ended up with was

[quoted text clipped - 12 lines]

 

[Arvin Meyer [MVP]]

Now I have to figure out a way to store that sum (most likely a separate
table) and add some columns for "% complete" and "hours remaining".

Unless you have a special reason for storing a sum, it is against relational
database normalization rules to do so. Since processing time is so fast, it
is also unnecessary since you can recalculate easily.

 

[DominicGreco via AccessMonster.com]

Yes, you are correct! Storing that sum would be stupid. I could alway re-calc
it.

But I still need to figure out a way to store the other data from the table.
Since each of the fields of the datasheet style form derives it's control
source from the query, I can't just tell it that the control source is now a
table. I've tried using code to say something like:

Me.txtWeek_end.value = Me.Week_End.Value
Me.Week_End.Value is the field in the datasheet form while "Me.txtWeek_end"
is an invisible text box whose control source is my new table.

I put this in the Beforeupdate event and it didn't work.

 

[Klatuu]

What I am saying about totals queries is that you can't change the data they
present nor can you add a field to store the percent complete like you said
was your objective.

As to changing the record source for the subform. You don't need to. The
way to do it would be to create an append query that would append records to
a table that looks like your totals query fields with an additional field to
enter the percent complete. Your totals query would be the source of the
append.

Run the append query before the user opens the form, then use the table as
the subform's recordset.

--
Dave Hargis, Microsoft Access MVP

You wrote:
"First, a totals query cannot be updated because each row you see is actually

an aggragate of 1 or more rows."

Not sure I understand what you mean. Can you clarify that a bit?

I do understand about needing a table to store the new data. The most likely
place would be a separate table that would look alot like the one my query
was written about (but it would not have as many fields) .

But right now I'm having trouble getting the data from the datasheet field
into my table. The control source is field in my query. You chance that and
the field fails. I'm thinking that I can make an invisble textbox that reads
the field in the datasheet then will in turn have it's control source be my
table. Sort of like a "Pass Through" box.

First, a totals query cannot be updated because each row you see is actually
an aggragate of 1 or more rows.

If you need to put these percentages in a table, you will have to have a
table to carry those data. It may be possible you could use the result of
your totals query to update a table that you could then use in your subform
that would be updatable.

Thanks! Before I read your reply I had been playing with Sub Queries and was
using the wizard to build a query of my main query. What I ended up with was

[quoted text clipped - 12 lines]

It's an aggregate or Totals query. Use the Sigma button in the query editor
to turn it on.

 

[DominicGreco via AccessMonster.com]

First off, thanks for the help. I appreciate it. But I'm a realtive newbie to
access and have a hard time following the nomenclature at times. So forgive
me if I ask some seemingly stupid questions.

OK, I got the part about the append query and have constructed that. I also
made a separate (blank) table to house this data. This table LOOKS like my
original one (the one the query is built around) but has fields for "%
Complete", and "Hours Remaining". In the "hours" field of the query, I
changed the Sigma to change it to a "Totals" and used sum to get me the total
hours. Now when I run the query it populates the new table with the data.

I've also added some criteria to the Employee and Week Ending fields that
looks at the "Employee Name" and "Week Ending" Listbox of my main form. So
when the query is run, and the form is active, I get data only relevant for
the employee name and week ending values in their associated list boxes.

You wrote:
".....with an additional field to enter the percent complete. Your totals
query would be the source of the append.....Run the append query before the
user opens the form, then use the table as the subform's recordset"

When you say "additional field to enter the percent complete", are you saying
that the new table would have this additional field (percent complete)? If so,
then I'm covered.

And I'm assuming that I would run the query using a VB app in the Forms
OnCurrent (or some similar) event. Right?

This is the first time I've used an append query. Every time it's run, does
it append the table? Or does it recognize that the appended data exists in
the table already?

So my Append query runs, then it updates the table. The table is tied to my
subform (or form) and this is where the user can enter the "%complete", and
"hours remaining"?

What I am saying about totals queries is that you can't change the data they
present nor can you add a field to store the percent complete like you said
was your objective.

As to changing the record source for the subform. You don't need to. The
way to do it would be to create an append query that would append records to
a table that looks like your totals query fields with an additional field to
enter the percent complete. Your totals query would be the source of the
append.

Run the append query before the user opens the form, then use the table as
the subform's recordset.

You wrote:
"First, a totals query cannot be updated because each row you see is actually

[quoted text clipped - 25 lines]

 

[Klatuu]

See answers below in line:
--
Dave Hargis, Microsoft Access MVP

First off, thanks for the help. I appreciate it. But I'm a realtive newbie to
access and have a hard time following the nomenclature at times. So forgive
me if I ask some seemingly stupid questions.

Not to worry, no stupid questions.

OK, I got the part about the append query and have constructed that. I also
made a separate (blank) table to house this data. This table LOOKS like my
original one (the one the query is built around) but has fields for "%
Complete", and "Hours Remaining". In the "hours" field of the query, I
changed the Sigma to change it to a "Totals" and used sum to get me the total
hours. Now when I run the query it populates the new table with the data.

Good. that is what you need to do.

I've also added some criteria to the Employee and Week Ending fields that
looks at the "Employee Name" and "Week Ending" Listbox of my main form. So
when the query is run, and the form is active, I get data only relevant for
the employee name and week ending values in their associated list boxes.

Exactly, that is what you need.

You wrote:
".....with an additional field to enter the percent complete. Your totals
query would be the source of the append.....Run the append query before the
user opens the form, then use the table as the subform's recordset"

When you say "additional field to enter the percent complete", are you saying
that the new table would have this additional field (percent complete)? If so,
then I'm covered.

Yes, you are covered. That is the point of the exercise.

And I'm assuming that I would run the query using a VB app in the Forms
OnCurrent (or some similar) event. Right?

Well, this may or may not be correct. It will depend on when you need it to
happen. If you are going to run the query specific to an employee and a week
einding, then you need to wait until you have those values for the query to
use. Since I don't know how your form works, I can't say for sure.

This is the first time I've used an append query. Every time it's run, does
it append the table? Or does it recognize that the appended data exists in
the table already?

Actually, a very astute question. You should use a WHERE clause in the
append query so it will not add additional rows or error out. You can use a
subquery to do that. it would be something like:
WHERE NOT IN (SELECT Employee, WeekEnding FROM TotalsQuery)
I don't know your field names or your Totals query names but so you will
have to tailor it to suit. I use Employee and WeekEnding, because based on
your posts, that seems to create a unique record. If you have to add other
fields so that it will not try to duplicate a record, then do that.

So my Append query runs, then it updates the table. The table is tied to my
subform (or form) and this is where the user can enter the "%complete", and
"hours remaining"?

Yes, the table would be the record source of the subform. The user would
then be able to enter the percentage and hours remaining there. You may want
to disable the controls that have the data you appended so the user can't
change them.

One other note here is that if a task has a number of hours assigned and the
user enters the number of hours worked, you want to calculate the percent
complete. That; however, would take some extra effort, because a task might
take more than one week to complete, so you would have to use something like
a DSum to find all the hours worked on that task by the employee. That would
be proper database normalization because values that can be calculated should
not be stored.

What I am saying about totals queries is that you can't change the data they
present nor can you add a field to store the percent complete like you said
was your objective.

As to changing the record source for the subform. You don't need to. The
way to do it would be to create an append query that would append records to
a table that looks like your totals query fields with an additional field to
enter the percent complete. Your totals query would be the source of the
append.

Run the append query before the user opens the form, then use the table as
the subform's recordset.

You wrote:
"First, a totals query cannot be updated because each row you see is actually

[quoted text clipped - 25 lines]

It's an aggregate or Totals query. Use the Sigma button in the query editor
to turn it on.

 

[Arvin Meyer [MVP]]

If the query is updateable, then the data in the table should be updateable.
Bound forms will automatically update the underlying data tables.

 

[DominicGreco via AccessMonster.com]

Thanks for the help! I KNEW there oughta' be a way to limit the append query
from writing data that already existed!

So you're saying that you would make a sub query off of the Append Query?
(Damn, I didn't think you could do that.) But where would I put the "WHERE"?
I already have the statement [Forms]![frmEng1]![cmbEmployee] in my Query's
"Criteria:" box

If it would help, the SQL for my query looks like this:
INSERT INTO tblEng2 ( employee, week_end, [Date], proj_num, Task, Div_Letter,
Total_hours )
SELECT tblEng.employee, tblEng.week_end, tblEng.Date, tblEng.proj_num, tblEng.
Task, tblEng.Div_Letter, Sum(tblEng.hours) AS SumOfhours
FROM tblEng
GROUP BY tblEng.employee, tblEng.week_end, tblEng.Date, tblEng.proj_num,
tblEng.Task, tblEng.Div_Letter
HAVING (((tblEng.employee)=[Forms]![frmEng1]![cmbEmployee]) AND ((tblEng.
week_end)=[Forms]![frmEng1]![week_end]))
ORDER BY tblEng.employee;

See answers below in line:

First off, thanks for the help. I appreciate it. But I'm a realtive newbie to
access and have a hard time following the nomenclature at times. So forgive
me if I ask some seemingly stupid questions.

Not to worry, no stupid questions.

OK, I got the part about the append query and have constructed that. I also
made a separate (blank) table to house this data. This table LOOKS like my
original one (the one the query is built around) but has fields for "%
Complete", and "Hours Remaining". In the "hours" field of the query, I
changed the Sigma to change it to a "Totals" and used sum to get me the total
hours. Now when I run the query it populates the new table with the data.

Good. that is what you need to do.

I've also added some criteria to the Employee and Week Ending fields that
looks at the "Employee Name" and "Week Ending" Listbox of my main form. So
when the query is run, and the form is active, I get data only relevant for
the employee name and week ending values in their associated list boxes.

Exactly, that is what you need.

You wrote:
".....with an additional field to enter the percent complete. Your totals

[quoted text clipped - 4 lines]

that the new table would have this additional field (percent complete)? If so,
then I'm covered.

Yes, you are covered. That is the point of the exercise.

And I'm assuming that I would run the query using a VB app in the Forms
OnCurrent (or some similar) event. Right?

Well, this may or may not be correct. It will depend on when you need it to
happen. If you are going to run the query specific to an employee and a week
einding, then you need to wait until you have those values for the query to
use. Since I don't know how your form works, I can't say for sure.

This is the first time I've used an append query. Every time it's run, does
it append the table? Or does it recognize that the appended data exists in
the table already?

Actually, a very astute question. You should use a WHERE clause in the
append query so it will not add additional rows or error out. You can use a
subquery to do that. it would be something like:
WHERE NOT IN (SELECT Employee, WeekEnding FROM TotalsQuery)
I don't know your field names or your Totals query names but so you will
have to tailor it to suit. I use Employee and WeekEnding, because based on
your posts, that seems to create a unique record. If you have to add other
fields so that it will not try to duplicate a record, then do that.

So my Append query runs, then it updates the table. The table is tied to my
subform (or form) and this is where the user can enter the "%complete", and
"hours remaining"?

Yes, the table would be the record source of the subform. The user would
then be able to enter the percentage and hours remaining there. You may want
to disable the controls that have the data you appended so the user can't
change them.

One other note here is that if a task has a number of hours assigned and the
user enters the number of hours worked, you want to calculate the percent
complete. That; however, would take some extra effort, because a task might
take more than one week to complete, so you would have to use something like
a DSum to find all the hours worked on that task by the employee. That would
be proper database normalization because values that can be calculated should
not be stored.

What I am saying about totals queries is that you can't change the data they
present nor can you add a field to store the percent complete like you said [quoted text clipped - 14 lines]
It's an aggregate or Totals query. Use the Sigma button in the query editor
to turn it on.

 

[DominicGreco via AccessMonster.com]

OK, I've tried deciphering the nomenclature for the WHERE NOT IN statement
with little or no luck! :>( HELP!!!!

From what I can understand it is saying "Append only the data WHERE the
entries for employee and week_end are NOT IN the table already"

Is the name "TotalsQuery" in your example the name of the query? Or the name
of the table? Because when I add the statement into my Query's SQL, and use
the name of my table it gives me an error