Joining Queries

[RJF]

I have created 2 queries that are joined in a final query. I'm trying to
figure out a way to have one query that will do it all instead of taking 3
queries to get the results.

Any suggestions? Please let me know if I haven't explained myself clearly
or if you need additional information.

qry_test_02

SELECT dbo_oss_equip.ACCTNO, dbo_oss_equip.ID
FROM dbo_oss_equip
GROUP BY dbo_oss_equip.ACCTNO, dbo_oss_equip.ID
HAVING (((dbo_oss_equip.ACCTNO)="188955"));

qry_test_01

SELECT dbo_oss_tran.machine, Sum(dbo_oss_tran.cost_units) AS SumOfcost_units
FROM dbo_oss_tran
WHERE (((dbo_oss_tran.ACCTNO)="188955") AND ((dbo_oss_tran.DATE)>=#1/1/2009#))
GROUP BY dbo_oss_tran.machine;


Final Query

SELECT qry_test_02.ID, qry_test_01.SumOfcost_units
FROM qry_test_01 RIGHT JOIN qry_test_02 ON qry_test_01.machine =
qry_test_02.ID;

Thank you very much.

 

[KARL DEWEY]

Try this --

SELECT dbo_oss_equip.ACCTNO, dbo_oss_tran.machine,
Sum(dbo_oss_tran.cost_units) AS SumOfcost_units
FROM dbo_oss_equip LEFT JOIN dbo_oss_tran ON dbo_oss_equip.ID =
dbo_oss_tran.machine
WHERE (((dbo_oss_tran.ACCTNO)="188955") AND ((dbo_oss_tran.DATE)>=#1/1/2009#))
GROUP BY dbo_oss_tran.machine;

If there is a problem then try changing the LEFT JOIN to RIGHT JOIN.

 

[RJF]

Hi Karl,
Thank you so much for the help and for such a quick response. I spoke with
my boss and he is satisfied using the 3 queries I had set up. I would still
like to get this to work sometime in the future but he doesn't want me to
spend any more time on it right now.

In any case, this is what I did before I spoke to my boss.

I tried the query you supplied and got the following error message:

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

So I changed the query to:

SELECT dbo_oss_equip.ACCTNO, dbo_oss_tran.machine,
Sum(dbo_oss_tran.cost_units) AS SumOfcost_units
FROM dbo_oss_equip LEFT JOIN dbo_oss_tran ON dbo_oss_equip.ID =
dbo_oss_tran.machine
WHERE (((dbo_oss_tran.ACCTNO)="188955") AND ((dbo_oss_tran.DATE)>=#1/1/2009#))
GROUP BY dbo_oss_equip.ACCTNO, dbo_oss_tran.machine;

It didn't give me all the IDs in the dbo.oss_equip table for AcctNo 188955.
I changed the "Left Join" to a "Right Join" and it still didn't give me what
I needed.

I really appreciated your response, but I hate to have you waste your time
on something I can't work on right now.

Thank you very much.