 |
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA. |
Welcome to the p2p.wrox.com Forums.
You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
|
|
|

February 17th, 2004, 05:18 PM
|
Authorized User
|
|
Join Date: Feb 2004
Posts: 12
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
QueryDef and/or Recordset?
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--
|

February 18th, 2004, 04:48 AM
|
 |
Wrox Author
|
|
Join Date: Jun 2003
Posts: 3,074
Thanks: 1
Thanked 38 Times in 37 Posts
|
|
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
Code:
SELECT DISTINCT tblFTP.[Flight No]FROM tblFTP ORDER BY tblFTP.[Flight No]
2) You might not need two append queries, I think you can just have
Code:
SELECT tblFTP.RecordNo, tblFTP.[Flight No], tblFTP.[Run No], tblFTP.[TIS No], tblFTP.[Date Flown]FROM tblFTP
WHERE (tblFTP.[Flight No] = ?) AND ((tblFTP.[Run No] Like "D*") OR ((tblFTP.[Run No] Like "A*")) ORDER BY tblFTP.RecordNo, tblFTP.[Flight No], tblFTP.[Run No] DESC, tblFTP.[TIS No];
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
|

February 18th, 2004, 04:34 PM
|
Authorized User
|
|
Join Date: Feb 2004
Posts: 12
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
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.
|

February 18th, 2004, 04:52 PM
|
Authorized User
|
|
Join Date: Feb 2004
Posts: 12
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
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
|
|
 |