By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,985 Members | 1,777 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,985 IT Pros & Developers. It's quick & easy.

Query stuck

P: n/a
When I run my query it stuck is it possible that i can debug it

SELECT TOP 1 tblCredits.OPR, Min(tbl_CRCDollars.SumOfAMOUNT) AS
MinOfSumOfAMOUNT, Avg(tblCredits.Credit) AS AvgOfCredit,
Count(tblCredits.OPR) AS CountOfOPR1,
tbl_WorkingCountDays.CountOfREPORT_DATE,
([qry_TopDollarAvgDays]![CountOfOPR])/([qry_TopDollarAvgDays]![CountOfREPORT_DATE])
AS AvgNum INTO tbl_TopRep
FROM tbl_CRCDollars, qry_TopDollarAvgDays, tbl_WorkingCountDays,
tblCredits
WHERE (((tblCredits.ADJRSN) Not Like "4*"))
GROUP BY tblCredits.OPR, tbl_WorkingCountDays.CountOfREPORT_DATE,
([qry_TopDollarAvgDays]![CountOfOPR])/([qry_TopDollarAvgDays]![CountOfREPORT_DATE])
ORDER BY Min(tbl_CRCDollars.SumOfAMOUNT);

Thank You

Aug 16 '06 #1
Share this Question
Share on Google+
1 Reply


P: n/a
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*********@gmail.comwrote in message
news:11**********************@m79g2000cwm.googlegr oups.com...
When I run my query it stuck is it possible that i can debug it
SELECT TOP 1 tblCredits.OPR,
Min(tbl_CRCDollars.SumOfAMOUNT) AS MinOfSumOfAMOUNT,
Avg(tblCredits.Credit) AS AvgOfCredit,
Count(tblCredits.OPR) AS CountOfOPR1,
tbl_WorkingCountDays.CountOfREPORT_DATE,
([qry_TopDollarAvgDays]![CountOfOPR])
/([qry_TopDollarAvgDays]![CountOfREPORT_DATE]) AS AvgNum
INTO tbl_TopRep
FROM tbl_CRCDollars, qry_TopDollarAvgDays, tbl_WorkingCountDays, tblCredits
WHERE (((tblCredits.ADJRSN) Not Like "4*"))
GROUP BY tblCredits.OPR,
tbl_WorkingCountDays.CountOfREPORT_DATE,
([qry_TopDollarAvgDays]![CountOfOPR])/([qry_TopDollarAvgDays]![CountOfREPORT_DATE])
ORDER BY Min(tbl_CRCDollars.SumOfAMOUNT);
Aug 16 '06 #2

This discussion thread is closed

Replies have been disabled for this discussion.