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

VBA/Query to update certain fields

P: n/a
There is a lotto system which picks 21 numbers every day out of 80
numbers.

I have a table (name:Lotto) with 22 fields (name:Date,P1,P2....P21)

Here is the structure and sample data:

"Date","P1","P2","P3","P4","P5","P6","P7","P8","P9 ","P10","P11","P12","P13","P14","P15","P16","P17", "P18","P19","P20","P21"
1/1/2005,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18, 19,20,21
1/2/2005,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19 ,20,21,22
1/3/2005,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,2 0,21,22,23
1/4/2005,1,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19 ,20,21,22

I have another table (name:Check) with 15 fields
(name:D1,D2....D10,Six,Seven,Eight,Nine,Ten)

"D1","D2","D3","D4","D5","D6","D7","D8","D9","D11" ,"Six","Seven","Eight","Nine","Ten"
1,2,3,4,5,6,7,8,24,25,26,,,,,
16,17,18,19,20,21,22,23,24,25,26,,,,,

Table "Lotto" contains lotto results i.e., 21 numbers picked every day.
I have data from 1st Sep 2004 till date. Table "Check" contains
combination of 10 numbers that I have created. The sample shows just
two records but I have a million combinations in my table. Field
"Six","Seven","Eight","Nine","Ten" contains vlaue 0 (zero). I don't
have any forms and reports in my database. I just have tables and
looking for query to update the "Seven","Eight","Nine","Ten" fields.

I want to read the first record in Check. I then want you to read all
records in Lotto. The first record in Check is compared to the first
record in Lotto and there a 6 or more matches, the number 1 is added to
the column of matching numbers. For example,: if 6 numbers match, then
increment the field Six by 1. If 7 numbers match, then increment the
field Seven by 1...and so on. Then skip through second, third and all
records in Lotto, doing the same process for the first record in Check.
Once that has been complete, skip to the next record in Check.

For example:

I have made few combinations of 10 numbers to check their frequency in
historical data (past days). Lets say the first combination of 10
numbers is 1,2,3,4,5,6,7,8,24,25,26. Now what the query should do is,
It should read all these 10 numbers in Jan1 and see how many numbers
matched. Now in this example, 8 numbers matched on Jan1
(1,2,3,4,5,6,7,8) therefore the query should update the EIGHT field
with the value 1.

Now read the same 10 numbers in Jan2 and see how many numbers matched,
on Jan2 7 numbers matched (2 3 4 5 6 7 8) therefore the query should
update the SEVEN field with the value 1.

Now read the same numbers in Jan3 and see how many numbers matched, on
Jan3 6 numbers matched (3 4 5 6 7 8) therefore the query should update
the SIX field with the value 1.

Now read the same numbers in Jan4 and see how many numbers matched, on
Jan4 again 7 numbers matched (1 3 4 5 6 7 8) therefore the query should
update (increment) the SEVEN field with the value 2 (existing value 1 +
new value 1 - since SEVEN field already had a value 1 before, it should
increment it by 1 and change the value to 2).

Here you have finished checking the first combination (record1 of Check
table) you are now ready to move to the next record (next combination
of
10 numbers i.e., 16,17,18,19,20,21,22,23,24,25,26).

After running the query, the Check table should look like this:

"D1","D2","D3","D4","D5","D6","D7","D8","D9","D11" ,"Six","Seven","Eight","Nine","Ten"
1,2,3,4,5,6,7,8,24,25,26,1,2,1,, ---------------(This is the first
combination of the check table)
' (8 numbers matched 1 2 3 4 5 6 7 8 on 1st Jan)
' (7 numbers matched 2 3 4 5 6 7 8 on 2nd Jan)
' (6 numbers matched 3 4 5 6 7 8 on 3rd Jan)
' (7 numbers matched 1 3 4 5 6 7 8 on 4th Jan)
16,17,18,19,20,21,22,23,24,25,26,1,2,1,, ---------------(This is the
second combination of the check table)
' (6 numbers matched 16 17 18 19 20 21 on 1st Jan)
' (7 numbers matched 16 17 18 19 20 21 22 on 2nd Jan)
' (8 numbers matched 16 17 18 19 20 21 22 23 on 3rd Jan)
' (7 numbers matched 16 17 18 19 20 21 22 on 4th Jan)

