Totals on report

[Stockwell43]

Hello,

I have a database with a subform with the following fields: Tasks and
Volume. Everything works fine and I can pull a report and it looks good.
However, I need to be able to total each task so as the users are entering
their volume for the month, I don't have to look for each separate task on
the report to add it.

Example:

Employee Date Tasks Volume
John 10/3/08 Advances 12
Jane 10/6/08 Cash Receipts 20
Ed 10/1/08 Item Processing 15
John 10/5/08 Advances 22
John 10/4/08 Advances 18
Jane 10/2/08 Cash Receipts 8

My report is grouped by employee so all of John, Jane etc are sectioned. My
problem is after a month of entering data, how would I add all the advances
John di for the month without having to go through each day on the report.
The same for Jane with Case Receipts etc....??

Thanks!!!

 

[KARL DEWEY]

Try this --
SELECT Employee, Format([DateField], "mmm yyyy") AS Work_Date, Tasks,
Sum([Volume]) AS Month_Volume
FROM YourTable
GROUP BY Employee, Format([DateField], "yyyymm"), Tasks;

 

[Stockwell43]

Hi Karl,

It doesn't work. I placed it in my query using FROM tblvolumes and it ask
for an employee, date and task (which I do not select anything because I want
to see all the people) and it comes up blank except for the volumes column
which total everything. Am I setting something up wrong?

I have two tables in my query tblemployees and tblvolumes.

tblemployee gives me the employee name, current date and comments fields.
tblvloumes gives me the task(listed in a separate table but selected on the
sub form from a cbo) and volumes.

All I want to do is during the month as the users enter the various tasks
they worked on I want to see the total of each task entered for each
employee. So in my previous example John worked on Advances 3 days of the
month so under Johns name on the report, I want to show Advances 52. Does
that make sense?

Thanks!!

Try this --
SELECT Employee, Format([DateField], "mmm yyyy") AS Work_Date, Tasks,
Sum([Volume]) AS Month_Volume
FROM YourTable
GROUP BY Employee, Format([DateField], "yyyymm"), Tasks;

--
KARL DEWEY
Build a little - Test a little

Hello,

I have a database with a subform with the following fields: Tasks and
Volume. Everything works fine and I can pull a report and it looks good.
However, I need to be able to total each task so as the users are entering
their volume for the month, I don't have to look for each separate task on
the report to add it.

Example:

Employee Date Tasks Volume
John 10/3/08 Advances 12
Jane 10/6/08 Cash Receipts 20
Ed 10/1/08 Item Processing 15
John 10/5/08 Advances 22
John 10/4/08 Advances 18
Jane 10/2/08 Cash Receipts 8

My report is grouped by employee so all of John, Jane etc are sectioned. My
problem is after a month of entering data, how would I add all the advances
John di for the month without having to go through each day on the report.
The same for Jane with Case Receipts etc....??

Thanks!!!

 

[KARL DEWEY]

Post the SQL of the query you ran.
How are the two tables related?
Post sample data from both tables.

--
KARL DEWEY
Build a little - Test a little

Hi Karl,

It doesn't work. I placed it in my query using FROM tblvolumes and it ask
for an employee, date and task (which I do not select anything because I want
to see all the people) and it comes up blank except for the volumes column
which total everything. Am I setting something up wrong?

I have two tables in my query tblemployees and tblvolumes.

tblemployee gives me the employee name, current date and comments fields.
tblvloumes gives me the task(listed in a separate table but selected on the
sub form from a cbo) and volumes.

All I want to do is during the month as the users enter the various tasks
they worked on I want to see the total of each task entered for each
employee. So in my previous example John worked on Advances 3 days of the
month so under Johns name on the report, I want to show Advances 52. Does
that make sense?

Thanks!!

Try this --
SELECT Employee, Format([DateField], "mmm yyyy") AS Work_Date, Tasks,
Sum([Volume]) AS Month_Volume
FROM YourTable
GROUP BY Employee, Format([DateField], "yyyymm"), Tasks;

--
KARL DEWEY
Build a little - Test a little

Hello,

I have a database with a subform with the following fields: Tasks and
Volume. Everything works fine and I can pull a report and it looks good.
However, I need to be able to total each task so as the users are entering
their volume for the month, I don't have to look for each separate task on
the report to add it.

Example:

Employee Date Tasks Volume
John 10/3/08 Advances 12
Jane 10/6/08 Cash Receipts 20
Ed 10/1/08 Item Processing 15
John 10/5/08 Advances 22
John 10/4/08 Advances 18
Jane 10/2/08 Cash Receipts 8

My report is grouped by employee so all of John, Jane etc are sectioned. My
problem is after a month of entering data, how would I add all the advances
John di for the month without having to go through each day on the report.
The same for Jane with Case Receipts etc....??

Thanks!!!

 

[Stockwell43]

Hi Karl,

