Moving records to tables based on criteria

[knowledge76]

HI Friends;

I am working on a problem for the last two day and till now I have no solution.

I have a table with a field DB-Key and it comes more than once in a table and another fields TYPE.

Now what I am trying to do is to go through each DB-KEY and when for a DB-Key the TYpe is 6 or 7 then the DB-Key should be saved in a table say A otherwise it should be saved in Table B.

Example of first possibitlities
DB-Key Type
1 5
1 4
1 6

The above DB-Key should be saved once in Table A, because for this DB-Key TYPE is 6 in the last record.


Example of second possibitlities
DB-Key Type
1 2
1 9
1 8



The above DB-Key should be saved once in Table B, because for this DB-Key TYPE 6 or 7is not found in the records.

I tried this with Access Queries but it is not working. Could you please suggest me how to implement this with VBA?
Thanks

 

[knowledge76]

I'm not quite with you on any of this, my first question is:::

in this example you provide here:

Example of first possibitlities
DB-Key Type
1 5
1 4
1 6

do you just want to save the last item 1 6 to Table A and not save the items 1 5, 1 4, ???

I want to add all records into table A.

 

[knowledge76]

I have just two fields as mentioned in the table. i.e. DB-Key and Type
Truly speaking I have no more information to share because there are no more information to share

 

[Mutdogus]

I think I may have an understanding.
basically you want to loop thru your table 3 records at a time, if any of them have a type of 6 or 7, add all the records. If not, move on to the next set of 3.

Am I understanding this correctly?

Dim rs As ADO.Recordset
'Assume you already have everything set up, just monkey code here

Set rs = New ADO.Recordset


'Assuming you have a set of 3 records you need to look at and add based on a 6 or 7
'being present
Do Until rs.EOF

fldType = rs.Fields("Type").Value

If fldType = 6 Or fldType = 7 Then
'Need to move 3 records previous
rs.MovePrevious
rs.MovePrevious
rs.MovePrevious
a = 0
For a = 0 To 2
'Now need to add the 3 records
DBKey = rs.Fields("DB-Key").Value
fldType = rs.Fields("DB-Key").Value

'Create your own SQL Statement to add them

DoCmd.RunSQL strSQL
rs.MoveNext
a = a + 1
Next a
End If


Loop

Just theory here. You still need to loop thru your entire recordset 3 at a time.

Am I on base here or still out in left field?

I still don't understand why you need to save them to different tables. You could just add another field and update the field with a value of 1 or 2 (1 being table a, 2 being table b).

I'm with Gizmo. We need more information as to why and what this is for.