Query Question

[Stockwell43]

Hello,

I have two tables that feed a form and subform.

tblmonthlytasks is the main form and has fields: EmployeeName,
TasksID(autonumber), CurrentDate

tblvolumes is the subform table and it's fields are: Volume, TaskID(number)
and TaskName

I made a totals query from tblvolumes with TaskName(groupBy) and volume(sum)
and EmployeeName(groupby) and works great. I made another query from
tblmonthlytasks with just the currentdate field and it works great.

My question is:

Now that I have the two queries that give me the information the way I want
it, how do I get the two to combine into one to show all four columns and
have the data look the same as when they are separate??

Thanks!!

 

[Jeff Boyce]

What (field/s) do the two queries have in common? In other words, what
would you use from each query to put the four columns together in a row?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Stockwell43]

Hi Jeff,

It's the TaskID

What (field/s) do the two queries have in common? In other words, what
would you use from each query to put the four columns together in a row?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Jeff Boyce]

So if you have Query1 with TaskID, Field1 and Field2, and you have Query2
with TaskID, Field3 and Field4, create Query3 and pull the other two queries
in, joined on TaskID.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Stockwell43]

Hi Jeff,

Thank you for your response. I didn't do the Group By. After I did that, it
works exactly like I wanted it to. Here is what I did:

SELECT tblvolumes.TaskName, Sum(tblvolumes.Volume) AS SumOfVolume,
tblmonthlytasks.EmployeeName
FROM tblmonthlytasks INNER JOIN tblvolumes ON tblmonthlytasks.TaskID =
tblvolumes.TaskID
WHERE (((tblmonthlytasks.CurrentDate) Between IIf(IsNull([Forms]![frmKPI
Volumes]![BeginningDate]),#1/1/1990#,[Forms]![frmKPI
Volumes]![BeginningDate]) And IIf(IsNull([Forms]![frmKPI
Volumes]![EndingDate]),Date(),[Forms]![frmKPI Volumes]![EndingDate])))
GROUP BY tblvolumes.TaskName, tblmonthlytasks.EmployeeName;

I do appreciate your time in helping with this. It was a oversight on my
part which these days is not uncommon. )

 

[Jeff Boyce]

de nada!

Thank YOU for posting back your solution. Other folks may run into a
similar issue in the future and your approach may save them some work.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hi Jeff,

Thank you for your response. I didn't do the Group By. After I did that,
it
works exactly like I wanted it to. Here is what I did:

SELECT tblvolumes.TaskName, Sum(tblvolumes.Volume) AS SumOfVolume,
tblmonthlytasks.EmployeeName
FROM tblmonthlytasks INNER JOIN tblvolumes ON tblmonthlytasks.TaskID =
tblvolumes.TaskID
WHERE (((tblmonthlytasks.CurrentDate) Between IIf(IsNull([Forms]![frmKPI
Volumes]![BeginningDate]),#1/1/1990#,[Forms]![frmKPI
Volumes]![BeginningDate]) And IIf(IsNull([Forms]![frmKPI
Volumes]![EndingDate]),Date(),[Forms]![frmKPI Volumes]![EndingDate])))
GROUP BY tblvolumes.TaskName, tblmonthlytasks.EmployeeName;

I do appreciate your time in helping with this. It was a oversight on my
part which these days is not uncommon. )

So if you have Query1 with TaskID, Field1 and Field2, and you have Query2
with TaskID, Field3 and Field4, create Query3 and pull the other two
queries
in, joined on TaskID.

Regards

Jeff Boyce
Microsoft Office/Access MVP