Here is the SQL:
SELECT tblmonthlytasks.EmployeeName, tblmonthlytasks.CurrentDate,
tblvolumes.TaskName, tblvolumes.Volume, tblmonthlytasks.Comments
FROM tblmonthlytasks INNER JOIN tblvolumes ON tblmonthlytasks.TaskID =
tblvolumes.TaskID;

I have two tables: tblmonthlytasks and tblvolumes. tblmonthlytasks has
TaskID-autonumber, Employee Name-Text, Current Date-Date and Comments-Memo.
tblvolumes has TaskID-Number, TaskName-Text, Volumes-Text.

They are related by TaskID for main form and subform. Table wise, they are
related as a one to many with tblmonthlytasks being the one and tblvolumes
being the many.

A sample of the data is posted on my last posting. It is a relatively simple
database but I always have problems with these subforms pulling data out. I
did manage to get what I want manipulating the reports but not as good as
working it through the query first. What I have will work but I don't have
the flexibility working with just the report which is why if it can be done
through the query, it would make it easier having to work with both.

Anything you can do to help would be most appreciated. If I have this set up
wrong, please let me know. Thank you for your help thus far and I appreciate
you staying with me.

Post the SQL of the query you ran.
How are the two tables related?
Post sample data from both tables.

--
KARL DEWEY
Build a little - Test a little

Hi Karl,

It doesn't work. I placed it in my query using FROM tblvolumes and it ask
for an employee, date and task (which I do not select anything because I want
to see all the people) and it comes up blank except for the volumes column
which total everything. Am I setting something up wrong?

I have two tables in my query tblemployees and tblvolumes.

tblemployee gives me the employee name, current date and comments fields.
tblvloumes gives me the task(listed in a separate table but selected on the
sub form from a cbo) and volumes.

All I want to do is during the month as the users enter the various tasks
they worked on I want to see the total of each task entered for each
employee. So in my previous example John worked on Advances 3 days of the
month so under Johns name on the report, I want to show Advances 52. Does
that make sense?

Thanks!!

Try this --
SELECT Employee, Format([DateField], "mmm yyyy") AS Work_Date, Tasks,
Sum([Volume]) AS Month_Volume
FROM YourTable
GROUP BY Employee, Format([DateField], "yyyymm"), Tasks;

--
KARL DEWEY
Build a little - Test a little


:

Hello,

I have a database with a subform with the following fields: Tasks and
Volume. Everything works fine and I can pull a report and it looks good.
However, I need to be able to total each task so as the users are entering
their volume for the month, I don't have to look for each separate task on
the report to add it.

Example:

Employee Date Tasks Volume
John 10/3/08 Advances 12
Jane 10/6/08 Cash Receipts 20
Ed 10/1/08 Item Processing 15
John 10/5/08 Advances 22
John 10/4/08 Advances 18
Jane 10/2/08 Cash Receipts 8

My report is grouped by employee so all of John, Jane etc are sectioned. My
problem is after a month of entering data, how would I add all the advances
John di for the month without having to go through each day on the report.
The same for Jane with Case Receipts etc....??

Thanks!!!

 

[Stockwell43]

Hi Karl,

I plugged it in and when I ran it, I got a message pop up:

You tried to execute a query that does not include the specified expression
'TaskName' as part of an aggregate function.

Was I suppose to change something?

Thanks!

Try this --
SELECT tblmonthlytasks.EmployeeName, tblmonthlytasks.CurrentDate,
tblvolumes.TaskName, Sum(tblvolumes.Volume) AS Monthly_Volume,
tblmonthlytasks.Comments
FROM tblmonthlytasks LEFT JOIN tblvolumes ON tblmonthlytasks.TaskID =
tblvolumes.TaskID
GROUP BY tblmonthlytasks.CurrentDate, tblmonthlytasks.EmployeeName;

--
KARL DEWEY
Build a little - Test a little

Hi Karl,

Here is the SQL:
SELECT tblmonthlytasks.EmployeeName, tblmonthlytasks.CurrentDate,
tblvolumes.TaskName, tblvolumes.Volume, tblmonthlytasks.Comments
FROM tblmonthlytasks INNER JOIN tblvolumes ON tblmonthlytasks.TaskID =
tblvolumes.TaskID;

I have two tables: tblmonthlytasks and tblvolumes. tblmonthlytasks has
TaskID-autonumber, Employee Name-Text, Current Date-Date and Comments-Memo.
tblvolumes has TaskID-Number, TaskName-Text, Volumes-Text.

They are related by TaskID for main form and subform. Table wise, they are
related as a one to many with tblmonthlytasks being the one and tblvolumes
being the many.

A sample of the data is posted on my last posting. It is a relatively simple
database but I always have problems with these subforms pulling data out. I
did manage to get what I want manipulating the reports but not as good as
working it through the query first. What I have will work but I don't have
the flexibility working with just the report which is why if it can be done
through the query, it would make it easier having to work with both.

