Welcome Guest
You last visited January 18, 2017, 5:17 pm
All times shown are
Eastern Time (GMT-5:00)

# Calculate last time number drawn using VB

Topic closed. 8 replies. Last post 12 years ago by Littleoldlady.

 Page 1 of 1
Austin
United States
Member #3734
February 16, 2004
57 Posts
Offline
 Posted: February 7, 2005, 7:18 pm - IP Logged

I have 2 fields ID and Picks, I want to add another field(Interval) that calculates the interval the last time a number was drawn.

Pennsylvania
United States
Member #1340
April 6, 2003
2450 Posts
Offline
 Posted: February 7, 2005, 9:50 pm - IP Logged

could you copy the code in? easier to see what's going on that way...

what version of VB?

Playing more than one ticket per game is betting against yourself.

Austin
United States
Member #3734
February 16, 2004
57 Posts
Offline
 Posted: February 7, 2005, 11:14 pm - IP Logged

I am using the following query but it takes too long

ID,Picks,Interval

SELECT A.ID, A.Picks,A.ID-Nz(Max(B.ID),A.ID) AS Interval

FROM Pick3day AS A LEFT JOIN Pick3day AS B ON A.ID>B.ID AND A.Picks=B.Picks

GROUP BY A.ID, A.Picks;

Chief Bottle Washer
New Jersey
United States
Member #1
May 31, 2000
23349 Posts
Offline
 Posted: February 7, 2005, 11:41 pm - IP Logged

Try this, and see if it's faster:

SELECT A.ID, A.Picks, ISNULL((SELECT TOP 1 B.ID-A.ID FROM Pick3day B WHERE (B.ID<A.ID) AND (B.Picks=A.Picks) ORDER BY B.ID DESC),0) AS Interval
FROM Pick3day A ORDER BY A.ID

Also, make sure the column ID is set to be a primary key.

Check the State Lottery Report Card

Sign the Petition for True Lottery Drawings
Help eliminate computerized drawings!

Austin
United States
Member #3734
February 16, 2004
57 Posts
Offline
 Posted: February 8, 2005, 1:00 am - IP Logged

"wrong number of arguments" error message

Chief Bottle Washer
New Jersey
United States
Member #1
May 31, 2000
23349 Posts
Offline
 Posted: February 8, 2005, 10:18 am - IP Logged

Are you using SQL Server?  What version?  (If you're using Access, it may not support the code I wrote.)

Check the State Lottery Report Card

Sign the Petition for True Lottery Drawings
Help eliminate computerized drawings!

East of Atlanta
United States
Member #6191
August 11, 2004
1389 Posts
Offline
 Posted: February 8, 2005, 5:25 pm - IP Logged

You may be able to get some additional assistance here

Sir Metro

Austin
United States
Member #3734
February 16, 2004
57 Posts
Offline
 Posted: February 9, 2005, 1:09 am - IP Logged

I'm using Access 2000 as the database.

Clarksville
United States
Member #487
July 15, 2002
17638 Posts
Offline
 Posted: February 13, 2005, 8:09 am - IP Logged