Automatically Mark First Ten Records? 2

[Hakala]

Hi, Tek-Tips!

I have a query which contains a Yes/No field. The sort is by a unique record identifier text field, and I'd like the query to automatically default the Yes/No field to Yes for the first ten records.

The user would then need to determine visually if more (or less) than ten records should be marked.

I have not had any luck in doing this. Is it even possible?

 

[Melagan]

As a starting point --
You'll probably want to have a look at the TOP predicate for JetSQL. What field are you sorting by to get your top 10? It would be something like this:
(sql view)

SELECT TOP 10 t.YourFieldX, t.YourYesNo
FROM tblYourTable as t
WHERE ...
ORDER BY t.YourFieldX

Where "YourFieldX" is the field you're basing your "TOP 10" off of, and "YourYesNo" is your yes/no field.

The above could be used as a sub-query in an UPDATE query, as such:

UPDATE tblYourTable as t SET t.YourYesNo = "Yes"

WHERE t.YourFieldX IN (SELECT TOP 10 a.YourFieldX FROM tblYourTable as a ORDER BY a.YourFieldX)

~Melagan
______
"It's never too late to become what you might have been.

 

[Hakala]

Thanks, Melagan!

I can almost understand what you've said.

My original query pulls data with criteria already, so I'm not sure how to incorporate this code. I can't find any help in Access or VBA which I can understand on the UPDATE command. I've tried to modify your code to work, but probably made a mash of it and I (of course) get errors (I'm also using your code from my other question you answered in order to get two recordsets):

Private Sub cmdChooseRENS_Click()

Dim rstLookup As Recordset, SQLText
Dim rstLookup10 As Recordset, SQLText10

SQLText = "SELECT Move, CustomerNumber, CustomerName, EffectiveDate, Batch, TOW, GroupNumber " _
& "FROM tblDataImport " _
& "WHERE isnull(tblDataImport.Batch) " _
& "AND TOW='RENS' "

Set rstLookup = CurrentDb.OpenRecordset(SQLText)

SQLText10 = "SELECT TOP 10 CustomerNumber, Move " _
& "From tblDataImport " _
& "Order by tblDataImport.CustomerNumber "

Set rstLookup10 = CurrentDb.OpenRecordset(SQLText10)

UPDATE tblDataImport SET tblDataImport.Move = "Yes"

WHERE tblDataImport.CustomerNumber IN (SQLTEXT10)

End Sub

I've also tried it this way:

'Update tblDataImport "Set tblDataImport.Move = 'Yes' " _
' & "WHERE tblDataImport.CustomerNumber IN (SQLTEXT10) "

End Sub

The first part of the code seems to be working.

 

[Melagan]

The trouble here is that you're trying to run standard SQL code in a VB environment. In this exersize, you probably don't need VBA at all unless you want your user to run the query by clicking a button on a form. Even then, you'd probably just have the button open a saved query as opposed to hard-coding the SQL behind your form. All that aside, here is what you should do:

Create a new query
Go to SQL view

Try pasting this in:

UPDATE tblDataImport as t SET t.Move = "Yes"

WHERE IsNull(t.Batch)[COLOR=red]=TRUE[/color]
AND t.TOW = 'RENS'
AND t.CustomerNumber IN 
(SELECT TOP 10 CustomerNumber FROM tblDataImport ORDER BY tblDataImport.CustomerNumber)

Again, this example doesn't give you exactly what you need in your original post - it just automatically updates your MOVE field to "Yes" for the TOP 10 values of CustomerNumber.


~Melagan
______
"It's never too late to become what you might have been.

 

[Hakala]

Ideally, I would love for the user to click a button and update 10 records, but it won't work for what they need; they have to visually make sure the break between Yes and No is where it should be.

I put your code into a query and ran it, but it updates 0 records, and I know there are over 3000 in that table matching the criteria.

At least it didn't error!

 

[PHV]

UPDATE tblDataImport
SET Move = True
WHERE Batch IS NULL AND TOW = 'RENS'
AND CustomerNumber IN (SELECT TOP 10 CustomerNumber FROM tblDataImport WHERE Batch IS NULL AND TOW = 'RENS' ORDER BY 1)


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Hakala]

It works!

You were right; I was trying to be too complicated. Ultimately, I ended up with a button on the form that runs two queries in succession:

Private Sub cmdChooseRENS_Click()
DoCmd.SetWarnings (WarningsOff)
DoCmd.OpenQuery "qupdImportBatchRENSTop10"

UPDATE tblDataImport SET tblDataImport.Move = True
WHERE (((tblDataImport.Batch) Is Null) AND ((tblDataImport.TOW)='RENS') AND ((tblDataImport.CustomerNumber) In (SELECT TOP 10 CustomerNumber FROM tblDataImport WHERE Batch IS NULL AND TOW = "RENS" ORDER BY 1)));

DoCmd.OpenQuery "qryBatchingRENS"

SELECT tblDataImport.Move, tblDataImport.CustomerNumber, tblDataImport.CustomerName, tblDataImport.EffectiveDate, tblDataImport.Batch, tblDataImport.TOW, tblDataImport.GroupNumber
FROM tblDataImport
WHERE (((tblDataImport.Batch) Is Null) AND ((tblDataImport.TOW)="RENS"))
ORDER BY tblDataImport.CustomerNumber, tblDataImport.EffectiveDate;

cmdImportRENS.Visible = True
DoCmd.SetWarnings (WarningsOn)
End Sub

The first one checks the Move box on the top ten records, and the second one opens the data in datasheet mode so the user can see if the checks are in the right place. It works like a champ now and I'm the hit of the office. You guys rock!