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

Linear update / make table query..

P: n/a
Actually I don't know if that subject line accurately describes my
problem...

This is a follow up from a delete query question I had the other day, sort
of the converse (or inverse):

GRP | CUST
12 | 3
10 | 4
9 | 5
7 | 6

The value for GRP can only be a number from 7 to 12. What I need to do is
add records for any CUST who's corresponding GRP value is >7. I need to add
all values up to 12. So the result on the above would be:

GRP | CUST
12 | 3
11 | 3
10 | 3
9 | 3
8 | 3
7 | 3
10 | 4
9 | 4
8 | 4
7 | 4
9 | 5
8 | 5
7 | 5
7 | 6

So, since the CUST value 6 has a GRP value of 7 no change is made. All the
others have initial GRP values of more than 7 so they get all values below
and down to 7 added into the records.

I've been fiddling for a while but it's not happening. Thanks very much.
Nov 12 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
Adam,
See under the code for test data for original table tblGrpCust and new table
tblGrpCust2. I created an empty copy of tblGrpCust calling it tblGrpCust2
before running the code. You'll need to set a reference to
Microsoft DAO 3.6 Object Library.

Function DoInsert()
Dim rst As Recordset
Dim strSQL As String
Dim i As Integer

Set rst = CurrentDb.OpenRecordset("tblGrpCust")
If Not rst.EOF Then
DoCmd.SetWarnings False
DoCmd.RunSQL "delete from tblGrpCust2"
rst.MoveFirst
While Not rst.EOF
If rst.Fields("Grp") = 7 Then
strSQL = "Insert into tblGrpCust2 (Grp,Cust) values " & _
"(" & rst.Fields("Grp") & "," & rst.Fields("Cust") & ")"
DoCmd.RunSQL strSQL
Else
For i = rst.Fields("Grp") To 7 Step -1
strSQL = "Insert into tblGrpCust2 (Grp,Cust) values " & _
"(" & i & "," & rst.Fields("Cust") & ")"
DoCmd.RunSQL strSQL
Next i
End If
rst.MoveNext
Wend
DoCmd.SetWarnings True
MsgBox "done"
End If

End Function

GRP CUST
12 3
10 4
9 5
7 6
GRP CUST
12 3
11 3
10 3
9 3
8 3
7 3
10 4
9 4
8 4
7 4
9 5
8 5
7 5
7 6

HTH. -Linda
"Adam" <ad**@firstmanmedia.com> wrote in message
news:94*******************@204.127.199.17...
Actually I don't know if that subject line accurately describes my
problem...

This is a follow up from a delete query question I had the other day, sort
of the converse (or inverse):

GRP | CUST
12 | 3
10 | 4
9 | 5
7 | 6

The value for GRP can only be a number from 7 to 12. What I need to do is
add records for any CUST who's corresponding GRP value is >7. I need to add all values up to 12. So the result on the above would be:

GRP | CUST
12 | 3
11 | 3
10 | 3
9 | 3
8 | 3
7 | 3
10 | 4
9 | 4
8 | 4
7 | 4
9 | 5
8 | 5
7 | 5
7 | 6

So, since the CUST value 6 has a GRP value of 7 no change is made. All the others have initial GRP values of more than 7 so they get all values below
and down to 7 added into the records.

I've been fiddling for a while but it's not happening. Thanks very much.

Nov 12 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.