Two Queries not getting along in another query

[colorado]

Thank you for helping me.

I have two queries, qryA and qryB. They are being used by qryC, which has
about 6 total queries in it. When qryA and qryB are both in qryC, qryC will
not run; it has a full status bar with 'Running Query' at the bottom of the
screen.

When I take out either qryA or qryB, the qryC runs. qryA and qryB run just
fine on their own.

Thanks again!

 

[Jerry Whittle]

Show us the SQL for the various queries. Open a query in design view. Next
go to View, SQL View and copy and past it here. Do this for A, B, and C
queries.

 

[colorado]

Show us the SQL for the various queries. Open a query in design view. Next
go to View, SQL View and copy and past it here. Do this for A, B, and C
queries.

Thank you for helping me.

[quoted text clipped - 7 lines]

Thanks again!

Thanks for helping me!
qryA:
SELECT tblDivSchedule.Fund, tblDivSchedule.Payable_Date AS Bus_Date, tblO2003.
RPO_CHECKS AS RPOChecks
FROM tblO2003 RIGHT JOIN tblDivSchedule ON (tblO2003.BUS_DATE =
tblDivSchedule.Payable_Date) AND (tblO2003.FUND = tblDivSchedule.Fund);

qryB:
SELECT tblDivSchedule.Fund, tblMerrillLynch.BUS_DATE, tblMerrillLynch.
MerrillLynch
FROM tblDivSchedule LEFT JOIN tblMerrillLynch ON tblDivSchedule.Fund =
tblMerrillLynch.FUND;

qryC:
SELECT DISTINCT qryMerrillLynch.Fund, qryMerrillLynch.MerrillLynch,
qryCheckRegTotal.CheckRegTotal, qryNetworkingSum.Networking, qryAcctLink.
AcctLink, qryDivExchng.DivExchng, Max(qryOthrSuppCksSum.OthrSuppCks) AS
OthrSuppCks, qryRPOChecks.RPOChecks
FROM ((((((tblDivSchedule LEFT JOIN qryMerrillLynch ON tblDivSchedule.Fund =
qryMerrillLynch.Fund) LEFT JOIN qryCheckRegTotal ON tblDivSchedule.Fund =
qryCheckRegTotal.FUND) LEFT JOIN qryNetworkingSum ON tblDivSchedule.Fund =
qryNetworkingSum.FUND) LEFT JOIN qryAcctLink ON tblDivSchedule.Fund =
qryAcctLink.FUND) LEFT JOIN qryDivExchng ON tblDivSchedule.Fund =
qryDivExchng.FUND) LEFT JOIN qryOthrSuppCksSum ON tblDivSchedule.Fund =
qryOthrSuppCksSum.Fund) LEFT JOIN qryRPOChecks ON tblDivSchedule.Fund =
qryRPOChecks.Fund
GROUP BY qryMerrillLynch.Fund, qryMerrillLynch.MerrillLynch, qryCheckRegTotal.
CheckRegTotal, qryNetworkingSum.Networking, qryAcctLink.AcctLink,
qryDivExchng.DivExchng, qryRPOChecks.RPOChecks;

 

[Jerry Whittle]

The first thing that I notice is there is a SELECT DISTINCT and GROUP BYs in
the same query. Remove the DISTINCT and see what it does.

Also it's not clear where qryA and qryB are in qryC. I'm guessing that
qryRPOChecks is qryA.

If removing the DISTINCT doesn't help, try further simplifying the query
until it does.

Remove the Max(qryOthrSuppCksSum.OthrSuppCks) line and the GROUP BYs. Does
it run then?
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Show us the SQL for the various queries. Open a query in design view. Next
go to View, SQL View and copy and past it here. Do this for A, B, and C
queries.

Thank you for helping me.

[quoted text clipped - 7 lines]

Thanks again!

Thanks for helping me!
qryA:
SELECT tblDivSchedule.Fund, tblDivSchedule.Payable_Date AS Bus_Date, tblO2003.
RPO_CHECKS AS RPOChecks
FROM tblO2003 RIGHT JOIN tblDivSchedule ON (tblO2003.BUS_DATE =
tblDivSchedule.Payable_Date) AND (tblO2003.FUND = tblDivSchedule.Fund);