Anything you can do to help would be most appreciated. If I have this set up
wrong, please let me know. Thank you for your help thus far and I appreciate
you staying with me.

Post the SQL of the query you ran.
How are the two tables related?
Post sample data from both tables.

--
KARL DEWEY
Build a little - Test a little


:

Hi Karl,

It doesn't work. I placed it in my query using FROM tblvolumes and it ask
for an employee, date and task (which I do not select anything because I want
to see all the people) and it comes up blank except for the volumes column
which total everything. Am I setting something up wrong?

I have two tables in my query tblemployees and tblvolumes.

tblemployee gives me the employee name, current date and comments fields.
tblvloumes gives me the task(listed in a separate table but selected on the
sub form from a cbo) and volumes.

All I want to do is during the month as the users enter the various tasks
they worked on I want to see the total of each task entered for each
employee. So in my previous example John worked on Advances 3 days of the
month so under Johns name on the report, I want to show Advances 52. Does
that make sense?

Thanks!!

:

Try this --
SELECT Employee, Format([DateField], "mmm yyyy") AS Work_Date, Tasks,
Sum([Volume]) AS Month_Volume
FROM YourTable
GROUP BY Employee, Format([DateField], "yyyymm"), Tasks;

--
KARL DEWEY
Build a little - Test a little


:

Hello,

I have a database with a subform with the following fields: Tasks and
Volume. Everything works fine and I can pull a report and it looks good.
However, I need to be able to total each task so as the users are entering
their volume for the month, I don't have to look for each separate task on
the report to add it.

Example:

Employee Date Tasks Volume
John 10/3/08 Advances 12
Jane 10/6/08 Cash Receipts 20
Ed 10/1/08 Item Processing 15
John 10/5/08 Advances 22
John 10/4/08 Advances 18
Jane 10/2/08 Cash Receipts 8

My report is grouped by employee so all of John, Jane etc are sectioned. My
problem is after a month of entering data, how would I add all the advances
John di for the month without having to go through each day on the report.
The same for Jane with Case Receipts etc....??

Thanks!!!

 

[KARL DEWEY]

Add 'TaskName' in the Group By line. Also include 'Comments.'
--
KARL DEWEY
Build a little - Test a little

Hi Karl,

I plugged it in and when I ran it, I got a message pop up:

You tried to execute a query that does not include the specified expression
'TaskName' as part of an aggregate function.

Was I suppose to change something?

Thanks!

Try this --
SELECT tblmonthlytasks.EmployeeName, tblmonthlytasks.CurrentDate,
tblvolumes.TaskName, Sum(tblvolumes.Volume) AS Monthly_Volume,
tblmonthlytasks.Comments
FROM tblmonthlytasks LEFT JOIN tblvolumes ON tblmonthlytasks.TaskID =
tblvolumes.TaskID
GROUP BY tblmonthlytasks.CurrentDate, tblmonthlytasks.EmployeeName;

--
KARL DEWEY
Build a little - Test a little

Hi Karl,

Here is the SQL:
SELECT tblmonthlytasks.EmployeeName, tblmonthlytasks.CurrentDate,
tblvolumes.TaskName, tblvolumes.Volume, tblmonthlytasks.Comments
FROM tblmonthlytasks INNER JOIN tblvolumes ON tblmonthlytasks.TaskID =
tblvolumes.TaskID;

I have two tables: tblmonthlytasks and tblvolumes. tblmonthlytasks has
TaskID-autonumber, Employee Name-Text, Current Date-Date and Comments-Memo.
tblvolumes has TaskID-Number, TaskName-Text, Volumes-Text.

They are related by TaskID for main form and subform. Table wise, they are
related as a one to many with tblmonthlytasks being the one and tblvolumes
being the many.

A sample of the data is posted on my last posting. It is a relatively simple
database but I always have problems with these subforms pulling data out. I
did manage to get what I want manipulating the reports but not as good as
working it through the query first. What I have will work but I don't have
the flexibility working with just the report which is why if it can be done
through the query, it would make it easier having to work with both.

Anything you can do to help would be most appreciated. If I have this set up
wrong, please let me know. Thank you for your help thus far and I appreciate
you staying with me.

:

Post the SQL of the query you ran.
How are the two tables related?
Post sample data from both tables.

--
KARL DEWEY
Build a little - Test a little


:

Hi Karl,

It doesn't work. I placed it in my query using FROM tblvolumes and it ask
for an employee, date and task (which I do not select anything because I want
to see all the people) and it comes up blank except for the volumes column
which total everything. Am I setting something up wrong?

I have two tables in my query tblemployees and tblvolumes.

tblemployee gives me the employee name, current date and comments fields.
tblvloumes gives me the task(listed in a separate table but selected on the
sub form from a cbo) and volumes.

