473,287 Members | 3,295 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,287 software developers and data experts.

VBA/Query to update certain fields

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
8 3671
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
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
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
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
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
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: Richard Williamson | last post by:
Hi all, I have managed, using a quite tortuous route, to select certain records in a UNION query. This is based on the results of other queries. Question: how the devil do I change the value...
7
by: rednexgfx_k | last post by:
All, Problem Summary: I've running about 30 make table queries via VBA in Access 2000, and my database goes from 14,000k to over 2,000,000k. In addition, the longer the procedure runs, the...
2
by: NigelMThomas | last post by:
I have an especially challenging problem. I know there are a few geniuses in this group so perhaps; you can advise me whether or not this can be done as an update query in Access. Thanks. I am...
0
by: phlype.johnson | last post by:
I'm struggling to find the best query from performance point of view and readability for a normalized DB design. To illustrate better my question on whether normalized designs lead to more complex...
0
by: jon | last post by:
Hi there, I'm brand new to Access and may be trying to do too much too soon, but I wanted to get some expert advice on how the best way to go about what I am trying to accomplish would be. I...
9
by: Kelii | last post by:
I've been trying to get this piece to work for a few hours, but have given up. I hope someone out there can help, I think the issue is relatively straightforward, but being a novice, I'm stumped....
4
MitchR
by: MitchR | last post by:
Good Morning; I have an issue that I need to guidance to resolve. I have a table called return_tbl with about 25k records. I have 23 Fields in this table. I am looking to update 3 of these 23...
16
by: ARC | last post by:
Hello all, So I'm knee deep in this import utility program, and am coming up with all sorts of "gotcha's!". 1st off. On a "Find Duplicates Query", does anyone have a good solution for...
1
by: jmarcrum | last post by:
Hey everyone, I have a question that's been troubling me for a bit. I work for Alabama Power. I have 1 table (tblOutages). Every morning I import a list of Power Outages for the state of...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.