qryB:
SELECT tblDivSchedule.Fund, tblMerrillLynch.BUS_DATE, tblMerrillLynch.
MerrillLynch
FROM tblDivSchedule LEFT JOIN tblMerrillLynch ON tblDivSchedule.Fund =
tblMerrillLynch.FUND;

qryC:
SELECT DISTINCT qryMerrillLynch.Fund, qryMerrillLynch.MerrillLynch,
qryCheckRegTotal.CheckRegTotal, qryNetworkingSum.Networking, qryAcctLink.
AcctLink, qryDivExchng.DivExchng, Max(qryOthrSuppCksSum.OthrSuppCks) AS
OthrSuppCks, qryRPOChecks.RPOChecks
FROM ((((((tblDivSchedule LEFT JOIN qryMerrillLynch ON tblDivSchedule.Fund =
qryMerrillLynch.Fund) LEFT JOIN qryCheckRegTotal ON tblDivSchedule.Fund =
qryCheckRegTotal.FUND) LEFT JOIN qryNetworkingSum ON tblDivSchedule.Fund =
qryNetworkingSum.FUND) LEFT JOIN qryAcctLink ON tblDivSchedule.Fund =
qryAcctLink.FUND) LEFT JOIN qryDivExchng ON tblDivSchedule.Fund =
qryDivExchng.FUND) LEFT JOIN qryOthrSuppCksSum ON tblDivSchedule.Fund =
qryOthrSuppCksSum.Fund) LEFT JOIN qryRPOChecks ON tblDivSchedule.Fund =
qryRPOChecks.Fund
GROUP BY qryMerrillLynch.Fund, qryMerrillLynch.MerrillLynch, qryCheckRegTotal.
CheckRegTotal, qryNetworkingSum.Networking, qryAcctLink.AcctLink,
qryDivExchng.DivExchng, qryRPOChecks.RPOChecks;

 

[colorado]

The first thing that I notice is there is a SELECT DISTINCT and GROUP BYs in
the same query. Remove the DISTINCT and see what it does.

Also it's not clear where qryA and qryB are in qryC. I'm guessing that
qryRPOChecks is qryA.

If removing the DISTINCT doesn't help, try further simplifying the query
until it does.

Remove the Max(qryOthrSuppCksSum.OthrSuppCks) line and the GROUP BYs. Does
it run then?[quoted text clipped - 34 lines]

CheckRegTotal, qryNetworkingSum.Networking, qryAcctLink.AcctLink,
qryDivExchng.DivExchng, qryRPOChecks.RPOChecks;

Removing distinct and group by worked! However, how do I now do these
functions for the query. (And yes, qryRPOChecks is qryA and qryB is
qryMerrillLynch. I'm sorry for not including that piece of information
earlier.)

 

[Jerry Whittle]

I don't think that you'll ever need the DISTINCT so don't worry about it.

I'd start by adding one part of the GROUP BY at a time. See what breaks it.
If all the GROUP BY works, then add the Max line and see what happens.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

The first thing that I notice is there is a SELECT DISTINCT and GROUP BYs in
the same query. Remove the DISTINCT and see what it does.

Also it's not clear where qryA and qryB are in qryC. I'm guessing that
qryRPOChecks is qryA.

If removing the DISTINCT doesn't help, try further simplifying the query
until it does.

Remove the Max(qryOthrSuppCksSum.OthrSuppCks) line and the GROUP BYs. Does
it run then?

Show us the SQL for the various queries. Open a query in design view. Next
go to View, SQL View and copy and past it here. Do this for A, B, and C

[quoted text clipped - 34 lines]

CheckRegTotal, qryNetworkingSum.Networking, qryAcctLink.AcctLink,
qryDivExchng.DivExchng, qryRPOChecks.RPOChecks;

Removing distinct and group by worked! However, how do I now do these
functions for the query. (And yes, qryRPOChecks is qryA and qryB is
qryMerrillLynch. I'm sorry for not including that piece of information
earlier.)

 

[colorado]

I don't think that you'll ever need the DISTINCT so don't worry about it.

I'd start by adding one part of the GROUP BY at a time. See what breaks it.
If all the GROUP BY works, then add the Max line and see what happens.[quoted text clipped - 17 lines]

qryMerrillLynch. I'm sorry for not including that piece of information
earlier.)

Thanks Jerry!