All I want to do is during the month as the users enter the various tasks
they worked on I want to see the total of each task entered for each
employee. So in my previous example John worked on Advances 3 days of the
month so under Johns name on the report, I want to show Advances 52. Does
that make sense?

Thanks!!

:

Try this --
SELECT Employee, Format([DateField], "mmm yyyy") AS Work_Date, Tasks,
Sum([Volume]) AS Month_Volume
FROM YourTable
GROUP BY Employee, Format([DateField], "yyyymm"), Tasks;

--
KARL DEWEY
Build a little - Test a little


:

Hello,

I have a database with a subform with the following fields: Tasks and
Volume. Everything works fine and I can pull a report and it looks good.
However, I need to be able to total each task so as the users are entering
their volume for the month, I don't have to look for each separate task on
the report to add it.

Example:

Employee Date Tasks Volume
John 10/3/08 Advances 12
Jane 10/6/08 Cash Receipts 20
Ed 10/1/08 Item Processing 15
John 10/5/08 Advances 22
John 10/4/08 Advances 18
Jane 10/2/08 Cash Receipts 8

My report is grouped by employee so all of John, Jane etc are sectioned. My
problem is after a month of entering data, how would I add all the advances
John di for the month without having to go through each day on the report.
The same for Jane with Case Receipts etc....??

Thanks!!!

 

[Stockwell43]

It works now, but still doesn't group the tasks by name with the totals each
task by the same employee. So for John, on 10/03/2008 if Advances is 6 and
10/06/2008 Advances is 22, it still shows like this in the query instead of
John Advances 28.

Add 'TaskName' in the Group By line. Also include 'Comments.'
--
KARL DEWEY
Build a little - Test a little

Hi Karl,

I plugged it in and when I ran it, I got a message pop up:

You tried to execute a query that does not include the specified expression
'TaskName' as part of an aggregate function.

Was I suppose to change something?

Thanks!

Try this --
SELECT tblmonthlytasks.EmployeeName, tblmonthlytasks.CurrentDate,
tblvolumes.TaskName, Sum(tblvolumes.Volume) AS Monthly_Volume,
tblmonthlytasks.Comments
FROM tblmonthlytasks LEFT JOIN tblvolumes ON tblmonthlytasks.TaskID =
tblvolumes.TaskID
GROUP BY tblmonthlytasks.CurrentDate, tblmonthlytasks.EmployeeName;

--
KARL DEWEY
Build a little - Test a little


:

Hi Karl,

Here is the SQL:
SELECT tblmonthlytasks.EmployeeName, tblmonthlytasks.CurrentDate,
tblvolumes.TaskName, tblvolumes.Volume, tblmonthlytasks.Comments
FROM tblmonthlytasks INNER JOIN tblvolumes ON tblmonthlytasks.TaskID =
tblvolumes.TaskID;

I have two tables: tblmonthlytasks and tblvolumes. tblmonthlytasks has
TaskID-autonumber, Employee Name-Text, Current Date-Date and Comments-Memo.
tblvolumes has TaskID-Number, TaskName-Text, Volumes-Text.

They are related by TaskID for main form and subform. Table wise, they are
related as a one to many with tblmonthlytasks being the one and tblvolumes
being the many.

A sample of the data is posted on my last posting. It is a relatively simple
database but I always have problems with these subforms pulling data out. I
did manage to get what I want manipulating the reports but not as good as
working it through the query first. What I have will work but I don't have
the flexibility working with just the report which is why if it can be done
through the query, it would make it easier having to work with both.

Anything you can do to help would be most appreciated. If I have this set up
wrong, please let me know. Thank you for your help thus far and I appreciate
you staying with me.

:

Post the SQL of the query you ran.
How are the two tables related?
Post sample data from both tables.

--
KARL DEWEY
Build a little - Test a little


:

Hi Karl,

It doesn't work. I placed it in my query using FROM tblvolumes and it ask
for an employee, date and task (which I do not select anything because I want
to see all the people) and it comes up blank except for the volumes column
which total everything. Am I setting something up wrong?

I have two tables in my query tblemployees and tblvolumes.

tblemployee gives me the employee name, current date and comments fields.
tblvloumes gives me the task(listed in a separate table but selected on the
sub form from a cbo) and volumes.

All I want to do is during the month as the users enter the various tasks
they worked on I want to see the total of each task entered for each
employee. So in my previous example John worked on Advances 3 days of the
month so under Johns name on the report, I want to show Advances 52. Does
that make sense?

Thanks!!

:

Try this --
SELECT Employee, Format([DateField], "mmm yyyy") AS Work_Date, Tasks,
Sum([Volume]) AS Month_Volume
FROM YourTable
GROUP BY Employee, Format([DateField], "yyyymm"), Tasks;

--
KARL DEWEY
Build a little - Test a little


