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

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, ???

My second question:

in this example you provide here:

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

do you want to save all of the above (1 2, 1 9, 1 8) into table B?

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.

I have just realized that I was mistakenly thinking that the values were all in the DB-Key field, where in fact I think you have two separate values one in the DB-Key field and the other value is in the "type" field.

For this reason I think I need more information....

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 :D

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.