Maxi

Nov 13 '05 #1
Share this Question
Share on Google+
8 Replies


P: n/a
Maxi wrote:
There is a lotto system which picks 21 numbers every day out of 80
numbers.

I have a table (name:Lotto) with 22 fields (name:Date,P1,P2....P21)

Here is the structure and sample data:

"Date","P1","P2","P3","P4","P5","P6","P7","P8","P9 ","P10","P11","P12","P13","P14","P15","P16","P17", "P18","P19","P20","P21"
1/1/2005,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18, 19,20,21
1/2/2005,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19 ,20,21,22
1/3/2005,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,2 0,21,22,23
1/4/2005,1,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19 ,20,21,22

I have another table (name:Check) with 15 fields
(name:D1,D2....D10,Six,Seven,Eight,Nine,Ten)

"D1","D2","D3","D4","D5","D6","D7","D8","D9","D11" ,"Six","Seven","Eight","Nine","Ten"
1,2,3,4,5,6,7,8,24,25,26,,,,,
16,17,18,19,20,21,22,23,24,25,26,,,,,

Table "Lotto" contains lotto results i.e., 21 numbers picked every day.
I have data from 1st Sep 2004 till date. Table "Check" contains
combination of 10 numbers that I have created. The sample shows just
two records but I have a million combinations in my table. Field
"Six","Seven","Eight","Nine","Ten" contains vlaue 0 (zero). I don't
have any forms and reports in my database. I just have tables and
looking for query to update the "Seven","Eight","Nine","Ten" fields.

I want to read the first record in Check. I then want you to read all
records in Lotto. The first record in Check is compared to the first
record in Lotto and there a 6 or more matches, the number 1 is added to
the column of matching numbers. For example,: if 6 numbers match, then
increment the field Six by 1. If 7 numbers match, then increment the
field Seven by 1...and so on. Then skip through second, third and all
records in Lotto, doing the same process for the first record in Check.
Once that has been complete, skip to the next record in Check.

For example:

I have made few combinations of 10 numbers to check their frequency in
historical data (past days). Lets say the first combination of 10
numbers is 1,2,3,4,5,6,7,8,24,25,26. Now what the query should do is,
It should read all these 10 numbers in Jan1 and see how many numbers
matched. Now in this example, 8 numbers matched on Jan1
(1,2,3,4,5,6,7,8) therefore the query should update the EIGHT field
with the value 1.

Now read the same 10 numbers in Jan2 and see how many numbers matched,
on Jan2 7 numbers matched (2 3 4 5 6 7 8) therefore the query should
update the SEVEN field with the value 1.

Now read the same numbers in Jan3 and see how many numbers matched, on
Jan3 6 numbers matched (3 4 5 6 7 8) therefore the query should update
the SIX field with the value 1.

Now read the same numbers in Jan4 and see how many numbers matched, on
Jan4 again 7 numbers matched (1 3 4 5 6 7 8) therefore the query should
update (increment) the SEVEN field with the value 2 (existing value 1 +
new value 1 - since SEVEN field already had a value 1 before, it should
increment it by 1 and change the value to 2).

Here you have finished checking the first combination (record1 of Check
table) you are now ready to move to the next record (next combination
of
10 numbers i.e., 16,17,18,19,20,21,22,23,24,25,26).

After running the query, the Check table should look like this:

