Query Question

S

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!!
 
J

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
 
S

Stockwell43

Hi Jeff,

It's the TaskID

Jeff Boyce said:
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
 
J

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
 
S

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. :eek:)
 
J

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



Stockwell43 said:
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. :eek:)

Jeff Boyce said:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top