:

Hello,

I have a database with a subform with the following fields: Tasks and
Volume. Everything works fine and I can pull a report and it looks good.
However, I need to be able to total each task so as the users are entering
their volume for the month, I don't have to look for each separate task on
the report to add it.

Example:

Employee Date Tasks Volume
John 10/3/08 Advances 12
Jane 10/6/08 Cash Receipts 20
Ed 10/1/08 Item Processing 15
John 10/5/08 Advances 22
John 10/4/08 Advances 18
Jane 10/2/08 Cash Receipts 8

My report is grouped by employee so all of John, Jane etc are sectioned. My
problem is after a month of entering data, how would I add all the advances
John di for the month without having to go through each day on the report.
The same for Jane with Case Receipts etc....??

Thanks!!!

 

[KARL DEWEY]

You can not roll them together and have separate date at the same time.
You can omit the date or format it differently such as month and year.
--
KARL DEWEY
Build a little - Test a little

It works now, but still doesn't group the tasks by name with the totals each
task by the same employee. So for John, on 10/03/2008 if Advances is 6 and
10/06/2008 Advances is 22, it still shows like this in the query instead of
John Advances 28.

Add 'TaskName' in the Group By line. Also include 'Comments.'
--
KARL DEWEY
Build a little - Test a little

Hi Karl,

I plugged it in and when I ran it, I got a message pop up:

You tried to execute a query that does not include the specified expression
'TaskName' as part of an aggregate function.

Was I suppose to change something?

Thanks!

:

Try this --
SELECT tblmonthlytasks.EmployeeName, tblmonthlytasks.CurrentDate,
tblvolumes.TaskName, Sum(tblvolumes.Volume) AS Monthly_Volume,
tblmonthlytasks.Comments
FROM tblmonthlytasks LEFT JOIN tblvolumes ON tblmonthlytasks.TaskID =
tblvolumes.TaskID
GROUP BY tblmonthlytasks.CurrentDate, tblmonthlytasks.EmployeeName;

--
KARL DEWEY
Build a little - Test a little


:

Hi Karl,

Here is the SQL:
SELECT tblmonthlytasks.EmployeeName, tblmonthlytasks.CurrentDate,
tblvolumes.TaskName, tblvolumes.Volume, tblmonthlytasks.Comments
FROM tblmonthlytasks INNER JOIN tblvolumes ON tblmonthlytasks.TaskID =
tblvolumes.TaskID;

I have two tables: tblmonthlytasks and tblvolumes. tblmonthlytasks has
TaskID-autonumber, Employee Name-Text, Current Date-Date and Comments-Memo.
tblvolumes has TaskID-Number, TaskName-Text, Volumes-Text.

They are related by TaskID for main form and subform. Table wise, they are
related as a one to many with tblmonthlytasks being the one and tblvolumes
being the many.

A sample of the data is posted on my last posting. It is a relatively simple
database but I always have problems with these subforms pulling data out. I
did manage to get what I want manipulating the reports but not as good as
working it through the query first. What I have will work but I don't have
the flexibility working with just the report which is why if it can be done
through the query, it would make it easier having to work with both.

Anything you can do to help would be most appreciated. If I have this set up
wrong, please let me know. Thank you for your help thus far and I appreciate
you staying with me.

:

Post the SQL of the query you ran.
How are the two tables related?
Post sample data from both tables.

--
KARL DEWEY
Build a little - Test a little


:

Hi Karl,

It doesn't work. I placed it in my query using FROM tblvolumes and it ask
for an employee, date and task (which I do not select anything because I want
to see all the people) and it comes up blank except for the volumes column
which total everything. Am I setting something up wrong?

I have two tables in my query tblemployees and tblvolumes.

tblemployee gives me the employee name, current date and comments fields.
tblvloumes gives me the task(listed in a separate table but selected on the
sub form from a cbo) and volumes.

All I want to do is during the month as the users enter the various tasks
they worked on I want to see the total of each task entered for each
employee. So in my previous example John worked on Advances 3 days of the
month so under Johns name on the report, I want to show Advances 52. Does
that make sense?

Thanks!!

:

Try this --
SELECT Employee, Format([DateField], "mmm yyyy") AS Work_Date, Tasks,
Sum([Volume]) AS Month_Volume
FROM YourTable
GROUP BY Employee, Format([DateField], "yyyymm"), Tasks;

--
KARL DEWEY
Build a little - Test a little


:

Hello,

I have a database with a subform with the following fields: Tasks and
Volume. Everything works fine and I can pull a report and it looks good.
However, I need to be able to total each task so as the users are entering
their volume for the month, I don't have to look for each separate task on
the report to add it.

Example:

Employee Date Tasks Volume
John 10/3/08 Advances 12
Jane 10/6/08 Cash Receipts 20
Ed 10/1/08 Item Processing 15
John 10/5/08 Advances 22
John 10/4/08 Advances 18
Jane 10/2/08 Cash Receipts 8