"D1","D2","D3","D4","D5","D6","D7","D8","D9","D11" ,"Six","Seven","Eight","Nine","Ten"
1,2,3,4,5,6,7,8,24,25,26,1,2,1,, ---------------(This is the first
combination of the check table)
' (8 numbers matched 1 2 3 4 5 6 7 8 on 1st Jan)
' (7 numbers matched 2 3 4 5 6 7 8 on 2nd Jan)
' (6 numbers matched 3 4 5 6 7 8 on 3rd Jan)
' (7 numbers matched 1 3 4 5 6 7 8 on 4th Jan)
16,17,18,19,20,21,22,23,24,25,26,1,2,1,, ---------------(This is the
second combination of the check table)
' (6 numbers matched 16 17 18 19 20 21 on 1st Jan)
' (7 numbers matched 16 17 18 19 20 21 22 on 2nd Jan)
' (8 numbers matched 16 17 18 19 20 21 22 23 on 3rd Jan)
' (7 numbers matched 16 17 18 19 20 21 22 on 4th Jan)

Maxi


Some comments.
You are not a programmer. You were provided code in the past that did
what you asked for based upon your description of the problem. From
that code, a real programmer could have modified it to their requirements.

You don't define your problem adequately or succinctly. You waste
peoples time.

You should have been able to find another person that programms in
Access in India to modify the code you were provided since you can't
program it.

If you can't program it, and you can't find another fellow Indian to
help you out, you should be willing to pay for the code.

This is a help group, not a "do the code for someone too lazy to figure
it out themselves" group.

You should not change table names and structures when defining the
problem between requests for help. For example, you removed C_No and
you removed D_NO and DateField in your above example.

I wasted time on this in the past, but since I had already invested some
time into this problem and wanted it solved, I'm providing you with a
solution. So here goes.

Make sure C_NO, D_NO, and DateField exist in the tables and that C_NO
has a unique value and DateField has a date value.

Two, create a new query. Query/New/Design/Close and press ViewSQL.
Paste this SQL into it and save.

SELECT Check.C_No, [Enter Start Date] AS StartDate, [Enter End Date] AS
EndDate, GetLottoCount1([C_NO],[StartDate],[EndDate]) AS Expr1
FROM Check;

The above query prompts for a start/end date range so it selects the
records in Lotto to scan and update the values in Check.

Next, drop the following code into a new code module. When done, run
the query you just created and check the results in table Checks.

'copy/paste code from here
Function GetLottoCount1(varCNo As Variant, datStart As Variant, _
datEnd As Variant) As Integer
If IsDate(datStart) And IsDate(datEnd) Then
Dim rstL As DAO.Recordset 'Lotto
Dim rstC As DAO.Recordset 'Check

Dim strSQL As String

Dim intC As Integer 'counter for number to check
Dim intTot As Integer 'number of matches

Dim intForC As Integer
Dim intForL As Integer

Dim strC As String 'holds the field names
Dim strL As String 'ditto

Dim strField As String 'field to update in Check

'find this C_NO and blank out fields Six..Ten
Set rstC = CurrentDb.OpenRecordset("Check", dbOpenDynaset)
rstC.FindFirst "C_No = " & varCNo
rstC.Edit
rstC!Six = 0
rstC!Seven = 0
rstC!Eight = 0
rstC!Nine = 0
rstC!Ten = 0
rstC.Update

strSQL = "Select Lotto.* From Lotto " & _
"Where [DateField] Between #" & datStart & "# And #" & _
datEnd & "#"
Set rstL = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)

If rstL.RecordCount > 0 Then
rstL.MoveFirst
Do While Not rstL.EOF
intTot = 0
For intForC = 1 To 11
'loop through D1..D11, ignore D10, does not exist
If intForC <> 10 Then
'create the "D1..D11" field name in Check
strC = "D" & intForC

'init counter for each field of D1..D11
intC = 0
For intForL = 1 To 21
'check the value in each field in
'lotto...D1 to D21 to the current
'value being checked from Check.

'create the P1..P21 field name
strL = "P" & intForL

If rstC(strC) = rstL(strL) Then
'there is a match for
'this number
intTot = intTot + 1
End If
Next

'now add the count to the total count
'intTot = intTot + intC
End If
Next

'if there aren't 6 matches then don't increment as it
'doesn't meet any criteria.
If intTot > 5 Then
Select Case intTot
Case 6
strField = "Six"
Case 7
strField = "Seven"
Case 8
strField = "Eight"
Case 9
strField = "Nine"
Case 10
strField = "Ten"
End Select

