How to limit a copy to one record; not the entire table

2003

I want to copy one record of TableA which meets conditionX to TableB

PLAN A

Originally I thought that the following VBA code would work:
....
....
Set rs = dBs.OpenRecordset("StarsRev", dbOpenDynaset)
....

Do Until ABSAmt Abs(rs!Amt) Or rs.EOF
If ActualAmt + rs!Amt = 0 And (DovNmbr = rs!Dov_NUMBER Or DocNmbr = rs!Doc_NUMBER) Then
With appAccess.DoCmd
.RunCommand acCmdSelectRecord
.RunSQL "INSERT INTO STARSOffsets * SELECT * FROM STARSRev"
End With
End if
Loop

Unfortunately the code failed with the error: "SelectRecord is not available here"

PLAN B

When PLAN A failed, I altered the VBA code above to:

Do Until ABSAmt Abs(rs!Amt) Or rs.EOF
If ActualAmt + rs!Amt = 0 And (DovNmbr = rs!Dov_NUMBER Or DocNmbr = rs!Doc_NUMBER) Then
dBs.Execute "INSERT INTO STARSOffsets SELECT * FROM STARSRev"
End if
Loop

As I expected, all records in STARSRev are copied in to STARSOffsets using the VBA code in PLAN B

What VBA code can I use to limit the copy to the ONE record meeting the IF condition?

Any thoughts appreciated!

EagleOne

Try this:

Dim dbs As DAO.Database
Dim strSQL As String

Set dbs = CurrentDb

strSQL = "INSERT INTO StarsOffsets" & _
" SELECT * FROM StarsRev" & _
" WHERE Amt + " & ActualAmt & " = 0" & _
" AND (Dov_NUMBER = " & DovNmbr & _
" OR Doc_NUMBER = " & DocNmbr & ")"

dbs.Execute strSQL

If its only possible for one row in StarsRev to meet this condition this
should work, but if more than one row can meet the condition you can select
one arbitrarily by using the SELECT TOP 1 option.

Ken Sheridan
Stafford, England

" wrote:

2003

I want to copy one record of TableA which meets conditionX to TableB

PLAN A

Originally I thought that the following VBA code would work:
....
....
Set rs = dBs.OpenRecordset("StarsRev", dbOpenDynaset)
....

Do Until ABSAmt Abs(rs!Amt) Or rs.EOF
If ActualAmt + rs!Amt = 0 And (DovNmbr = rs!Dov_NUMBER Or DocNmbr = rs!Doc_NUMBER) Then
With appAccess.DoCmd
.RunCommand acCmdSelectRecord
.RunSQL "INSERT INTO STARSOffsets * SELECT * FROM STARSRev"
End With
End if
Loop

Unfortunately the code failed with the error: "SelectRecord is not available here"

PLAN B

When PLAN A failed, I altered the VBA code above to:

Do Until ABSAmt Abs(rs!Amt) Or rs.EOF
If ActualAmt + rs!Amt = 0 And (DovNmbr = rs!Dov_NUMBER Or DocNmbr = rs!Doc_NUMBER) Then
dBs.Execute "INSERT INTO STARSOffsets SELECT * FROM STARSRev"
End if
Loop

As I expected, all records in STARSRev are copied in to STARSOffsets using the VBA code in PLAN B

What VBA code can I use to limit the copy to the ONE record meeting the IF condition?

Any thoughts appreciated!

EagleOne

Thank you.

Unfortunately, there can be more than one "solution" (I am stuck with the data recorded)

"SELECT TOP 1" I have not used before. Does it possible mean (effectively or in fact) the current
record only? If true, this should work.

I have been attempting to "play" with rs.AbsolutePosition by attempting a WHERE clause that picks
only the record that has the "record number."

Bottom line, I want only the current record which meets the condition in the IF clause, as I toggle
through rs.

Thoughs?

Ken Sheridan wrote:

Try this:

Dim dbs As DAO.Database
Dim strSQL As String

