Memory error, rows=millions, can Access do it?

[blackduck]

I have a query that will take about 10mins to run the first 100 TimeIDs but get a memory error if I try to run first 1000 (I actually need to run all 27,000 TimeIds).

The subquery multiplies 12 probabilities together to get a product (there are 500,000 of these products for each TimeId). Then the Make table query sums these products to get the 1 SumOfProduct for each TimeID.

Make Table Query:
SELECT [OverallResult Product].TimeID, Sum([OverallResult Product].ProductOfProbability) AS SumOfProductOfProbability INTO [Result2 Time100]
FROM [OverallResult Product]
GROUP BY [OverallResult Product].TimeID;

This is the subquery called OverallResult Product:
SELECT tblProbablility.TimeID, Exp(Sum(Log([tblProbablility].[Probablility]))) AS ProductOfProbability
FROM tblOverall INNER JOIN (tblPossible LEFT JOIN tblProbablility ON (tblPossible.MatchID = tblProbablility.MatchID) AND (tblPossible.WinnerID = tblProbablility.WinnerID)) ON tblOverall.PossibleID = tblPossible.PossibleID
GROUP BY tblProbablility.TimeID, tblOverall.OverallResult, tblPossible.PossibleID
HAVING (((tblProbablility.TimeID)<101) AND ((tblOverall.OverallResult)=2));

I know it is huge, but am I writing the SQL inefficiently or will Access never be able to do it?
If Access isn't capable, would it work if I find someone who has and knows SQL server?

 

[hneal98]

Access can actually do whatever your desktop has the resources for; however, not very gracefully once you get over 50000 records. I don't know how much memory you have on you system, but if possible, I would look into having this done in SQL Server.

If that is not possible, you might think about splitting up the job into 1000 or less based on what you stated, and run that query on each 1000 record sets and link to those you already did with a NOT IN.

Let me know if that does not make sense. I know it is a pain, but if you don't have the resources, it may be your only option.

http://www.totalsecurityforyou.com

for any personal security products.

 

[PHV]

What about this ?

SELECT C.TimeID, Exp(Sum(Log(C.Probablility))) AS ProductOfProbability
FROM (tblOverall A
INNER JOIN tblPossible B ON A.PossibleID = B.PossibleID)
INNER JOIN tblProbablility C ON B.MatchID = C.MatchID AND B.WinnerID = C.WinnerID
WHERE C.TimeID < 101 AND A.OverallResult = 2
GROUP BY C.TimeID

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[blackduck]

PHV gave your code a go, but the output for TimeId 1 was 0 and TimeId 2 also 0. The result should be TimeId 1 be 0.108074373615488 and very similar for TimeId 2.

Your code is definetly a lot cleaner than mine though. But not sure why didn't quite work.