Query stuck

[colleen1980]

When I run my query it stuck is it possible that i can debug it

SELECT TOP 1 tblCredits.OPR, Min(tbl_CRCDoll ars.SumOfAMOUNT ) AS
MinOfSumOfAMOUN T, Avg(tblCredits. Credit) AS AvgOfCredit,
Count(tblCredit s.OPR) AS CountOfOPR1,
tbl_WorkingCoun tDays.CountOfRE PORT_DATE,
([qry_TopDollarAv gDays]![CountOfOPR])/([qry_TopDollarAv gDays]![CountOfREPORT_D ATE])
AS AvgNum INTO tbl_TopRep
FROM tbl_CRCDollars, qry_TopDollarAv gDays, tbl_WorkingCoun tDays,
tblCredits
WHERE (((tblCredits.A DJRSN) Not Like "4*"))
GROUP BY tblCredits.OPR, tbl_WorkingCoun tDays.CountOfRE PORT_DATE,
([qry_TopDollarAv gDays]![CountOfOPR])/([qry_TopDollarAv gDays]![CountOfREPORT_D ATE])
ORDER BY Min(tbl_CRCDoll ars.SumOfAMOUNT );

Thank You

[Allen Browne]

Colleen, your query has 4 tables, with no joins.

This gives you a Cartesian product, i.e. every possible combination. So,
even if there were only 100 records in each table, this query would generate
100 million records so it can select the top 1. Do you think this might be
why it gets "stuck"?

One of those source tables looks like it might be a query also, so there's
also more than that going on.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

<co*********@gm ail.comwrote in message
news:11******** **************@ m79g2000cwm.goo glegroups.com.. .

When I run my query it stuck is it possible that i can debug it

SELECT TOP 1 tblCredits.OPR,
Min(tbl_CRCDoll ars.SumOfAMOUNT ) AS MinOfSumOfAMOUN T,
Avg(tblCredits. Credit) AS AvgOfCredit,
Count(tblCredit s.OPR) AS CountOfOPR1,
tbl_WorkingCoun tDays.CountOfRE PORT_DATE,
([qry_TopDollarAv gDays]![CountOfOPR])
/([qry_TopDollarAv gDays]![CountOfREPORT_D ATE]) AS AvgNum
INTO tbl_TopRep
FROM tbl_CRCDollars, qry_TopDollarAv gDays, tbl_WorkingCoun tDays, tblCredits
WHERE (((tblCredits.A DJRSN) Not Like "4*"))
GROUP BY tblCredits.OPR,
tbl_WorkingCoun tDays.CountOfRE PORT_DATE,
([qry_TopDollarAv gDays]![CountOfOPR])/([qry_TopDollarAv gDays]![CountOfREPORT_D ATE])
ORDER BY Min(tbl_CRCDoll ars.SumOfAMOUNT );