Set dbs = CurrentDb

strSQL = "INSERT INTO StarsOffsets" & _
" SELECT * FROM StarsRev" & _
" WHERE Amt + " & ActualAmt & " = 0" & _
" AND (Dov_NUMBER = " & DovNmbr & _
" OR Doc_NUMBER = " & DocNmbr & ")"

dbs.Execute strSQL

If its only possible for one row in StarsRev to meet this condition this
should work, but if more than one row can meet the condition you can select
one arbitrarily by using the SELECT TOP 1 option.

Ken Sheridan
Stafford, England

" wrote:

2003

I want to copy one record of TableA which meets conditionX to TableB

PLAN A

Originally I thought that the following VBA code would work:
....
....
Set rs = dBs.OpenRecordset("StarsRev", dbOpenDynaset)
....

Do Until ABSAmt Abs(rs!Amt) Or rs.EOF
If ActualAmt + rs!Amt = 0 And (DovNmbr = rs!Dov_NUMBER Or DocNmbr = rs!Doc_NUMBER) Then
With appAccess.DoCmd
.RunCommand acCmdSelectRecord
.RunSQL "INSERT INTO STARSOffsets * SELECT * FROM STARSRev"
End With
End if
Loop

Unfortunately the code failed with the error: "SelectRecord is not available here"

PLAN B

When PLAN A failed, I altered the VBA code above to:

Do Until ABSAmt Abs(rs!Amt) Or rs.EOF
If ActualAmt + rs!Amt = 0 And (DovNmbr = rs!Dov_NUMBER Or DocNmbr = rs!Doc_NUMBER) Then
dBs.Execute "INSERT INTO STARSOffsets SELECT * FROM STARSRev"
End if
Loop

As I expected, all records in STARSRev are copied in to STARSOffsets using the VBA code in PLAN B

What VBA code can I use to limit the copy to the ONE record meeting the IF condition?

Any thoughts appreciated!

EagleOne

SELECT TOP n….. returns the first n rows from a query's result set, so TOP 1
returns the first row. Normally the query would have an ORDER BY clause to
sort the rows returned so the TOP rows would be first in the sort order, e.g.
you might want to return the three runners with the fastest times in a race:

SELECT TOP 3 Runner, RunTime
FROM Race
ORDER BY RunTime DESC;

Without an ORDER BY clause the 'first' row is an arbitrary one as tables are
sets, and concepts such as 'first' and 'last' are completely foreign to sets,
which have no intrinsic order.

As to whether it returns the 'current' record depends on what do you mean by
'current' record. When you say ' current record which meets the condition in
the IF clause, as I toggle through rs' this doesn't really make a lot of
sense. If many records can match the condition each of them will be the
'current record' at some time in the iteration through the recordset. So
what determines which of the many you want inserted into the other table?

If criteria can determine which record is the one you want as you iterate
through a recordset then the same criterion can determine which row a query
returns, so there is no point in using a recordset at all, just build the
WHERE clause of the 'append' query to return the row in question.

There is a more fundamental question of course. Why duplicate a row from
one table into another in the first place? Its an important principle of the
database relational model that each 'fact' is stored once and once only.

Ken Sheridan
Stafford, England

" wrote:

Thank you.

Unfortunately, there can be more than one "solution" (I am stuck with the data recorded)

"SELECT TOP 1" I have not used before. Does it possible mean (effectively or in fact) the current
record only? If true, this should work.

I have been attempting to "play" with rs.AbsolutePosition by attempting a WHERE clause that picks
only the record that has the "record number."

Bottom line, I want only the current record which meets the condition in the IF clause, as I toggle
through rs.

Thoughs?

Ken Sheridan wrote:

Try this:

Dim dbs As DAO.Database
Dim strSQL As String

Set dbs = CurrentDb