My report is grouped by employee so all of John, Jane etc are sectioned. My
problem is after a month of entering data, how would I add all the advances
John di for the month without having to go through each day on the report.
The same for Jane with Case Receipts etc....??

Thanks!!!

 

[Stockwell43]

Yeah, when I looked at it I kinda thought that was the case and unfortunately
I need the date field in the report.

Well thank you anyway Karl, I appreciate the time you spent with me on this.

You can not roll them together and have separate date at the same time.
You can omit the date or format it differently such as month and year.
--
KARL DEWEY
Build a little - Test a little

It works now, but still doesn't group the tasks by name with the totals each
task by the same employee. So for John, on 10/03/2008 if Advances is 6 and
10/06/2008 Advances is 22, it still shows like this in the query instead of
John Advances 28.

Add 'TaskName' in the Group By line. Also include 'Comments.'
--
KARL DEWEY
Build a little - Test a little


:

Hi Karl,

I plugged it in and when I ran it, I got a message pop up:

You tried to execute a query that does not include the specified expression
'TaskName' as part of an aggregate function.

Was I suppose to change something?

Thanks!

:

Try this --
SELECT tblmonthlytasks.EmployeeName, tblmonthlytasks.CurrentDate,
tblvolumes.TaskName, Sum(tblvolumes.Volume) AS Monthly_Volume,
tblmonthlytasks.Comments
FROM tblmonthlytasks LEFT JOIN tblvolumes ON tblmonthlytasks.TaskID =
tblvolumes.TaskID
GROUP BY tblmonthlytasks.CurrentDate, tblmonthlytasks.EmployeeName;

--
KARL DEWEY
Build a little - Test a little


:

Hi Karl,

Here is the SQL:
SELECT tblmonthlytasks.EmployeeName, tblmonthlytasks.CurrentDate,
tblvolumes.TaskName, tblvolumes.Volume, tblmonthlytasks.Comments
FROM tblmonthlytasks INNER JOIN tblvolumes ON tblmonthlytasks.TaskID =
tblvolumes.TaskID;

I have two tables: tblmonthlytasks and tblvolumes. tblmonthlytasks has
TaskID-autonumber, Employee Name-Text, Current Date-Date and Comments-Memo.
tblvolumes has TaskID-Number, TaskName-Text, Volumes-Text.

They are related by TaskID for main form and subform. Table wise, they are
related as a one to many with tblmonthlytasks being the one and tblvolumes
being the many.

A sample of the data is posted on my last posting. It is a relatively simple
database but I always have problems with these subforms pulling data out. I
did manage to get what I want manipulating the reports but not as good as
working it through the query first. What I have will work but I don't have
the flexibility working with just the report which is why if it can be done
through the query, it would make it easier having to work with both.

Anything you can do to help would be most appreciated. If I have this set up
wrong, please let me know. Thank you for your help thus far and I appreciate
you staying with me.

:

Post the SQL of the query you ran.
How are the two tables related?
Post sample data from both tables.

--
KARL DEWEY
Build a little - Test a little


:

Hi Karl,

It doesn't work. I placed it in my query using FROM tblvolumes and it ask
for an employee, date and task (which I do not select anything because I want
to see all the people) and it comes up blank except for the volumes column
which total everything. Am I setting something up wrong?

I have two tables in my query tblemployees and tblvolumes.

tblemployee gives me the employee name, current date and comments fields.
tblvloumes gives me the task(listed in a separate table but selected on the
sub form from a cbo) and volumes.

All I want to do is during the month as the users enter the various tasks
they worked on I want to see the total of each task entered for each
employee. So in my previous example John worked on Advances 3 days of the
month so under Johns name on the report, I want to show Advances 52. Does
that make sense?

Thanks!!

:

Try this --
SELECT Employee, Format([DateField], "mmm yyyy") AS Work_Date, Tasks,
Sum([Volume]) AS Month_Volume
FROM YourTable
GROUP BY Employee, Format([DateField], "yyyymm"), Tasks;

--
KARL DEWEY
Build a little - Test a little


:

Hello,

I have a database with a subform with the following fields: Tasks and
Volume. Everything works fine and I can pull a report and it looks good.
However, I need to be able to total each task so as the users are entering
their volume for the month, I don't have to look for each separate task on
the report to add it.

Example:

Employee Date Tasks Volume
John 10/3/08 Advances 12
Jane 10/6/08 Cash Receipts 20
Ed 10/1/08 Item Processing 15
John 10/5/08 Advances 22
John 10/4/08 Advances 18
Jane 10/2/08 Cash Receipts 8

My report is grouped by employee so all of John, Jane etc are sectioned. My
problem is after a month of entering data, how would I add all the advances
John di for the month without having to go through each day on the report.
The same for Jane with Case Receipts etc....??

