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.
KARL DEWEY said:
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!!!