strSQL = "INSERT INTO StarsOffsets" & _
" SELECT * FROM StarsRev" & _
" WHERE Amt + " & ActualAmt & " = 0" & _
" AND (Dov_NUMBER = " & DovNmbr & _
" OR Doc_NUMBER = " & DocNmbr & ")"

dbs.Execute strSQL

If its only possible for one row in StarsRev to meet this condition this
should work, but if more than one row can meet the condition you can select
one arbitrarily by using the SELECT TOP 1 option.

Ken Sheridan
Stafford, England

" wrote:

2003

I want to copy one record of TableA which meets conditionX to TableB

PLAN A

Originally I thought that the following VBA code would work:
....
....
Set rs = dBs.OpenRecordset("StarsRev", dbOpenDynaset)
....

Do Until ABSAmt Abs(rs!Amt) Or rs.EOF
If ActualAmt + rs!Amt = 0 And (DovNmbr = rs!Dov_NUMBER Or DocNmbr = rs!Doc_NUMBER) Then
With appAccess.DoCmd
.RunCommand acCmdSelectRecord
.RunSQL "INSERT INTO STARSOffsets * SELECT * FROM STARSRev"
End With
End if
Loop

Unfortunately the code failed with the error: "SelectRecord is not available here"

PLAN B

When PLAN A failed, I altered the VBA code above to:

Do Until ABSAmt Abs(rs!Amt) Or rs.EOF
If ActualAmt + rs!Amt = 0 And (DovNmbr = rs!Dov_NUMBER Or DocNmbr = rs!Doc_NUMBER) Then
dBs.Execute "INSERT INTO STARSOffsets SELECT * FROM STARSRev"
End if
Loop

As I expected, all records in STARSRev are copied in to STARSOffsets using the VBA code in PLAN B

What VBA code can I use to limit the copy to the ONE record meeting the IF condition?

Any thoughts appreciated!

EagleOne

Since your 1st comment, I came to realize how significant that comment was/is. The last few months
represent my initial "trip" into the world of VBA Access programming. Therefore, issues that are
second-nature to many are not yet easily popping into my head.

You have quite an excellent track-record in the Access world. Thanks for sharing you expertise.

The reason for the copy is strictly due to the need to provide an "audit trail" documenting which
records were considered a match.

The audit-trail is mandated by the Sarbanes-Oxley law in the US.

Ken Sheridan wrote:

SELECT TOP n….. returns the first n rows from a query's result set, so TOP 1
returns the first row. Normally the query would have an ORDER BY clause to
sort the rows returned so the TOP rows would be first in the sort order, e.g.
you might want to return the three runners with the fastest times in a race:

SELECT TOP 3 Runner, RunTime
FROM Race
ORDER BY RunTime DESC;

Without an ORDER BY clause the 'first' row is an arbitrary one as tables are
sets, and concepts such as 'first' and 'last' are completely foreign to sets,
which have no intrinsic order.

As to whether it returns the 'current' record depends on what do you mean by
'current' record. When you say ' current record which meets the condition in
the IF clause, as I toggle through rs' this doesn't really make a lot of
sense. If many records can match the condition each of them will be the
'current record' at some time in the iteration through the recordset. So
what determines which of the many you want inserted into the other table?

If criteria can determine which record is the one you want as you iterate
through a recordset then the same criterion can determine which row a query
returns, so there is no point in using a recordset at all, just build the
WHERE clause of the 'append' query to return the row in question.

There is a more fundamental question of course. Why duplicate a row from
one table into another in the first place? Its an important principle of the
database relational model that each 'fact' is stored once and once only.

Ken Sheridan
Stafford, England

" wrote:

Thank you.

Unfortunately, there can be more than one "solution" (I am stuck with the data recorded)

"SELECT TOP 1" I have not used before. Does it possible mean (effectively or in fact) the current
record only? If true, this should work.

I have been attempting to "play" with rs.AbsolutePosition by attempting a WHERE clause that picks
only the record that has the "record number."

Bottom line, I want only the current record which meets the condition in the IF clause, as I toggle
through rs.