Thanks!!!

 

[KARL DEWEY]

You can total in one query and join it in another to show the dates.
Alternatively do the totaling in report section footer.
--
KARL DEWEY
Build a little - Test a little

Yeah, when I looked at it I kinda thought that was the case and unfortunately
I need the date field in the report.

Well thank you anyway Karl, I appreciate the time you spent with me on this.

You can not roll them together and have separate date at the same time.
You can omit the date or format it differently such as month and year.
--
KARL DEWEY
Build a little - Test a little

It works now, but still doesn't group the tasks by name with the totals each
task by the same employee. So for John, on 10/03/2008 if Advances is 6 and
10/06/2008 Advances is 22, it still shows like this in the query instead of
John Advances 28.

:

Add 'TaskName' in the Group By line. Also include 'Comments.'
--
KARL DEWEY
Build a little - Test a little


:

Hi Karl,

I plugged it in and when I ran it, I got a message pop up:

You tried to execute a query that does not include the specified expression
'TaskName' as part of an aggregate function.

Was I suppose to change something?

Thanks!

:

Try this --
SELECT tblmonthlytasks.EmployeeName, tblmonthlytasks.CurrentDate,
tblvolumes.TaskName, Sum(tblvolumes.Volume) AS Monthly_Volume,
tblmonthlytasks.Comments
FROM tblmonthlytasks LEFT JOIN tblvolumes ON tblmonthlytasks.TaskID =
tblvolumes.TaskID
GROUP BY tblmonthlytasks.CurrentDate, tblmonthlytasks.EmployeeName;

--
KARL DEWEY
Build a little - Test a little


:

Hi Karl,

Here is the SQL:
SELECT tblmonthlytasks.EmployeeName, tblmonthlytasks.CurrentDate,
tblvolumes.TaskName, tblvolumes.Volume, tblmonthlytasks.Comments
FROM tblmonthlytasks INNER JOIN tblvolumes ON tblmonthlytasks.TaskID =
tblvolumes.TaskID;

I have two tables: tblmonthlytasks and tblvolumes. tblmonthlytasks has
TaskID-autonumber, Employee Name-Text, Current Date-Date and Comments-Memo.
tblvolumes has TaskID-Number, TaskName-Text, Volumes-Text.

They are related by TaskID for main form and subform. Table wise, they are
related as a one to many with tblmonthlytasks being the one and tblvolumes
being the many.

A sample of the data is posted on my last posting. It is a relatively simple
database but I always have problems with these subforms pulling data out. I
did manage to get what I want manipulating the reports but not as good as
working it through the query first. What I have will work but I don't have
the flexibility working with just the report which is why if it can be done
through the query, it would make it easier having to work with both.

Anything you can do to help would be most appreciated. If I have this set up
wrong, please let me know. Thank you for your help thus far and I appreciate
you staying with me.

:

Post the SQL of the query you ran.
How are the two tables related?
Post sample data from both tables.

--
KARL DEWEY
Build a little - Test a little


:

Hi Karl,

It doesn't work. I placed it in my query using FROM tblvolumes and it ask
for an employee, date and task (which I do not select anything because I want
to see all the people) and it comes up blank except for the volumes column
which total everything. Am I setting something up wrong?

I have two tables in my query tblemployees and tblvolumes.

tblemployee gives me the employee name, current date and comments fields.
tblvloumes gives me the task(listed in a separate table but selected on the
sub form from a cbo) and volumes.

All I want to do is during the month as the users enter the various tasks
they worked on I want to see the total of each task entered for each
employee. So in my previous example John worked on Advances 3 days of the
month so under Johns name on the report, I want to show Advances 52. Does
that make sense?

Thanks!!

:

Try this --
SELECT Employee, Format([DateField], "mmm yyyy") AS Work_Date, Tasks,
Sum([Volume]) AS Month_Volume
FROM YourTable
GROUP BY Employee, Format([DateField], "yyyymm"), Tasks;

--
KARL DEWEY
Build a little - Test a little


:

Hello,

I have a database with a subform with the following fields: Tasks and
Volume. Everything works fine and I can pull a report and it looks good.
However, I need to be able to total each task so as the users are entering
their volume for the month, I don't have to look for each separate task on
the report to add it.

Example:

Employee Date Tasks Volume
John 10/3/08 Advances 12
Jane 10/6/08 Cash Receipts 20
Ed 10/1/08 Item Processing 15
John 10/5/08 Advances 22
John 10/4/08 Advances 18
Jane 10/2/08 Cash Receipts 8

My report is grouped by employee so all of John, Jane etc are sectioned. My
problem is after a month of entering data, how would I add all the advances
John di for the month without having to go through each day on the report.
The same for Jane with Case Receipts etc....??

Thanks!!!

 

[Stockwell43]