rstC.Edit
rstC(strField) = rstC(strField) + 1
rstC.Update

GetLottoCount1 = GetLottoCount1 + intTot
End If
rstL.MoveNext
Loop
End If
rstC.Close
rstL.Close
Set rstC = Nothing
Set rstL = Nothing
End If
End Function
'to here
Nov 13 '05 #2

P: n/a
Salad, didn't you normalize his structure?

--
Bas Cost Budde, Holland
http://www.heuveltop.nl/BasCB/msac_index.html
For human replies, replace the queue with a tea

Nov 13 '05 #3

P: n/a
I am sorry for the inconvenience. I am getting the following error:

Run-time error `3265':
Item not found in this collection.

at line "If rstC(strC) = rstL(strL) Then"

I don't know if it is because of the references. I have following
things checked:
1. Visual Basic For Applications
2. Microsoft Access 10.0 Object Library
3. Microsoft DAO 3.6 Object Library
4. Microsoft ActiveX Data Objects 2.8 Library
5. Microsoft ActiveX Data Objects Recordset 2.8 Library.
6. OLE Automation
7. Microsoft Visual Basic for Applications Extensibility 5.3

Microsoft Knowledge base states that it is by design. It says, When you
issue an Update, Insert, or Delete statement followed by a Select
statement from an ADO client application, if you reference the ADO
Recordset object, you receive the following error:
Run-time error '3265': and to use
1. The first option is to provide a SET NOCOUNT ON
2. The second option is to call the Next Recordsetmethod to process the
actual recordset.

Article ID : 197528

Can you help?

Nov 13 '05 #4

P: n/a
Its working now........... I don't know what went wrong, when I tried
it again after restarting the computer, it worked. thankx for your
efforts

Maxi

Nov 13 '05 #5

P: n/a
Last two questions:

1. I need a message box at the end that will tell me that "Processing
is finished". I tried to add the Msgbox before End Function but it
gives a prompt after updating every record. May be because there is a
query that is dependent of a VBA code.

2. I have a around 325 records in Lotto table and a million record in
check. The processing is too slow (2 records update in 1 second). Is
there any way to speed it up. I am using PIV 2 Gig 256M Ram.

Maxi

Nov 13 '05 #6

P: n/a
Everything is working as desired however few questions:

1. I want to get a message prompt after the processing is over. I tried
to put msgbox before End Function but it prompts me after updating
every record in Check. Where can I put the msgbox?

2. I have 325 records in Lotto and around a million records in Check.
The processing is very slow and takes around 1 second to update two
records. Is there any way to speed this up? I am using PIV 2Gig 256M
RAM.

3. The result of the query is displayed in datasheet view. It goes on
updating and if in between if the screen saver starts of if the comp
goes in sleep mode, when coming back the processing starts all over
again from the first record. If I use the scroll bar to check how many
records it has finished updating, it does not update or skips few
records. Can the datasheet view be blocked and run the code directly
from the code window and at the end it should let me know with a
message that everything is done.

Maxi

Nov 13 '05 #7

P: n/a
I have resolved my two questions on how to get the message box and
changing the datasheet view.

The only thing I need to know is how to increase the speed. It takes
around 40 seconds to update 100 records and I have more than a million
records in my Check table. Is there any way to speed it up?

Maxi

Nov 13 '05 #8

P: n/a
Your code compares every single number from check with lotto and keeps
incrementing the counter to see if there is a match that is greater
than 5. That definitely is the efficient way of comparing but can this
be done using some kind of array instead of single number comparing to
increase the updating speed?

No doubt the code is working but if I use it, it will take around 4-5
days non-stop if I have to update 1 million + records.

There is an alternate way by selecing few date range first and then
another set of range but I am just curious if something can be done to
increase the processing speed.

Maxi

Nov 13 '05 #9

This discussion thread is closed

Replies have been disabled for this discussion.