Thoughs?

Ken Sheridan wrote:

Try this:

Dim dbs As DAO.Database
Dim strSQL As String

Set dbs = CurrentDb

strSQL = "INSERT INTO StarsOffsets" & _
" SELECT * FROM StarsRev" & _
" WHERE Amt + " & ActualAmt & " = 0" & _
" AND (Dov_NUMBER = " & DovNmbr & _
" OR Doc_NUMBER = " & DocNmbr & ")"

dbs.Execute strSQL

If its only possible for one row in StarsRev to meet this condition this
should work, but if more than one row can meet the condition you can select
one arbitrarily by using the SELECT TOP 1 option.

Ken Sheridan
Stafford, England

" wrote:

2003

I want to copy one record of TableA which meets conditionX to TableB

PLAN A

Originally I thought that the following VBA code would work:
....
....
Set rs = dBs.OpenRecordset("StarsRev", dbOpenDynaset)
....

Do Until ABSAmt Abs(rs!Amt) Or rs.EOF
If ActualAmt + rs!Amt = 0 And (DovNmbr = rs!Dov_NUMBER Or DocNmbr = rs!Doc_NUMBER) Then
With appAccess.DoCmd
.RunCommand acCmdSelectRecord
.RunSQL "INSERT INTO STARSOffsets * SELECT * FROM STARSRev"
End With
End if
Loop

Unfortunately the code failed with the error: "SelectRecord is not available here"

PLAN B

When PLAN A failed, I altered the VBA code above to:

Do Until ABSAmt Abs(rs!Amt) Or rs.EOF
If ActualAmt + rs!Amt = 0 And (DovNmbr = rs!Dov_NUMBER Or DocNmbr = rs!Doc_NUMBER) Then
dBs.Execute "INSERT INTO STARSOffsets SELECT * FROM STARSRev"
End if
Loop

As I expected, all records in STARSRev are copied in to STARSOffsets using the VBA code in PLAN B

What VBA code can I use to limit the copy to the ONE record meeting the IF condition?

Any thoughts appreciated!

EagleOne

Remember, though, that you have to be careful with SELECT TOP n. TOP n does
NOT necessarily only return the first n rows.

In your example, should the top runner have a RunTime of 1.03, two more
runners have times of 1.05 and three more runners have times of 1.06, your
query would actually return 6 rows, not 3.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"Ken Sheridan" wrote in message
...
SELECT TOP n... returns the first n rows from a query's result set, so
TOP 1
returns the first row. Normally the query would have an ORDER BY clause
to
sort the rows returned so the TOP rows would be first in the sort order,
e.g.
you might want to return the three runners with the fastest times in a
race:

SELECT TOP 3 Runner, RunTime
FROM Race
ORDER BY RunTime DESC;

Well I learned more concepts. I had no idea the conceptual depth of TOP and/or "top runner."

My interpretation, correct if appropriate, "TOP" is a metric that is an indication of a measurement
of the (run) time it took to "find" a record?

Also, that TOP 1 is not the TOP one-record-only but all records with a run time between 1 and 2 ?

EagleOne


"Douglas J. Steele" wrote:

Remember, though, that you have to be careful with SELECT TOP n. TOP n does
NOT necessarily only return the first n rows.

In your example, should the top runner have a RunTime of 1.03, two more
runners have times of 1.05 and three more runners have times of 1.06, your
query would actually return 6 rows, not 3.

I used "runner" because that's what Ken's example was. (presumably he was
storing the results of races in a table named Runners)

"Runner" has no relevance to Access, and no, "TOP" has nothing to do with
how long it took to find a record.

From the Help file:

TOP n [PERCENT]

Returns a certain number of records that fall at the top or the bottom of a
range specified by an ORDER BY clause. Suppose you want the names of the top
25 students from the class of 1994:

SELECT TOP 25
FirstName, LastName
FROM Students
WHERE GraduationYear = 1994
ORDER BY GradePointAverage DESC;