Do you suppose you might be willing to share how I could set it up? What
fields in each query and how do I join them together so of speak to get one?
Or do you mean create two queries and then create a report with one and use
the other query as a sub report?

You can total in one query and join it in another to show the dates.
Alternatively do the totaling in report section footer.
--
KARL DEWEY
Build a little - Test a little

Yeah, when I looked at it I kinda thought that was the case and unfortunately
I need the date field in the report.

Well thank you anyway Karl, I appreciate the time you spent with me on this.

You can not roll them together and have separate date at the same time.
You can omit the date or format it differently such as month and year.
--
KARL DEWEY
Build a little - Test a little


:

It works now, but still doesn't group the tasks by name with the totals each
task by the same employee. So for John, on 10/03/2008 if Advances is 6 and
10/06/2008 Advances is 22, it still shows like this in the query instead of
John Advances 28.

:

Add 'TaskName' in the Group By line. Also include 'Comments.'
--
KARL DEWEY
Build a little - Test a little


:

Hi Karl,

I plugged it in and when I ran it, I got a message pop up:

You tried to execute a query that does not include the specified expression
'TaskName' as part of an aggregate function.

Was I suppose to change something?

Thanks!

:

Try this --
SELECT tblmonthlytasks.EmployeeName, tblmonthlytasks.CurrentDate,
tblvolumes.TaskName, Sum(tblvolumes.Volume) AS Monthly_Volume,
tblmonthlytasks.Comments
FROM tblmonthlytasks LEFT JOIN tblvolumes ON tblmonthlytasks.TaskID =
tblvolumes.TaskID
GROUP BY tblmonthlytasks.CurrentDate, tblmonthlytasks.EmployeeName;

--
KARL DEWEY
Build a little - Test a little


:

Hi Karl,

Here is the SQL:
SELECT tblmonthlytasks.EmployeeName, tblmonthlytasks.CurrentDate,
tblvolumes.TaskName, tblvolumes.Volume, tblmonthlytasks.Comments
FROM tblmonthlytasks INNER JOIN tblvolumes ON tblmonthlytasks.TaskID =
tblvolumes.TaskID;

I have two tables: tblmonthlytasks and tblvolumes. tblmonthlytasks has
TaskID-autonumber, Employee Name-Text, Current Date-Date and Comments-Memo.
tblvolumes has TaskID-Number, TaskName-Text, Volumes-Text.

They are related by TaskID for main form and subform. Table wise, they are
related as a one to many with tblmonthlytasks being the one and tblvolumes
being the many.

A sample of the data is posted on my last posting. It is a relatively simple
database but I always have problems with these subforms pulling data out. I
did manage to get what I want manipulating the reports but not as good as
working it through the query first. What I have will work but I don't have
the flexibility working with just the report which is why if it can be done
through the query, it would make it easier having to work with both.

Anything you can do to help would be most appreciated. If I have this set up
wrong, please let me know. Thank you for your help thus far and I appreciate
you staying with me.

:

Post the SQL of the query you ran.
How are the two tables related?
Post sample data from both tables.

--
KARL DEWEY
Build a little - Test a little


:

Hi Karl,

It doesn't work. I placed it in my query using FROM tblvolumes and it ask
for an employee, date and task (which I do not select anything because I want
to see all the people) and it comes up blank except for the volumes column
which total everything. Am I setting something up wrong?

I have two tables in my query tblemployees and tblvolumes.

tblemployee gives me the employee name, current date and comments fields.
tblvloumes gives me the task(listed in a separate table but selected on the
sub form from a cbo) and volumes.

All I want to do is during the month as the users enter the various tasks
they worked on I want to see the total of each task entered for each
employee. So in my previous example John worked on Advances 3 days of the
month so under Johns name on the report, I want to show Advances 52. Does
that make sense?

Thanks!!

:

Try this --
SELECT Employee, Format([DateField], "mmm yyyy") AS Work_Date, Tasks,
Sum([Volume]) AS Month_Volume
FROM YourTable
GROUP BY Employee, Format([DateField], "yyyymm"), Tasks;

--
KARL DEWEY
Build a little - Test a little


:

Hello,

I have a database with a subform with the following fields: Tasks and
Volume. Everything works fine and I can pull a report and it looks good.
However, I need to be able to total each task so as the users are entering
their volume for the month, I don't have to look for each separate task on
the report to add it.

Example:

Employee Date Tasks Volume
John 10/3/08 Advances 12
Jane 10/6/08 Cash Receipts 20
Ed 10/1/08 Item Processing 15
John 10/5/08 Advances 22
John 10/4/08 Advances 18
Jane 10/2/08 Cash Receipts 8

My report is grouped by employee so all of John, Jane etc are sectioned. My
problem is after a month of entering data, how would I add all the advances
John di for the month without having to go through each day on the report.
The same for Jane with Case Receipts etc....??

Thanks!!!