I need to append data from one table to another table using the results from a group query as the parameter in the append query. The group query once was part of a combo box.

The purpose of this query is to resort data by flight number. The only problem is the criteria. The sort is "D" before "A".
Therefore, I need two append queries for each group.

I need to run this process via code instead of by form. The pulldown box is as follows: SELECT tblFTP.[Flight No]FROM tblFTP GROUP BY tblFTP.[Flight No] ORDER BY tblFTP.[Flight No];

The append queries are as follows: INSERT INTO tblB (the other table) ( RecordNo, [Flight No], [Run No], [TIS No], [Date Flown])
SELECT tblFTP.RecordNo, tblFTP.[Flight No], tblFTP.[Run No], tblFTP.[TIS No], tblFTP.[Date Flown]FROM tblFTP
WHERE (((tblFTP.[Flight No])=[Forms]![frmAppenFTP]![Combo0]) AND ((tblFTP.[Run No])[u] Like "D*"))</u>
ORDER BY tblFTP.RecordNo, tblFTP.[Flight No], tblFTP.[Run No], tblFTP.[TIS No];

The second append query is the same but its criteria is [u]Like A*"</u>

I do not wish to use the combo box to select the groups to append to the other table. I would like to append each group automatically clicking a command button, looping thru the group or Combo box, appending data until EOF.

__________________
In matters of style, swim with the current. In matters of principle, stand like a rock. Thomas Jefferson

-------\\\\\\|///
--------(oo)
--oOOo-(_)-oOOo--

You didn't say which version of Access you are using but here are my suggestions:
1) The query you use to select the [Flight No] doesn't need a group by if you can use 2) You might not need two append queries, I think you can just have as your sub query with ? to be replaced as below.
3) Instead of the combo box open a recordset with the query in (1) and loop through the rows returned. For each one use the replace function to insert the [Flight No] into the query in (2) and execute it. Alternatively set the query in (2) as a stored query, a querydef, with a parameter for the [Flight No] and execute it passing the Flight No as its parameter.



--

Joe

Thanks Joe,

I am using Access 2002. I am trying to be an expert in development, but it seems I'm not. This is what I have so far,
Sub sResetFTP()
    Dim rstFTPNumbers As Recordset
    Dim defFTPNumber As QueryDef
    Dim sSql As String
    Dim strcut As String
    Dim strFlight As String
    strcut = "appFTPD-TableB"
    Dim db As Object
    Set db = CurrentDb()
    Set rstFTPNumbers = db.OpenRecordset("SELECT DISTINCT tblFTP.[Flight No]From tblFTP ORDER BY tblFTP.[Flight No];")
    Set defFTPNumber = db.QueryDefs(strcut)
    rstFTPNumbers.MoveFirst
    Do Until rstFTPNumbers.EOF
    strFlight = rstFTPNumbers![Flight No]
    'DoCmd.OpenQuery "appFTPD-TableB"
    sSql = "INSERT INTO tblB ( RecordNo, [Flight No], [Run No], [Emitter/WRPR No], [TIS No], Site, [Date Flown], ***SELECT tblFTP.RecordNo, tblFTP.[Flight No], tblFTP.[Run No], tblFTP.[Emitter/WRPR No]tblFTP.[TIS No], tblFTP.Site, tblFTP.[Date Flown], *** where (((rstFTPNumbers![Flight No]) And (tblFTP.[Run No]) Like D*)ORDER BY tblFTP.RecordNo, tblFTP.[Flight No], tblFTP.[Run No], tblFTP.[Emitter/WRPR No], tblFTP.[TIS No];"
    Beep
    rstFTPNumbers.MoveNext
    Loop
    End Sub
For sure, I am looping thru my recordset but the action is not happening.

One more thing Joe,

I need to append these records to [u]tblB</u> via recordset by flight number; with the D* RunNo first for each group of flights then the A*
Then the next group. I am sorting test results for each flight....

Sub sResetFTP()
    Dim rstFTPNumbers As Recordset
    Dim defFTPNumber As QueryDef
    Dim sSql As String
    Dim strcut As String
    Dim strFlight As String
    strcut = "appFTPD-TableB"

(your suggestion)
Where do I use it or how

    '"SELECT tblFTP.RecordNo, tblFTP.[Flight No], tblFTP.[Run No], tblFTP.[Emitter/WRPR No]tblFTP.[TIS No], tblFTP.Site, tblFTP.[Date Flown], From tblFTP where (((rstFTPNumbers![Flight No]=?) And (tblFTP.[Run No]) Like D*)OR (tblFTP.[Run No]) Like A*)ORDER BY tblFTP.RecordNo, tblFTP.[Flight No], tblFTP.[Run No], tblFTP.[Emitter/WRPR No], tblFTP.[TIS No];" Dim db As Object

    Set db = CurrentDb()
    Set rstFTPNumbers = db.OpenRecordset("SELECT DISTINCT tblFTP.[Flight No]From tblFTP ORDER BY tblFTP.[Flight No];")
    Set defFTPNumber = db.QueryDefs(strcut)
    rstFTPNumbers.MoveFirst
    Do Until rstFTPNumbers.EOF
    strFlight = rstFTPNumbers![Flight No]

    'DoCmd.OpenQuery "appFTPD-TableB"

(My append code)
  'sSql = "INSERT INTO tblB ( RecordNo, [Flight No], [Run No], [Emitter/WRPR No], [TIS No], Site, [Date Flown], SELECT tblFTP.RecordNo, tblFTP.[Flight No], tblFTP.[Run No], tblFTP.[Emitter/WRPR No]tblFTP.[TIS No], tblFTP.Site, tblFTP.[Date Flown], From tblFTP where (((rstFTPNumbers![Flight No]) And (tblFTP.[Run No]) Like D*)ORDER BY tblFTP.RecordNo, tblFTP.[Flight No], tblFTP.[Run No], tblFTP.[Emitter/WRPR No], tblFTP.[TIS No];"
    Beep
    rstFTPNumbers.MoveNext
    Loop

    End Sub