If you don't include the ORDER BY clause, the query will return an arbitrary
set of 25 records from the Students table that satisfy the WHERE clause.The
TOP predicate doesn't choose between equal values. In the preceding example,
if the twenty-fifth and twenty-sixth highest grade point averages are the
same, the query will return 26 records.

You can also use the PERCENT reserved word to return a certain percentage of
records that fall at the top or the bottom of a range specified by an ORDER
BY clause. Suppose that, instead of the top 25 students, you want the bottom
10 percent of the class:

SELECT TOP 10 PERCENT
FirstName, LastName
FROM Students
WHERE GraduationYear = 1994
ORDER BY GradePointAverage ASC;

The ASC predicate specifies a return of bottom values. The value that
follows TOP must be an unsigned Integer.

TOP doesn't affect whether or not the query is updatable.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


wrote in message
...
Well I learned more concepts. I had no idea the conceptual depth of TOP
and/or "top runner."

My interpretation, correct if appropriate, "TOP" is a metric that is an
indication of a measurement
of the (run) time it took to "find" a record?

Also, that TOP 1 is not the TOP one-record-only but all records with a run
time between 1 and 2 ?

EagleOne


"Douglas J. Steele" wrote:

Remember, though, that you have to be careful with SELECT TOP n. TOP n
does
NOT necessarily only return the first n rows.

In your example, should the top runner have a RunTime of 1.03, two more
runners have times of 1.05 and three more runners have times of 1.06, your
query would actually return 6 rows, not 3.

Thanks for the laugh and the reminder that no matter how well we think we've
explained a concept, it can still be misinterpreted.

Ken's example of Runtimes is "how much time passed from start of race to when
runner crossed the finish line" and is stored in the column name RunTime.
The query example tries to answer the question of "who are the 3 slowest
runners?" by finding the 3 lowest times to complete the race. (If Ken had
sorted the without the "desc" he would have been asking "who are the 3
fastest runners?") Has nothing to do with how long it takes to run the query
and find those 3 slowest runners. Doug was pointing out that if there are
ties, the query will return more than 3 rows.

The top 1 returns all rows matching the maximum value - if the sort is
descending. If it's only 1 row in the table with the maximum value, that
will result in 1 row returned by the query, but if there are 82 rows with the
same maximum value the top 1 query will return those 82 rows.

Chris
Microsoft MVP


wrote:

My interpretation, correct if appropriate, "TOP" is a metric that is an indication of a measurement
of the (run) time it took to "find" a record?

Also, that TOP 1 is not the TOP one-record-only but all records with a run time between 1 and 2 ?

EagleOne

Remember, though, that you have to be careful with SELECT TOP n. TOP n does
NOT necessarily only return the first n rows.

In your example, should the top runner have a RunTime of 1.03, two more
runners have times of 1.05 and three more runners have times of 1.06, your
query would actually return 6 rows, not 3.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200809/1

Sorry, I meant "The query example tries to answer the question of 'who are
the 3 slowest runners?' by finding the 3 highest times to complete the race".

Chris
Microsoft MVP


Chris O'C wrote:
Thanks for the laugh and the reminder that no matter how well we think we've
explained a concept, it can still be misinterpreted.

Ken's example of Runtimes is "how much time passed from start of race to when
runner crossed the finish line" and is stored in the column name RunTime.
The query example tries to answer the question of "who are the 3 slowest
runners?" by finding the 3 lowest times to complete the race. (If Ken had
sorted the without the "desc" he would have been asking "who are the 3
fastest runners?") Has nothing to do with how long it takes to run the query
and find those 3 slowest runners. Doug was pointing out that if there are
ties, the query will return more than 3 rows.

The top 1 returns all rows matching the maximum value - if the sort is
descending. If it's only 1 row in the table with the maximum value, that
will result in 1 row returned by the query, but if there are 82 rows with the
same maximum value the top 1 query will return those 82 rows.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200809/1